Monday, January 24, 2011

PostgreSQL 9.1: Neat Stuff is Coming

Up until the last month or so, the development arc for PostgreSQL 9.1 was looking fairly humdrum.   Quite a bit of good refactoring and some good general enhancements, but not a lot of sizzle.  That seems to be changing now.  Of course, in one sense, that's a bad thing: we're only three weeks from the end of the development cycle, and ideally it would have been nicer to have some of these big patches land just a little bit sooner.  Still, I feel pretty good about the quality of what's been committed.

In my previous post on big patches, I listed nine large patches that were in the works for 9.1.  We've made significant progress on several of those patches: synchronous replication seems to be coming on well, and we're moving toward a commit-quality patch with broad consensus.  I have committed a patch implementing basic SE-Linux integration from KaiGai Kohei, which is still a little rough around the edges, but I think we can file down the worst of those problems over the next few weeks.  Some of the more serious architectural restrictions will need to wait for PostgreSQL 9.2 and beyond, but since this has been in the works for more than two and a half years, what's there now represents very good and concrete progress.  Unfortunately, but not surprisingly, we've had to give up on MERGE.  Review of Shigeru Hanada's work on SQL/MED is continuing, and it seems likely that this will also make it in to 9.1.

As expected, there are also a few new major pieces of work that have materialized at the last minute.  Jeff Davis has submitted his very interesting work on range types, which, when complete, will allow handling of many interesting cases that are difficult to manage efficiently now, especially in regards to temporal data.  Noah Misch has submitted a flock of patches that aim to reduce the number of table rewrites and index rebuilds that are required by ALTER TABLE.  For example, a change from type varchar to type text in PostgreSQL is purely cosmetic, so in theory we ought to be able to avoid rewriting the whole table when that operation is requested.  This is great work, but like Jeff's patches, parts of it may need more review and polishing than we have time to give it this release cycle.  Finally, Magnus Hagander has launched into a frenzy of very welcome work on simplifying base backups.

Even though the shape of the final release is not yet quite clear, I'm starting to feel pretty excited about it.  Although synchronous replication, SQL/MED, and SE-Linux integration are all multi-year projects, and the implementations in PostgreSQL 9.1 will be basic, all of them open up new territory for PostgreSQL, providing us with really novel capabilities that we simply haven't had up until now.  To some degree, the same thing can be said about unlogged tables, though that's not really a multi-year project: it's pretty much done.  Equally important (though a bit off-topic for this particular blog post), this release will feature the usual host of usability improvements and performance tweaks to features added in past releases.

I think it's going to be a good release.  Stay tuned!


  1. Did you improve the bulk_update and bulk_delete performance problems in db?

  2. PostgreSQL has no such operations.

  3. No, i mean when i try to update or delete more than 1000 records then it takes too much time. Maybe it's because of my configuration.

  4. About big new features, will PostgreSQL ever consider using multiple cores for a single query?

    I often have single complicated queries that takes minutes to run, eating up all the CPU power of a single core, will my other 23 cores (dual 12 core machine) are sitting completely idle.

  5. @Anonymous: DELETEs are normally really fast in PostgreSQL; and updating 1000 records is not normally a problem either. You could post details (ideally with EXPLAIN ANALYZE output) on pgsql-performance for more help.

    @Robert Whane: I imagine we'll get that feature eventually, but I don't think it'll be soon, unless someone has some serious money to throw at sponsored development.