PostgreSQL 9.6 with Parallel Query vs. TPC-H

I decided to try out parallel query, as implemented in PostgreSQL 9.6devel, on the TPC-H queries.  To do this, I followed the directions at https://github.com/tvondra/pg_tpch - thanks to Tomas Vondra for those instructions.  I did the test on an IBM POWER7 server provided to the PostgreSQL community by IBM.  I scaled the database to use 10GB of input data; the resulting database size was 22GB, of which 8GB was indexes.  I tried out each query just once without really tuning the database at all, except for increasing shared_buffers to 8GB.  Then I tested them again after enabling parallel query by configuring max_parallel_degree = 4.

Parallel Query Is Getting Better And Better

Back in early November, I reported that the first version of parallel sequential scan had been committed to PostgreSQL 9.6.  I'm pleased to report that a number of significant enhancements have been made since then.  Of those, the two that are by the far the most important are that we now support parallel joins and parallel aggregation - which means that the range of queries that can benefit from parallelism is now far broader than just sequential scans.

No More Full-Table Vacuums

I just committed a very important patch to PostgreSQL.  The short summary for the patch is "Don't vacuum all-frozen pages." and it follows up on a patch I committed last week, whose short summary was "Change the format of the VM fork to add a second bit per page."  This led Andres Freund to respond with a one word email: "Yeha!"

PostgreSQL Past, Present, and Future: Moving The Goalposts

It's nice to see that PostgreSQL 9.5 is finally released!  There are a number of blog posts out about that already, not to mention stories in InfoWorld, V3, and a host of other publications.  Of all the publicity, though, I think my favorite piece is a retrospective post by Shaun Thomas reviewing how far PostgreSQL has come over the last five years.  As Shaun notes, both the scalability and the feature set of PostgreSQL have increased enormously over the last five years.  It's easy to miss when you look one release, even (as I do) one commit at a time, but the place where we are now is a whole new world compared to where we were back then.

Parallel Sequential Scan is Committed!

I previously suggested that we might be able to get parallel sequential scan committed to PostgreSQL 9.5.  That did not happen.  However, I'm pleased to report that I've just committed the first version of parallel sequential scan to PostgreSQL's master branch, with a view toward having it included in the upcoming PostgreSQL 9.6 release.

Planning Parallel and Distributed Queries

I have been somewhat lax about blogging for the last six months or so due to having been even busier than usual with various projects, and I think that's likely to continue for at least the next month or two as I work to finish the first version of parallel query for PostgreSQL.  If you have been following the PostgreSQL commit log recently, you will have noticed many new commits building up towards that goal.

However, I wanted to take a minute to point out the presentation that I did yesterday at 2015.pgconf.eu, which I have now uploaded to my presentations web site.  The title of the presentation is "Planning Parallel and Distributed Queries".  If you have not closely followed the development of parallel query, you might find this presentation interesting to review, because it gives examples of the types of query plans I hope that PostgreSQL will be able to generate in the future.

(Everything in the talk represents future work ... and not all of it will be in 9.6!)

PostgreSQL Shutdown

PostgreSQL has three shutdown modes: smart, fast, and immediate.  For many years, the default has been "smart", but Bruce Momjian has just committed a patch to change the default to "fast" for PostgreSQL 9.5.  In my opinion, this is a good thing; I have complained about the current, and agreed with others complaining about it, many times, at least as far back as December of 2010.  Fortunately, we now seem to have now achieved consensus on this change.