PGCon2010 - Final Release III

PGCon 2010
The PostgreSQL Conference


Chris Bohn

Embedded Messaging with PgMQ, the PostgreSQL Message Queueing add-on

PgMQ embeds messaging directly into PostgreSQL so that committed transactions can be published to message queues via various popular messaging protocols (AMQP, STOMP, OpenWire). Supports ActiveMQ (STOMP, OpenWire) and any transport supporting AMQP (such as RabbitMQ). PgMQ easily enables "eventually consistent" replication and/or sharding along customized data boundaries. PgMQ also introduces an index extension that enhances temporal data types (timestamp, date, etc.) by firing an event (trigger) when the value is equal to current_time, an aid to replication and partitioning. This lecture will show how to set up and configure PGMQ, with realtime examples.

PgMQ (PostgreSQL Message Queueing) is an add-on that embeds a messaging client inside PostgreSQL. It supports the AMQP, STOMP and OpenWire messaging protocols, meaning that it can work with all of the major messaging systems such as ActiveMQ and RabbitMQ. PgMQ enables two replication capabilities: "Eventually Consistent" Replication and sharding. PgMQ has been developed at, a top 50 internet site, as a solution to its own replication challenges.

"Eventually Consistent" Replication means that slaves are not guaranteed to be in sync with the master at any moment in time because of latency in the replication process. However, the data is guaranteed to arrive and eventually (in practice, a very short time) the slave and master are in sync. For many applications, this is acceptable. With PgMQ, "Eventually Consistent" replication is easily done by publishing data commits (insert/update/delete) to a message queue to which slave subscribes. In this scenario, the slave is "eventually consistent" with the master. Most replicated data can be probably work with an "Eventually Consistent" model.

PgMQ operates much like a per-row "after" trigger: committed tuples (insert/update/delete) are published to a message queue. Configuration is very easily done, and PgMQ has granularity such that commits on any table can be published to any number of queues, in any of the supported protocols. This enables master data to be easily be "sharded" to various slaves, simply by publishing the sharded data to different queues, to which the sharded data servers subscribe.

The presenter is the principal author of PgMQ, and a database engineer at The presentation will give a brief technical description of PgMQ, then cover how PgMQ can be used to set up "Eventually Consistent" replication. Real examples from the experience of using PgMQ in a production environment will be used. This will be a very good presentation for anyone who has faced the issue of replication with PostgreSQL.


cb at pgmq dot org