jueves, julio 31, 2008

PostgreSQL vs Firebird


PostgreSQL vs Firebird feature comparison (for Enterprise Use)

Feature PostgreSQL 8.2.x
Firebird 2.0.x
MVCC Yes Yes
Row level Locking Available Yes Yes
Max Database Size Unlimited* Unlimited*
Max table Size 32 TB ~32 TB
Max Row Size 1.6 TB 64 KB
Max Field Size 1 GB
Max size for all types including blobs
Firebird seems to be a better choice if you need huge blob support.
Depends on Type
(Varchar max size is 32k, Max Blob Size is 32GB)
Max Rows per Table Unlimited* > 16 Billion
Max Columns Per Table 50 - 1600 depending on column types Depends on data types used.
Max Indexes Per Table Unlimited* 65,535
Multi Threaded Architecture Available? No Yes (super server)
Ability to re-order table columns without re-creating from a temp table. No Yes
Stores Transaction Information in same file as data No Yes
Auto Increment Columns Yes
(serial type that uses sequences)
(must use a generator and a trigger)
True Boolean column type Yes No
Table Inheritance Yes No
Domains Yes Yes
Table Partitioning Yes (basic) No
Updateable Views Yes (via rules system) Yes
Event/Notification System Yes Yes
Temporary Tables Yes No
Rich Built in Functions Yes No
Multi Lang Stored Procedures Yes (PLPGSQL,PLPerl,PlJava etc) No
Compiled External Function (UDF) Support Yes (C,C++, Delphi possible but C headers would have to be ported) Yes (C,C++,Delphi)
Exception handling in stored procedures Yes Yes
2 Phased Commit Yes Yes
Native SSL support Yes No
Multiple authentication methods
(i.e. LDAP)
Yes No
Compound Indexes Yes Yes
Unique Indexes Yes Yes
Partial Indexes Yes Yes
Functional Indexes Yes Yes
Multiple Index Storage Types Yes (btree,hash etc) No
Point in Time Recovery Yes No
Schema Support Yes No
Strongly conforms to ANSI-SQL 92/99 Yes Yes
Limit/Offset support Yes Yes (FIRST/SKIP)
Create user defined types Yes No
Create user defined operators Yes No
Create user defined Aggregates Yes No
Log Shipping Yes No
Write ahead logging
(Important for Point In Time Recovery and Log Shipping)
Yes No
Tablespaces Yes No
Save Points Yes Yes
Open Source Async Replication Yes
(Slony )
(Commercial solutions available. Database shadowing is also present.)
Online/Hot Backups Yes Yes
File System based backups possible Yes
(Postmaster must be stopped)

Require backup/restore to compact (not just reuse space, but shrink the physical size of the DB) No Yes
Fully ACID Compliant Yes Yes
Native Win32 Port Yes
(only runs on NTFS files systems)
Text/Memo field type
(Having a type of TEXT or MEMO aids in porting from other databases and in maintaining a abstraction layer)

(Can store text via BLOB Type 1 While FB can store text it is not referenced in DDL as TEXT or MEMO rather BLOB subtype 1. Can also use a domain to alias blob subtype 1 as TEXT or MEMO. Domain alias must be created by user.)

BLOB support Yes
(limited to the max field size of 1 GB)
(Can be up to 32GB)
UTF8 support Yes Yes
Define charactersets/collations per database (default) Yes
(PostgreSQL can also define a characterset for the entire database cluster during the initdb process)
Define charactersets and collations on a per column level No Yes
Foreign Keys Yes Yes
Check Constraints Yes Yes
Unique Constraints Yes Yes
Not Null Constraints Yes Yes
Multiple Transaction Isolation levels Yes Yes
Fully relational System Catalogs Yes Yes
Information Schema Yes No (no schema support)
Native GIS support via GIST or other native means Yes (PostGIS ) No
Open Source Full Text Search (Official part of Project) Yes No
Use POSIX Regular Expressions in queries Yes No
Database Monitoring Yes
(Supports among other things statement logging at server via config setting. Aids in debugging of client applications)
Ability to query databases on other servers local or remote. Yes (Dblink ) No
Ability to query foreign databases (MS SQL Server, Oracle etc) Yes (DBI-Link ,DBLink-TDS ) No
Read Only Databases No Yes
Regular Version Updates Yes
Major release once per year with frequent minor updates.
(Often several years between major versions) Minor updates are more frequent. Project is active.

No hay comentarios: