Clustering, development, and galactic conquest at PGCon 2012

Every year, the PostgreSQL community has a developer meeting in Ottawa called PGCon. This year's PGCon was the largest yet, with 210 contributors and users attending. As usual, though, the most interesting developments happened outside the regular conference sessions. This year's events included new replication and clustering projects, changes to the PostgreSQL development process, and a battle for galactic conquest.

PostgresXC 1.0 beta

There were two major announcements made at the one-day Postgres Clustering Summit before the main conference. The PostgresXC project announced its 1.0 beta release, and 2ndQuadrant announced its plans for a new replication method for PostgreSQL. The Clustering Summit, a meeting of developers working on replication and database clustering for PostgreSQL, was sponsored by NTT Open Source of Japan.

PostgresXC is a project to build a write-scalable cluster for PostgreSQL. A PostgresXC cluster is intended to be a co-located group of four to twenty servers, and is designed to be able to handle larger numbers of database connections and concurrent write queries than a single server. The goals of the project are to build something which satisfies a lot of the same use cases as Oracle Real Application Clusters (RAC), Oracle's database clustering technology. PostgresXC implements a shared-nothing architecture instead of RAC's shared-storage architecture in order to survive a larger variety of hardware failure situations.

As of the 1.0 beta release, PostgresXC supports most PostgreSQL syntax and functionality for a small cluster of database servers so you can theoretically deploy most PostgreSQL-based applications on PostgresXC. Notable limitations with 1.0 beta are the lack of triggers and savepoints, as well as the inability to add new nodes without rebuilding the cluster. These issues will be addressed in 1.1 and later versions.

One of the primary differences between regular PostgreSQL and PostgresXC is the notion of distributed tables. Each table you create can be distributed across the cluster based on round-robin, a hash, or a column value, instead of being replicated to all cluster nodes. This allows the database cluster to support more data than any single node can.

The PostgresXC project primarily consists of developers from NTT Open Source and EnterpriseDB, and it is licensed under The PostgreSQL license. Once PostgresXC is more stable, expect rapid adoption due to the needs of users for bigger, more fault-tolerant database servers.

New "logical" replication project

The second replication announcement was 2ndQuadrant's plan to create a new replication technology for PostgreSQL, which they call "Bi-Directional Replication", mostly to distinguish it from other forms of replication. They have secured sponsorships from companies like McAfee, have begun work on the patches, and plan to get the feature done for PostgreSQL 9.3 in 2013. 2ndQuadrant is a PostgreSQL consulting company headquartered in the United Kingdom, lead by committer Simon Riggs.

At an evening pizza session, they explained how the new replication will work. PostgreSQL's streaming binary replication, introduced in version 9.0, allows a replication server to receive copies of data pages over a normal PostgreSQL database connection (usually port 5432). This connection is called the "walsender" and its associated listener process on the replica is called the "walreceiver" (WAL stands for Write-Ahead Log). This works well for whole-database replication, but does not permit users to replicate only specific parts of the database, or to run any kind of write queries on the replicas. As a result, other types of replication are also needed.

The new feature will allow clients to choose to receive either replicated query statements or changed rows over the same walsender connection, so that listeners can filter or modify the replication stream. More importantly, it would allow a replica to create database changes as well as receiving them, provided that conflicts were handled by database or middleware code, thus implementing one form of multi-master replication for PostgreSQL.

This type of replication configuration is already possible with replication tools such as Slony-I, Londiste, and Bucardo. However, all of those tools suffer from administration and performance overhead which many users find unacceptable because they are implemented in Postgres user space instead of in the Postgres backend. Bi-Directional Replication is expected to be more efficient, and thus useful to a wider array of users. It may even become the underlying data transport mechanism for future versions of the older replication systems.

Bi-Directional Replication is also intended to solve a different use-case than PostgresXC. While PostgresXC is designed to provide scalability and consistency for database writes within a single data center, the new replication is intended to support asynchronous, geographically distributed replication, at the expense of consistency. Such "eventual consistency" systems have become popular among web startups recently, and PostgreSQL users are no exception.

CommitFests and the PostgreSQL development process

One of the other meetings at PGCon was the annual Developer Meeting, at which 20 of the largest code contributors to PostgreSQL meet in person and discuss current and future development of the database. At the Developer Meeting three years ago, the PostgreSQL project adopted the CommitFest model for managing patch review and annual development. At this one, the group reviewed how the CommitFest process was going, and decided to make some minor changes to try to improve it.

The central idea of the CommitFest model is to completely clear out the queue of pending patches every two months, in order to prevent catastrophic levels of unreviewed patch buildup. CommitFests have allowed the project to accept a large number of patch submissions and make sure that everything submitted got a serious review. At the developer meeting, contributor Noah Misch said that the guarantee of code review was one of the things which attracted him to the PostgreSQL project in the first place.

Unfortunately, over the last three releases, the last of the four CommitFests have been getting longer: one month for 9.0, two months for 9.1, and three for 9.2. Contributing to this problem is that the number of patch reviewers has not been growing as fast as the number of submissions, leading to a risk of reviewer burn-out.

The main reason the final CommitFest does not finish on schedule has been large, complex, not-quite-ready patches which need extensive review by core committers. In order to address this, PostgreSQL will add a planning period after the third, or penultimate, CommitFest in order to separate pending patches into large or small, and ready for committers or not quite ready. Then, in the third week of the final CommitFest, the team will be able to do a final triage of what's ready for release and what's not, and conclude the CommitFest after the expected 30 days.

Another cause of contributor pain has been submitting new features without an agreed specification, often causing those patches to need complete recoding. In order to add a more formal process around specification review, the project will be adding a "design specification" section to the CommitFests and encouraging people to submit specifications for review. The developers also adopted a "one patch, one review" policy, which means that patch contributors are obligated to review at least one patch contributed by someone else.

Conquering the galaxy with PostgreSQL

As a new event this year, PGCon included a Schemaverse tournament. Schemaverse is a text-mode galactic conquest wargame, played entirely in a PostgreSQL database. Users issue commands to build and move spaceships, engage in battles, and conquer and mine planets, all in the form of SQL queries. You win the game either by conquering the most planets, or by successfully hacking the database.

Schemaverse was invented by Josh McDougall as a fun data security exercise for the DEFCON 2011 security conference. Notably, no player at DEFCON, PGCon, nor in the open online version has yet managed to win by hacking the PostgreSQL database. The game also demonstrates how PostgreSQL allows sophisticated application functionality to be built either inside or outside the database.

The PGCon tournament, which ran for the entirety of the conference, was won by Chris Browne of the domain registrar Afilias. Chris spent weeks leading up to PGCon honing automated "artificial intelligence" scripts to control his fleet of spaceships, and rapidly seized over 500 planets. He won several prizes including a championship sweatshirt.

Other conference highlights

Peter van Hardenberg, database lead at platform-as-a-service company Heroku, delivered the keynote for PGCon. His talk was about Heroku's users, the cloud, NoSQL, and the future of PostgreSQL. First he discussed the large number of PostgreSQL databases (1.6 million) belonging to Heroku's hundreds of thousands of users, who are mostly small-team agile-development web application developers. Van Hardenberg appealed to the PostgreSQL developers to keep Heroku's users in mind when working on PostgreSQL and new features, particularly emphasizing the need for usability improvements.

In case studies at the conference NASA scientists talked about using Postgres for data collection on the Aura spacecraft for ozone monitoring. 2ndQuadrant staff presented migrating top website Fotolog from MySQL to PostgreSQL. A contributor to open source library management system Evergreen demonstrated their application and explored its data architecture. And Mozilla staff talked about the Socorro crash collection system.

Other talks focused on many of the features and internals of PostgreSQL 9.2, currently in beta. This included Range Types, the new replication protocol, and the internals of how many of the performance improvements were implemented. As they did last year, PostgreSQL's Russian development team introduced another new indexing idea, this time for similarity searching.

PGCon 2012 was a busy, intense week for PostgreSQL developers. Users can expect a lot more great things coming out of the project because of it. If your job centers around PostgreSQL, you should consider attending next year. Later this year, you can attend Postgres Open, a business and user-oriented conference in Chicago, or PostgreSQL Europe in Prague.