Postgres vs Firebird

Question

I'm looking to use either Firebird or Postgres in my next development project ... largely because both are available under a BSD-like license.

I found a great comparison of the two database at http://www.amsoftwaredesign.com/pg_vs_fb

But this comparison is a good 2+ years old and both databases have come a long ways since.

Does anyone mind updating the comparison table to be relevant for the current versions of both Firebird and Postgres ... or have a link to a site that does a good recent comparison between the two database?

Answer 1

One more recent comparison (2008):

• http://nikolajlindberg.blogspot.com/2008/03/firebird-vs-postgresql.html

Answer 2

I can comment on Firebird. The following changed since the first comparison:

Multi Threaded Architecture Available? Yes (super server)

Actually, Classic Server is the preferred server type for scalability. Starting from Firebird 2.5 (currently in beta) there is also an architecture called SuperClassic. I wrote a small post on the differences: SuperServer, ClassicServer or SuperClassic?

Stores Transaction Information in same file as data? Yes (in red)

I don't see how this could be a problem to be written in red. Firebird uses careful writes to ensure the database structure is always consistent.

Temporary Tables? No

Available in Firebird 2.1. Release Notes

Rich Built in Functions? No

Firebird 2.1 also improved this. Release Notes

Multiple authentication methods(i.e. LDAP) No

While there is no LDAP support, Firebird 2.1 allows Windows authentication. Release Notes

Create user defined types? No

I'm not sure what the author means here, but Firebird has had domains forever.

Require backup/restore to compact? Yes (in red)

Arguable. Database space is reused. I find it hard to imagine the need to shrink a database in production.

Use POSIX Regular Expressions in queries? No

Firebird 2.5 (beta) supports regular expressions. Release Notes

Database Monitoring? No

Yes, Firebird 2.1 supports database monitoring via system tables. Firebird 2.5 adds support to trace facilities.
I know at least one excellent database monitoring tool for Firebird. ;)

Ability to query databases on other servers local or remote. No

Firebird 2.5 (beta) can query remote databases. Release Notes

Answer 3

AFAIK the each No in the Postgres column of the page you cited is still a No, although read-only databases can be effectively had by granting only select permissions on all tables in a database. However, there are many new features since 8.2. Some from 8.4:

1. Windowing Functions
2. Common Table Expressions and Recursive Queries
3. Default and variadic parameters for functions
4. Parallel Restore
5. Column Permissions
6. Per-database locale settings

...and some from 8.3:

1. Full text search is integrated into the core database system
2. Support for the SQL/XML standard, including new operators and an XML data type
3. Enumerated data types (ENUM)
4. Arrays of composite types
5. Universally Unique Identifier (UUID) data type
6. Add control over whether NULLs sort first or last
7. Updatable cursors
8. Server configuration parameters can now be set on a per-function basis
9. User-defined types can now have type modifiers

...whether Firebird has these, I don't know.

Answer 4

File System based backups possible
Yes(Postmaster must be stopped)

is not true. since 8.1 (if i remember correctly) it's possible to activate WAL archiving and then creating filesystem based backups without shutting down anything. Look for PITR (point-in-time-recovery) in the docs for more information

Answer 5

I've been working with Firebird for a long time and recommend to NOT use it in any way. They have many fundamental problems that Postgres does not have. Not to say many, just say they don't still have 'alter view' command. To alter a view you must drop and recreate it and all views depending on it. This is one of many misfeatures of FB. It's not correct to compare these DB Engines at all. I've had many cases when Firebird database was corrupted even without power loss, with usual workloads, and so on. If you still deciding, don't use Firebird! If you already chose FB, you will remember my words some time later :)

Answer 6

One important thing is deployment:

• Firebird doesn't perform well with ext3 on Linux, you should use XFS or something similar.
• As far as I know, PostgreSQL still doesn't run on Windows servers.

Answer 7

For those who are using Firebird, I would suggest reading the Firebird 2.1 Release Notes to understand a database corruption issue wrt to older Firebird versions running on Linux.

In short it has to do with a bug in Linux. For older Firebirds, the workaround is to mount the affected filesystem with the sync option. The problem is fixed in FB2.1.

Answer 8

You have to choose technology like RDBMS not (only) on comparing list of features but on answering questions like these:

• does it reliable enough for your needs?
• does it suit all your needs?
• what are perspectives/support/developmet/documentation?

For me FB is not a good choice. I can point two stories:

• I wrote constraint on table. Users add some strings to table. After a while user is trying to add one more string, but FB complains on constraint. What's wrong? This new string is surely under constraint. Problem is in a last string that was added before it! That string was added some time ago, it was wrong, and so far it is in DB, and who knows how many wrong data are now in DB. Very bad. FB implements constraints in a wrong manner.
• I wrote a Stored Procedure (SP). FB stored it in DB, FB can run it, and it works - select data as expected. No one warning. Then backup is done without errors. And only on restore FB complains that something is wrong with SP and ... what ... you have no restorable backup copy now.

I can point two or three more but these are enough for me to say: no, FB is not my choice, it's not RDBMS, it's a toy.

And they have bad documentation. In fact there is no actual references with each release. When people ask in forums: what is a full list of functions in release XX? The standard answer is: take a list from last official Interbase release and add (remove) functions from all later releases's "release notes".

And they have strange support/development strategy. They are working on what are interesting for them, on deep technical issues, not on what are really critical or annoying for users.