Reading Live Databases Safely

Every query an extract sends to a production database competes with the application the database serves. Checkout wants a few milliseconds per query and a cache full of hot rows, while a bulk extract wants every page of a big table once. Left alone, the extract wins because it asks for more, so a safe reader is built to lose.

Four ways a reader hurts its source

MechanismWhat the extract doesWhat the application feels
Held snapshotReads a 400M-row table inside 1 transaction for 6 hoursOld row versions pile up, so every query reads more pages
Cache evictionPulls the cold pages of a full scan into memoryHot rows get pushed out and are read from disk again
Disk and CPU contentionReads sequentially at full speedEvery query sharing those disks waits longer
Locks and connectionsTakes a table lock to dump, or opens 32 parallel connectionsWrites queue behind the lock, or the app's connection pool runs dry

Databases that keep multiple row versions leave each updated row's old version in place until no open transaction could still read it, so a 6-hour transaction makes every busy table carry 6 hours of dead versions. Live queries then scan more pages for the same rows, and latency climbs while CPU graphs look normal.

Read from a replica, in short transactions

A read replica takes cache and CPU contention off the primary, making it the first move for any large extract. But a replica constantly replays the primary's changes, and a long query can hold rows a replayed change must remove, so the replica cancels the query or pauses replay and falls behind. Reporting the replica's open snapshots to the primary stops the cancellations by moving the old-version pileup back onto the primary. Either way, the extract must run as many short transactions.

Keyset chunks instead of OFFSET

Paging with OFFSET makes the database read and discard every row before the page it returns, so each page costs more than the last. Keyset paging starts each chunk after the last key it read: every chunk is an index range read of 10,000 rows in its own short transaction, and the reader can stop or resume between chunks. On a 400M-row orders table read 10,000 rows at a time, the totals compare like this:

SELECT
    order_id,
    customer_id,
    status,
    total_cents,
    updated_at
FROM orders
WHERE order_id > 184020000
ORDER BY order_id
LIMIT 10000

Throttle on the application's own signals

A fixed pause between chunks is a guess that fails on every busy day. One workable rule instead: start at 5 chunks per second; when replica lag passes 10 seconds or checkout's p99 latency passes 200 ms, halve the rate; after a full minute with both healthy, add 1 chunk per second. Then add limits the throttle can never raise, such as 2 connections and a 30-second statement timeout, so a bad query plan dies in seconds.

A consistent snapshot without a table lock

Starting log-based CDC on an existing table needs a full copy plus every change after it. The old recipe locks the table, records the log position and dumps the rows, blocking writes until the dump finishes. The watermark recipe keeps the log reader running and copies in keyset chunks, bracketing each chunk with 2 marker rows written to a small dedicated table: a low watermark before the SELECT, a high watermark after it. These are log positions, unrelated to event-time watermarks. Any key the log shows changing between the 2 markers is taken from the log and dropped from the chunk, since the log version is at least as new.

StepReader actionEmitted downstream
1Insert low watermark LNothing
2SELECT order_id 5001 to 5003; all 3 read as placedNothing yet
3Insert high watermark HNothing
4Read the log from L to H: order 5002 changed to shipped5002 shipped, from the log
5Reach H in the log and drop 5002 from the chunk5001 placed, 5003 placed

A nightly extract on a replica pages through orders with OFFSET in 1 transaction lasting 5 hours. The replica kept canceling it, so the team turned on snapshot reporting to the primary. It now finishes, but checkout latency on the primary creeps up every night. What fixes the cause?

About this lesson

This section is part of the Ingestion Patterns: Advanced lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.