Postgres vs Firebird [closed]_问答_开发者_运维开发者技术经验分享

Closed. This question does not meet Stack Overflow guidelines. It is not currently accepting answers.

We don’t allow questions seeking recommendations for books, tools, software libraries, and more. You can edit the question so it can be answered with facts and citations.

Closed 5 years ago.

Improve this 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 com开发者_JAVA技巧parison of the two database at http://web.archive.org/web/20100305134128/http://www.amsoftwaredesign.com/pg_vs_fb

But this comparison is a good 4+ 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?

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

is it reliable enough for your needs?
does it suit all your needs?
what are perspectives/support/development/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 restoreable 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.

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 :)

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 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 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 can query remote databases. Release Notes

One more recent comparison (2008):

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

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:

Windowing Functions
Common Table Expressions and Recursive Queries
Default and variadic parameters for functions
Parallel Restore
Column Permissions
Per-database locale settings

...and some from 8.3:

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

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

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

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.

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.