Re: PG -v- MySQL

On Tue, May 13, 2008 at 9:42 AM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
> Here are some other things we have v. mysql:
> *) Transactional DDL: much safer and easier to roll out changes to 24/7 systems

It's also useful for lots and lots of other things. For instance,
let's say you had an index on a good sized table, and that index took
three hours to build. You look at the stats and it's not getting used
much. You want to see if you can get rid of it, but don't want to
drop it and then have to spend three hours rebuilding it. You have
other indexes you want to still be used while testing, so set
enable_indexscan off is out of the question. You can do this:

begin;
drop humongous index;
explain analyze complexquerygoeshere;
-- run it a few more times to see how caching affects it
explain analyze complexquerygoeshere;
explain analyze complexquerygoeshere;
-- all done, got my numbers...
rollback;

While it's not safe to do in production 24/7 servers because of
locking issues, it's quite a sane to do on test or reporting servers.

> *) Better HA: PostgreSQL wal shipping/warm standby is much more robust
> than mysql binary log shipping

This can't be stressed enough. MySQL's replication is of the "gee, I
sure hope it's working" variety, and there are LOTS of horror stories
of it just quitting with no notice.

> *) MVCC: (I'm sure this is up there, but it's worth
> repeating)...smarter locking/concurrency for high write activity
> (important) applications. There is more to databases than phpbb
> backend.

MySLQ has MVCC, if you're willing to give up full text indexing, and
use an engine now owned by Oracle, which may or may not be available
in the future. (i.e. InnoDB). And there are a few things that innodb
has that postgresql doesn't, like predicate locking, which can be
really useful (allows you to refer to things like count(id) in the net
query and be guaranteed that it's right), and really aweful (to do
that you lock EVERY row referenced in the previous count(id)),
depending on your perspective.

> *) Much better shell

I'd disagree. I'd say it's the best damned shell period. :) Having
used a lot of command line db interfaces, nothing touches psql.
Learning it is easy, it's self documenting, and starting with 8.2 the
line buffer goes by query not by line.

And I totally agree with everything you said Merlin. Especially about
the community.