Firebird

By Deane Barker on June 14, 2004

As will surprise nobody who knows me, the shininess of the Firebird database server had been blinding me since I posted that interview yesterday. It was calling my name, so I just had to download and install it.

I’m extremely happy with what I found — it’s a free database server with a ton of features you’re not going to find in MySQL. There’s a self-extracting Windows installer, it installs itself as a service, and it even gives you a little Control Panel applet to manage it.

It has support for foreign keys, views, triggers, and stored procedures (with what looks like a very rich procedure language). There’s full PHP support through the ibase_* set of functions and it’s supported in the ADODB database abstraction library.

There’s not much in the way of free clients. It comes with a command line client, of course, but I didn’t find any free graphical clients. There’s this one and this one, but they run from $79 to $250.

I still don’t quite have a handle on the genesis of the project. I gather it came out of Borland’s InterBase product, but I’m not sure if InterBase continues, or if it was abandoned (there’s a Windows 2003 version of it, so it’s probably still alive in some form). Firebird (and InterBase) penetration appears to be most heavy in Europe. It’s commonly used with the Delphi language (another Borland product).

In browsing some of the message boards about Firebird, I got the same…vibe that I got about MySQL five years or so ago. There’s a lot of buzz, and there seems to be a lot of transfer from MySQL to Firebird. The talk all seems to be exceedingly positive.

So why would you use MySQL over Firebird given that the latter has support for so many more features and is just as free? I guess I’m a little more confident with MySQL’s battle-worthiness, given that it’s been around the block so many times. (But by the same token, InterBase wasn’t in beta or anything — that was a production quality database server.)

So am I ready to switch all my stuff over to Firebird? No…not yet. But I don’t think I’ll look at MySQL quite the same way ever again. This seems like a very attractive option.

Gadgetopia
What This Links To
What Links Here

Comments

  1. Then I guess I don’t understand where FireBird came from. Why would you open-source a product then continue to try and sell it? What am I missing here?

  2. Looking at the price, maybe I understand —

    InterBase Desktop is only $60. The server-based version is only $200. Maybe if you buy InterBase over using Firebird, you’re basically just buying the support contract and perhaps some improved tools. A lot my MySQL, actually.

    On another note, a lot of noise is made about Firebird’s “small footprint.” I don’t see it — I have both a Firebird server and a MySQL server on my machine, and neither have much load on them. Firebird is using 8MB of RAM right now, while MySQL is only using 2.5MB.

  3. As with any DB, it’s probably best to consider the application before making the choice. One of the advantages MySQL has going for it is that it’s hella fast, due in part to the fact that it lacks a number of features (referential integrity and sub-selects chief among them). In your average data-driven web site, you’re in a write once-read many type of mode, so MySQL works very well. If your requirements are different, a different DB may be a better option.

    Firebird sounds pretty interesting. Most folks will raise PostgreSQL as a reference-aware alternative to MySQL, but there have been questions over the years regarding recovery and DB corruption (or so I’ve heard – maybe I’ve just talked to the wrong people). Options are always a good thing.

  4. InterBase was open sourced during very different times. The stock market was soaring up, and open source offerings were tied to meteoric IPOs. The division had been mismanaged at Borland and wasn’t doing well. Borland started negotiations to spin off InterBase as a separate company and open sourced it in the process. Then the stock market collapsed and Borland and the folks who were to run the company couldn’t agree on terms. Now InterBase is under new management at Borland and is doing very well there.

    The Firebird fork was created almost immediately after IB 6 was open sourced, and as both InterBase and Firebird have had some significant updates since the split they have increasingly less in common and IMHO should be considered two different databases.

  5. As Craig notes, the history goes something like this (I’ve no idea of the politics, just the results):

    1. Interbase was open sourced by Borland

    2. The Firebird fork was established

    3. Borland reversed the open source decision, returning to a commercial model

    4. Firebird continues today (so does Interbase)

    I evaluated Interbase at work shortly after it was open sourced. We didn’t have truly heavy duty enterprise needs, but in general I found it very comparable to MS SQL Server. It came with a graphical client called IBConsole (Windows only). I also tried out a freeware client called IBAccess, IIRC.

    The free DBTools ( http://www.dbtools.com.br ) application, which currently supports MySQL and PostgreSQL, has Interbase/Firebird support on their ToDo list.

  6. Craig, given your experience with these products (thanks Google…), what are your opinions on Firebirds “enterprise-ability”?

    Running on the same hardware, is it as scalable as, say, SQL Server? Is it mission critical, “I would use it for a database tracking the bank balances of every human on Earth” reliable?

  7. The main drawback at present with Firebird (for ‘enterprise’ apps) is its lack of support for SMP. They’re working on this (Vulcan project for 64 bit processors?). At the moment you have to fix the db server to a single processor on multi-processor machines.

    You’ll also have to find seperate commercial solutions for replication, incremental backups, full-text searching etc.

    The trigger and stored procedure language is very powerful — enough to use it for an app server if you’re using someting like PHP!

    Multi Versioning (or as they call it, “Multi Generational Architecture”) can take some getting used to – but it’s highly concurrent, and this kind of feature is due for release in SQL Server 2005 (Yukon) too.

    Sometimes the query planner isn’t as good as it could be — particularly with outer joins.

    So I wouldn’t compare FB with SQL Server or Oracle at this stage (maybe in 5 years?). But its far ahead of MySQL and other open source db’s.

    Best of all, it can solve queries in 30mb that it takes SQL Server 600mb to solve!! I think the project leader of SQL Server has shares in a memory-fabrication plant! LOL

    John

  8. John, you are sure about: “Multi Versioning (or as they call it, “Multi Generational Architecture”) can take some getting used to – but it’s highly concurrent, and this kind of feature is due for release in SQL Server 2005 (Yukon) too. ” ?

  9. John,

    “So I wouldn’t compare FB with SQL Server or Oracle at this stage (maybe in 5 years?). But its far ahead of MySQL and other open source db’s.”

    Have you ever installed both systems? The download for the server is 3.8 MB – not GB, MB! This expands to approx. 12 – 13 (not on home machine), with a sample database. The same thing for Oracle is 2 GB. It depends what you want – pay shitloads for Oracle, or have something that can do 95% of what Oracle can do, with little or no expense.

    JMHO.

    Paul…

  10. Hi Paul,

    Oh a agree that SQL Server (the commercial system I’m most familiar with) and Oracle are massive and bloated. FB is small and tight. I just don’t think in terms of features they’re in the same league. That isn’t to take anything away from FB – I’m just lucky enough to have licenced access to SQL Server 2000 so can use either without and commercial considerations (granted, that is unusual!).

    SQL Server has built in support for replication, full text indexing, materialised views, plugs into Visual Studio in a cool way that makes me more productive. The ADO.NET provider for FB is great (thanx Carlos) but you still have the issues with typed datasets… or lack of.

    I don’t what this to sound like I’m dissing FB, I’m not. I’ve been using it since 2000 (IB6 beta days) and I love it. I just think they do it a disservice to compare it to systems that have massive development teams behind them, and are always going to be slicker.

    The ‘target audience’ for FB should be other open source db users, and people writing c* desktop apps in Access! LOL

    John

  11. Deane, I can’t authoritatively answer the question you asked me about Firebird’s “enterprise-ability” since I use InterBase and not Firebird. But I can comment about InterBase. Sometimes comments about InterBase are apropos to Firebird, sometimes not.

    InterBase is excellent for mission-critical applications as its multi-generational architecture means that it can recover quickly from crashes, and with forced writes enabled it is very difficult to corrupt the database.

    InterBase 7 introduced full support for SMP hardware, and InterBase 7.1 includes enhancements for hyper-threading.

    But SQL Server includes some scalaility features which InterBase — and, to the best of my knowledge, Firebird as well — does not. These include a “fiber” mode which eliminates some of the overhead associated with Windows threads when using servers with a relatively large number of processors (more than 8 or so).

    So part of the comparison would be a consideration of how big you want to scale. If you’re fitting out a 32 processor server, InterBase would not be a great choice. But it’s just excellent for, say, a four processor machine.

  12. “and it even gives you a little Control Panel applet to manage it.”

    Sorry, where you found it ? In IB6.0 was Control Panel applet with Automatic/Manual,as service/Start-Stop, but in FB1.5 i can’t find it… :(

  13. Great things about Firebird 1.5 as opposed to SQL Server 2000:

    Free! Cool concurrency (once you get used to it, and may take some re-designing or tables). Nice stored procedure language (easier and safer to define cursors, for example). Triggers fire once-per-record not once-per-action as in SQL Server (easier logic). Very low memory usage (in my systems, 10-15Mb).

    Things I find better in SQL Server 2000:

    Derived tables (SQL syntax). Great for ordering UNIONs etc without need for views. UDF’s (FB equiv, selectable stored procedures) can return indexed tables, making joining faster. Auto recompute index statistics. (surely this would be sooo easy to implement in FB engine??) No need for indexes on foreign keys (when selectivity is low, the FB requirement just wastes time and disc space). Query planner kicks ass. More traditional locking semantics mean it hurts my brain less. Caching query plans, even for ‘ad hoc’ queries when they turn out to be not-so-ad-hoc! Gives me something to do with all those spare gigabytes of ram ;)

    The Firebird Gurus will I’m sure sort these minor weaknesses out soon (maybe not the RAM one!!).

    One concern for 24×7 systems on FB might be the built in ‘life span’ of the database file; The transaction counter constantly increments (you can’t even SELECT without having a transaction open), and metadata changes are limited to (I think) 255 per table. Once this is reached you’re supposed to “Backup / Restore” and everything is new any shiny again!!

    BUT that does mean taking your DB offline while the process takes place (backup can be done ‘hot’, but restoring cant).

    How often should you backup / restore? people have their own opinions. I do it once a year on my (stable) systems, and that seems fine. Some people claim once a week is good!!!! Thats not so much 24×7 as 24×6.8, grin

    I’d like to see a way to regenerate the db file ‘hot’ in a future release.

    John

  14. For the record, there are a few solid freeware GUI’s available for Firebird. There are a lot of tools I have not used yet and it may be that some of them are stronger than what I use.

    But in the interim, MiTeC Interbase Query and Marathon are working out well for me.

    The IBQuery download…. http://www.ibphoenix.com/main.nfs?a=ibphoenix&page=ibpadmintools

    The Marathon download…. http://gmarathon.sourceforge.net/

    They are more than enough to make Firebird the only open source RDBMS I bother with any more.

    Highest Regards, Guy

  15. This is a slightly old thread so not sure if anyone still cares… :) But I have extensive experience with Oracle, MS SQL, Informix, Firebird, and InterBase… (All told about 10 years…) I have developed enterprise applications in all of them. And I will say that I will never recommend MS SQL to a client/employer ever again. I would only recommend Oracle to those who can afford the HIGH QUALITY fulltime DBA support required to manage it, and even then, not as often as Firebird.

    The main application that I developed with Firebird, in it’s current largest incarnation supports a large number of locations and pushes approximately 10gig of data through the system a month. With no slowdown (a key requirement)! Damned near 100% up time (the rare occasions that it goes down is almost always hardware and is never the fault of the database). And the project is growing… They keep adding new locations, and pushing more and more data.

    As far as feature “richness”… If we are talking purely RDBMS feature sets, Firebird has MS SQL beat by a mile, while not really coming close to Oracle without adding a number of 3rd party tools and even then doesn’t have everything.

    And as far as stability… MS SQL isn’t even in the same class with Firebird and Oracle (with the right DBA). Anyone who would choose MS SQL over Firebird really needs to have someone sit down with them and go over the real differences between the databases. Firebird is an enterprise level database (especially now that some of the multiprocessor features have been handled), while MS SQL IMHO isn’t truly viable in an enterprise situation. And before I get flamed for that, I know that it is being used in those types of environments (I am in one now). However, we require an entire staff of people working to keep the database live and barely succeeding. I have seen AV’s come out of MS SQL (NOT acceptable for an RDBMS). I have seen insanely idiotic locking issues (readers blocking writers, what the hell is that?). Transaction control does not pass the ACID test.

    Btw, I used to say things like “Firebird is good for small installs, but Oracle is where you need to go to scale”, etc… That was until price forced my hand into a decision for Firebird on that application I mentioned earlier. We pushed it to the limit, and it didn’t flinch. It turned out to be the best decision that I could have made, and now with my experience with Firebird, I truly believe it to be comparable only to the highest end databases (of which MS SQL doesn’t make the cut).

    In fact, I currently have a fairly ambitious web project being developed which I believe will give me superior results to similar projects with PostgreSQL and MySQL. I haven’t decided whether to release it to the public or not yet (it is purely for my company’s use right now).

  16. p.s. There is only one tool to use when it comes to Firebird, and that can be found at http://sqlmanager.net/products

    They also have products for many other databases now, including MS SQL (I own that one as well), MySQL, PostGreSQL, and one in the works for Oracle…

    It may cost a few bucks, but it is VERY worth it…

Comments are closed. If you have something you really want to say, email editors@gadgetopia.com and we‘ll get it added for you.