How do you export a huge dataset without impacting the live system?

Question

IT Interview Practice · Accepted Answer

**Read from a replica or snapshot (never the primary), page through with keyset pagination instead of `OFFSET`, throttle the read rate, and stream results straight to object storage.** The goal is to export 500M rows without holding long locks, spiking primary CPU, or blowing up memory.

## Export pipeline

```text
  Primary DB ──async replication──▶ Read replica / snapshot
                                          │  (isolated from live traffic)
                                          ▼
                                   Export worker
                              keyset page: WHERE id > :last
                                          │  throttle (sleep / rate limit)
                                          ▼
                                   Stream (batches)
                                          │
                                          ▼
                                   S3 / GCS  (NDJSON / Parquet)
```

## Why each piece

- **Read replica or snapshot.** Point the job at a follower so heavy reads never compete with production writes on the primary. A **snapshot** gives a stable, point-in-time view so the export is consistent even while live data keeps changing.
- **Keyset pagination, not `OFFSET`.** `OFFSET 10000000` makes the DB scan and discard 10M rows every page — O(n²) over the export. Keyset remembers the last key and does `WHERE id > :last ORDER BY id LIMIT 5000`, which rides the index in O(1) per page.
- **Throttle.** Sleep between batches or cap rows/sec so the replica isn't saturated and replication lag stays bounded.
- **Stream to object store.** Write each batch to S3/GCS as NDJSON or Parquet; never load the whole result set into memory. Use a cursor/server-side cursor so the driver streams rows.

## Interview perspective

The interviewer checks whether you protect the live system. Naming a **replica** is table stakes; the senior signal is **keyset over OFFSET** (and being able to explain the O(n²) trap) plus **backpressure/throttling** and **consistency** via a snapshot. A common trap: a single `SELECT *` in one transaction — it holds a read view open for hours, bloats the DB (Postgres vacuum can't clean rows the long snapshot still sees), and buffers everything in memory.

**Trade-offs:** a replica may lag, so the export reflects a slightly stale moment — usually fine for analytics, not for financial reconciliation needing exact-now data. Keyset needs a stable, unique, indexed sort key; it's awkward for arbitrary sort orders. When the table is enormous and you own the storage layer, a native tool (`pg_dump`, snapshot export to S3, `COPY`) often beats hand-rolled paging.

Export pipeline

Why each piece

Interview perspective

Related questions