Initial Snapshot Strategies for Large Postgres Tables

A CDC pipeline cannot stream changes into existence out of nothing. Before it can emit a single update, it has to establish a consistent baseline of the rows already sitting in the table, and on Postgres that baseline is built from a long-running read held under a REPEATABLE READ snapshot, tied directly to a replication slot. That transaction does more than read data. It holds open a WAL position: the replication slot blocks the publisher from discarding WAL segments from that position forward until the subscriber confirms it has consumed them. A snapshot that takes hours or days to complete means WAL accumulates for hours or days, and on a busy primary, that accumulation can fill disk. The open transaction also suppresses autovacuum on the tables it touches for its full duration, so a write-heavy table left under an open snapshot for an extended period builds dead-tuple bloat that can take hours to clean up once the snapshot finally releases. Layered on top of both is the raw I/O cost of a sequential scan: a 400 GB table scanned during business hours produces visible query latency on the primary, competing directly with the production traffic the database exists to serve. None of these three costs, WAL accumulation, vacuum suppression, and I/O contention, operates in isolation. They compound together, and they scale with the size of the table being snapshotted, not with how the connector happens to be configured.

The snapshot-to-stream handoff problem that every strategy must solve

Every strategy for snapshotting a large table solves one constraint: capture a consistent point-in-time read of existing rows, then hand off to WAL streaming at exactly the LSN that snapshot represented, with no gap in coverage and no row duplicated between the baseline and the stream. Postgres provides a specific mechanism for this: pg_export_snapshot() exports the current transaction's snapshot so other connections, including parallel workers, can import it and share the same visibility window. go-pq-cdc relies on pg_export_snapshot() as its mechanism for achieving a zero-data-loss, consistent point-in-time snapshot before the switch to CDC streaming, and that same primitive is what makes CTID-partitioned or chunked reads consistent across multiple workers rather than a set of independent, potentially conflicting views of the table.

Timing governs correctness here. The replication slot has to be created before or at the very beginning of the baseline read, never after it. If you capture the CDC start position late, any change that happened during the load window is lost permanently, with no way to recover it later. Interrupted snapshots expose the sharpest version of this handoff problem: when a snapshot restarts partway through, the slot still has to hold WAL back to the restart_lsn fixed at slot creation. Because the slot has not advanced since the snapshot began, retention pressure keeps growing, so WAL from both the failed attempt and the retry gets retained at the same time.

One structural constraint cuts across every strategy that follows: tables without a stable, unique primary key cannot be safely chunked. A non-unique column produces overlapping ranges. If a column's values shift under load, rows can migrate between ranges mid-scan. Either condition silently loses rows from the baseline or duplicates them, and no runtime check catches it after the fact. This constraint eliminates options outright rather than merely complicating them, a fact that governs every section below.

Monolithic full-table copy: correctness and risk

The simplest strategy is a single-transaction full copy: one long-running read, one consistent snapshot, one handoff to streaming at the end. For smaller tables, this is the correct default. It is operationally simple, it needs no coordination machinery between workers to guarantee consistency, and it finishes before WAL accumulation becomes a material problem on the source.

The strategy becomes dangerous as table size and write volume on the source both climb. The failure mode that ends monolithic snapshots in practice is catch-up failure: once the baseline copy finishes, the tablesync worker enters a final catch-up phase, applying every change that occurred between snapshot start and the final LSN. If write activity during the copy was high, that catch-up phase can grow large enough to exhaust resources or time out before it finishes, stranding the pipeline in an incomplete state after hours of work.

The restart behavior is where the strategy's risk becomes structural rather than incidental. A monolithic snapshot that gets interrupted does not resume from where it stopped. It restarts from the beginning of the table, full stop, while the replication slot continues holding WAL from the original starting LSN the entire time. Every failed attempt and every retry stretches the WAL retention window further instead of shrinking it, so if a table is large enough to need multiple attempts, it is large enough to put real pressure on the publisher's disk with each one. Because a restart is all-or-nothing, chunking and resumability become necessary once tables cross a certain size, not just a convenience.

CTID-based parallel snapshotting: trading slot pressure for speed

Parallelism is the first real alternative to a single serial read, and on Postgres it is commonly implemented by partitioning the table on CTID, the internal tuple identifier that marks the physical block number and in-block position of each row. go-pq-cdc builds on this with an auto-selection strategy: it inspects the table's characteristics and chooses among integer-range partitioning, CTID block partitioning, or offset partitioning accordingly, falling back to CTID specifically for tables that lack a clean integer primary key.

CTID ranges assume physical stability during the scan, but a table under heavy write load during the snapshot can have page contents shift underneath the readers as rows move or get rewritten. The exported snapshot from pg_export_snapshot() handles row visibility correctly regardless, but the parallel workers still have to be designed around the possibility of page-level movement. The approach holds up because every worker shares the same consistent view through the exported snapshot, not because the CTID ranges themselves stay fixed once the scan begins.

What CTID parallelism buys is speed. More workers complete the scan faster, which shrinks the window during which the slot holds back WAL, but the underlying mechanism is unchanged: the slot still retains WAL from the snapshot's starting LSN until every worker finishes and the handoff to streaming completes. Parallelism should be sized to the I/O headroom the source can actually absorb. More workers mean faster completion, but each additional worker adds I/O pressure to the primary, so the right level of parallelism depends on the specific database's available I/O budget rather than some fixed number of workers that works everywhere. The deeper WAL retention problem gets addressed by a different strategy entirely, covered further below.

Incremental (chunked) snapshots interleaved with streaming: resumability at the cost of coordination

An interruption means resuming from the last completed chunk, leaving the already-read portion of the table untouched.

This resumability eliminates the all-or-nothing failure mode that makes monolithic copies dangerous at scale. The replication slot still holds WAL back to the snapshot's starting LSN for the duration, but because progress persists durably between chunks, a restart no longer compounds retention pressure by re-reading data that was already captured. Some connectors trigger this process through a signal table, where a row written to a dedicated table on the source kicks off snapshotting, and that same mechanism supports partial re-snapshotting of specific rows, making incremental snapshots the right tool for repairing row-level drift in a pipeline that is already running, not only for initial loads.

The coordination overhead that comes with this benefit is real and should be planned for before the rollout begins. The connector needs write access to a signal table on the source. WAL and slot retention need monitoring for the entire span of the snapshot, which can run considerably longer in wall-clock time than a monolithic copy of the same table. The source also has to support whatever signaling protocol the connector uses to coordinate chunk progress. None of these requirements is exotic, but all three need to be in place before the strategy is viable.

The keyless-table constraint introduced earlier becomes an absolute boundary here. A column that isn't unique produces overlapping chunk ranges. If a column's values change under load, rows can drift between chunks mid-scan. Either condition silently produces duplicates or gaps in the baseline, and there is no runtime check that catches it after the fact. If the table cannot guarantee a stable, unique key, incremental snapshotting is not an option, regardless of how large or how important the table is.

Replica-based snapshot offload: removing the primary from the snapshot equation

Running the initial snapshot against a physical read replica instead of the primary removes the snapshot's I/O contention and its vacuum-blocking effect from the production write path. The primary only sees the overhead of the logical replication slot itself, not the sequential scan that would otherwise compete with live queries.

That isolation comes with the most demanding coordination requirement of any strategy covered here. The CDC start position, the LSN at which streaming begins, has to be captured on the primary before or at the exact moment the baseline load starts on the replica. Loading from the replica first and establishing the CDC position afterward opens a gap between the end of the baseline and the first streamed change, a gap that cannot be closed retroactively.

Postgres 17 introduced failover replication slots, which propagate slot state to a standby so that if the primary fails and the standby gets promoted, the CDC pipeline resumes from the correct position without requiring a full re-snapshot. That capability directly closes one of the historical failure modes of replica-based architectures, where a primary failover would orphan the slot and force teams back to square one. For very large tables, the practical pattern combines a parallel export from the replica, using source partitioning to speed up the read, with the CDC connector holding an older slot position on the primary throughout, ensuring that no change between baseline start and streaming start slips through uncaptured.

Replica offload does not make WAL retention pressure disappear. The slot on the primary still holds WAL from the snapshot's starting LSN until streaming catches up, and if the replica lags meaningfully behind the primary, the effective retention window stretches longer than the snapshot's own wall-clock duration. The strategy is a genuine upgrade for the specific problem of isolating the primary from read and vacuum pressure. It is not a universal fix for every cost a large snapshot imposes.

Skip-snapshot with external seeding: decoupling the baseline from the replication slot entirely

The most architecturally aggressive option skips the connector's built-in snapshot mechanism. External seeding loads the baseline through a separate bulk export tool, such as pg_dump --snapshot=, or a parallel ETL flow, and starts the CDC connector in a no-data snapshot mode, pointing it at a slot position captured before the bulk load began. The PostgreSQL documentation describes the --snapshot= option precisely for this purpose: using a specified synchronized snapshot when dumping a database, useful for synchronizing that dump with a logical replication slot.

The sequence matters more than almost anything else in this strategy. The replication slot is created and its LSN captured first. The bulk load then runs independently, at whatever speed and parallelism the team chooses, entirely outside the connector's control. When streaming finally begins, the connector replays only the WAL from that captured position forward. The snapshot itself never passes through the replication slot at all, so this approach removes the slot-driven WAL retention pressure that every in-slot strategy above has to manage.

The correctness requirement for this pattern is strict and does not bend. The slot's LSN must be captured before or at the very start of the bulk load, never after. Writes that land between slot creation and the start of the load are covered by WAL replay once streaming begins. Any gap, however brief, between the completion of the load and the start of CDC streaming is unrecoverable data loss. There is no partial credit here.

The failure mode unique to this pattern is position recycling. If wal_keep_size is set too small or the server is under heavy write load, the bulk load can run longer than the server's WAL retention window allows, and the slot's starting LSN can be overtaken by WAL recycling before streaming ever begins. When that happens, the entire process has to restart from scratch. External seeding is the right call for tables large enough that any in-slot snapshot strategy becomes impractical on its own terms, not a shortcut to reach for by default.

Snapshot batch configuration and its effect on throughput and source load

Within any chunked or incremental strategy, the size of each batch is an execution detail with real consequences for both predictability and source load. Batching strictly by row count produces wildly inconsistent I/O per batch on tables with wide rows, TOAST columns, or a mixed workload: an identical row count pulled from a narrow lookup table and the same row count pulled from a wide events table represent entirely different amounts of actual work for the source database.

Batching by data size instead produces a steadier cadence of WAL acknowledgment. Because batch completion is what drives slot advancement in incremental strategies, evenly sized batches mean the slot advances at a more consistent rate, keeping the WAL retention window from swelling unpredictably between batches.

Even data-size batching has a limit. Tables with very large individual row values, columns that exceed typical page boundaries, or TOAST-stored data push past what row-count or simple byte-count batching can account for, because what actually matters for I/O is physical page reads, not logical row size. That is why CTID block-based partitioning, which maps directly onto heap pages, tends to produce more consistent I/O profiles than primary-key range partitioning on tables where row width varies significantly.

WAL retention monitoring and the operational safety net every strategy requires

No strategy above is self-correcting. A monolithic copy, a CTID-parallel scan, an incremental chunked load, a replica offload, and an externally seeded backfill all share the same dependency on a replication slot holding WAL back to some starting LSN, and all five can fail in the identical catastrophic way if that slot's growth goes unwatched: WAL accumulates until it fills the publisher's disk, at which point the primary itself is at risk, not just the snapshot.

Monitoring slot lag and WAL retention has to run for the full duration of the snapshot rather than just at kickoff, because any of these strategies can only complete safely if that monitoring holds, and the monitoring window for an incremental approach is correspondingly longer, since incremental snapshots can run longer in wall-clock time than a monolithic copy of the same table. External seeding introduces a sharp deadline of its own: the bulk load has to finish before WAL recycling overtakes the captured starting LSN, and that deadline is only visible to a team that is actively watching retention, not one that assumes the load will finish in time.

The strategies in this piece differ in where they place I/O load, how they handle interruption, and how tightly they couple the baseline to the replication slot. They do not differ in this one respect: every one of them depends on WAL retention staying inside a bounded, watched window from the moment the slot is created until streaming catches up. Choosing the right strategy for a given table matters. Watching the slot while that strategy runs matters just as much, regardless of which one gets chosen.