write scalability for UPDATE operations

Yesterday, Heikki Linnakangas committed this patch:

commit a3115f0d9ec1ac93b82156535dc00b10172a4fe7
Author: Heikki Linnakangas
Date:   Wed Mar 12 22:46:04 2014 +0200

    Only WAL-log the modified portion in an UPDATE, if possible.
   
    When a row is updated, and the new tuple version is put on the same page as
    the old one, only WAL-log the part of the new tuple that's not identical to
    the old. This saves significantly on the amount of WAL that needs to be
    written, in the common case that most fields are not modified.
   
    Amit Kapila, with a lot of back and forth with me, Robert Haas, and others.

This patch is the result of a lot of work, and a lot of testing, principally by Amit Kapila, but as the commit message says, also by Heikki, myself, and others.  So, how much does it help?

Linux's fsync() woes are getting some attention

In two weeks, I'm headed to LSF/MM and the Linux Collaboration Summit, by invitation of some Linux kernel hackers, to discuss how the Linux kernel can better interoperate with PostgreSQL.  This is good news for PostgreSQL, and hopefully for Linux as well.  A post from Mel Gorman indicates that this topic is attracting a lot of interest, and that MariaDB and MySQL developers have now been invited to participate as well.  His summary of the discussion so far quotes some blunt words from one of my posts:

 IMHO, the problem is simpler than that: no single process should
 be allowed to completely screw over every other process on the
 system.  When the checkpointer process starts calling fsync(), the
 system begins writing out the data that needs to be fsync()'d so
 aggressively that service times for I/O requests from other process
 go through the roof.  It's difficult for me to imagine that any
 application on any I/O scheduler is ever happy with that behavior.
 We shouldn't need to sprinkle of fsync() calls with special magic
 juju sauce that says "hey, when you do this, could you try to avoid
 causing the rest of the system to COMPLETELY GRIND TO A HALT?".
 That should be the *default* behavior, if not the *only* behavior. 

VACUUM FULL doesn't mean "VACUUM, but better"

There's a persistent belief among some users of PostgreSQL that VACUUM and VACUUM FULL do the same thing, but that VACUUM FULL does it better.  If VACUUM is the moral equivalent of running the Dust Buster across the room a few times, VACUUM FULL must be the equivalent of hiring a professional cleaning crew to shampoo the carpets, and maybe repaint the walls as well.  Unfortunately, this mental model is not accurate.

Query Planning Gone Wrong: The Video

If you missed my talk on Query Planning Gone Wrong at PGCon and Postgres Open, but you'd still like to hear it, videos of both versions of the presentation are available on YouTube.  Here's me giving the talk the first time, at PGCon, and here I am giving it again, at PG Open.

Parallelism Progress

For the last several months, I have been spending a large percentage of my time trying to bring parallelism to PostgreSQL.  Previous blog posts on the future direction of PostgreSQL development have often mentioned this as a priority, although the top spot has usually been reserved for materialized views, a feature which now exists in PostgreSQL 9.3 and which has been improved in PostgreSQL 9.4.  My colleague at EnterpriseDB, Kevin Grittner, is continuing to work on further improvements in that area.  But my focus is on parallelism.  So, how's that going?

MVCC Catalog Access

Some wag, riffing on Rudyard Kipling, once wrote that "if you can keep your head when all around you are losing theirs, maybe you just don't understand the situation".  I thought of that line this morning, while committing a patch to use MVCC snapshots for system catalog access.

Query Planning Gone Wrong

Over the past few years, I've been making notes on pgsql-performance postings, specifically those postings which relate to query performance issues.  Today, I gave a talk at PGCon on the data I've been able to gather.

If you attended the talk, please leave feedback through the PGCon web site or feel free to leave a comment below with your thoughts.  If not, you can find the slides on my Presentations web page.  A few people asked me to post the raw data on which the talk was based, including links to the original threads.  I have created a Query Performance section on my Google Site and posted the information there.

The version posted on the web site incorporates a few minor corrections as compared to what I presented in the talk; and I have left out (for the sake of politeness) the cases I attributed to user error.  There were actually only 2 such cases, not 3 as I said in the talk, but either way it seems more polite not to post specific links.  Please contact me if you find other mistakes in what I have posted and I will correct them.

Many thanks to all those who said nice things about my talk!