Re: Database Selection

On Tue, 2006-04-25 at 01:26, IvoD wrote:
> I read many web pages about both PostgreSQL and MySQL, I read also
> "case studies" at pg web, but I prefer opinions of real users :-) I
> installed both db engines on my PC three weeks ago and now I test it.
> But I'm sure there should be "features" that I am not able to catch
> (e.g. MIN() function speed problems in previous pg versions) and that
> are not fixed yet. And therefore I ask all you - real users - about
> real experience and real enterprise applications. And last but not
> least - I must run db engine on Win platform (not Linux) and all the
> "success stories" assume Linux platform. So does somebody here know
> some good experience of "enterprise app" on M$ Win platform?

> My "sixth sense" tells me that PostgreSQL is better than MySQL,
> therefore for main app I prefer PostgreSQL; but I am in doubt to run
> only one db engine for two databases. But my "inner space" tells me to
> separate newsgroups system and company data system and run two
> different db engines - in light of security (although only one db
> engine looks promissing).

I've combined your two posts here into one for easy answering.

MySQL was originally just a SQL front end to isam files. While much has
been done to it over time, it's roots still show, and in ways that I
personally don't really like. For instance, way back when, in order to
make it easy to import schema from real databases like Oracle, MySQL
swallowed but ignored column level constraint syntax. So, creating a
table like:

create child_table (i1 int, parent_id int references parent(id));

resulted in no error, but NO foreign key either. To me, that's the
worst possible failure mode, a silent one.

This philosophy still exists today. While MySQL supports foreign key
constraints via innodb tables, it only supports the syntax in a table
level format (i.e. (i1 int, parent_id int, foreign key ....)) and if you
give it to mysql in a column level, it ignores it but produces no error.

The philosophy of PostgreSQL is the polar opposite. If something
doesn't work right, PostgreSQL throws and error and refuses to proceed,
expecting you to take care of the problem NOW. Which is better? I
prefer the postgresql way, because the mysql way leads to madness.
Imagine thinking you've got FKs when you don't, and finding out 2 years
down the road that all your data is incoherent because your database
tricked you into thinking it was doing FK when it wasn't.

About the security thing. Security is a process, and you won't get it
from using two different database engines. There are other
considerations. You can run multiple versions of PostgreSQL on the same
box if that's what you need. Each needs to use a different tcp/ip
port. Creating two separate databases within a single PostgreSQL server
is the way I'd do it. That way they're both on the same port, and use
the same shared memory, but for all intents and purposes, they are
separate databases. Note that you can edit the pg_hba.conf file to
allow only certain users to connect to one db or another.

I wouldn't pick MySQL or PostgreSQL or both based on the security
issue. You could just as easily run both on separate boxes for REAL
security anyway.