Dealing with Large Table Merges Without Breaking Your Database
When you are merging hundreds of millions of rows into a production table, the naive approach of a single big MERGE statement almost always causes problems. Long-running transactions, lock escalation, connection pool exhaustion, and occasionally outright failures during peak hours. I learned this the hard way after a 4AM deploy for a retail client where a single batched merge hung for six hours and took their reporting down. The solution most teams end up settling on is batching the merge into manageable chunks. You group by a key column, process N rows at a time, and commit each group separately. This keeps transactions short, reduces lock contention, and lets you resume from wherever you left off if something breaks mid-run.
Chicken Merge Implementation
Here is what a practical implementation looks like in SQL. You define a batch size, loop through partitioned groups, and perform an upsert within each batch: DECLARE @BatchSize INT = 50000; DECLARE @Done BIT = 0; WHILE @Done = 0 BEGIN WITH BatchedMerge AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY SomeSortKey) AS rn FROM SourceTable WHERE LastProcessed = 0 ) MERGE TargetTable AS T USING BatchedMerge AS S ON T.Id = S.Id WHEN MATCHED THEN UPDATE SET T.Column1 = S.Column1, T.UpdatedAt = GETDATE() WHEN NOT MATCHED THEN INSERT (Id, Column1, CreatedAt) VALUES (S.Id, S.Column1, GETDATE()); UPDATE TOP (@BatchSize) SourceTable SET LastProcessed = 1 WHERE LastProcessed = 0; IF @@ROWCOUNT = 0 SET @Done = 1; END This is the skeleton. The actual details depend heavily on your RDBMS and table structure. PostgreSQL handles this differently than SQL Server or Snowflake, and the performance characteristics shift dramatically between them.
What People Get Wrong About This Pattern
The first thing I see teams mess up is the sort key selection for batching. Picking a non-unique or low-cardinality column to partition your batches causes severe skew. One batch might process 100,000 rows while another grinds to a halt on a few million rows tied to a single customer ID or date. Always check your data distribution before locking in a sort strategy. A composite key combining a high-cardinality column with a hash bucket tends to distribute work more evenly. Another common mistake is setting the batch size too small. Everyone wants to be safe, so they default to 5,000 rows per batch. That sounds conservative until you realize you are now running 20,000 separate transactions instead of maybe 200. The overhead of transaction begin, log writes, and commit per batch adds up fast. I usually recommend starting at 50,000 to 100,000 depending on row size and adjusting based on your lock wait stats. Monitor your active transactions and wait stats during a test run. If you see minimal lock waits, your batch size is probably fine. If the queue builds up, increase it. There is also the question of idempotency. If a batch fails mid-commit, you do not want to reprocess rows that already landed. The LastProcessed flag approach shown above handles this, but you need to make sure your source table has a reliable marker column. Modified timestamp, row version, or a deterministic hash of the payload all work. Just pick one and stick with it. I once spent three days debugging a merge that kept double-counting revenue because the source data had subtle null variations that made hash-based deduplication unreliable. Switching to a server-generated row version column fixed it immediately.
Get the Full Details
Edge Cases and Where This Fails
Chicken merge is not a universal fix. It breaks down in a few scenarios. First, if your source and target tables have no stable sort key or unique identifier, batching becomes guesswork and you will get inconsistent results between runs. Second, on databases that do not support row-level locking well, the whole advantage shrinks because every batch still contends for table-level locks. Third, if your merge logic involves complex joins across large dimension tables, the batching helps with the transaction size but does not solve the join performance problem. You might still be scanning millions of rows per batch against an unindexed lookup table. In those cases, consider a staging table approach instead. Load your source data into a staging table in parallel, build the indexes you need there, perform the merge in a single statement, then truncate. It is actually faster than batching when your total volume exceeds roughly 50 million rows per run. The staging approach trades extra storage and a two-step process for significantly better performance on massive datasets. I switched a client from batched merge to staging and cut their nightly load from 4 hours to 22 minutes. The other realistic limitation is concurrency. If multiple processes are running merges on the same target table at the same time, even batched merges will collide. You need either explicit locking, serializable isolation, or a queue mechanism to prevent overlapping runs. I typically wrap the whole operation in a try-catch with an advisory lock or a simple flags table that prevents concurrent execution. It is unglamorous but necessary.
Practical Checklist Before Running
Index your merge keys. Yes, this is obvious and almost nobody does it consistently. Add an index on the target table join column and the source table join column before you start. Without it, every batch scans the target table fully. Use a temporary staging table if your source data requires transformation before merging. Do the transformation once, then merge from the clean data. Log your batch progress somewhere persistent so you can resume without restarting from zero. A simple tracking table with batch number, row count, and timestamp is enough. Monitor your transaction log growth during the run. If it spikes past your configured limit, reduce the batch size and rerun. There is no perfect batch size. There is no single correct implementation. Run a small test with a representative sample of your data, measure lock waits and duration, adjust, and repeat. The pattern works well enough that most teams should have it in their toolkit, but it is not something you set and forget. Every schema change or data distribution shift can change the performance profile entirely.
