Ta yaya za ka yi query kan records biliyan 10 a ƙasa da 100ms?

Question

IT Interview Practice · Accepted Answer

Yi ƙira don **access pattern da farko**, sannan ka zaɓi partitioning + index/PK daidai domin kowace query ɗaya ta taɓa ƙaramin, iyakantaccen bangare — ba scan na rows biliyan 10 ba. Ƙasa da 100ms kan records biliyan 10 ba game da disk mai sauri ba ne; game da mai da working set ƙarami ne.

```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 suna motsa komai

Kafin zaɓar fasaha, tambaya: **point lookup ko range/aggregate?** "Samo orders 20 na ƙarshe na user X" query ne na point/range — kana son partition da sort key su daidaita da shi daidai. "Sum revenue ta yanki wannan wata" analytical scan ne — row store kayan aiki ne mara kyau. Modeling ɗin query da farko shi ne abin da ke raba ƙira da ke aiki daga wanda yayi kama mai ma'ana amma yana full-scan.

## Partitioning yana rage sararin bincike

Raba table da key da ke bayyana a kowace query — yawanci **tenant_id + time**. Da time partitions, query na "kwana 7 na ƙarshe" yana karanta partitions 7 kuma yana yin watsi da sauran shekaru 3. Wannan *partition pruning* ne: router yana kawar da 99.9% na data kafin a nemi wani index.

## Index / PK design daidai

A cikin partition, **primary key dole ya sanya columns na filter na query a gaba**. A DynamoDB: partition key = `tenant`, sort key = `timestamp` → range queries su ne O(log n) + sequential read. A columnar store kamar **ClickHouse**, sorting key (`ORDER BY (tenant, ts)`) yana bar engine ya tsallake granules kuma ya karanta matching blocks kawai. Covering index yana guje wa extra row fetch gaba ɗaya.

## Zaɓin store

- **Point lookups, write mai yawa:** NoSQL key-value (DynamoDB, Cassandra) — kusan O(1) da key, yana scale horizontally.
- **Analytics/aggregates:** columnar (ClickHouse, BigQuery) — yana karanta columns da ake buƙata kawai, vectorized.
- **Cache hot 1%:** Redis a gaba yana ɗauke da reads da ake maimaitawa a <5ms; rows biliyan 10 amma 'yan miliyoyin hot keys.

## Ra'ayin hira

Mai yin hirar yana son ganin ka ce **"menene queries?" kafin "wace database?"**. 'Yan takara masu kyau suna neman indexes; masu kyau sosai suna lura cewa index kan rows biliyan 10 shi kansa yana da girma — don haka kana *partition da farko don rage abin da index ke rufewa*. Ambaton rabuwar row-vs-columnar da cache tier yana nuna gogewar ainihin sikeli.

**Tarkon gama gari:** ba da shawarar B-tree index ɗaya mai girma a matsayin amsar. Yana taimaka point lookups amma bai yi komai ga analytical scans ba, kuma rubuce-rubucensa suna toshe ingestion.

## Sulhu & hanyoyin gazawa

- **Partition key mara kyau** = cross-partition scatter-gather, latency yana faɗaɗɗa. Zaɓi key da ke cikin queries.
- **Hot partition:** celebrity tenant yana cika shard ɗaya — ƙara salting suffix.
- **Cache staleness:** invalidate a write ko ka karɓi TTL lag.
- **Denormalization cost:** sau da yawa kana kwafin data ga kowane access pattern; storage mai arha ne, query na daƙiƙa 10 ba haka ba.

Access patterns suna motsa komai

Partitioning yana rage sararin bincike

Index / PK design daidai

Zaɓin store

Ra'ayin hira

Sulhu & hanyoyin gazawa

Tambayoyi masu alaƙa