Unaquery vipi records bilioni 10 chini ya 100ms?

Question

IT Interview Practice · Accepted Answer

Buni kwa ajili ya **access pattern kwanza**, kisha chagua partitioning + index/PK sahihi ili query yoyote moja iguse kipande kidogo, chenye mipaka — kamwe isiskan rows bilioni 10. Chini ya 100ms juu ya records bilioni 10 si kuhusu disk ya haraka; ni kuhusu kufanya working set kuwa ndogo.

```text
Query ─▶ Router ──▶ Partition (by tenant/time) ──▶ Index/PK lookup ─▶ ~thousands of rows
   │                    (prune 99.9% of data)         (B-tree / sort key)
   └─▶ Cache (Redis) ─▶ hit? return in <5ms
                        miss ▼
                   Columnar store (ClickHouse) for aggregates / scans
```

## Access patterns huendesha kila kitu

Kabla ya kuchagua tech, uliza: **point lookup au range/aggregate?** "Pata orders 20 za mwisho za user X" ni point/range query — unataka partition na sort key vilingane nayo hasa. "Jumlisha revenue kwa region mwezi huu" ni analytical scan — row store ni chombo kibaya. Kumodeli query kwanza ndiko kunakotenganisha usanifu unaofanya kazi na ule unaoonekana wa busara lakini hufanya full-scan.

## Partitioning hukata nafasi ya utafutaji

Gawanya jedwali kwa key inayoonekana katika kila query — kwa kawaida **tenant_id + time**. Kwa time partitions, query ya "siku 7 zilizopita" husoma partitions 7 na kupuuza miaka 3 mingine. Hii ni *partition pruning*: router huondoa 99.9% ya data kabla index yoyote haijashauriwa.

## Usanifu sahihi wa index / PK

Ndani ya partition, **primary key lazima iweke mbele filter columns za query**. Katika DynamoDB: partition key = `tenant`, sort key = `timestamp` → range queries ni O(log n) + sequential read. Katika columnar store kama **ClickHouse**, sorting key (`ORDER BY (tenant, ts)`) huruhusu engine kuruka granules na kusoma blocks zinazolingana tu. Covering index huepuka row fetch ya ziada kabisa.

## Chaguo la store

- **Point lookups, high write:** NoSQL key-value (DynamoDB, Cassandra) — O(1)-ish kwa key, huscale kwa usawa (horizontally).
- **Analytics/aggregates:** columnar (ClickHouse, BigQuery) — husoma columns zinazohitajika tu, vectorized.
- **Cache the hot 1%:** Redis mbele hunyonya reads zinazorudiwa kwa <5ms; rows bilioni 10 lakini mamilioni machache ya hot keys.

## Mtazamo wa mahojiano

Mhojiwa anataka kukuona ukisema **"queries ni zipi?" kabla ya "database gani?"**. Watahiniwa wazuri hukimbilia indexes; bora sana hutambua kuwa index juu ya rows bilioni 10 yenyewe ni kubwa — hivyo *unapartition kwanza ili kupunguza kile index inachofunika*. Kutaja mgawanyiko wa row-vs-columnar na cache tier huashiria uzoefu halisi wa scale.

**Mtego wa kawaida:** kupendekeza B-tree index moja kubwa kama jibu. Husaidia point lookups lakini haifanyi lolote kwa analytical scans, na writes zake huzuia ingestion.

## Maelewano na aina za hitilafu

- **Partition key mbaya** = cross-partition scatter-gather, latency huvimba. Chagua key iliyopo katika queries.
- **Hot partition:** tenant maarufu hulemea shard moja — ongeza salting suffix.
- **Cache staleness:** invalidate kwenye write au kubali TTL lag.
- **Gharama ya denormalization:** mara nyingi hurudia data kwa kila access pattern; storage ni nafuu, query ya sekunde 10 si nafuu.

Access patterns huendesha kila kitu

Partitioning hukata nafasi ya utafutaji

Usanifu sahihi wa index / PK

Chaguo la store

Mtazamo wa mahojiano

Maelewano na aina za hitilafu

Maswali yanayohusiana