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
| Mechanism | What the extract does | What the application feels |
|---|---|---|
| Held snapshot | Reads a 400M-row table inside 1 transaction for 6 hours | Old row versions pile up, so every query reads more pages |
| Cache eviction | Pulls the cold pages of a full scan into memory | Hot rows get pushed out and are read from disk again |
| Disk and CPU contention | Reads sequentially at full speed | Every query sharing those disks waits longer |
| Locks and connections | Takes a table lock to dump, or opens 32 parallel connections | Writes 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:
- 8 trillion
- 20,000x
SELECT
order_id,
customer_id,
status,
total_cents,
updated_at
FROM orders
WHERE order_id > 184020000
ORDER BY order_id
LIMIT 10000Throttle 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.
| Step | Reader action | Emitted downstream |
|---|---|---|
| 1 | Insert low watermark L | Nothing |
| 2 | SELECT order_id 5001 to 5003; all 3 read as placed | Nothing yet |
| 3 | Insert high watermark H | Nothing |
| 4 | Read the log from L to H: order 5002 changed to shipped | 5002 shipped, from the log |
| 5 | Reach H in the log and drop 5002 from the chunk | 5001 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?
- Turn reporting off again and give the replica more memory, so the long query stops getting canceled.
- Rewrite the extract as keyset chunks in short transactions, so no snapshot stays open for hours anywhere.
- Keep the single transaction and switch OFFSET to keyset paging, so each page reads far fewer rows.
- Move the extract back to the primary, where no replay happens and no query gets canceled.
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.