Rob Wultsch's MySQL Talk at PostgreSQL West

I thought this talk deserved a blog post of its own, so here it is.  I have to admit that I approach this topic with some trepidation.  The MySQL vs. PostgreSQL debate is one of those things that people get touchy about.  Still, I'm pleased that not only Rob, but a number of other MySQL community members who I did not get a chance to meet, came to the conference, and it sounds like it will be our community's turn to visit their conference in April of next year.  Rob was kind enough to offer to introduce me to some of the MySQL community members who were there, and I, well, I didn't take him up on it.  That's something I'd like to rectify down the road, but unfortunately this was a very compressed trip for me, and the number of people I had time to talk to and meet with was much less than what I would have liked.

So, on to the talk.  Rob talked about what he views as mischaracterizations of MySQL by PostgreSQL
people; complained about PostgreSQL a bit; and then launched into a discussion of what he sees as the advantages of MySQL over PostgreSQL.  At the end, Josh Drake of Command Prompt took the floor to offer a rebuttal.  In this blog post, I'd like to skip over everything non-technical that was said and focus in on a few technical points.  The non-technical points may be worth discussing as well, but that's not what I want to focus on here.

The main complaint Rob Wultsch levied against PostgreSQL was that, in two words, VACUUM sucks.  I think this complaint is somewhat out of date.  The last few releases have made tremendous progress in this area.  From PostgreSQL 8.3 onward, autovacuum is enabled by default and supports multiple threads; and HOT substantially reduces the amount of bloat that accumulates and must be removed by vacuuming.  From PostgreSQL 8.4 onward, the visibility map greatly reduces vacuum overhead for large, slowly changing tables; automatic freespace management removes an incredibly annoying tunable parameter that could cause vacuum to fail to prevent your database from ballooning out of control; and better snapshot management prevents most sessions left idle in transaction from causing database bloat.  PostgreSQL 9.0 does not have major improvements to VACUUM generally, but did change VACUUM FULL so that it rewrites the table rather than trying to compact it in place, which turns out to be far more performant.

I would not say, nor do I think very many PostgreSQL contributors would say, that all of our VACUUM problems are now solved.  In particular, large, insert-only tables tend to sit there without any VACUUM activity for a long time, and then, after approximately 2 billions transactions have gone by, they get suddenly get vacuumed to prevent something called transaction ID wraparound.  This sometimes causes a sudden, unexpected I/O spike.  This is not necessarily a common scenario (many tables are not insert-only, and not all databases stick around for 2 billion transactions, and some systems have enough spare I/O capacity that that the vacuum is not a big deal when it does kick in, and you can choose the timing of the vacuum by running a manual VACUUM FREEZE yourself instead of waiting for autovacuum to do it) but it would be nice to improve it, probably by moving to 64-bit transaction IDs at some point down the road.  Still, at least from where I sit, the number of people who are having issues with VACUUM appears to be quite small, and an increasing percentage of those injuries appear to be self-inflicted.  When someone reports an issue that appears to be vacuum-related, the first question we ask is usually - have you changed the settings?  And if so, have you considered changing them back?

On to the list of features.  Rob listed the following technical advantages of MySQL: simple replication, no vacuum, rare statistics issues, covering indexes, page level compression, checksums, secondary index buffering.  Because Rob talked about vacuum quite a bit before he even reached this set of slides, I talked about it above.  As to the others, here are my thoughts:

1. Simple replication.  It's actually fairly simple to set up a simple replication configuration in PostgreSQL 9.0, as I discussed in a recent contributed article on Linux.com.  Unfortunately, more complicated configurations are still require fairly close reading of our documentation, and our support for monitoring the status of the replication connection is, quite frankly, terrible.  I have already committed a patch by Fujii Masao that should make this a little better for PostgreSQL 9.1, but I think we need quite a bit more.  Proposals (on pgsql-hackers) are welcome; patches are even more welcome.

2. Rare statistics issues.  I'm simply baffled by this comment, because I've been using PostgreSQL for more than 10 years, and I wasn't aware that it had statistics issues.  I'm sure there Rob has encountered some problem here that he's basing this comment on, but I don't know what it is, so I can't comment intelligently.  Prior to PostgreSQL 8.4, our default_statistics_target parameter defaulted to 10, which sometimes wasn't high enough, but at least in my experience, it still worked well in most cases.  Since PostgreSQL 8.4, the default is 100, which is high enough for nearly everyone (if anything, we could probably ease back a bit and save a bit of performance, especially for small tables).

3. Covering indexes.  In InnoDB, it's apparently possible to obtain partial tuple contents from a secondary index without touching the main table.  I don't quite understand how this works with MVCC, but it's obviously a great performance feature, and we need PostgreSQL to do something similar.   This is pretty much at the top of my list of performance features that we need in PostgreSQL, but unfortunately we've been unable to find the resources to get it done yet.

4. Page-level compression.  Databases are growing faster than main memory sizes are growing (though those seem to be growing quickly too - am I the only one who remembers thinking of 1 GB as a lot of main memory?), so for some workloads it's worth trading CPU time for I/O time.  I had been under the impression that table pages probably would not compress well, but apparently they in fact do, and InnoDB takes advantage of this.  Of course, this feature isn't valuable if your database fits in memory, but many don't, so it would be nice to have this.

5. Checksums.  There is a significant amount of interest in implementing checksums in PostgreSQL, but the problem is vastly complicated by our use of so-called hint bits.  Only in the last week have I really come to understand the trade-off here.  Hint bits are necessitated by the fact that our old row versions are stored in the table itself, rather than in a separate undo log.  The benefit of this is that we can abort transactions very, very quickly, and we can essentially forget about them as fast as we abort them.  InnoDB must keep aborted transaction IDs in each MVCC snapshot until they've been cleaned up.  So, strangely, one of the penalties for efficient aborts is that it's hard to have block checksums.  Obviously, this is a trade-off, and I'm not entirely certain we've come down on the right side of the line.  But at least now I understand what the trade-off is.

6. Secondary index buffering.  The idea here seems to be basically that you batch up a bunch of records that need to be inserted into secondary indexes, and then shove them in all at once.  We do something like this for GIN indexes, and it speeds things up a lot.  However, we don't do it for btree indexes, which are the kind that most people use most of the time.  That's probably something we should consider in the future.

7. Cheap connections.  This is another excellent point.  Our connections are too expensive, and the requirement to use an external connection pooler for large installations is onerous and another possible point of failure and/or performance loss.

8. Query cache.  I'm less convinced that this is a good idea, and I'm not sure I understand how it works in MySQL.  But it's not the first time I've heard this request, so maybe there's something to it.

As was pointed out to me by several people after the talk, to make a really fair comparison between PostgreSQL and MySQL,  you need to list not only the areas where MySQL is ahead of PostgreSQL, but also the other way around, and certainly we have a lot of great features, but this post is already quite long, so I'm not going to list them all here.  The point I take away from this talk, more than anything, is that although PostgreSQL is already a great product, it can still be better, and the good things that other products are doing that we are not doing are good places to start looking.