Community Query: DML Change Tracking with PostgreSQL

February 26, 2014

EnterpriseDB decided to put me on the spot recently by inviting queries from the community via social channels as I prepared for teaching a new PostgreSQL course. It’s a familiar spot for me. I regularly field questions about PostgreSQL given my role in the community and I was happy to get a question through Twitter.

The question, which came from a Twitter user in Sweden, was: "Is native lightweight data change tracking on the roadmap? (Similar to what MSSQL is offering today.)"

MS SQL Server enables applications to determine DML changes (insert, update and delete) operations through change tracking and change data capture. In PostgreSQL, we have three solutions related to DML change tracking.

The first is streaming replication, which ships a binary change log to a standby server. However, since it is binary, it isn't easy to process externally. Second is Slony, which uses triggers to accumulate DML changes into tables that are periodically shipped to standby servers. Slony allows per-table replication.

Finally, the third option, which the community is working on, is logical changeset logging that can be used by any application wanting to track database changes. This will be used eventually to implement more fine-grained replication solutions.  The following link will take you to an overview of the community PostgreSQL project, which should be completed in the next few years though the logical changeset part may be done much sooner:

Hope this answers the question. If you have others, please reach out to us via Twitter, Facebook or Google+. Find the buttons for these social outlets, and LinkedIn, on our homepage:

Bruce Momjian is a Senior Database Architect at EnterpriseDB and co-founder of the PostgreSQL Global Development Group.

Share this