Re: 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(at)scw(dot)org>
Director of Information Systems, Support Center of Washington