We have similar databases running on MySql, Postgresql and Firebird. One of the reasons for moving away from MySql was the fact that the UTF8 support didn't work properly. I cannot remember the details, but it had to do with non-Latin-1 data, such as text in Czech or Russian. In some situations MySql refused to correctly identify equal UTF8 strings. You put in some word that you cannot retrieve again, bleh!
Furthermore, we've never understood how the user permissions are supposed to work in MySql (we always end up frantically running all possible variants of the GRANT ALL command).
We moved to Postgresql, which worked a lot better. Now we've started using Firebird, that also seems like a very nice piece of software.
Here is list of a few things I've noticed when moving from Postgresql to Firebird:
* Firebird lacks built-in support for regular expressions. (We make heavy use of complex string searches of natural language data. If we hadn't got help from an expert, who helped us compile some user defined functions, UDF:s, for this purpose, this would have been a show-stopper.)
* Postgres' psql command line tool is better than Firebird's isql(-fb). (If you are a Windows user, see Carlos' comment below)
* Firebird database files grow and grow. This is true even if you delete data. You have to manually back-up and restore a database to reclaim disk space. Maybe this is not a great problem in normal usage, but I noticed that the databases I use for running test suits against keep growing, though the test database itself is quite small (and the data are cleared out between test runs). [Update: Please notice that long-time users of Firebird insist that this is not a problem. See Carlos', Sergio Marcelo's and also Michal's comments below.]
* I've never had any luck installing Firebird from a Debian package. I have had to do a manual install to get it to work
* Firebird has a useful GUI, FlameRobin, that let's you inspect and change your databases. FlameRobin comes with an editor useful for writing/editing stored procedures. The editor has code completion, that helps you with suggestions of table and column names and the like as you type.
* Firebird has a nice way to manage database files: all tables of a database end up in a single file, that you can name whatever you like, and put wherever you like.
* It appears to be easier to find useful documentation for Postgres than for Firebird (but Firebird does have a nice FAQ site)
Answer to Darius Damalakas comment below: I'm not the right person to comment on the performance of the different DBMSs. However, we haven't noticed any significant difference in performance between MySql, Postgresql and Firebird. Currently, the bottlenecks in our software are to be found outside of the databases, so the performance of the individual DBMSs has not been a big concern. They're all fast enough.
Firebird does seem to be a snappy system, and I would be surprised to find it to perform less good than Postgres.
So far, the only difference in features that has mattered to us, is the lack of built-in support for regular expressions in Firebird (see above). In all other respects (of importance to us), the functionality of Postgres and Firebird seems equivalent.
Update: Support for regular expressions is scheduled for the upcoming 2.5.0 release of Firebird.
Update: In response to an anonymous (and rather critical) comment, mariuz has added some useful links in a comment below.
Update: In a comment below, Michal has posted some information on DatabaseGrowthIncrement, taken from the release notes of Firebird 2.1.
Tuesday, 18 March 2008
Firebird vs Postgresql
Subscribe to:
Post Comments (Atom)
11 comments:
Firebird doesnt shrink the database when you delete records because it will use that "free space" to store new records, etc. It is faster to use pre-allocated space on the disk than asking SO to allocate more space.
About isql, if you plan to run it on windows, you can try an "enhanced" version from Ivan Prenosil (aka. fbsql). You may give it a try.
Nice!
Though are there any qualitative differences between FB and Postgresql? Any performance, sql differences?
I am doing some research to determine what os database to use and I have eliminated Mysql as it has been bought by sun. so only firebird and postgers sql are on my list right now.
that's the 2nd article I've read about firebird db just growing and growing in size. and you have to back up and restore to compress the size. I think this is a major weakness as in a 24x7 environment, you won't have that luxury or backing up and restoring database very often.
another article I've read indicated that postgres sql never crashes, whereas firebird does (although very rarely)
and for those reasons, I think I will choose postgre sql over firebird. I still have to look at the "richness" of the stored procedure language triggers and functions available.
thanks for providing your updates on firebird.
1) We need support for regular expressions too. Yes, Firebird lacks.
2) Firebird has features in SQL syntax. Some of them are neutral while others are souring. For example:
- you must use or must not use double quotes for all aliases in a query;
- names in a query are case sensitive and you must use double quotes for them always (except you use upper case names only) so queries are full up with quotes;
- order of CHARSET - DEFAULT - COLLATE in a char field definition is fixed;
- you can not alter table if there exists view using it;
- EXECUTE PROCEDURE does not return result set as SELECT does so you must use different procedures in host language (Delphi in my case) for queries of that two types.
3) Firebird has very poor documentation.
4) PostgreSQL looks more professional system in all aspects. Firebird seems frozen in 15-years old "support level". May be 2.5 will be better.
1. REgular expressions are added in 2.5
http://www.firebirdsql.org/rlsnotesh/rlsnotes25.html#rnfb25-dml-regex
3. See the new documentation added
http://www.firebirdsql.org/?op=doc#category_2
The Firebird 2.0 Language Reference Update (HTML | PDF) documents all the changes in Firebird SQL since IB 6. Use in conjunction with the InterBase 6 Language Reference. Versions for Firebird 1.5 were already available (HTML | PDF). Updates for Firebird 2.1, as well as a complete reference, are in preparation.
As Carlos said, Firebird database file growing is EXPECTED once it will speed up writing data later. It is a feature instead of 'problem'. I'm using firebird for years up to now and I have nothing to complain.
Quotation from 2.1 release notes:
DatabaseGrowthIncrement
V. Khorsun
(V.2.1) For better control of disk space preallocation, the new parameter DatabaseGrowthIncrement has been added to firebird.conf. It represents the upper limit for the size, in bytes, of the chunk of disk that will be requested for preallocation as pages for writes from the cache. Default: 134,217,728 bytes (128 MB). For background information, please refer to the topic Enlarge Disk Allocation Chunks in the chapter “Global Improvements in Firebird 2.1”.
When the engine needs to initialize more disk space, it allocates a block that is 1/16th of the space already allocated, but not less than 128 KB and not greater than the DatabaseGrowthIncrement value. The DatabaseGrowthIncrement value can be raised to increase the maximum size of newly-allocated blocks to more than the default 128 MB. Set it to zero to disable preallocation.
Note
* The lower limit of the block size is purposely hard-coded at 128 KB and cannot be reconfigured.
* Space is not preallocated for database shadow files.
* Preallocation is disabled for a database that has the “No reserve” option set.
One thing is not mentioned here, Firebird has more time runing on Linux and Windows natively, Postgresql include support for Windows recently in verision 8.2 and above, both Firebird and Postgreslq are great DBMS, they are 100% completely free and more robust than MySQL, MySQL is faster, but it lacks so many features which Firebird and Postgresql not, however MySQL is working great on so many web sites. Finally I think the choice depends on your needs ;-)
Currently, with the advancement of control of the source code of Persistence
program, we do not have to worry about roles and other things, we must
worry about the administrative part of the DBMS, so I say, if you want robustness
FB, if you want speed and simultaneous access MySQL, if you want to complications
query building a better organization but PG.
Hello, anyone has an experience about how "easy" is to adapt a long SQL script done for MySql (but doing nothing more than a lot of DROP, CREATE, ALTER, INSERT, UPDATE) to Firebird and how easy to PostgreSQL ?
In other words, which one of them is the "closest" to MySql from a pure SQL syntax point of view ?
Post a Comment