Tuesday, July 22, 2008

PostgreSQL - Firebird comparison

Google led me to a comparison sheet about PostgreSQL and Firebird by AMSoftwareDesign, as it looks a bit outdated I decided to add some infos about the latest Firebird release, see it in action:

PostgreSQL vs Firebird feature comparison
Feature PostgreSQL 8.2.x Firebird 2.0.x Firebird 2.5 Alpha
MVCC Yes Yes Yes
Row level Locking
Yes Yes Yes
Database Size
Unlimited* Unlimited* Unlimited*
table Size
32 TB ~32 TB ~32 TB
Row Size
1.6 TB 64 KB 65 KB
Rows per Table
Unlimited* > 16 Billion > 16 Billion
Columns Per Table
50 - 1600 depending on column types Depends on data types used. Depends on data types used.
Indexes Per Table
Unlimited* 65,535 66,535
SQL statement size
Unlimited* 64kb 64kb
Multi Threaded
Architecture Available?
No (see "Features we do NOT want" in the TODO list) Yes (super server) and No (classic server)
Yes (super server), architectures will be unified with full SMP support in 3.0
Ability to re-order
table columns
without re-creating
No Yes Yes
Transaction Information
in same file as data
No Yes Yes
Auto Increment
Yes (serial type that uses sequences) Yes (must use a generator and a trigger) Yes (must use a generator and a trigger)
True Boolean
column type
Yes No No
Table Inheritance Yes No No
Domains Yes Yes Yes
Table Partitioning Yes (basic) No No
Updateable Views No (workaround available via rules system) Yes Yes
Event/Notification System Yes Yes Yes
Temporary Tables Yes No Yes
Rich Built in Functions Yes Yes
Multi Lang Stored Procedures Yes (PLPGSQL,PLPerl,PlJava etc) No No (support for Java stored procedures is scheduled for 3.0)
Compiled External Function
(UDF) Support
Yes Yes
Exception handling
in stored procedures
Yes Yes Yes
2 Phased Commit Yes Yes Yes
Native SSL support Yes No No
Multiple auth methods
(i.e. LDAP)
Yes No Yes (database auth or integrated windows auth)
Compound Indexes Yes Yes Yes
Unique Indexes Yes Yes Yes
Partial Indexes Yes Yes Yes
Functional Indexes Yes Yes Yes
Multiple Index Storage Types Yes (btree,hash etc) No No
Point in Time Recovery Yes No No
Schema Support Yes No No
Conforms to ANSI-SQL 92/99 Yes Yes Yes
Limit/Offset support Yes Yes Yes
Create user defined types Yes No No
Create user defined operators Yes No No
Create user defined Aggregates Yes No No
Log Shipping (for Point In Time Recovery and Log Shipping) Partial No No
Write ahead logging Yes No No
Tablespaces Yes No No
Save Points Yes Yes Yes
Open Source
Async Replication
Yes (Slony ) No (Commercial solutions available. Database shadowing is also present.) No (Commercial solutions available. Database shadowing is also present.)
Online/Hot Backups Yes Yes Yes
File System based
backups possible
Yes (Postmaster must be stopped) Yes Yes
Require backup/restore to compact No Yes Yes
Fully ACID Compliant Yes Yes Yes
Native Win32 Port Yes Yes Yes
Text/Memo field type Yes Yes
BLOB support Yes (limited to the max field size of 1 GB) Yes (Can be up to 32GB) Yes (Can be up to 32GB)
UTF8 support Yes Yes Yes
Define charactersets/collations per database (default) Partial (PostgreSQL can also define a characterset for the entire database cluster during the initdb process, it is not recommend to run databases in different
encodings than the encoding chosen at initdb time
Yes Yes
Define charactersets and collations on a per column level No Yes Yes
Foreign Keys Yes Yes Yes
Check Constraints Yes Yes Yes
Unique Constraints Yes Yes Yes
Not Null Constraints Yes Yes Yes
Multiple Transaction Isolation levels Yes Yes Yes
Fully relational System Catalogs Yes Yes Yes
Information Schema Yes No (no schema support) but equivalent system tables No (no schema support) but equivalent system tables
Native GIS support via GIST or other native means Yes (PostGIS ) No No
Open Source Full Text Search Yes No No
Use POSIX Regular Expressions in queries Yes No Yes, through standard predicate SIMILAR TO
Database Monitoring Yes No Yes, through system tables and triggers
Ability to query databases on other servers local or remote. Yes (Dblink ) No Yes (through EXECUTE STATEMENT)
Ability to query other databases Yes (DBI-Link ,DBLink-TDS ) No No
Read Only Databases No Yes Yes
Regular Version Updates Yes Yes Yes

* Unlimited but still restricted by system resources.


Bithunter said...

There are a couple of free solutions for replication in Firebird, and some OpenSource also:

Replicador Firebird (http://replicadorfirebird.ich.pro.br)
Replicador-BR (http://www.replicadorbr.com.br)
FBReplicator (http://www.meta.com.au)
ADAReplicador (http://edv4ld0.homeip.net:81/t/adareplicador-1.3.zip)

Jobin said...

Truly appreciate the effort to do a fair comparision.

Edvaldo said...

AdaReplicador 1.3:

TheMouse said...

I'd be interested to have an up-to-date re-review of the features of these two programs.