Re: Advantages of PostgreSQL over MySQL 5.0

On Wed, 2006-22-03 at 11:34 -0800, Benjamin Smith wrote:
> 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.

You can dump a running DB. Unless you have a hardware failure you should
not require any down time. I have been running a PG database that is
backed up every day, and has been been running for 5 years with one
5 min interruption when the server was moved to a new rack.

I am in the process of building a replacement machine, because the
hardware is bound to fail sometime, and it will be nice to upgrade
the OS. Unfortunately the base program I heavily customized to
put the collected data directly into PostgreSQL is no longer
maintained, so I need to build a whole new management and client
interface system around the new program, that has native support
for PostgreSQL but uses a significantly different table system.

>
> > 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.

MySQL also does not properly Support NULL, has glaring errors in the
scope of some data types and does not have robust support for many of
the data types I use on a regular basis.

If you are only interested in varchar and blobs MySQL may have a small
advantage. If you need proper support for NULL and/or robust data
types with proper scope handling, MySQL would not serve you well. The
performance and features of MySQL also depend on the type of table you
use and if your needs change you need to dump/drop/create/restore with
the type of table you require later on. PostgreSQL has one table type
and has well developed locking mechanisms that allow a table to be
dumped even when in use, where MySQL locks the whole table while it
is being dumped.

I have used both DB's depending on the requirements of the project, but
by far prefer PostgreSQL for anything that is not overtly simple. I
find that with MySQL the programmer has to take more precautions to
ensure proper variable scope and NULL handling work as expected, or
weird bugs crop up once in a while.