Ta yaya za ka export babban dataset ba tare da shafar live system ba?

Question

IT Interview Practice · Accepted Answer

**Karanta daga replica ko snapshot (ba primary ba), page ta keyset pagination maimakon `OFFSET`, throttle read rate, kuma ka stream sakamako kai tsaye zuwa object storage.** Manufa ita ce export rows miliyan 500 ba tare da riƙe long locks ba, spike primary CPU, ko fashe 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)
```

## Me ya sa kowane bangare

- **Read replica ko snapshot.** Tura job zuwa follower domin heavy reads kada su yi gasa da production writes a primary. **Snapshot** yana ba da stable, point-in-time view domin export ɗin ya zama consistent ko da live data yana ci gaba da canzawa.
- **Keyset pagination, ba `OFFSET` ba.** `OFFSET 10000000` yana sa DB ya scan da watsar da rows 10M kowane page — O(n²) a kan export. Keyset yana tuna last key kuma yana yin `WHERE id > :last ORDER BY id LIMIT 5000`, wanda ke hawa index a O(1) ga kowane page.
- **Throttle.** Yi sleep tsakanin batches ko cap rows/daƙiƙa domin replica kada ya cika kuma replication lag ya kasance iyakantacce.
- **Stream zuwa object store.** Rubuta kowane batch zuwa S3/GCS a matsayin NDJSON ko Parquet; kada ka taɓa loading dukan result set zuwa memory. Yi amfani da cursor/server-side cursor domin driver ya stream rows.

## Ra'ayin hira

Mai yin hirar yana bincike ko kana kare live system. Ambaton **replica** table stakes ne; senior signal shi ne **keyset over OFFSET** (da iya bayanin O(n²) trap) tare da **backpressure/throttling** da **consistency** ta snapshot. Tarkon gama gari: single `SELECT *` a transaction ɗaya — yana riƙe read view a buɗe na sa'o'i, yana kumbura DB (Postgres vacuum ba zai iya tsaftace rows da long snapshot har yanzu ke gani ba), kuma yana buffer komai a memory.

**Sulhu:** replica na iya lag, don haka export yana nuna ɗan stale moment — yawanci yayi kyau ga analytics, ba ga financial reconciliation da ke buƙatar exact-now data ba. Keyset yana buƙatar stable, unique, indexed sort key; yana da wahala ga arbitrary sort orders. Lokacin da table yana da girma ƙwarai kuma kana da storage layer, native tool (`pg_dump`, snapshot export zuwa S3, `COPY`) sau da yawa yana doke hand-rolled paging.

Export pipeline

Me ya sa kowane bangare

Ra'ayin hira

Tambayoyi masu alaƙa