MySQL vs. PostgreSQL, Part 1: Table Organization

I'm going to be starting an occasional series of blog postings comparing MySQL's architecture to PostgreSQL's architecture.  Regular readers of this blog will already be aware that I know PostgreSQL far better than MySQL, having last used MySQL a very long time ago when both products were far less mature than they are today.  So, my discussion of how PostgreSQL works will be based on first-hand knowledge, but discussion of how MySQL works will be based on research and - insofar as I'm can make it happen - discussion with people who know it better than I do.  (Note: If you're a person who knows MySQL better than I do and would like to help me avoid making stupid mistakes, drop me an email.)

Profiling PostgreSQL

I did a little bit of work Tuesday night and Wednesday profiling PostgreSQL.  I ran two different tests.  The first test was designed just to measure the overhead of repeatedly connecting to the database without doing anything, while the second test looked running pgbench with 36 concurrent threads.  The best thing that can happen to you when you fire up the profiler is to have something pop up the profile that you never would have expected.  At least in my experience, when you see what you expect to see, that typically means it's something you've already thought about optimizing, and is therefore probably reasonably efficient already.  When you see something totally unexpected, it's probably something you've never thought about optimizing, and of course the first optimization is always the easiest.

Anyhow, that's what happened to me with the "repeated connections" test.  It turns out that a big chunk of the CPU time was actually being spent during backend exit, rather than (as I had anticipated) backend startup.  We had a check in there to forcibly release any buffer pins that hadn't been cleaned up properly during normal execution.  Originally, this was probably a necessary and valuable check, but we've subsequently added much more thorough and robust cleanup mechanisms which should mean that this code never finds anything to release.  If it does find something, gracefully cleaning up the pin is the wrong idea: we want the code to yell and scream, so that we find and fix the underlying bug.

So, after some discussion with Tom Lane, I ripped this code out and replaced it with some code that will run only in assert-enabled builds (which are typically used only for development and debugging) that will check for leftover buffer pins and fail an assertion if any are found, which will hopefully make it easier to find any current or future bugs in this area.  In non-assert-enabled builds, we no longer do anything at all here (the best kind of optimization!).

Unfortunately, this was the only really surprising thing that popped up in the profiling results.  Further improvements are going to take a bit more work.

Happy Thanksgiving!

Index-Only Scans

There seems to be a lot of interest in the as-yet-unimplemented performance feature called index-only scans, so I thought it would be useful to explain a little bit more about what this feature is, how it will help PostgreSQL, and where the bodies are buried.

First, the name.  What do we mean by an index-only scan?  In PostgreSQL today, an index scan always accesses both the index itself and the underlying table.  You might think this unnecessary.  For example, if you have the query SELECT name FROM table WHERE id = 10, and there is an index on (id, name), you might assume that we could use the index to check for tuples with id = 10, and the if one is found, return the name directly from the index tuple, without consulting the underlying table.  Unfortunately, this does not work, because that tuple might not actually be one that the SELECT statement can see.  If the tuple was inserted by a transaction which began after the SELECT statement took its MVCC snapshot, or deleted by a transaction which committed before the SELECT statement took its MVCC snapshot, then the SELECT statement must not return it.  If it did, we would quickly get very surprising wrong answers out of the database.  So PostgreSQL first looks at the index tuple, and then the heap (table) tuple, decides what the right thing to do is, and does it.  By an index ONLY scan, we mean one which will look at just the index, and not at the corresponding table; the trick is to figure out how to make that happen without returning wrong answers.

Best Patches of 9.1CF3

Back in July, I wrote a blog post on the best patches submitted for the first CommitFest for PostgreSQL 9.1 development (so far, the first two out of the three have been committed).  I didn't end up writing a similar post for the second CommitFest, because there wasn't a lot of stuff that really grabbed my attention, but the third CommitFest is here now, and there are a ton of exciting patches.

When Your Data Isn't Made of Gold

Josh Berkus' recent blog posting on What We Should Be Learning from MySQL, part 2 includes the following quote: "We tend to treat all data in Postgres as if it were made of gold, and not all data is equally valuable."  He goes on to wonder what we can do to better provide for the case where your data isn't made of gold.

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.

PostgreSQL West Talks

As I blogged about before the conference, I gave two talks this year at PostgreSQL West.  The first was a talk on the query optimizer, which I've given before, and the second talk was on using the system catalogs, which was new.  While the second one was well-attended, the first one was packed.  I keep hoping I'll think of something to talk about that people find even more interesting than the query planner, but so far no luck.  Slides for both presentations are now posted; I've added two slides to the system catalogs presentation that weren't there when I gave the talk, but probably should have been.

Nearly all the talks I attended were good.  Some of the best were Greg Smith's talk on Righting Your Writes (slides), Gabrielle Roth's talk on PostgreSQL monitoring tools, and Joe Conway's talk on Building an Open Geospatial Technology Stack (which was actually given in part by Jeff Hamann, who has a company, and a book).  All three of these, and a number of the others, were rich with the sort of anecdotal information that it's hard to get out of the documentation: How exactly do you set this up? How well does it actually work?  What are its best and worst points?

Another memorable talk was Rob Wultsch's talk entitled "MySQL: The Elephant in the Room".  But that talk really deserves a blog post all of its own.  Stay tuned.