Past, Present, and Future: Logical Decoding and Replication in PostgreSQL | POSETTE 2026

Hi everyone. My name is Harikiran. Today we are going to track the full arc of logical decoding and replication. We'll go from its earliest building blocks in PostgreSQL 9.4 all the way to the multi-master distributed future that's being actively built right now. Let's dive into past, present, future of logical decoding and PostgreSQL logical replication. This is an important topic. Quick one, who am I? Uh 20 years with the databases and the last 5 years building a PostgreSQL startup. So yeah, I have seen both worlds. Uh I'm the founder of Open Source DB and I love building communities. Hyderabad Pug, Visakhapatnam Pug, and Postgres Women India. I speak at Postgres conferences, teach as an adjunct professor, and I'm a strong open source evangelist. Before this, I had stints at General Electric, Optum, Oracle, Second Quadrant, and EDB. So yeah, I have uh uh looked at uh different ecosystems. Uh so if I sum it up, Postgres community and startups, that's my zone. Now let's look at what we will cover today, right? Here's our roadmap. We will start with the past where logical decoding came from, how extensions like pglogical, pglogical outlook uh shaped the uh ecosystem before native support in PG 10 uh existed. Then we will move into the present looking at native logical replication features from PG 10 through PG 18, including CDC pipelines, real-world deployment patterns. After that, we will look at the future DDL replication, the multi-master architectures, and what the community is building for PG 19 and beyond. And over and throughout, we will do an architecture deep dive into the internals wall, output plugins, and replication slots, so you don't uh just you know look at these features, but how they work under the hood. Let's get started. You would have seen, you know, physical and a logical replication. Now, before we dive into the history, let's make sure uh we are all on, you know, the same page with respect to uh the replication topologies and the architecture. The logical replication actually is and you know how it differs from physical replication. Everybody uh would have seen physical replication, right? It is shipping the raw wall bytes from primary to standby. It's an exact block-level copy, which means standby runs the same major Postgres version. You replicate the replicate the entire cluster, and the standbys are read-only. In contrary to the logical replication, right? It slightly works differently. Instead of copying raw bytes, it streams the row-level changes based on the replication identity, typically the primary key. This gives you some really powerful properties. You can replicate across different Postgres major versions. You can selectively choose which tables and even which columns of rows, you know, thanks to some of the row filtering features that were introduced in the latest releases. And critically, it opens the door to multi-master setups where multiple nodes can accept rights. The important thing to know is that both mechanisms can run concurrently in PostgreSQL. You can have a physical standby for HA. You can have a logical replication for CDC or cross-version migration on the same cluster. They can coexist. And moving on, you know, let's look at the uh logical decoding, you know, how it actually works under the hood. This architecture is fundamental to everything else we discuss in this uh uh uh 20-odd minutes. It starts with the wall. When you set wall level is equal to logical, PostgreSQL starts writing additional information into the wall. That allows it to reconstruct all the logical changes, not just the raw bytes, but the actual row-level inserts, updates, and the uh CRUD operations that are happening on the primary server. That wall data that passes through the logical decoding layer, and this is the engine that reads the raw wall records and translates them into a stream of logical change events. It handles transaction boundaries, the snapshot management, and the ordering guarantees that make uh replication super, super reliable. Net Next comes the output plugin, right? This is the output plugin where the decoding changes get formatted for consumption. Postgres ships this the output plugin called as PG output. And this is the default plugin used to by the native logical replication. But there are alternatives like wall2json which formats the chase changes as JSON which is popular in the CDC pipelines. And test decoding is another more popular plugin which is useful for debugging the changes that are happening on the primary systems. Finally, thanks to the 9.5 feature we had something called as replication slots. This is the persistent mechanism. A slot tracks every wall file and each consume consumer has read up to that wall stream or not. If a consumer disconnects and again reconnects after a planned or an unplanned outage, it picks up right where it left off, right? Slot persists across server restarts, right? Which is the powerful feature that you know some of the planned outages can have, right? But it it's also something you need to monitor carefully, right? Because an active slot will prevent all of your walls to be uh you know not cleaned up which will again you know bloat your storage. We'll come back to that slots aspect in our performance section. At the end of this pipeline sits the subscriber, right? The target logical subscriber. That could be another Postgres instance or it could be an external CDC consumer like Debezium you feeding into Kafka which has been a really important use case in the CDC pipeline ecosystem that we learn. Now, let's look at the past right now. What what what was the foundation that was laid as part and parcel of Postgres 9.5? Now, let's rewind a decade or a decade and a half right Postgres 9.4 was the first release where there were the building blocks for logical replication right now that introduced into the core of the Postgres. And Postgres 9.4 gave us three critical primitives right first replication slots that persist the tracking mechanism I just described about. Second is the replica identity for the table level parameter that controls what information gets written to the wall for updates, deletes, and the CRUD operations. By default Postgres uses primary key. But you can also set it to a full by using the replica identity and no write the entire old row or nothing. And then the third is the logical decoding API itself right the framework that plugs that can use the entire decoded changes and then push it across to the target instance. But here are the things 9.4 gave us right the API but not the complete feature set. There was no built-in create publication. There was no create subscription no publication on the source and subscription on the target side right we always needed an extension like I mentioned PG logical and PG logical output. That's where the extension PG logical came up, right? And then it built the full publish and the subscribe model and you know, on top of the 9.4 logical decoding API. It supported row filtering, conflict detection, and was you know, the technical foundation for the entire bi-directional replication, 1.0, which was a completely open-source extension we had. Right? In 2017, Postgres 10 brought native logical replication into the core with publication subscription syntaxes, right? That's where we say, create publication, create subscription. You know, we will see an example in one of our slides, right? This was powered by the PG output plugin. This feature to a first-class Postgres capability. Now, let's move on and see the evolution from Postgres 10, where we introduced the first native logical replication till the current major version of PG 18. One logical replication, you know, indeed, you know, landed in core of, you know, PG 10, the community just did not stop, right? Every subsequent major release has added meaningful improvements. And this slides gives us the entire timeline, you know, what happened on PG 10, what happened in 13, you know, 14, 15s, and you know, so on and so forth, right? But let's just, you know, double-click on PG 10. It gave us the basic publish and the subscribe model, right? And then PG 11 added the truncate replication. Previously, truncate was uh not captured by some of the logical decode, right? Now, PG 13 was important because it introduced the logical decoding in progress, right? Now, the in progress transactions before that, very large transactions. Now, we are now aware that some of the applications perform large transactions would only be decoded after the commit, which means now there is a million records that are being processed on the source side. Uh you could only look at decoding them and sending across to the logical replica after the commit. Which could uh cause massive lag spikes, right? The real game-changer came in PG 15 and 60, right? 15 gave us row and column filtering uh uh finally. I know you could you could just publish only a subset of the rows, right? Now, with a where clause. Uh and and you know, you would appreciate the fact that you know, off of the entire you know, 10 million table, you want to broadcast only a million or a few hundreds of case of records and choose exactly which columns to replicate. PG 16 added parallel apply, right? Multiple workers processing changes. Currently, uh and you know, concurrently uh binary node of the you know, uh initial table copy, which is significantly faster. And then you know, another important uh uh feature was the failover of replication slots that came in 17, making logical replication viable for HA architectures. And in PG 18, which is in in our last September September 2025 brought us the detail replication support, conflict detection and monitoring, and parallel streaming as the default. Each of these releases, now when we look at some of these blocks, I don't know the foundational blocks from the 9.4 world to the 10 to the 18, right? So, we've been building towards making native logical replication truly production ready, right? And I'd argue that as of PG 18, we're now far more ahead of the curve when it comes to the replication. Now, let's look at some of the 15 and 16 production ready replication, right? Let me zoom in on two important features that I think were the biggest inflection points, right? Row and then the column filtering in PG 15 and the parallel up in, right? And then, you know, most of you all have seen the new gugs that were introduced, right? Max parallel apply workers per subscriptions. Row and column filtering, you know, fundamentally changed what's practical with logical replication. Before PG 15, a publication was all or nothing, right? You publish the entire table. Now, you can write something like, I will create a publication for a specific table called orders where region is equal to APAC, and only APAC religion, I mean, APAC region orders get replicated to the subscribers that are there in place, right? Column list, you know, similarly, you can choose to publish only specific columns. This has huge implications. Now, assuming that you are looking at a multi-tenant architecture, data sovereignty compliance is a very important, you know, feature or the offering that some of the platforms do. And simply reducing the bandwidth and storage cost of replication. If you are running a SaaS platform and you are in need to replicate customer data to regional clusters, this is the feature that makes it, you know, practical without having to reconstruct your entire schema. And then another feature, parallel apply in PG 16, addressed the major bottleneck of the throughput, right? The throughput between the source and the target. Before 16, you know, a single apply worker processed all the changes in a sequential manner, right? If your publisher had a high write rate, the subscriber would fall behind, you know, naturally, you know, because the velocity at which the changes are happening on source versus, you know, how they are applied. With parallel apply, now Postgres can use multiple workers per subscription, right? That's the guc that I was referring to. Applying non-conflicting transactions in a concurrent way, right? Combined with binary mode for the initial table synchronization, which skips the text conversions step, PG 16 dramatically reduced both initial sync and the steady state of the replication lag. And moving on, right? What is What is the, you know, the latest advances that we have made in the PG 17s and 18, right? You know, continuing the momentum. Failover replication slots in PG 17 solved a real operational pain point. Uh before this, if you are primary failed uh over uh to a standby, your logical replication slots were lost, right? Subscribers uh >> [clears throat] >> wouldn't disconnect, uh potentially needed a full resync. PG 17 allows >> [clears throat] >> slots to survive failover, and the new PG create subscriber utility, right, makes it pretty straightforward to convert a physical standby, right? You know, physical standby, uh you don't need any uh uh downtime, per se, right? That physical standby was transformed into a >> [clears throat] >> logical subscriber, which is incredibly useful for a zero-downtime major version upgrade. Now, we'll talk about that zero-downtime upgrades in a moment. And then, in PG 18, the headline for logical replication is the conflict detection and monitoring, right? The new PG stat uh subscription stat views gives you a per subscription counters for different conflict types, the insert conflicts where a row already exists, update conflicts where the origin differs, missing row conflicts. This visibility is essential if you are running any kind of multi-node topology. In PG 18, uh it also lets you replicate stored generated columns, which was a pain point for most of the database uh models. You know, uh when when your subscribers are non-Postgres systems, you know, they can't uh recompute those values and create subscription now defaults to parallel streaming mode, right? So, you get better throughput throughout the you know out of the box without any manual tuning. Now, moving on native versus extension based this is the comparison that you know I wanted to look at, right? So, given all the improvements in core and then I wanted to touch up on extensions like PG logical, Spark, and you know BDR. Where would they fit in, right? But look at some of the advanced features, right? You know DDL replication you know just arrived in PG 18, right? Extensions like you know BDR, PG and Spark extensions have had that auto DDL for a while, right? And conflict resolution in core is still you know basic level of monitoring gives you detection and monitoring but not the sophisticated last writer wins, first writer wins, and so all the conflict handlers that the PG logical and Spark extensions provide, right? And then the big one in PG 18 is the multi-master application, right? Native PostgreSQL still does not support it completely. PG logical has had a partial support. You can set up bi-directional replication with some conflict handling. BDR and Spark offer full multi-master mesh topology, right? Bottom line you know if if you look at the use cases you know they're straightforward, right? You know if if you have a pub sub replication, CDC, or a cross-version migration, right? Native logical replication is now excellent, right? You need multi-master advanced conflict resolution, or automatic DDL synchronization you know in complex topologies extensions still lead you there. Now, let's look at the use cases, right? The change data capture. You know, let's talk about this super super important aspect, right? The CDC. This pipeline is probably the most common production pattern. I don't know. Uh You have Postgres database publishes changes through a replication slot. Those changes are consumed by a CDC pipeline like Debezium. Right? It's a It's a popular option which feeds into a a streaming platform like Kafka. From there, you know, downstream consumers can you know push it to a data warehouse, analytics engines, you know, ClickHouse is is a good example, you know, which can consume those events. Right? Why logical replication is the right right foundation for a CDC, right? It is fast, minimal performance impact, right? Before we were reading from the wall, there's no trigger overhead, right? Your OLTP performance isn't affected by the CDC, right? Secondly, right? Transactional consistency, you know, changes are delivered in commit order. You never see a child record before its parent, right? So, the acid compliance. You will never see a partial transaction. This is the guarantee that polling-based or time-based CDC approaches simply can't provide or you know, it it it cannot give you that compliance. Third is the flexible output formats, right? The PG output plugin gives you an efficient binary format. WalToJSON gives you JSON and you know, easy parsing. You can write even custom output plugins for specialized need. Spock is a good example. And the last one, the zero downtime migration. Now, set up a logical replication to new Postgres version, let it catch up, the cutover lag is zero. Combine it with the pre PG create subscriber, the workflow is now remarkably streamlined. Now, let's look at the the the quick setup that we can do with with with with a simple set of commands. On the publisher, you need three configuration parameters, right? Wall wall level and then you know your max wall senders and the max replication slots, right? The default values are good enough for max replication slots and the wall senders. You got to change only the wall level from uh replica to logical. On the subscriber side, it's a single SQL statement, uh create subscription pointing at the publisher's connection string and the publication name, right? Postgres handles initial table syncs and then you know starts the entire streaming changes from your source to the target. Now, look at the more you know advanced SQL examples there, I know which is 15 PG 15. You can create a publication with row filter like I said, right? You're saying you know region is you know uh specifically US. You want only those matching rows broadcasted and then you know you can specify exactly which operation to publish, whether you need an insert, whether you need a delete or you need any combination of them. And with 18, you can now monitor replication health programmatically by querying PG stat subscription stats. Right? These conflict counters are I mentioned earlier, conflict insert exists, conflict update missing, they're all right there in the system view. Right? Now, let's look at some of the real world patterns that are emerging. Right? Let me quickly walk you through uh four production patterns that you know we have been seeing uh where uh logical replication is making a huge difference. Zero downtime migration updates upgrades. Uh this is a This is probably the most common use case use setup. And in a logical replication for your PG 16 cluster to a PG 80. Once the subscriber catch up, you are good. I don't know you can do the uh upgrade. I don't know uh the PG upgrade brings in that uncertainty, I don't know, in a large database that could be avoided. Multi-region data distribution, I don't know, it it can replicate subsets of your data to regional clusters. And the real-time analytics pipeline, you know, we talked about, you know, the CDC uh uh ecosystem, right? From your OLTP systems through the Debezium Kafka uh of ClickHouse, right? Now, database consolidation uh is is an inwards pattern. Multiple source databases, you know, each published to a single subscriber for centralized uh reporting and analytics, right? So, that's that's that's one uh uh of the uh important stuff I would say. Now, uh uh moving on to the performance tuning and the gotchas that I wanted to, you know, uh uh double-click on, right? On the best practices side, you know, prefer uh logical identity using index or la- la- mean replica identity full, right? On the large tables. Because you are going to be constructing a lot of full rights. Enable uh, parallel apply if you are on PG 16 and later, right? Now, that that uh, the got that I referred to, right? And monitoring the replication lag continuously with PG stat replication. The max slot keep wall size to limit how much of wall and inactive slot can retain. And use binary mode for the initial table copy. It's significantly faster than text mode. Right? And then missing primary keys will break the replication updates and deletes. So, any changes that are that are being happening for the schema needs to be, you know, applied onto your logical replicas. And then long-running transactions on the publishers block slot advances. Now, let's move to the uh, future of it, right? Now, the distributed databases, the multi-master uh, system, right? The holy grail of many Postgres users is, you know, multi-master replication, right? Uh, what you see on, you know, is slide is uh, three Postgres nodes geographically distributed, each accepting both uh, reads and writes with changes flowing through bidirectionally between them. Right? This is active-active replication. The key capabilities needed for this are active-active rights every node handles and the reads and writes simultaneously, right? Automatic conflict resolution where two nodes update the same row concurrently, the system needs a policy. Last writer wins based on timestamps is the most common one we were looking at, right? Who's building it today? You know, PG Edge with their open-source Spock extension provides multi-master replication for community Postgres. EriDB's BDR and you know, the most once in five nines availability and you know, Postgres community itself is contributing foundational pieces, you know, conflict detection in PG 18, the sequence replication, failover slots that are building towards the future in core. Right? And the horizon, I would say, right? What is there in you know, in PG 19 and beyond? Google has been contributing significant work on automatic conflict detection. Sequence replication is maturing. As structured conflict log, a dedicated table for conflict events. Parallel PG dump improvements combined with logical replications will make that initial data sync in in in large migrations really really faster. And the big question uh will active-active replication land in core? The honest answer for me is the community is building towards it incrementally. Each release adds another foundational piece. PG 18 added conflict monitoring. Future releases may add more and more conflict resolution policies. Now, looking at the key takeaways. The key takeaways that you know, I would say are you know, the five important ones. What I have here is uh logical decoding laid the foundation, native replication in PG 10. And extensions like PG logical, Spock, and BDR pushed the boundaries. And then the fourth and then the fine no important pieces are the CDC pipelines and the future of distributed databases. The active active PostgreSQL no that is being built into the open releases. Right? And you know that brings about the last of my slides. Now that's everything I have got for you. Thank you so much for spending this time with me at Post set 2026.