PostgreSQL Now Has Logical Decoding

Robert Haas March 18, 2014

A few weeks ago, Josh Berkus wrote a blog post on Why HStore2/jsonb is the most important patch of 9.4.  Everybody's going to have their own opinion on these kinds of questions, but for what it's worth, I think the most important new feature in 9.4 is going to turn out to be logical decoding, the result of a lot of hard work mostly by Andres Freund.

And I've just finish committing the last two patches in that series, for pg_recvlogical and documentation for the whole series of patches, so I'm feeling pretty fired up right now.  (We don't ordinarily commit documentation separately from the main patch, but due to the size of this patch set, I made an exception.)

Just over three years ago, I wrote a blog post entitled The Case For Logical Replication.  The truth of what I wrote in that blog post has not dimmed with time, and now we've got the beginnings of this functionality in PostgreSQL.  I say "the beginnings" because logical replication can be thought of as two distinct features: (1) the ability to efficiently determine which tuples have been inserted, updated, or deleted and (2) the ability to apply those changes to a second copy of the data stored elsewhere.  This patch set, as big and complicated as it is, addresses only the first of those needs - so that the second one will remain, for now, the province of tools outside the PostgreSQL core.

But even that is a big step forward.  There are certainly ways of capturing tuple-level changes in existing releases of PostgreSQL, and there are a number of replication tools for PostgreSQL that do just that.  Typically, that's done by writing the changes to user tables to a separate table which is managed by the replication system, and then reading the data from there to apply to other copies of the database.  This new functionality, however, is more elegant and more performant than anything we have today.  Even though much more work will be needed in order to deliver all the capabilities that users want, I think it's fair to say that we are on our way.

The official documentation build, all nice and pretty, is not up yet.  But if you're in a rush, you can read the raw SGML here:

http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=doc/src/sgml/logicaldecoding.sgml;h=eabdd5f592e6af39048946809a95be2e41473f03;hb=49c0864d7ef5227faa24f903902db90e5c9d5d69

 

Robert Haas

Robert is Chief Architect, Database Server, employed at EnterpriseDB as well as a PostgreSQL Committer. Robert is an expert in OLTP query tuning, schema design, triggers and stored procedures, and internals development, as well as an experienced UNIX/Linux system administrator. Additionally, Robert is a full-life-cycle web developer with skills in needs analysis, application design, database schema design, user interface design, development, testing, user training and acceptance testing, and maintenance.