PostgreSQL has come a long way from its early days as a single-node database. If you’ve worked with PostgreSQL for a while, you’ll know its journey to robust logical replication was anything but straightforward, shaped by community efforts, creative workarounds, and an ever-evolving need for high availability and flexibility.
Having been closely involved in projects like londiste, pglogical, multiple iterations of PGD (BDR) and the built-in logical replication in PostgreSQL itself, I’ve had the privilege of witnessing and contributing to this transformation firsthand.
The Early Days: Triggers, Hacks, and Determination
Back in the 2000s, PostgreSQL's philosophy was simple: focus on a single-node experience. High availability (HA)? Not a priority. As a result, there was no built-in replication.
To fill this gap, we saw the rise of trigger-based replication systems:
- Slony, one of the earliest attempts
- Londiste, which I helped develop under the Skype’s skytools suite
- Bucardo, trying to build active-active solution using similar basic ideas of the above two
There were other, less popular solutions as well. These tools were clever but inherently limited. At the same time, physical replication methods like warm standby and hot standby were gaining traction, largely thanks to the efforts of Simon Riggs, paving the way for more robust options.
Enter Logical Decoding and BDR
Building on the vision of Simon Riggs to further improve replication in PostgreSQL, a major breakthrough arrived with logical decoding in PostgreSQL 9.4, enabling developers to extract changes from the WAL stream. This laid the groundwork for:
- BDR 1, an early attempt at active-active replication
- pglogical 1, delivering logical replication as a user-friendly extension
Forks, Plugins, and the Push for Built-in Support
As demand grew, so did the replication tool ecosystem:
- BDR 2 was short-lived; it was focused on removing and replacing features that required PostgreSQL patching. After a few early commits, it was taken to closed source, where BDR/PGD remained until today
- pglogical 2 emerged, building on both pglogical 1 with many new features and UI improvements.
Then came the two real turning points.
First, Peter Eisentraut and I spent around 10 months working on a patch to get logical replication as a feature to upstream PostgreSQL 10. This meant that after a multi-version effort (starting in 9.4 with logical decoding) users could finally use logical replication features without having to use 3rd party extensions.
Second, we decided to do major architectural changes to our own extensions:
- pglogical 3 — a closed-source continuation with a pluggable architecture, enabling replication to external systems like Kafka and RabbitMQ
- BDR 3 — a complete rewrite as a plugin for pglogical 3, initially introducing more active-active friendly features like:
- Robust node management via Raft
- Safe global DDL
- Sequence synchronization
- Consistency modes like CAMO
Going Independent and Widening the Ecosystem
From there, things escalated:
- Later versions of BDR 3 brought features like parallel apply
- BDR 4 became standalone, decoupled from pglogical and brought new durability configuration system called commit scopes
- BDR 5 brought built-in location aware routing, simplifying data flow across nodes
- BDR 6 introduced a built-in connection manager, better Postgres compatibility and many quality of life type of features for easier use
Meanwhile, forks and derivatives popped up:
- Postgres Pro forked pglogical 1 quite early into their active-active replication, which required invasive changes to PostgreSQL in order to work
- pgEdge forked pglogical 2, branded it Spock, and built an active-active solution
- AWS forked early BDR 2 commits (before it went closed source) and launched pgactive on its cloud platform
- Eventually, pgactive was open-sourced, adding yet another flavor to the mix
Built-in Logical Replication Comes of Age
With consistent improvements across releases, PostgreSQL’s native logical replication matured rapidly:
- Support for continuation of replication after failover
- Partitioned table replication and re-partitioning
- Column and row filtering
- Two phase transactions replication support
- Transaction streaming
- Various performance improvements
- UI and monitoring improvements
By version 17, it incorporated enough features to effectively render pglogical 2 obsolete.
Final Thoughts
The journey of logical replication in PostgreSQL has been primarily driven by the needs of its users. From hand-crafted trigger-based systems to pluggable, active-active environments, each iteration brought us closer to true flexibility and high availability.
It’s a story of resilience, collaboration, and relentless innovation, and it’s far from finished.