PostgreSQL vs. Interbase

On Mon, 7 May 2001, Arnold Gamboa wrote:

I'm currently in serious research on what database to use for a B2B site
which is expected to hold millions of records. I have in so far considered
two open source databases - Interbase and PostgreSQL. With this in mind, I'm
sending this email to both the PostgreSQL and Interbase mailing lists and
also on PHP's. I would appreciate what ever information you can provide.
Thanks :)

My inquiry:

Please discuss as to your point of view the advantage of PostgreSQL over
Interbase and/or vise versa. I'm considering three (3) important points

1. Speed
2. Data Reliability
3. Portability

I don't have tons of experience w/Interbase, but have used it in a few
projects. I'm quite experienced w/PostgreSQL.

Interbase is, overall, a *very nice* database system. It supports a slew
of features, like triggers, procedures, user-defined functions,
transactions, etc. So does PostgreSQL.

The differences, largely, as I see them, are:

PostgreSQL:

* stable community of Open Source users and wonderful mailing list
support
* commercial support from GreatBridge, Postgresql, Inc., and others
* excellent procedural language support: you can code triggers,
functions, etc., in pl/pgsql (an Oracle pl/sql-alike language), perl,
python, tcl, ruby, and sql.
* established and proven on Unix/Linux machines
* no stored procedures ( != functions, above, as stored procedures return
recordsets.) There are views, which provide about 90% of the features of a
stored procedure.
* Windows NT/2000 version is less supported and doesn't appear to be
widely used.
* has client programs, such as psql, pgAdmin, and pgaccess, that can be
used to administer the server, but the best of these tools aren't as
polished as Interbase's.

Interbase:

* newer to Open Source, dreadful history of company support for Open
Source, and therefore, consequently, a somewhat fractured community (see
the confusion between the ibphoenix, firebird, and 'offical' interbase
sites for a sense of the confusion.)
* commercial support from borland, and support for open source versions
from companies like ibphoenix, though they're a bit newer to the support
game.
* stored procedures that return recordsets
* user defined functions can only be written in C, unlike PostgreSQL's
many options
* lacks CASE..WHEN..END, NULLIF, COALESCE, and other conditional SQL
statements. This makes some things quite a pain.
* Windows NT/2000 version looks well-supported and widely used.
* Linux version is a fairly new.
* IBConsole, Interbase's configuration program, is very polished, and
easy to use.

I don't have any data on performance comparisons between the two,
sorry. PostgreSQL has demonstrated excellent performance in recent
benchmarks; I have less knowledge of Interbase.

Both have seemed reliable to me, and both have supported million-record,
high-availabiilty databases.

Portability is more tricky: PostgreSQL runs under a trillion operating
systems, all of them POSIX/Unix/Linux-y, and runs under NT/2000 with
Cygwin, a portability library. Interbase (IIRC) runs under Linux,
Solaris/SPARC and 95/98/NT/2000. There are sources available for
Interbase, but I haven't heard that anyone is using it under more exotic
Unices, or the whole family BSDs. (Of course, I haven't been listening to
hard, either, so it's possible that people are.)

In our shop, we apply the following rule:

* Does it _have_ to run under Windows? If so, slap the client. Does it
_still_ have to run under Windows? If so, use Interbase.
* Otherwise, use PostgreSQL.

(we only work w/nonprofits, so we don't have the first question: do they
want to pay lots and lots and lots and lots of money? If so, use Oracle)

HTH,
--
Joel Burton <jburton@scw.org>
Director of Information Systems, Support Center of Washington

1. Speed
2. Data Reliability

The two above depend very much (IMHO) on SYSDBA's skills. IB and its
descendants has, probably, less options to tweak, or, more correctly, less
obvious ways to improve its performance.
OTOH, if you have strong theoretical RDBMS bacground and considerable
practical experience with either, you'll find that performance and
stability tips and tricks for IB and PGSQL are very similar.

3. Portability

Does that really matter? What are you going to port? The code of DBMS
server itself? Some of the accompanied tools? Your data? If you're going to
run a busy site with a huge DB, portability is the last thing to worry
about. It's more important, IMHO, to pick the right platform at design
stage.

--

������������������������������������

My inquiry:

Please discuss as to your point of view the advantage of
PostgreSQL over
Interbase and/or vise versa. I'm considering three (3)
important points

1. Speed

Postgresql is "spotty". Some things it can do fast other
things are very slow. It's really mixed. So Postgres' speed
depends a lot of if you are so unlucky as to require some
feature that is not so fast. I found recently for example that
"GROUP BY" is intolerably slow while other query types are
almost as good as Oracle in performance. If you are designing
a brand new application you can design around this.
I'm sure each of the free DBMSes have something that must be
"designed around". You just take your pick.