Redshift Merge Performance at High CDC Throughput

Redshift's MERGE performance falls apart at CDC throughput because of how the storage engine handles updates at the block level. A row-level UPDATE inside Redshift is never a simple in-place write, and once change data capture starts delivering hundreds or thousands of mutations a second, that volume directly dictates cluster performance.

Redshift's storage model and the internal mechanics of a row update

Redshift's columnar storage model means that an UPDATE is never an in-place modification: internally, every UPDATE executes as a DELETE of the existing row plus an INSERT of the new row. The deleted row does not disappear from disk when this happens. It persists as what the engine calls a ghost row, and it stays visible to the query planner until a VACUUM operation physically removes it.

That persistence has a cost that compounds at the block level. Redshift organizes column data into one-megabyte blocks, each carrying zone-map metadata that lets the query planner skip blocks that can't contain relevant rows. A single ghost row sitting inside a block defeats that optimization for the whole block, because zone maps work on ranges, not on row-level exclusion. The planner has no way to know a block's ghost rows don't matter until VACUUM has already marked them reclaimable.

None of this reflects a flaw in Redshift's design. Columnar engines trade write flexibility for read throughput, and the ghost-row mechanic is the direct, predictable cost of that trade. A warehouse optimized for scanning billions of rows across a handful of columns has no reason to support cheap in-place row mutation, and Redshift doesn't pretend otherwise. The problem only becomes acute once a workload starts asking the engine to do something it wasn't built for: absorb a continuous stream of small, individual row changes rather than periodic bulk loads.

Why ghost-row accumulation compounds under continuous CDC load

CDC pipelines exist to translate every row-level change in a source system, the PostgreSQL WAL, a MySQL binlog, a DynamoDB stream, into a corresponding event downstream. At meaningful throughput, that means hundreds or thousands of individual mutations landing on Redshift every second, and each one produces at least one ghost row. Critically, the accumulation rate tracks the table's update frequency, not its size. A table that receives frequent UPDATEs accumulates ghost rows proportional to its update rate, not its size.

Every one of those ghost rows gets scanned by subsequent queries against the table, because the planner can't exclude what it hasn't yet been told to discard. Query cost rises with ghost-row count, independent of how much real data the table holds.

The unsorted region compounds the same problem from a different angle. Unsorted regions grow in parallel: continuous INSERTs land outside the table's sorted region, so both ghost-row accumulation and unsorted-region growth happen simultaneously, each degrading MERGE performance for the other. Each one makes the other worse, since a MERGE against an unsorted, ghost-heavy table takes longer, which gives more time for new mutations to arrive and add to both problems before the next maintenance cycle. Teams that provision a cluster sized for steady-state query volume often discover the degradation only after it has already become non-linear, a slow creep that turns into a wall.

The VACUUM catch-22: the maintenance that should fix the problem makes it worse under load

VACUUM is the only mechanism that removes ghost rows and merges unsorted regions, but its cost scales with the size of the unsorted region.

The operation itself runs in two stages. Redshift first sorts the rows sitting in the unsorted region, then merges those newly sorted rows into the table's existing sorted region. On large tables, Redshift performs this incrementally, but the incremental sort work isn't durable. If VACUUM gets interrupted partway through, only the rows that had already been merged before the interruption survive a restart. Everything sorted but not yet merged has to be redone.

VACUUM and ongoing DML also contend for the same cluster resources, so running both at once slows each of them. A CDC pipeline that keeps writing while VACUUM runs degrades the pipeline's own write throughput at the same time it degrades VACUUM's reclamation rate. This is a circular trap: unsorted regions slow MERGE, MERGE generates more unsorted regions, VACUUM needed to clear them runs slower under write pressure, and the unsorted region grows faster than VACUUM can shrink it.

Queries against the affected table keep running throughout this cycle. Nothing in Redshift blocks a read query while VACUUM is mid-operation. But the query plans running against a ghost-heavy, unsorted table get progressively less efficient, and that erosion tends to be invisible in any log until the query times downstream have already degraded to a point someone notices. Tuning VACUUM's schedule or frequency doesn't break this cycle, because the cycle is generated by write volume itself. The only real exit is architectural: change how mutations arrive at the table in the first place, rather than trying to out-schedule the maintenance that cleans up after them.

The staging-table MERGE pattern as the correct architectural response

The staging-table MERGE pattern addresses the mechanic directly rather than patching around it. CDC events get loaded into a staging table in bulk, and only then does a single MERGE statement apply the accumulated changes to the target, reducing ghost-row accumulation by collapsing many individual mutations into fewer, larger, sorted writes rather than issuing row-by-row upserts. Instead of issuing thousands of individual row-level upserts, each generating its own ghost row and its own unsorted insertion, the pipeline collapses a batch of mutations into one large, sorted write.

Redshift does offer a native MERGE statement for direct upserts, but AWS's own guidance for bulk CDC workloads on standard managed tables still favors the staging pattern. COPY operations land data into sorted, compressed blocks, while a stream of individual row-level statements produces the fragmented, unsorted writes that create the exact ghost-row and unsorted-region problems described above. Batching also collapses redundant mutations. Batching mutations before the MERGE means multiple updates to the same row within a micro-batch are collapsed to one write, reducing the total number of ghost rows created per unit of source change volume.

Latency is the trade-off: Redshift's scheduler has a one-minute floor on scheduled query intervals, so teams needing sub-minute delivery have had to route around it, typically using MSK event-source mapping paired with Lambda to trigger stored procedures as CDC events arrive, subject to a configurable batching window. That bridges the gap between Redshift's native scheduling granularity and something closer to streaming latency, but it adds moving parts.

None of this eliminates vacuum pressure on the target table. The staging table itself should be designed with encoding and distribution aligned to the target table to minimize the cost of the final MERGE step, since poor staging-table design moves the bottleneck rather than eliminating it. The pattern is a mitigation with real mechanical grounding but not a complete fix.

Separating the extract and load flows to contain failure and sustain throughput

Splitting the CDC pipeline into independent extract and load flows, an S3-buffered dual-flow architecture, decouples failure domains so that a load-side stall does not stop event capture, and vice versa. The extract flow streams CDC events into S3 continuously; the load flow runs bulk COPY operations from S3 into Redshift on its own independent schedule.

That separation pays off during failure. In a single coupled flow, a stall on the load side, say, a Redshift slowdown caused by vacuum contention, pauses capture too. Decoupled, the extract flow keeps landing events in S3 regardless of what's happening downstream, and the load flow catches up once Redshift is ready.

The stakes of getting this wrong aren't symmetric across sources. DynamoDB Streams retains records for a fixed, limited window. A pipeline that pauses capture, not just loading, past that window loses those records permanently and needs a full backfill to recover. Coupling extract and load turns a transient Redshift issue into a source-side incident. Decoupling contains the blast radius to the load side alone, where recovery is a matter of catching up rather than reconstructing lost history.

Using WLM isolation to prevent CDC write pressure from degrading query latency

Decoupled flows and staged writes address what happens outside Redshift and how data arrives at its door. Inside the cluster, CDC ingestion and analytical queries are still drawing from the same pool of compute unless something actively separates them. Redshift's Workload Management system is that separation mechanism: it lets ingestion workloads run in a dedicated queue so long-running COPY and MERGE operations don't consume the concurrency slots that dashboard and ad-hoc queries need.

Without that isolation, the failure mode is direct and ironic. A CDC MERGE running long because it's fighting vacuum pressure can occupy every available concurrency slot, blocking the interactive queries the whole pipeline exists to support. Analysts querying a dashboard experience that as Redshift being slow, when the actual cause is an ingestion job elsewhere in the cluster tangled up in the ghost-row and unsorted-region mechanics described earlier.

Concurrency Scaling can add cluster capacity automatically once read queues fill up, but it doesn't remove the need to test queueing behavior, resource contention, and tail latency against the specific mix of workloads a cluster runs. Queue design also determines which queries even qualify for that scaling offload in the first place. Auto WLM is the AWS-recommended default on provisioned clusters, and teams with mixed CDC-plus-analytics workloads should validate that ETL queues are configured to prevent starvation of interactive query queues before concurrency scaling kicks in. Even monitoring traffic deserves its own queue: routing CloudWatch or DMS log queries into a separate low-priority queue keeps instrumentation overhead from adding to contention during periods of already-heavy CDC load.

The pattern to recognize is that latency problems attributed to "the CDC pipeline" often originate inside Redshift's own resource management, in the interaction between vacuum pressure and a WLM configuration that never separated the two workloads to begin with.

Apache Iceberg v3 deletion vectors as a structural alternative to the ghost-row model

Every pattern covered so far manages the consequences of ghost rows. Apache Iceberg v3, now supported on Redshift, offers something different: a storage model where row deletion doesn't generate ghost rows at all. Deletions get recorded as metadata markers, deletion vectors, rather than triggering the physical rewrite that Redshift's native table format requires.

The read path filters out deleted rows by consulting the vector directly, instead of depending on VACUUM having already swept the physical row off disk. Physical reclamation gets deferred rather than eliminated, but the query planner no longer has to wait on that reclamation to get an accurate, efficient scan. Iceberg v3 also introduces row lineage, which lets incremental and CDC pipelines process only the rows that actually changed rather than rescanning wider ranges. At high CDC volume, that compounds with the deletion-vector benefit: less data touched per cycle, and mutation bookkeeping handled through metadata rather than through the sort-and-merge cycle native Redshift tables depend on.

This is a genuine shift in table format, requiring more than a setting flipped on an existing pipeline. A team adopting Iceberg v3 for a CDC target is changing how maintenance pressure, vacuum cadence, and MERGE behavior interact at the storage layer, and that decision doesn't graft cleanly onto pipelines built around native Redshift tables. Iceberg v3 on Redshift is a newer path, and its operational maturity, tooling integration, and edge-case behavior under sustained CDC load are less documented than the native staging-MERGE pattern. Choosing between the two requires understanding the ghost-row mechanics this article has walked through from the start, because the entire case for Iceberg v3 rests on avoiding a problem that only makes sense once you've seen how it forms inside native Redshift tables.

Requirements for a reliable Redshift replication tool

Every pattern above, staging MERGE, dual-flow extraction, WLM isolation, Iceberg v3, assumes the tool feeding Redshift is itself reliable. That assumption doesn't hold universally. A replication layer that can't handle schema evolution, can't guarantee exactly-once delivery, or can't recover cleanly from failure will generate correctness problems no amount of Redshift-side tuning can fix.

Schema evolution is the quiet failure point at high throughput. Source tables gain columns, drop columns, and change types over time, and a replication tool that can't adapt automatically will fail outright, silently drop changes, or land malformed rows into the staging table, corrupting the target in ways that are hard to catch and expensive to unwind once discovered. Delivery guarantees matter for a more specific reason tied directly to everything covered above: a duplicated INSERT followed by a MERGE produces the same ghost-row accumulation as a genuine source-side UPDATE, even when no update happened on the source at all. At-least-once delivery, however sub-second its capture latency, leaves that duplication risk sitting in the pipeline, and closing it becomes the operator's job rather than the tool's. AWS DMS is reasonable for short-lived, AWS-native migrations but generates additional operational overhead for always-on multi-source pipelines where schema evolution and failure recovery must be managed continuously.

A managed replication service that handles schema evolution automatically, guarantees exactly-once semantics, and separates extract from load as a built-in property removes these tool-layer failure modes before they ever reach Redshift. That's the condition under which every pattern in this piece, the staging MERGE, the dual-flow architecture, WLM isolation, actually delivers the throughput and latency it's designed for, rather than getting quietly undermined by duplicate events or a schema change nobody caught. A production DynamoDB-to-Redshift pipeline built on automatic schema inference and sub-minute delivery shows what that interaction looks like in practice, with the staging-MERGE pattern doing its job because the tool feeding it isn't generating correctness problems of its own. Redshift's architecture can be tuned with real precision. Whether that tuning holds up under load depends on what's feeding it long before a single row ever reaches a block.