Re: Advantages of PostgreSQL over MySQL 5.0

On Wednesday 22 March 2006 03:06, Jimbo1 wrote:
> Hello there,
>
> I'm a freelance Oracle Developer by trade (can almost hear the boos now
> ;o)), and am looking into developing my own Snowboarding-related
> website over the next few years. Anyway, I'm making some decisions now
> about the site architecture, and the database I'm going to need is
> obviously included. If my site works out, I'm expecting reasonably
> heavy traffic, so want a database that I'm confident can cope with it.

I've built many sites based on PostgreSQL. Originally,like most, I started
with MySQL, but after I discovered PG in about 2000, I've switched all
development to it, and have never looked back. I have "enterprise" systems
developed with PostgreSQL with 500 users, 50 online at a time, > 100 database
tables. Although the data sample is still not that impressive, (71 MB sql
file with pg_dump) the database itself is quite complex, with multiple
foreign keys in a single table being the norm.

It's just been a dream. It's solid, reliable, and virtually always behaves as
expected.

My only caveat is that occasionally, you really have to watch the use of
indexes. I had one query (nasty, with 7-8 tables involved in a combined
inner->outer->inner join) that was taking some 20 seconds to execute. Just
changing the order of some of the tables in the query, without logically
changing the result at all, dropped that time down to < 50 ms!

> Regarding MySQL, I've been put off by Oracle's recent purchase of
> InnoDB and realise this could badly impact the latest version of the
> MySQL database. I can almost hear Larry Ellison's laughter from here
> (allegedly)! I've also been put off by the heavy marketing propaganda
> on the MySQL website.

Perhaps the single thing I most like about PostgreSQL is the feeling that "it
can't be taken away from me". The license is sufficiently open, and the
product is sufficiently stable, that I don't ever wonder if I'm "compliant"
or "paid up", nor do I wonder if my growth will be particularly limited
anywhere in the forseeable future.

> "With MySQL, customers across all industries are finding they can
> easily handle nearly every type of database workload, with performance
> and scalability outpacing every other open source rival. As Los Alamos
> lab (who uses MySQL to manage their terabyte data warehouse) said, "We
> chose MySQL over PostgreSQL primarily because it scales better and has
> embedded replication.".".

PostgreSQL has replication, as well. From what I've read, it's probably about
on par with MySQL in terms of manageability and reliability.

But, truthfully, having dealt with database replication, it's a PAIN IN THE
ARSE and very unlikely worth it. In fact, systems that I've worked on that
included replication are generally less reliable than those that simply do a
dump/copy every hour or two, due to the increased management headaches and
niggling problems that invariably seem to occur.

Consider replication if the cost of a full-time DB Admin is justified by
saving perhaps a few hours of uptime per year. If so, go for it. Be honest
about it - most people grossly overestimate the actual cost of few hours of
downtime every other year.

> If any PostgreSQL devotees on this group can comment on the above and
> its accuracy/inaccuracy, I'd really appreciate it.

PG does constraints wonderfully. It's performance is midline with simple
schemas. It handles very complex schemas wonderfully, and, with a little
tuning, can make very effective use of memory to speed performance.

My $0.02. Cheers!

-Ben
--
"The best way to predict the future is to invent it."
- XEROX PARC slogan, circa 1978