Thread: Postgres vs Firebird?
As a long-time user of Postgres, (First started using it at 7.0) I'm reading recently that Firebird has been taking off as a database. Perhaps this is not the best place to ask this, but is there any compelling advantage to using Firebird over Postgres? We have a large database (almost 100 tables of highly normalized data) heavily loaded with foreign keys and other constraints, and our application makes heavy use of transactions. I say this as my company's growth has been exponential, showing no sign of letting up soon, and I'm reviewing clustering and replication technologies so that we can continue to scale as nicely as we have to date with our single server. (now with a load avg around .30 typically) -Ben -- "The best way to predict the future is to invent it." - XEROX PARC slogan, circa 1978
Why not try both, by configuring a database and running your applications against both systems? Kick the plug out of the wall during the middle of a transaction. Run a simulated load of 10,000 users against it. If you are growing exponentially, it will be much cheaper to do your experiments now than five years from now, when everything has grown to titanic size. If you use a standards based interface like ODBC, OLEDB, JDBC or a .NET provider, then you can point your applications at the new system by simply changing the data source name. With all other things being about equal, I prefer PostgreSQL because of its superior license scheme. It also appears to me that the PostgreSQL community is more active than the Firebird community and that more interesting research is happening using PostgreSQL as a base. But I certainly would not try to dissuade you. Rather, I suggest that you use standards based interfaces to your database systems and then the data store becomes practically irrelevant, in that you can choose it or change it on a whim. On the other hand, if you have a huge investment in using pqlib as your interface, then I think it will be a lot harder to move to a new system. In such a circumstance, you should measure your choices much more carefully. If you have thousands of lines of PostgreSQL specific stored procedures, then you must also take that into consideration. Probably, nobody can be in a better position to know if a move might have benefit than you are. While you are at it, you might also try commercial database systems and additional open source choices. Why narrow the choice to two systems when there are a large number of possibilities? Of course, the testing effort will be proportional to the number of systems you implement. Remember also that 80% of the cost of any software system is maintenance. Does your organization have expertise in the target systems that you are considering? If not, then you are doing them a great disservice. > -----Original Message----- > From: pgsql-general-owner@postgresql.org [mailto:pgsql-general- > owner@postgresql.org] On Behalf Of Benjamin Smith > Sent: Wednesday, May 04, 2005 11:49 AM > To: pgsql-general@postgresql.org > Subject: [GENERAL] Postgres vs Firebird? > > As a long-time user of Postgres, (First started using it at 7.0) I'm > reading > recently that Firebird has been taking off as a database. > > Perhaps this is not the best place to ask this, but is there any > compelling > advantage to using Firebird over Postgres? We have a large database > (almost > 100 tables of highly normalized data) heavily loaded with foreign keys and > other constraints, and our application makes heavy use of transactions. > > I say this as my company's growth has been exponential, showing no sign of > letting up soon, and I'm reviewing clustering and replication technologies > so > that we can continue to scale as nicely as we have to date with our single > server. (now with a load avg around .30 typically) > > -Ben > -- > "The best way to predict the future is to invent it." > - XEROX PARC slogan, circa 1978 > > ---------------------------(end of broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings
Benjamin Smith wrote: > As a long-time user of Postgres, (First started using it at 7.0) I'm reading > recently that Firebird has been taking off as a database. > > Perhaps this is not the best place to ask this, but is there any compelling > advantage to using Firebird over Postgres? Firebird is a nice database but I don't think it can scale as well as PostgreSQL. IIRC they just added support for SMP. Also, although their community is very active I do not believe it is as large nor does it have the commercial backing like PostgreSQL. Sincerely, Joshua D. Drake Command Prompt, Inc.
On Wed, 2005-05-04 at 13:48, Benjamin Smith wrote: > As a long-time user of Postgres, (First started using it at 7.0) I'm reading > recently that Firebird has been taking off as a database. > > Perhaps this is not the best place to ask this, but is there any compelling > advantage to using Firebird over Postgres? We have a large database (almost > 100 tables of highly normalized data) heavily loaded with foreign keys and > other constraints, and our application makes heavy use of transactions. > > I say this as my company's growth has been exponential, showing no sign of > letting up soon, and I'm reviewing clustering and replication technologies so > that we can continue to scale as nicely as we have to date with our single > server. (now with a load avg around .30 typically) With some of the changes Tom recently made in the code in CVS, PostgreSQL now looks capable of scaling to >4 CPUS (somewhere between 8 and 12 is where things start to drop off suddenly) while for firebird, handling >1 CPU is a relatively recent development. I'd say try them both, benchmark them, and see what you think. But keep in mind that you really need to use a 4+ CPU machine to get a feel for the scalability of both in a large server environment.
while we're on "scalability", any thoughts on needs/plans for 64-bit PG?
-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Scott Marlowe
Sent: Wednesday, May 04, 2005 3:19 PM
To: lists@benjamindsmith.com
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Postgres vs Firebird?
On Wed, 2005-05-04 at 13:48, Benjamin Smith wrote:
> As a long-time user of Postgres, (First started using it at 7.0) I'm
> reading
> recently that Firebird has been taking off as a database.
>
> Perhaps this is not the best place to ask this, but is there any
> compelling
> advantage to using Firebird over Postgres? We have a large database (almost
> 100 tables of highly normalized data) heavily loaded with foreign keys and
> other constraints, and our application makes heavy use of transactions.
>
> I say this as my company's growth has been exponential, showing no
> sign of
> letting up soon, and I'm reviewing clustering and replication technologies so
> that we can continue to scale as nicely as we have to date with our single
> server. (now with a load avg around .30 typically)
With some of the changes Tom recently made in the code in CVS, PostgreSQL now looks capable of scaling to >4 CPUS
(somewherebetween 8 and 12 is where things start to drop off suddenly) while for firebird, handling >1 CPU is a
relativelyrecent development.
I'd say try them both, benchmark them, and see what you think. But keep in mind that you really need to use a 4+ CPU
machineto get a feel for the scalability of both in a large server environment.
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Mohan, Ross wrote: > while we're on "scalability", any thoughts on needs/plans for 64-bit PG? You mean beyond the fact that we have supported 64-bit for a couple of years? Sincerely, Joshua D. Drake > > > > -----Original Message----- > From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Scott Marlowe > Sent: Wednesday, May 04, 2005 3:19 PM > To: lists@benjamindsmith.com > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] Postgres vs Firebird? > > > On Wed, 2005-05-04 at 13:48, Benjamin Smith wrote: > >>As a long-time user of Postgres, (First started using it at 7.0) I'm >>reading >>recently that Firebird has been taking off as a database. >> >>Perhaps this is not the best place to ask this, but is there any >>compelling >>advantage to using Firebird over Postgres? We have a large database (almost >>100 tables of highly normalized data) heavily loaded with foreign keys and >>other constraints, and our application makes heavy use of transactions. >> >>I say this as my company's growth has been exponential, showing no >>sign of >>letting up soon, and I'm reviewing clustering and replication technologies so >>that we can continue to scale as nicely as we have to date with our single >>server. (now with a load avg around .30 typically) > > > With some of the changes Tom recently made in the code in CVS, PostgreSQL now looks capable of scaling to >4 CPUS (somewherebetween 8 and 12 is where things start to drop off suddenly) while for firebird, handling >1 CPU is a relativelyrecent development. > > I'd say try them both, benchmark them, and see what you think. But keep in mind that you really need to use a 4+ CPU machineto get a feel for the scalability of both in a large server environment. > > ---------------------------(end of broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match > > ---------------------------(end of broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match
LOL! (thanks) Redfaced, and going back to check compiler flags, -- Ross -----Original Message----- From: Joshua D. Drake [mailto:jd@commandprompt.com] Sent: Wednesday, May 04, 2005 3:42 PM To: Mohan, Ross Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Postgres vs Firebird? Mohan, Ross wrote: > while we're on "scalability", any thoughts on needs/plans for 64-bit > PG? You mean beyond the fact that we have supported 64-bit for a couple of years? Sincerely, Joshua D. Drake > > > > -----Original Message----- > From: pgsql-general-owner@postgresql.org > [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Scott Marlowe > Sent: Wednesday, May 04, 2005 3:19 PM > To: lists@benjamindsmith.com > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] Postgres vs Firebird? > > > On Wed, 2005-05-04 at 13:48, Benjamin Smith wrote: > >>As a long-time user of Postgres, (First started using it at 7.0) I'm >>reading >>recently that Firebird has been taking off as a database. >> >>Perhaps this is not the best place to ask this, but is there any >>compelling >>advantage to using Firebird over Postgres? We have a large database (almost >>100 tables of highly normalized data) heavily loaded with foreign keys and >>other constraints, and our application makes heavy use of transactions. >> >>I say this as my company's growth has been exponential, showing no >>sign of >>letting up soon, and I'm reviewing clustering and replication technologies so >>that we can continue to scale as nicely as we have to date with our single >>server. (now with a load avg around .30 typically) > > > With some of the changes Tom recently made in the code in CVS, > PostgreSQL now looks capable of scaling to >4 CPUS (somewhere between > 8 and 12 is where things start to drop off suddenly) while for > firebird, handling >1 CPU is a relatively recent development. > > I'd say try them both, benchmark them, and see what you think. But > keep in mind that you really need to use a 4+ CPU machine to get a > feel for the scalability of both in a large server environment. > > ---------------------------(end of > broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match > > ---------------------------(end of > broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match
An observation: the one recent study that pumped up Firebird seemed to come out of nowhere, and its findings have yet tobe corroborated elsewhere. While as others have noted, Firebird is a fine product (and has a longer history on the Windowsplatform), I think a little skepticism as to its market penetration and community size is warranted. Could be thatthe study was oversampling legacy Interbase users, for example. Benjamin Smith wrote: > As a long-time user of Postgres, (First started using it at 7.0) I'm reading > recently that Firebird has been taking off as a database. > > Perhaps this is not the best place to ask this, but is there any compelling > advantage to using Firebird over Postgres? We have a large database (almost > 100 tables of highly normalized data) heavily loaded with foreign keys and > other constraints, and our application makes heavy use of transactions. > > I say this as my company's growth has been exponential, showing no sign of > letting up soon, and I'm reviewing clustering and replication technologies so > that we can continue to scale as nicely as we have to date with our single > server. (now with a load avg around .30 typically) > > -Ben
On Wed, May 04, 2005 at 11:48:44AM -0700, Benjamin Smith wrote: > > As a long-time user of Postgres, (First started using it at 7.0) I'm reading > recently that Firebird has been taking off as a database. > > Perhaps this is not the best place to ask this, but is there any compelling > advantage to using Firebird over Postgres? Well, is there anything that makes you *want* to switch away now or later ? Any actual or prospective problems you perceive ? If so why not just test them ? Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
Hi, We have a big project here that we originaly did in Firebird 1.0, it worked well, though was missing tons of features like built in functions, temp tables etc etc. We then updated to Firebird 1.5 and again it worked good. We always had issues with the stupid Firbird OAT (oldest active transaction), if the OAT gets stuck your database will start to get huge as transaction data is stored in the database file itself. Firebird does not have the concept of a tranasction log (at lease a seperate one) The only way to compact a Firebird DB is to do a backup and restore, and we had lots of issues with this when the OAT got stuck. You have to constantly monitor the OAT and the OIT (oldest interesting transaction) Anyway soon after our update to FB 1.5 I started playing around with Postgres 7.2 or 7.3 and man was I impressed. I could do temp tables had tons of built in functions, there was no weird SQL dialects(firebird has 3 SQL dialects) I could just use any SQL i wanted from functions(firebird has several types of SQL PSQL,DSQL etc and you can't use one from the other. Firebird does not have a freely available replication system or a GUI admin tool(there are third party ones available) I can tell you from experience Postgresql 8.x is WAY BETTER than Firebird. Benjamin Smith wrote: > As a long-time user of Postgres, (First started using it at 7.0) I'm reading > recently that Firebird has been taking off as a database. > > Perhaps this is not the best place to ask this, but is there any compelling > advantage to using Firebird over Postgres? We have a large database (almost > 100 tables of highly normalized data) heavily loaded with foreign keys and > other constraints, and our application makes heavy use of transactions. > > I say this as my company's growth has been exponential, showing no sign of > letting up soon, and I'm reviewing clustering and replication technologies so > that we can continue to scale as nicely as we have to date with our single > server. (now with a load avg around .30 typically) > > -Ben -- Tony Caduto AM Software Design Home of PG Lightning Admin for Postgresql 8.x http://www.amsoftwaredesign.com
A few additional caveats about trying to support both PostgreSQL and Firebird: 1) I was unable to find a large text field type (equivalent to varchar() or TEXT) in Firebird. All Varchar fields require a length specifyer. 2) I found stored procedures to be a serious pain in Firebird. 3) PostgreSQL like most RDBMS's stores column names lower case. Firebird stores them in upper case. 4) No variable length arrays either in Firebird. In general, I don't recommend porting from PostgreSQL to Firebird unless you have to. Unfortunately I have had to do this on a few occasions. It has never been pleasent. These are however caveats and are not insurmountable. However, I would choose Firebird anyday for larger Windows installations, and it is quite a bit better than MySQL... Best Wishes, Chris Travers Metatron Technology Consulting Tony Caduto wrote: > Hi, > We have a big project here that we originaly did in Firebird 1.0, it > worked well, though was missing tons > of features like built in functions, temp tables etc etc. We then > updated to Firebird 1.5 and again it worked good. > We always had issues with the stupid Firbird OAT (oldest active > transaction), if the OAT gets stuck your database will start > to get huge as transaction data is stored in the database file itself. > Firebird does not have the concept of > a tranasction log (at lease a seperate one) > The only way to compact a Firebird DB is to do a backup and restore, > and we had lots of issues with this when the OAT > got stuck. You have to constantly monitor the OAT and the OIT (oldest > interesting transaction) > Anyway soon after our update to FB 1.5 I started playing around with > Postgres 7.2 or 7.3 and man was I impressed. > I could do temp tables had tons of built in functions, there was no > weird SQL dialects(firebird has 3 SQL dialects) > I could just use any SQL i wanted from functions(firebird has several > types of SQL PSQL,DSQL etc and you can't use one from > the other. > Firebird does not have a freely available replication system or a GUI > admin tool(there are third party ones available) > > I can tell you from experience Postgresql 8.x is WAY BETTER than > Firebird. > > > > Benjamin Smith wrote: > >> As a long-time user of Postgres, (First started using it at 7.0) I'm >> reading recently that Firebird has been taking off as a database. >> Perhaps this is not the best place to ask this, but is there any >> compelling advantage to using Firebird over Postgres? We have a large >> database (almost 100 tables of highly normalized data) heavily loaded >> with foreign keys and other constraints, and our application makes >> heavy use of transactions. >> I say this as my company's growth has been exponential, showing no >> sign of letting up soon, and I'm reviewing clustering and replication >> technologies so that we can continue to scale as nicely as we have to >> date with our single server. (now with a load avg around .30 typically) >> -Ben > >
Joshua D. Drake wrote: > Benjamin Smith wrote: > >> As a long-time user of Postgres, (First started using it at 7.0) I'm >> reading recently that Firebird has been taking off as a database. >> Perhaps this is not the best place to ask this, but is there any >> compelling advantage to using Firebird over Postgres? > > Firebird is a nice database but I don't think it can scale as well as > PostgreSQL. IIRC they just added support for SMP. Also, although their > community is very active I do not believe it is as large nor does it > have the commercial backing like PostgreSQL. > > Sincerely, > > Joshua D. Drake > Command Prompt, Inc. > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend > Haven't used it, but your question sparked a little interest so I spent some time trawling through their web-site. Like many open-source projects, documentation seems to be lagging a *very* long way behind the code. One of the things that has really impressed me with Postgres is the quality and thoroughness of the documentation. As a developer I need API documentation, not just a quick start guide and list of errors. I tend to avoid projects that don't bother with the documentation (I gave up on Slony-I very quickly for exactly the same reason!) Pete -- http://www.whitebeam.org http://www.yellowhawk.co.uk
Based on the extensive feedback here, as well as other information from other websites found since asking here, I've decided that I'm still, very happily, a PG user. No significant issues to date - PG has "just worked" for me for 5 years now, and the frustrating limitations (EG: alter table drop field, row size limits) functionally disappeared some time ago. It's always good to reevaluate where you are now to make sure you're not doing something stupid. Replication is the only thing that remains, and there appears to be commercially viable solutions for that, as well, though we've not yet implemented it. We're still in a very heavy product development phase, and we have a pretty good backup solution. When will PG replication come ready "out of the box" with the RH RPMs? Anyway, once again, Postgres is my friend... -Ben -- "The best way to predict the future is to invent it." - XEROX PARC slogan, circa 1978
> Replication is the only thing that remains, and there appears to be > commercially viable solutions for that, as well, though we've not yet There are two very popular replication solutions, one of them is OSS which is Slony-I. The other is commercial which is Mammoth PostgreSQL Replicator. > implemented it. We're still in a very heavy product development phase, and we > have a pretty good backup solution. > > When will PG replication come ready "out of the box" with the RH RPMs? When RH starts shipping 8 you will see Slony-I as it is already part of contrib. Sincerely, Joshua D. Drake
On Wed, May 04, 2005 at 12:08:47PM -0700, Joshua D. Drake wrote: > Firebird is a nice database but I don't think it can scale as well as > PostgreSQL. IIRC they just added support for SMP. Also, although their > community is very active I do not believe it is as large nor does it > have the commercial backing like PostgreSQL. Support it in what way? Do they allow for parallel query execution? -- Jim C. Nasby, Database Consultant decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"