Thread: Postgres vs Firebird?

Postgres vs Firebird?

From
Benjamin Smith
Date:
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

Re: Postgres vs Firebird?

From
"Dann Corbit"
Date:
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

Re: Postgres vs Firebird?

From
"Joshua D. Drake"
Date:
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.

Re: Postgres vs Firebird?

From
Scott Marlowe
Date:
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.

Re: Postgres vs Firebird?

From
"Mohan, Ross"
Date:
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

Re: Postgres vs Firebird?

From
"Joshua D. Drake"
Date:
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


Re: Postgres vs Firebird?

From
"Mohan, Ross"
Date:
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


Re: Postgres vs Firebird?

From
Ned Lilly
Date:
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

Re: Postgres vs Firebird?

From
Karsten Hilbert
Date:
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

Re: Postgres vs Firebird?

From
Tony Caduto
Date:
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

Re: Postgres vs Firebird?

From
Chris Travers
Date:
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
>
>


Re: Postgres vs Firebird?

From
Peter Wilson
Date:
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


Re: Postgres vs Firebird?

From
Benjamin Smith
Date:
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

Re: Postgres vs Firebird?

From
"Joshua D. Drake"
Date:
> 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

Re: Postgres vs Firebird?

From
"Jim C. Nasby"
Date:
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?"