Unaexport vipi dataset kubwa bila kuathiri live system?

Question

IT Interview Practice · Accepted Answer

**Soma kutoka replica au snapshot (kamwe si primary), page kupitia keyset pagination badala ya `OFFSET`, throttle read rate, na stream matokeo moja kwa moja kwenda object storage.** Lengo ni kuexport rows 500M bila kushikilia long locks, kuspike primary CPU, au kulipua 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)
```

## Kwa nini kila kipande

- **Read replica au snapshot.** Elekeza job kwenye follower ili heavy reads zisigombane na production writes kwenye primary. **Snapshot** hutoa mtazamo thabiti wa point-in-time ili export iwe consistent hata wakati live data inaendelea kubadilika.
- **Keyset pagination, si `OFFSET`.** `OFFSET 10000000` hufanya DB kuscan na kutupa rows 10M kila page — O(n²) juu ya export. Keyset hukumbuka key ya mwisho na hufanya `WHERE id > :last ORDER BY id LIMIT 5000`, ambayo hupanda index katika O(1) kwa page.
- **Throttle.** Sleep kati ya batches au weka cap ya rows/sekunde ili replica isijae na replication lag ibaki na mipaka.
- **Stream kwenda object store.** Andika kila batch kwa S3/GCS kama NDJSON au Parquet; kamwe usiload result set nzima kwenye memory. Tumia cursor/server-side cursor ili driver istream rows.

## Mtazamo wa mahojiano

Mhojiwa hukagua kama unalinda live system. Kutaja **replica** ni msingi tu; senior signal ni **keyset juu ya OFFSET** (na uwezo wa kueleza mtego wa O(n²)) pamoja na **backpressure/throttling** na **consistency** kupitia snapshot. Mtego wa kawaida: `SELECT *` moja katika transaction moja — hushikilia read view wazi kwa masaa, huvimbisha DB (Postgres vacuum haiwezi kusafisha rows ambazo long snapshot bado inaziona), na hubuffer kila kitu kwenye memory.

**Maelewano:** replica inaweza kuchelewa, hivyo export huakisi wakati uliochakaa kidogo — kwa kawaida sawa kwa analytics, si kwa financial reconciliation inayohitaji exact-now data. Keyset inahitaji stable, unique, indexed sort key; ni ngumu kwa arbitrary sort orders. Wakati jedwali ni kubwa mno na unamiliki storage layer, native tool (`pg_dump`, snapshot export kwenda S3, `COPY`) mara nyingi hushinda hand-rolled paging.

Export pipeline

Kwa nini kila kipande

Mtazamo wa mahojiano

Maswali yanayohusiana