[ILUG] Linux and (free) databases

[Andy Ferguson]

On Mon, 15 Nov 1999, Ian Swainson wrote:

>      Could someone using MySQL explain why you chose it over PostgreSQL? - 
>      Is there something I should know?

A few years ago Postgres was in the doldrums; it was slow,
featureless, and possibly unstable and essentially a research
project in progress. A "free" alternative was msql which
was a cut-down SQL engine from David Hughes. Several tools quickly
came out to assist with interfacing msql to perl, web browsing and
other things. MySQL developed from msql, possibly as a reaction to
the odd licencing policy with msql, and included translation tools
to migrate from msql to MySQL.

MySQL and tools like PHP and integration with perl DBI and ODBC have
continued to develop; most tools broadening their support to other
engines including commercial systems like Oracle, Solid and Adabas
(free versions on linux exist for all these I think).

PostGres meanwhile moved from a research/educational project into
the OpenSource domain and presumably it continues to develop in the
direction of commercial DBs but through the Bazaar. Now is proabably
a good time to revisit Postgres and see where its at; it was
supposed to be a complete SQL implementation unlike MySQLs sub-set.

So there are two practical reasons why MySQL is/was favoured
over PostGres;

1. historically MySQL was more stable and functional
2. MySQL is optimised for read access; PostGres is a general purpose
engine

So if you want to work with a real SQL engine then use PostGres; if
you just want an SQL engine to act as a dataserver then consider
MySQL. The MySQL authors have no pretensions about their objective
and AFAIK have no plans to even consider implementing features like
transactions and rollback - limited functionality is planned for
transactions but their point is that transactions conflict with
MySQLs design goal of high performance reading and as I recall this
is what sent PostGres into the doldrums in the first place.

Or to put it another way; if your data changes frequently as in an
accounting or payroll system, use PostGres. If the data is basically
static and rarely changes, if at all, consider MySQL (or even msql).
Tools like PHP and perl DBI support both equally well; though maybe
not ODBC if this is still relevant these days.

Commercial offerings like Adabas and Solid (maybe Oracle) have
restrictions on their "free for personal use only" versions; with
Adabas its the number of users the server will support.

Andy Ferguson
~~~~~~~~~~~~~
Sysop MOIL BBS +44-1247-273357