PostgreSQL CDC Backfills at Scale: Running a Multi-Day Backfill While Production Keeps Writing

PostgreSQL CDC backfills can run for hours or days while production keeps writing. During that time, long chunk queries can increase source I/O, delay replication acknowledgments, retain more WAL, and make recovery harder if the replication slot is lost.

This guide explains the failure modes we’ve seen in production, including WAL retention, slow chunk progress, slot invalidation, and source pressure, and the settings that help control them.

What a large backfill does to a live database

During a PostgreSQL CDC backfill, the connector reads existing rows in chunks while production continues generating changes. This historical copy is often called an initial snapshot; Estuary calls it a backfill.

At large scale, that process can run for hours or days. Chunk reads compete with production for I/O, PostgreSQL may need to retain WAL for longer, and historical rows must be reconciled with newer changes that arrive while those rows are being read. A resilient CDC system must also checkpoint its progress so an interruption does not force the historical read to restart from the beginning.

The fixes pull against each other. Reading faster loads the source harder. A cap on WAL retention protects the disk, but a backfill that holds WAL past it loses its replication slot. Shorter chunks release WAL sooner at the price of a longer backfill. Logical replication setup is in the PostgreSQL CDC guide and slot health in depth is in the replication slots article.

Why historical rows and live changes have to be reconciled

A CDC system prevents a stale snapshot row from overwriting a newer change by treating every backfill chunk as older than any WAL event that arrives while the chunk is being read. It reconciles the two before the chunk is emitted. The sequence that makes this necessary:

1. The backfill reads row A.
2. The application updates row A.
3. PostgreSQL emits the newer version of row A through the WAL.
4. The older version from step 1 is still buffered, or still moving through the pipeline.
5. Unless something intervenes, the stale historical value can become the final downstream state.

Whether a stale historical row can overwrite a newer change depends first on whether the connector keeps consuming WAL while the backfill is running. Some snapshot designs copy tables before switching to streaming. Estuary instead reads key-ordered backfill chunks while continuing to process WAL, so changes that occur during a chunk are observed before that chunk is committed downstream. See the backfill documentation for configuration details.

Estuary uses a watermark-and-fence mechanism to determine which WAL changes overlap each backfill chunk. After reading a chunk, the connector records the current WAL position as a fence and writes a watermark to a small published table. It then continues consuming replication events until the stream passes that fence.

The chunk and overlapping changes are committed together on Estuary's side, with newer changes ordered after the historical rows. This prevents an older backfill value from becoming the final downstream state. The PostgreSQL connector documentation covers the watermarks table and its configuration.

Historical rows and newer CDC events are reconciled so that the final downstream state is correct. The term "CDC initial snapshot" suggests a single event. The snapshot-to-stream handoff happens at every chunk boundary, for the backfill's entire duration, and not once when the copy finishes. How the watermark algorithm works in detail is covered in CDC Done Correctly.

Why WAL piles up during a backfill

Backfill reads are not the primary source of WAL. Production writes continue generating WAL at their normal rate, while the replication slot determines how much of that WAL PostgreSQL must retain. During a long backfill, delayed acknowledgments can prevent older WAL from being recycled on its normal schedule, so retained WAL grows even though the historical scan itself is read-only.

A logical replication slot pins the WAL its consumer still needs, and PostgreSQL can't recycle anything older than the slot's restart_lsn. That position stays put whenever the consumer hasn't acknowledged progress, since restart_lsn trails the position the consumer has confirmed, confirmed_flush_lsn. A long-running write transaction on the server pins it too, because logical decoding has to keep the WAL from where that transaction began to replay it.

During a backfill, the important effect here is delayed acknowledgment rather than a long-running write transaction. Estuary continues reading WAL between chunks, but it acknowledges progress after the chunk and its overlapping changes are committed downstream. While a slow chunk is still running, the confirmed replication position may remain unchanged. In order:

a chunk that takes hours → the slot's confirmed position stays put → application writes keep producing WAL → older WAL can't be removed → retained WAL grows → disk and replication-slot risk rise

The second cause, a long-running transaction, shows up during backfills as well, and it looks the same from the outside: retained WAL climbing while a backfill runs. In one production investigation, Estuary engineers traced a large volume of retained WAL to a long-running open transaction on the source database, which pinned restart_lsn for as long as it remained open. No chunk-size change would have released that WAL. Before tuning the backfill, check pg_stat_activity for a transaction that predates the growth.

The write rate multiplies the retained WAL, so a three-hour chunk on a busy system can retain more than a three-day backfill on a quiet one. Another production investigation found retained WAL growing by multiple gigabytes within a few hours while replication progress was stalled. Measure your WAL generation rate before you start, by sampling pg_current_wal_lsn() an hour apart (pg_wal_lsn_diff() turns the two values into bytes written) or with Estuary's guide to measuring WAL throughput. The arithmetic is rate times the longest chunk query you're prepared to run, plus consumer lag.

If max_slot_wal_keep_size is set and retained WAL crosses it, PostgreSQL invalidates the slot to protect the disk. Sizing that cap, alerting on safe_wal_size in pg_replication_slots, and recovering an invalidated slot belong to the replication slots article. A backfill is a common reason a healthy slot suddenly needs a much larger WAL budget than it did at steady state.

Chunk size is a database-safety setting

Choose a PostgreSQL CDC backfill chunk size by how long you're willing to go between acknowledgments to PostgreSQL, and only then by throughput. The intuition most teams start with runs the other way: larger chunks mean fewer queries, fewer round trips, and a backfill that finishes sooner, so larger must be better.

Chunk size sets throughput, acknowledgment interval, and restart cost at once.

• Throughput: Fewer, larger queries move more rows per second, up to the point where memory and result-set size start to dominate.
• Acknowledgment interval: Each chunk is one downstream commit and one acknowledgment to PostgreSQL, and its duration is the window in which the slot's confirmed position can't move. For a plain key-ordered scan, a chunk half the size runs in roughly half the time, and the database holds about half as much WAL for it. As an illustration, on a database generating 6 GB of WAL an hour, a 20-minute chunk query retains about 2 GB and a 2-minute one about 200 MB.
• Restart cost: A chunk that fails, times out, or is interrupted is read again from its start. Smaller chunks lose less work per failure, which matters more on a flaky network or a database that cancels long queries.

Estuary advances backfill progress from rows actually returned. A key-ordered scan may return 4,096 rows after scanning roughly that many rows, but a filter the index cannot serve can force PostgreSQL to scan millions of rows to find the same 4,096 matches. If the query repeatedly hits its statement timeout before returning rows, the backfill may make little or no progress. This is one reason a PostgreSQL CDC initial snapshot can appear stuck even when the table itself is not especially large. See Estuary's backfill documentation for the available backfill modes and controls.

Estuary's PostgreSQL connector exposes backfill_chunk_size, which defaults to 4,096 rows, and applies a default 2-minute statement_timeout to each backfill query. The timeout limits how long a single chunk query can run. If a filtered query cannot return rows before the timeout, progress can stall; if it returns some rows before timing out, the connector can resume from the last returned row. See the PostgreSQL connector documentation for configuration details.

In our experience operating PostgreSQL CDC pipelines, some high-WAL or slow-query workloads have required substantially smaller chunks before chunk duration and WAL release came under control. Smaller chunks shorten each cycle and allow the confirmed position to advance more frequently, which can reduce peak WAL retention at the cost of a longer overall backfill. In high-WAL environments, or wherever chunk queries are slow, tune the setting around chunk duration, source load, and retained WAL, with throughput as a secondary consideration.

While tuning, watch each chunk query's duration (its query_start in pg_stat_activity), the distance between confirmed_flush_lsn and pg_current_wal_lsn(), and safe_wal_size on the slot, rather than the backfill's rows per second. If those are comfortable, raise the chunk size. If any is trending the wrong way, lower it and accept the longer backfill.

What the backfill costs the source

A well-designed backfill reads in bounded, indexed range scans, and the work per chunk stays flat from the first row to the last. The query shape is a keyset scan on the primary key: fetch the next N rows whose key is greater than the last key returned, in key order. Each query touches one index range and the rows behind it, so chunk 10,000 takes about as long as chunk 1. A large-table backfill draws on production I/O and buffer cache for as long as it runs.

Estuary's per-table priority setting lets teams control the order in which tables are backfilled. Higher-priority bindings can complete before lower-priority ones, which lets teams prioritize smaller or business-critical tables before beginning a very large table. A backfill that can pause and resume can be kept to off-peak hours. And the reads can move off the primary: on PostgreSQL 16 and later, logical decoding can run on a standby, so both the WAL stream and the chunk reads can come from a replica.

For standby capture, Estuary's read-only capture mode requires hot_standby_feedback to be enabled so PostgreSQL does not remove catalog metadata that logical decoding on the standby still needs.

Moving the capture to a standby does not make the primary completely immune to backfill effects. Standby feedback communicates retention requirements back to the primary, which can delay vacuum cleanup under some conditions. Keep chunk duration bounded even when CDC and backfill reads are running from a replica.

Do you need the whole table? Five ways to backfill less

You can backfill part of a PostgreSQL table, and you can skip the historical rows entirely. Which is right depends on what the destination has to be able to answer. For a very large database the first question is whether the destination needs a multi-terabyte historical copy, or only changes from this point forward, and only after that how to backfill faster. Estuary's backfill documentation exposes the choice per table, and the definitions quoted below come from it.

1. Normal backfill: The default mode reads existing rows in key-ordered chunks while WAL processing continues. Precise mode adds stronger ordering guarantees for supported tables by reconciling historical rows and live changes into a consistent sequence for each key, so consumers see an insert before a later update or delete. Precise mode is not available for every table because reliable ordered-key comparison is not possible in some cases.
2. Incremental re-backfill: Re-reads source data into the existing collection without dropping destination tables. This is useful for recovery, including re-establishing consistency after a PostgreSQL replication-slot failure. Standard materializations merge the re-read records by key; delta-update bindings append them and can therefore create duplicates during a re-backfill.
3. XID-limited backfill: Uses PostgreSQL's xmin system column to restrict a re-backfill to rows above or below a specified transaction ID. This can reduce how much historical data must be re-read after an outage, but the filter is not indexed, cannot detect deleted rows, and requires additional care when transaction IDs have wrapped. Clear the XID limit after recovery so it does not unintentionally constrain a future backfill.
4. Filtered backfill: Adds a SQL predicate to each backfill query so only matching historical rows are read. Ongoing replication is unaffected. This works well when application knowledge can safely narrow the required history, especially when an index can serve the filter efficiently.
5. Only Changes: Skips the historical read and begins with ongoing changes. This eliminates the large-table backfill entirely, but the resulting collection does not contain pre-existing history, so future destinations cannot recover that history from the collection.

What happens when a PostgreSQL CDC backfill fails at 80%?

What happens depends on which of five things survived: the backfill's cursor in each table, the connector's place in the WAL, the durable checkpoint that records both, the replication slot, and the WAL the slot still needs. If all five exist, the backfill resumes where it stopped. If the slot or its WAL is gone, no restart can recreate the changes PostgreSQL has already discarded, and a new replication position plus a re-backfill or reconciliation is needed.

Interruption with everything intact: A connector restart, a network drop, or a statement timeout mid-chunk loses at most the chunk in flight. Estuary records backfill progress as rows come back from each query and writes it into the checkpoint after each chunk, so after a restart the backfill continues from its checkpointed cursor and WAL consumption resumes from the checkpointed log sequence number. The slot went unacknowledged only for the outage plus the chunk in flight, so retained WAL stayed bounded.

Slot loss or invalidation: A restart cannot recover this class of failure. In one production case, a finite max_slot_wal_keep_size was configured. During the initial backfill, retained WAL exceeded the available slot budget and PostgreSQL removed WAL that the replication slot still required. Once that WAL is gone, the previous replication position cannot simply be resumed. Recovery requires establishing a new replication position and re-backfilling or otherwise reconciling the affected tables. Estuary's PostgreSQL replication slot recovery guide covers the recovery process.

Unlimited or very high WAL retention protects replication continuity for longer, at the risk of filling the database's disk. A finite max_slot_wal_keep_size protects the disk, at the risk that a sufficiently delayed consumer loses required WAL and its capture position with it. During a backfill the consumer reads the WAL between chunks, but it acknowledges once per chunk, so its confirmed position lags by however long the current chunk takes. A cap sized for steady-state lag can be crossed by a backfill on a busy database even though the connector was healthy the whole time. Size the cap for the backfill's retention budget for the duration, then tighten it afterwards. How to size it, how to alert before it fires, and how to recover a slot that has already been lost are in the replication slots article. Estuary's slot recovery guide covers the XID-limited re-backfill that avoids re-reading everything, with the caveat that "rows deleted during the outage window will not be detected and will remain in your destination tables."

CDC snapshot recovery differs between architectures. A Debezium initial snapshot, per the connector's documentation, runs in a single transaction that completes before streaming starts, and "if the connector stops during a snapshot, the connector begins a new snapshot when it restarts." A Debezium incremental snapshot, added in a later release, is chunked and runs alongside streaming, and after an interruption "the snapshot begins at the point where it stopped, rather than recapturing the table from the beginning." Both ship in the same connector; the incremental snapshot is the one that survives an interruption. Whether a snapshot can resume is a property of its design; check it per tool and per mode before a multi-day backfill starts.

Why an almost-idle database can make a backfill crawl

A low-traffic PostgreSQL CDC pipeline in read-only capture mode needs a heartbeat table because a replication slot can only advance, and release WAL, when a change to a table in its publication comes through. If the captured tables rarely change while other databases in the cluster write heavily, the slot has nothing to acknowledge and WAL from the busy databases accumulates behind it. The connector's chunk cycle depends on the same condition at a smaller scale, since each chunk ends by waiting for a commit on a published table to pass its fence. We have watched backfills on almost-idle databases progress far more slowly than the table size explains.

In Estuary's default capture mode, the watermark write provides a regular published change that lets the connector advance through the replication stream. Read-only mode removes those writes, so Estuary requires either regular changes to at least one captured table or a heartbeat table included in the publication and updated every few minutes.

If the captured tables remain idle while other activity on the PostgreSQL server continues generating WAL, the slot may be unable to acknowledge further progress and retained WAL can keep growing. The PostgreSQL connector documentation covers the heartbeat requirement for read-only captures.

Tables that break the scan

You can backfill a PostgreSQL table without a primary key, but the connector has to walk it by physical location instead of by key, the correctness guarantee is weaker, and on some tools the scan gets slower the further it goes. Large backfills work best when the connector can read the table in order through a unique index, because each chunk query is then a bounded range scan whose duration doesn't depend on how much of the table has already been read.

Without that, a tool has two choices. It can page by offset, and offset pagination re-scans everything before the offset on every query. The work for chunk N grows with N, and the total approaches O(N²) on a large table. Or it can walk physical location. For PostgreSQL, Estuary's Without Primary Key mode "uses an alternative physical row identifier (such as a Postgres ctid) to scan backfill chunks, rather than walking the table in key order," which keeps each chunk a bounded range scan. The documentation adds: "This mode lacks the exact correctness properties of the Normal backfill mode," because, in PostgreSQL's own words, "a row's ctid will change if it is updated or moved by VACUUM FULL," and both can happen while a backfill is in progress. The mitigations, in order of preference: add a unique index the connector can use as the key; choose a better collection key if the table has a unique one that isn't declared; and only then fall back to the physical-row mode.

A table without a primary key also has no usable replica identity by default. With REPLICA IDENTITY DEFAULT, PostgreSQL treats a table without a primary key like REPLICA IDENTITY NOTHING. If that table is included in a publication that replicates UPDATE or DELETE operations, those operations will error on the publisher until you configure a suitable unique replica-identity index or use REPLICA IDENTITY FULL.

REPLICA IDENTITY FULL includes the old row image for UPDATE and DELETE events, which can increase WAL volume and source write overhead on wide or high-churn tables. Prefer a suitable primary key or unique replica-identity index where possible. The PostgreSQL CDC guide covers the available options.

Partitioned tables need the same check. An index created on the partitioned table itself propagates: PostgreSQL "automatically creates a matching index on each partition, and any partitions you create or attach later will also have such an index." Indexes created per partition do not, so a table whose key index was built partition by partition can have partitions without it, and a chunk query that was a range scan on one partition becomes a sequential scan on another. Check the index on every partition as well as the parent. And decide before the backfill whether changes should arrive under the root table's name (publish_via_partition_root = true on the publication, which Estuary's setup script sets) or per partition, because switching afterwards is a re-backfill.

How Estuary runs a large backfill

A resilient multi-day CDC backfill needs four things: continuous WAL consumption while historical rows are read, bounded chunk duration, durable checkpoints for restart, and a way to control how much history each table reads.

Estuary's PostgreSQL capture combines key-ordered chunk reads with continuous WAL processing and uses a watermark-and-fence mechanism to reconcile historical rows with newer changes. backfill_chunk_size defaults to 4,096 rows, and backfill queries use a default 2-minute statement_timeout. Table priorities control backfill order, while per-table modes, XID bounds, and filters control how much history is read.

Estuary checkpoints progress after each chunk, so an interrupted backfill can resume from its durable position rather than restarting the historical copy. Read-only capture removes watermark writes for locked-down databases and supported PostgreSQL 16+ standbys. One customer Hayden AI backfilled 5 TB of PostgreSQL data into Amazon Redshift and cut replication lag from about 24 hours to about one.

The concessions are these: Read-only mode requires a heartbeat table or a regularly changing captured table. A slot that PostgreSQL invalidates leaves a capture that "will fail and require manual recovery," and the XID-limited recovery can't see deletes. The 4,096-row default is a starting point. High-WAL environments or tables with slow backfill queries may benefit from a smaller chunk size to shorten acknowledgment intervals and limit retained WAL. Only Changes mode skips the historical rows, so the collection never holds them and a later destination cannot be backfilled from it.

Conclusion

A large PostgreSQL CDC backfill is not just a data-copy job. The operational risk comes from how long chunk queries run while production continues generating WAL, how often the connector can acknowledge replication progress, whether the slot has enough WAL budget, and whether the destination actually needs the table's full history.

Before the backfill starts, measure the WAL generation rate and set max_slot_wal_keep_size for the backfill rather than for steady state. Then pick a chunk size from the longest chunk query you can afford to run at that write rate, since the connector acknowledges once per chunk. A slot invalidated during the backfill cannot be recovered by a restart.

If you would rather not build the pipeline around it, Estuary's PostgreSQL capture connector runs the chunked, concurrently streaming backfill described above, with per-table backfill modes, XID and filter controls, and a read-only mode for standbys and locked-down databases. The backfill documentation walks through the options.

Ready to build a PostgreSQL CDC pipeline? Start building with Estuary