Re: PG -v- MySQL

At 10:39a -0400 on Tue, 13 May 2008, Dave Gauthier wrote:
> I’d like to introduce PG,
> but want to be able to address the "Why not use MySQL" questions when
> they arise. I know in the past there have been many comparisons between
> the 2 DBs but would like to know if there are any good, recent ones.

In addition to Josh's links, you may find Greg Smith's (et al) article
useful. It is well-written, thorough, and recent (less than a year old).
http://wiki.postgresql.org/wiki/Why_PostgreSQL_Instead_of_MySQL:_Comparing_Reliability_and_Speed_in_2007

> Also, any "off-the-cuff" comments would be welcome, especially if you
> have used both DBs.

I use both on a daily basis, and as developer, my opinion is that
Postgres is much nicer:

* The error messages are much more informative: typo something in your
SQL and compare for yourself the error messages b/w the two DBs.

* If you work with the command line client, tab-completion is much nicer
in psql. (I daresay, it's more complete. :-) ) You can complete almost
anything. (It even understands the context of where the cursor is in
the statement you're writing.)

* I am a member of both MySQL and Postgres mailing lists. In my
opinion, the community help from Postgres is (in general) much more ...
complete. You will regularly get answers straight from the devs, with a
definitive answer on how to fix your problem. And, the community is
more generally concerned with helping your larger problem, than just the
problem at hand.

* #postgres on irc.freenode.net follows the same path as the mailing lists.

* The documentation on the website for Postgres is by far the better of
the two DBs. MySQL's is decent and very useful, but Postgres'
documentation just plain rocks. In fact, the Postgres documentation is
one of the very best examples of documentation for any project with
which I've had to deal, OpenSource or otherwise. Head and shoulders.

* The \* commands are much easier for me to work with, although that may
just be my preference. \d a_table, for instance is easier for me than
typing SHOW CREATE TABLE a_table. (And tab-completion works there too...)

As a proponent of data-correctness:

* The default for PG is ACID compliance. As opposed to MySQL's default
table type of MyISAM, which, although fast for selects, is not ACID
compliant. This is especially hazardous since MySQL's internal data
structure is all MyISAM.

* The community in general is oriented towards doing it "the right way"
as opposed to just getting it done.

As a proponent of speed and concurrency:

* Postgres may not be as fast for certain boundary cases like
SELECT COUNT(*) FROM a_table; -- (read link above)
but it far outstrips MySQL if you have much concurrent access. Someone
else will hopefully serve out some links on this soon since I don't have
them handy at the moment.

As a DBA for different clients, I /really/ like the fact that Postgres
has transactional DDL statements. Even Oracle doesn't have
transactional DDL statements. Just DML.

HTH,

Kevin