Logical Replication CDC From Postgres to Cloud Warehouses

Postgres runs more production workloads than any other database among professional developers today, with adoption above 55% in recent surveys of the field. That scale matters here because the way Postgres exposes its changes to the outside world, through logical replication, has become the default path for getting data out of an operational database and into a warehouse without waiting on a batch job. Financial services platforms, real-time analytics teams, and AI pipelines all share the same complaint about the old way of doing things: downstream systems need to know about a change the moment it commits, not three hours later when the nightly load finally runs.

Batch pipelines make that lag concrete in a way that's easy to underestimate. An overnight batch data pipeline means an analyst opens a dashboard at 9 a.m. and reconciles numbers that are already stale against whatever happened in the six hours since the extract ran. A fraud model trained on data that's hours old isn't catching fraud, it's writing a report about fraud that already happened. Logical replication closes that gap because it doesn't invent new work for Postgres to do. The database already writes every committed change to a durable log before it touches a single table. Change data capture just reads that log.

What the WAL contains and how logical decoding turns it into a change stream

The database's crash-recovery log exists for crash recovery, not for CDC. Postgres records every change to the data files before it applies that change, so if the server dies mid-write, it can replay the log and get back to a consistent state. That's the whole reason WAL exists, and it existed decades before anyone used it to feed a warehouse.

Logical decoding is what turns that recovery mechanism into something a downstream system can consume. Raw WAL is physical: byte offsets, disk blocks, page numbers, none of it means anything outside the context of that one specific Postgres instance. Logical decoding takes that physical record and reconstructs it as row-level events instead, so what comes out the other end reads more like "row 4471 in the orders table was updated, here is the new value for status." That distinction is what makes the output portable. Because it describes rows and columns rather than disk addresses, the same stream of changes can go to another Postgres instance, to Kafka, to a warehouse, or to a purpose-built CDC platform.

The mechanics run on a publish-and-subscribe model that will look familiar to anyone who's worked with message queues. On the source side, a publication declares which tables get exposed to replication. On the consuming side, a subscription (or any client speaking the replication protocol) pulls from that publication. The first time a subscription connects, Postgres takes a snapshot of the published tables and copies that data over as a baseline, then switches to streaming ongoing changes as they commit. Order is preserved throughout: changes arrive at the subscriber in the same commit order they occurred on the publisher, so a downstream consumer never sees an update before the insert that created the row.

Minimum configuration to enable logical replication on a Postgres instance

Turning this on requires exactly one mandatory setting: wal_level = logical in postgresql.conf. Everything else downstream depends on this flag, because Postgres writes a leaner form of WAL by default that doesn't carry enough information to reconstruct row-level changes. Changing it demands a full server restart. That restart is the one unavoidable moment of downtime in standing up logical replication, so it should be planned around rather than discovered during a maintenance window that wasn't scheduled for it.

Three more parameters need sizing before the setup holds up in production. max_replication_slots should be set to cover the number of subscribers actually connecting plus spares held in reserve for failover, since every active subscription claims one slot. max_wal_senders needs to be at least as large as max_replication_slots, never smaller, because each active slot needs a WAL sender process to serve it. And max_slot_wal_keep_size puts a ceiling on how much WAL a lagging slot is allowed to pin before Postgres cuts it off; PlanetScale's guidance is to start above 4 GB and tune from there based on actual write volume and available disk.

With the server configured, two objects need to exist. A publication on the source declares the tables in scope:

CREATE PUBLICATION my_pub FOR TABLE orders, customers;

And a subscription on the consumer side points at that publication using a connection string. A two-table replica set up this way needs nothing beyond the pgoutput plugin that ships with Postgres itself, no extension, no third-party module. A dedicated role with the REPLICATION attribute should be created for this traffic rather than routing it through the superuser account, since a compromised or misbehaving replication client shouldn't inherit full administrative rights on the database it's reading from.

Replication slots: the mechanism that guarantees delivery and the risk that comes with it

A replication slot is Postgres's bookmark. It records how far a given consumer has read in the WAL stream, and Postgres will not recycle or delete any WAL segment until that consumer has confirmed it read past it. That guarantee is what makes CDC durable: if a connector crashes and restarts an hour later, it resumes from precisely where it left off instead of forcing a full re-sync of the source tables.

The same mechanism that provides that guarantee is also the thing that can quietly take a production server down. If a consumer disconnects, stalls, or falls far enough behind, Postgres has no choice but to keep every WAL segment since that slot's last confirmed position, because the contract says it can't discard anything the slot hasn't consumed yet. An inactive slot acts like an anchor: it blocks WAL cleanup even while every other, healthy replica reading from the same database is keeping up just fine. A test consumer that got spun up once, connected, and was never cleanly torn down can sit there indefinitely, and disk fills up at the rate the database is generating WAL, which on a busy write-heavy system can be fast enough to become an incident within hours rather than days.

Watching for this doesn't require anything exotic. The pg_replication_slots view exposes slot_name, active, wal_status, confirmed_flush_lsn, and inactive_since, which together tell an operator whether a slot is being read from and how long it's been sitting idle. Dropping an abandoned one is a single call:

SELECT pg_drop_replication_slot('slot_name');

Nobody enjoys running that command under pressure at 2 a.m. with a disk alert firing. Building slot monitoring into routine checks, rather than treating it as an edge case, separates a minor cleanup task from a production incident.

Production hazards that aren't about slots: schema changes, REPLICA IDENTITY, and TOAST

Postgres logical replication does not replicate DDL, a hard boundary rather than a configuration option to flip. Postgres logical replication does not replicate DDL, a hard boundary rather than a configuration option to flip. An ALTER TABLE or a new index applied on the source has to be applied to the destination manually, and the order matters: adding a column on the subscriber before it appears on the source avoids a situation where incoming rows carry a column the subscriber doesn't know about yet. Get that order backwards and the replication worker hits a row it can't map to a destination column.

When that mismatch happens, the worker doesn't skip the bad transaction and move on. It pauses, and it stays paused until someone intervenes. Meanwhile the slot stays pinned at the exact point of the failure. WAL keeps accumulating on the source for as long as the mismatch goes unresolved, tying this hazard directly back to the disk-pressure risk slots already carry on their own.

REPLICA IDENTITY decides what Postgres actually writes to the WAL when a row is updated or deleted. The default setting uses the table's primary key to identify which row changed, which keeps WAL overhead low because Postgres doesn't need to record the row's full previous state. Tables without a primary key break this entirely: updates and deletes on them fail to replicate unless REPLICA IDENTITY is set to FULL. FULL solves the problem by writing the entire previous row image into the WAL on every change, but that's real overhead on a high-write table, so the setting should be reserved for tables that genuinely lack a usable unique key rather than applied everywhere as a default precaution.

TOAST adds a subtler version of the same problem. Postgres stores unusually large column values, long text fields, big JSON blobs, out of the main row and calls this The Oversized Attribute Storage Technique. When an update touches other columns in a row but leaves a TOASTed column untouched, Postgres may leave that column out of the WAL record entirely, on the theory that it hasn't changed and doesn't need to be resent. Without REPLICA IDENTITY FULL, a consumer receiving that update can end up seeing a null or missing value for a large column that was never actually touched, simply because the WAL record didn't carry it. Some CDC tools work around this without requiring FULL at all: Estuary Flow, for instance, applies merge logic that carries forward the last known value for an untouched TOASTed column, which removes the need to pay the REPLICA IDENTITY FULL overhead purely to solve this one issue.

The full hop sequence a CDC event travels from Postgres WAL to a cloud warehouse

Three architectural patterns cover most of how this actually gets built, and they trade off latency against operational complexity in different ways.

A direct connector reads straight from the replication slot and writes straight to the destination, with a managed CDC service handling schema mapping and delivery in between. This is the fewest moving parts option: no intermediate message broker, no separate infrastructure to run, and schema evolution is typically handled by the tool rather than by hand.

A Kafka-mediated pipeline adds a layer in the middle: Debezium decodes the WAL and publishes row-level events onto Kafka topics, and a sink connector, a Snowflake Kafka Connector or a BigQuery Kafka Connector, reads those topics and loads them into the warehouse. This costs more in latency and in operational surface area, since now there's a Kafka cluster to run and monitor alongside everything else. What it buys in exchange is a genuinely real-time, decoupled stream that multiple downstream consumers can read from independently, something a batch-oriented tool structurally can't offer.

The third pattern skips managed infrastructure entirely: application code speaks the logical replication protocol directly over a libpq connection in replication mode. This suits narrow, latency-sensitive jobs, invalidating a cache, updating a search index, writing an audit log, where standing up a full CDC platform would be overkill for what's really a single, specific consumer.

Whichever pattern is in play, the sequence starts the same way. The first time a subscription connects, Postgres takes a consistent snapshot of the source tables and copies that snapshot to the destination, establishing the baseline everything after it builds on. Only once that snapshot lands does the incremental phase begin, streaming inserts, updates, and deletes in the exact order they committed on the source.

What happens when those events land varies by destination. Upsert or merge is the most common pattern: the connector translates each row change into a MERGE statement or its equivalent, which requires the destination table to have a stable primary key to merge against. Append-only ingestion takes a simpler path, writing every change event as a new row and pushing deduplication onto query time, which trades cheaper, simpler writes for more expensive reads later. A third pattern, incremental deduplication done as a batch step rather than at query time, has shown measurable payoff: incremental dedup mode has been documented to consume 55% less BigQuery slot time compared to full refresh per day, a number that changes a warehouse bill materially at scale.

Matching the right CDC tool to each Postgres-to-warehouse destination pair

The right tool depends heavily on which warehouse sits on the other end, and on how much of the surrounding cloud stack a team is already committed to.

For Postgres into BigQuery, Google's own Datastream is the natural choice for teams already deep in the GCP ecosystem, since it integrates tightly with IAM and doesn't require standing up separate credential management. Teams with less infrastructure tied to a particular cloud provider often lean on managed, cloud-hosted CDC connectors that handle schema normalization automatically, or on self-hosted open-source options for teams that want direct control over the pipeline.

For Postgres into Redshift, AWS DMS (Database Migration Service) is the obvious fit for teams already running inside an AWS account, since it plugs into the same IAM and networking model everything else is already using. DMS can encounter performance constraints on high-velocity change streams, a known consideration when sizing replication instances. More importantly, if replication slots aren't managed carefully alongside it, WAL files can accumulate without bound on the source, which is the same slot-pinning hazard described earlier, just arriving through a specific, well-known integration path.

For Postgres into ClickHouse, the tooling has consolidated somewhat since the company behind ClickHouse. acquired PeerDB in July 2024 and folded its capabilities into ClickPipes, the company's managed ingestion service for ClickHouse Cloud. ClickPipes offers native Postgres source support built on that underlying logical decoding technology, packaged as a managed offering rather than something a team has to operate and monitor itself. For a destination pair that used to require piecing together a message-queue-based path or a custom decoder, that's a meaningfully shorter route from a Postgres WAL to a queryable ClickHouse table.

None of these choices erase the underlying mechanics covered above. Every one of these tools is still reading a replication slot, still bound by whatever REPLICA IDENTITY is set on the source tables, still exposed to the same WAL bloat risk if a slot goes stale. Picking the right destination connector matters, but it doesn't substitute for understanding what's actually happening upstream in Postgres itself.