Adana & query transaction history ga ɗarurruwan miliyoyin accounts

Question

IT Interview Practice · Accepted Answer

**Partition ɗin ledger da account da time, ka ajiye recent data hot da old data cold, kuma ka ɗauki rows a matsayin append-only immutable ledger — domin history na account ɗaya ya zama ƙarami, indexed slice, ba scan na biliyoyin rows ba.** Access pattern koyaushe shi ne "account ɗaya, date range", don haka partition don daidaita da shi.

## Gine-gine

```text
Write ─▶ Ledger table (append-only, immutable)
          partition by (account_hash, month)
              │
     ┌────────┴─────────┐
  HOT tier            COLD tier
  last 3 mo           older
  Postgres/SSD        S3/Parquet, Glacier
     │                    │
  index-seek        keyset paginate ──▶ API (account+range)
```

## Yadda yake aiki

- **Partition da account + time.** Hash account id zuwa buckets N, sannan range-partition da month. Query na account/month ɗaya yana bugun partition ɗaya daidai — DB yana prune sauran. Wannan yana iyakance kowane read ba tare da la'akari da jimlar girma ba.
- **Immutable ledger.** Kada ka taɓa `UPDATE` transaction; gyare-gyare sabbin reversing rows ne. Wannan yana ba da audit trail, safe replication, kuma yana bar ka cache da ƙarfi (rows ba sa canzawa).
- **Hot/cold tiering.** Ajiye kwana kusan 3 na ƙarshe a fast SSD/Postgres don low-latency reads; naɗa tsofaffin partitions zuwa **S3/Parquet** (query ta Athena/Trino) ko Glacier. 95% na queries suna bugun recent data, don haka yawancin storage na iya zama mai arha.
- **Keyset pagination.** Page da `WHERE (ts, id) < (:last_ts, :last_id) ORDER BY ts DESC LIMIT 50` — ba `OFFSET` ba. Offset yana scan da watsar da rows da aka tsallake; keyset yana seek index kai tsaye, O(1) ga kowane page.

## Ra'ayin hira

Mai yin hirar yana bincike **zaɓin partition-key** da **pagination**. Amsoshi masu kyau suna cewa "partition da account"; amsoshi masu kyau sosai suna bayanin **me ya sa keyset ke doke OFFSET** a deep pages, me ya sa **ledger immutable ne** (auditability, babu lock contention), da yadda **tiering** ke rage cost. Tarko shi ne single giant table da composite index — yana aiki har sai index bai daidaita a RAM ba kuma kowane read ya zama disk-bound.

## Sulhu & kurakurai

- **Partition key yana da wuya a canza.** Zaɓi account+time tun farko; re-partitioning ɗarurruwan miliyoyin rows daga baya aikin migration ne.
- **Cross-account queries suna wahala.** "Total volume across all accounts" yanzu yana fan out a kan partitions — tura waɗannan zuwa OLAP rollup maimakon.
- **Cold reads suna jinkiri.** Samo statement na shekaru 3 daga Glacier yana ɗaukar mintuna; saita tsammanin user ko pre-warm a buƙata.
- **Hot partitions.** Whale account ko wata na yanzu na iya zama hotspot; sub-partition ko cache shi.
- **Lokacin da ba za a yi ba:** miliyoyin rows (ba ɗarurruwan miliyoyin ba) suna daidaita a Postgres table ɗaya mai index — kada ka gina tiering da ba za ka buƙata ba. Nemi wannan a sikeli inda index na table ɗaya bai ci gaba da daidaita a memory ba.

Gine-gine

Yadda yake aiki

Ra'ayin hira

Sulhu & kurakurai

Tambayoyi masu alaƙa