Tuesday, March 18, 2014

PostgreSQL Now Has Logical Decoding

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


7 comments:

  1. Robert, FWIW, if there were more pieces there ... like if we had schema change replication ... then I'd be calling LSR the most important feature of 9.4. As it is, it'll be the most important feature of 9.5 (or maybe 10.0, who knows?).

    Anyway, I'm very excited and looking forward to covering changeset extraction, jsonb and materialized views as we promote the new version. Way cool stuff.

    ReplyDelete
    Replies
    1. Although it can't replicate schema changes, it can continue replicating even when the table schema is changed under it in arbitrary ways, something that's hard to achieve with current solutions. I agree that it'll probably take a few more releases for this to really hit its stride, but I also think that the hardest part of the journey is now behind us. And that's pretty exciting.

      Delete
  2. I've been anticipating this with no small amount of glee. Being able to completely discard trigger-based replication will make many a DBA elated beyond comprehension.

    ReplyDelete
  3. Do you know of any tool (slony, bucardo, londiste, etc) that is actually planing to use this logical replication framework as it stands in 9.4 ?

    ReplyDelete
    Replies
    1. AFAICS, Slony developers are adding support for logical replication infrastructure. See http://www.postgresql.org/message-id/BLU0-SMTP30D7892E90D1FF9E4EA77EDC570@phx.gbl

      Delete
  4. This comment has been removed by the author.

    ReplyDelete
  5. 2ndQuadrant has an entire project nearly done for full BiDirectional Replication (Multi-Master) in Postgres, an extension of their streaming replication work. This contribution by Andres was a part of that effort.

    http://wiki.postgresql.org/wiki/BDR_Project

    ReplyDelete