Snowflake Continuous Replication From Postgres

Nightly batch jobs mean the numbers on a dashboard are, by definition, old news by the time anyone reads them. A support rep pulls up an order and sees a status that hasn't reflected the warehouse scan from six hours ago. This isn't a minor cosmetic lag: it's the direct, mechanical result of how nightly syncs work, and it degrades every downstream decision that depends on the data being current.

Three failure modes get worse as the data volume grows. Full or incremental extracts hit the production database exactly when the business needs that database to be fast, because nightly jobs tend to cluster around the same low-traffic windows everyone else's batch jobs use. And deletes vanish without a trace: a row that no longer exists cannot be selected by any query, so a customer who deletes their account or a product that gets discontinued just silently persists downstream until someone notices the count doesn't add up.

The costs that show up on an invoice, ETL licensing, pipeline compute, connector fees, are the smallest part of the bill. Data inconsistencies produce these effects: they quietly erode trust in reporting, they create governance risk when regulated data drifts between systems, and they burn engineering hours on maintenance and delayed decisions from stale data. More data, more teams, and more demand for information that's actually fresh follow from that growth.

The stakes change again once AI agents enter the picture. A nightly sync turns staleness from an inconvenience into an active liability: a customer who updates their billing address at 2pm and asks an AI agent about it at 2:05pm should get a current answer, not yesterday's snapshot. An agent that confidently repeats stale data is wrong in a way that looks authoritative. That's the structural mistake: treating replication as an overnight chore when the systems consuming that data now operate in real time.

Continuous replication capabilities already built into Postgres

None of this requires bolting some exotic new subsystem onto Postgres. The raw material for continuous replication already exists inside every running instance, because Postgres has to write it anyway to protect itself from crashing.

Every data modification, an INSERT, an UPDATE, a DELETE, even a schema change, gets written to the Write-Ahead Log before it ever touches the heap, the actual table data on disk. That ordering is not incidental. A transaction starts when a client issues a query, the changes get made in memory against the shared buffers, and then a WAL record describing what changed is written to disk as a fast, sequential append. The synchronous_commit setting determines when that record is flushed, and only then does Postgres mark the transaction committed. If the server crashes a millisecond later, it replays the WAL on restart and rebuilds what should have happened.

Nobody designed it as a replication feed. But its structure, sequential, append-only, a complete and ordered record of everything that happened to the database, makes it an almost perfect source of truth for change capture. Every insert, update, and delete the database has ever processed sits there in order, waiting to be read.

Raw WAL is not designed to be read by anything other than Postgres itself. A WAL record expresses something like "a particular data page changed," in terms of physical storage: block numbers, byte offsets, internal tuple formats. It does not say "the price of product 42 changed to 99." That's a meaningful, row-level, business fact, and there's a real gap between what the WAL physically contains and what any external system needs in order to act on a change. Closing that gap is what the next layer of Postgres is built to do.

Logical decoding: translating raw WAL bytes into row-level change events

Logical decoding is the subsystem that closes that gap. It takes the raw bytes sitting in the WAL and turns them into structured, row-level change events that something outside Postgres can actually read and act on.

It works through a plugin interface. Postgres itself handles the hard, low-level work of parsing WAL records, and a plugin sits on top, deciding how to format each change and hand it off. The plugin most production CDC tooling relies on is pgoutput, which has been built directly into PostgreSQL since version 10 and is maintained as part of the core project, so there's no separate extension to compile or install. Critically, pgoutput only emits a change after the transaction that made it has committed. A rolled-back transaction never appears downstream, and a consumer never sees a partial, half-finished transaction hanging in the middle of a stream.

Which changes get decoded is governed by a publication, a server-side declaration of exactly which tables and which operation types, INSERT, UPDATE, DELETE, are in scope. This is deliberately fine-grained: a team can publish only the handful of tables that actually feed a dashboard, which keeps the WAL traffic and the noise a consumer has to filter down to a minimum.

The replication slot is what makes all of this reliable across restarts and network blips. A slot is Postgres's own bookmark for a given consumer: it points at a specific Log Sequence Number and tells the server how much WAL history it still needs to keep around. Internally it tracks two watermarks, restart_lsn, the earliest position the slot might still need to replay, and confirmed_flush_lsn, the point the consumer has explicitly acknowledged as safely processed. That bookmark is what lets a CDC connector disconnect, come back an hour later, and resume from exactly where it left off, without losing a single change in between. It's also strictly one slot per consumer; slots cannot be shared across two independent readers.

The replication slot is the most dangerous artifact in your Postgres CDC setup

A slot goes inactive at 3:47 AM after a transient network partition. The connector tries to restart, but cannot reconnect. Postgres keeps accumulating WAL from the slot's last confirmed position, because the slot's entire job is to guarantee nothing gets discarded before the consumer confirms it. Eventually max_slot_wal_keep_size kicks in, and Postgres drops the slot outright to protect the disk from filling up. The connector finally reconnects, finds no slot waiting for it, and exits with an error. Nobody notices until 11 a.m., when an analyst files a ticket because three metric dimensions on the revenue dashboard haven't moved since midnight.

That scenario is structural. It's structural. An inactive replication slot acts as an anchor: it blocks WAL cleanup on the entire server, even while every other healthy replica is consuming changes just fine. The very property that makes a slot trustworthy, its promise to retain WAL until the consumer confirms it's safe to let go, is precisely what turns dangerous the moment that consumer vanishes. A slot doesn't fail quietly. It fails by threatening to fill the disk, or it gets killed to prevent that, and either way something breaks downstream.

Treating replication slot lag as a first-class operational alert, not a background metric, is the only sane response. Watching pg_replication_slots and paging someone before disk usage climbs into dangerous territory costs nothing compared to the alternative. max_slot_wal_keep_size deserves a deliberate value chosen for the workload. Fine-grained publications help here too, since limiting a slot to only the tables it actually needs to track reduces the total volume of WAL it has to hold onto. Configuring a heartbeat so the slot stays warm through quiet overnight windows, when there might otherwise be no write activity to keep it current, closes another common gap.

Postgres 18, released September 25, 2025, adds a purpose-built answer to part of this problem: idle_replication_slot_timeout, a time-based setting that invalidates a slot after a configurable stretch of inactivity. Setting that window to a few days lets a slot survive a brief outage without risk, while a connector that's truly gone for good stops silently eating disk space forever. On the high-availability side, PostgreSQL 17 adds opt-in failover slot synchronization, building on PG16's minimal logical decoding on standbys, so a slot configured with failover=true can persist through failovers. Aurora Postgres does the same for its own failovers, preserving logical replication slots automatically. It's worth knowing, too, that Aurora Postgres does not support logical replication from read replicas at all, which matters for any team hoping to offload CDC traffic off the primary. RDS handles that case better: Postgres 16 and later on RDS supports reading WAL directly from a read replica, which takes the performance question off the primary entirely.

Managed Postgres environments: what changes about CDC configuration on RDS, Aurora, Cloud SQL, and Supabase

Almost nobody runs bare-metal Postgres anymore, and every managed provider adds its own layer of configuration on top of the mechanics above. Logical replication is never on by default; it has to be turned on explicitly, regardless of which platform is hosting the database. On RDS and Aurora that means setting rds.logical_replication=1 before anything else works. Cloud SQL, Supabase, Neon, CrunchyData, and PlanetScale all support logical replication too, each with its own specific configuration steps to get there.

TOAST columns: Postgres stores large values (large text fields, JSONB blobs) in separate TOAST tables, and CDC tools must handle TOAST decoding correctly on updates, or large-column changes will be silently dropped or misread. Partitioned tables are another: as of Postgres 11, adding only the parent table to a publication automatically includes changes from all child partitions, and CDC tools can either preserve partition structure in the destination or flatten to a single table.

Tables without a primary key deserve particular attention. Postgres needs some form of replica identity, a primary key, a suitable unique index, or the blunt instrument of REPLICA IDENTITY FULL, in order to decode updates and deletes correctly. Falling back to REPLICA IDENTITY FULL means every single column gets written into the WAL on every change, which gets expensive fast at any real scale. The actual fix is almost always simpler: add a primary key to the table. And for databases running at genuinely high write throughput, logical replication does add some overhead, since the slot has to retain WAL segments until the consumer processes them. That overhead is negligible for most workloads, but disk usage should be monitored and max_wal_senders set appropriately when transaction rates climb.

The path change events travel from the Postgres WAL to Snowflake tables

Once a change event exists in decoded form, getting it into a queryable Snowflake table follows a consistent three-stage shape: capture, transport, and materialization.

Capture is where a connector attaches to Postgres over logical replication, takes a full backfill snapshot of whatever's already in the tables, and then switches into streaming mode from the exact LSN where that snapshot ended. That handoff matters because the switch happens at a known, precise WAL position, so nothing gets missed or double-counted in the transition between the initial snapshot and the live stream.

Transport is the buffering stage. Change events land in a durable, replayable log rather than getting pushed directly at Snowflake, which decouples the producer, Postgres, from the consumer, Snowflake. That separation means a downstream reader can restart, retry a failed batch, or even get added later entirely, without ever touching the Postgres source again.

Materialization is where events are written into Snowflake tables, via MERGE for 1:1 tables, which gives exactly-once semantics on retry, or via append for history/audit tables. Tables that mirror Postgres one-to-one use MERGE, which delivers exactly-once semantics even when a retry happens. History or audit tables use append instead, keeping every version of every row rather than collapsing to the latest state.

The MERGE pattern gives exactly-once semantics on retry. Incoming data lands in a staging table first, and only then gets merged into the target using the Postgres primary key, composite keys included, as the join condition. If a MERGE fails halfway through, for any reason, a retry from that same staging table is idempotent: no duplicate rows appear, because the merge condition matches on the same key it always would.

Getting the types right across the two systems is its own quiet discipline. TIMESTAMPTZ becomes TIMESTAMP_TZ. UUID typically lands as VARCHAR(36). None of this is exotic, but getting it wrong is how a pipeline quietly corrupts data without throwing an obvious error.

Schema evolution is where a lot of pipelines actually fall over in production. Most DDL changes, an ALTER TABLE that adds a column being the most common, need to propagate automatically. A pipeline that chokes on a new column forces someone to manually intervene, and every minute that takes is lag piling up on top of whatever the pipeline was already supposed to be handling in real time. ARRAY maps to ARRAY or VARIANT. ENUM, RANGE, and geometric types map to VARCHAR or VARIANT.

History mode closes the loop on something batch jobs simply cannot do: by keeping every insert, update, and delete as its own row rather than collapsing to a current-state snapshot, the pipeline can answer a question like what a record looked like at 2 p.m. on March 12th. That capability carries direct practical weight. It's directly relevant to SOX compliance, GDPR subject access requests, and the kind of internal discrepancy investigation that starts with someone asking why a number changed and nobody remembering. JSONB / JSON maps to VARIANT. HSTORE, composite types, and TSVECTOR map to VARIANT or VARCHAR.

Snowflake's native Postgres mirroring: what it is, how it works, and what it requires

In June 2025, Snowflake acquired Crunchy Data, a provider of open-source PostgreSQL technology, for roughly $250 million, a clear signal that Snowflake intends to own the Postgres-to-warehouse pipeline as a first-party product rather than leaving it entirely to outside connectors. Postgres mirroring is the resulting feature: a Snowflake-native capability, currently in public preview, that continuously replicates data from Postgres into a Snowflake database with minimal lag. Every insert, update, delete, and schema change is captured on the target automatically, with no external ETL pipeline and no third-party connector sitting in between.

The constraints are real and should be stated rather than glossed over. It's public preview, not generally available yet. It runs only on AWS and Microsoft Azure for now. And it requires the source to be a Snowflake Postgres instance on either the STANDARD or HIGH MEMORY tier. This is Snowflake's own managed Postgres offering, not a mechanism for pulling from an arbitrary external Postgres database sitting somewhere else.

Under the hood sits a new Postgres extension called snowflake_cdc, which continuously pushes batches of changes into per-table change logs and a shared "meta log" in the background through what Snowflake calls base workers. That extension is built on pg_lake, a set of open-source Postgres extensions that let Postgres query, manage, and write directly to Iceberg tables sitting in object storage, the exact same storage layer Snowflake reads from, with no intermediary system in between. Because the extension runs inside Postgres itself rather than polling it from outside, it can coordinate schema changes and complex DML and DDL transactions with a level of precision an external reader couldn't match, since it knows what's happening at the storage level as it happens.

The internal flow follows a now-familiar shape. A logical replication publication gets created on the Postgres source through snowflake_cdc, and per-table change log tables, written in Iceberg format, get produced by a CDC worker operating through pg_lake directly on the Postgres instance. It's the same architecture described earlier in this piece, publication, slot, decoded change stream, just implemented as a first-party Snowflake feature instead of a separately operated connector. For teams already running Postgres inside Snowflake's own managed tiers, it collapses what used to be a multi-vendor pipeline into something that lives entirely inside one platform's operational boundary, which is a meaningfully different proposition than bolting a CDC tool onto a database Snowflake has no visibility into.