Ta yaya za ka partition table ɗaya da rows biliyoyin?

Question

IT Interview Practice · Accepted Answer

**Raba giant table zuwa ƙananan physical child tables da partition key, domin kowace query ta taɓa partitions da take buƙata kawai (partition pruning) maimakon scan na biliyoyin rows.** Table da order rows biliyan 5 har yanzu yana nuna kamar logical table ɗaya, amma engine yana tsallake 99% na shi lokacin da `WHERE` ɗinka ke filter a kan partition key.

## Siffar partitioned table

```text
                 orders (logical parent)
                        │  partition key = created_at
       ┌────────────────┼────────────────┬───────────────┐
       ▼                ▼                ▼               ▼
 orders_2024_q1   orders_2024_q2   orders_2024_q3   orders_2025_q1
  (child table)    (child table)    (child table)    (child, active)
       │                                                  │
   local index                                        local index
   (per child)                                        (per child)
```

## Zaɓar partition strategy

- **Range** — da date/id ranges. Mafi kyau ga time-series (orders, logs, events); recent data yana zama hot kuma old ranges suna da sauƙin drop.
- **Hash** — even spread a kan partitions N da `hash(key) % N`. Yi amfani lokacin da babu natural range kuma kana son balanced load (misali `user_id`).
- **List** — explicit buckets (`region IN ('EU','US')`). Yayi kyau ga tenant ko region isolation.

```sql
-- Postgres: range partition by month, prune on created_at
CREATE TABLE orders (id bigint, created_at date, amount numeric)
  PARTITION BY RANGE (created_at);

CREATE TABLE orders_2025_01 PARTITION OF orders
  FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

-- This scans ONLY orders_2025_01, not the whole table
SELECT sum(amount) FROM orders
 WHERE created_at >= '2025-01-01' AND created_at < '2025-02-01';
```

## Pruning, indexes, maintenance

- **Partition pruning** yana aiki kawai idan query ta filter a kan **partition key**. Filter a kan wani abu kuma kana scan kowace partition — kuskuren #1.
- **Local index** = index ɗaya ga kowane child (default a Postgres); mai arha don gina da drop tare da partition. **Global index** (Oracle) yana shafar dukan partitions — yana taimaka unique lookups a kan partitions amma dole a sake gina shi lokacin da ka drop ɗaya.
- **Maintenance** yana zama O(partition): archive old data da `DROP TABLE orders_2019` (nan take, babu giant `DELETE`), kuma `ATTACH`/`DETACH` don rolling windows.

## Ra'ayin hira

Mai yin hirar yana bincike ko ka fahimci **partitioning ba indexing ba ne** — yana rage data da aka scan, index yana rage rows da aka bincika a cikin scan; sau da yawa kana buƙatar duka. Amsa mai kyau tana ambaton range/hash/list; amsa mai kyau sosai tana nuna cewa **partition key dole ya daidaita da dominant query filter ɗinka**, tana gargaɗi cewa key mara kyau yana kashe pruning, kuma tana ambaton cheap archival ta `DROP`.

**Sulhu:** cross-partition queries (babu key filter, ko unique constraints a kan partitions) suna zama masu jinkiri kuma unique enforcement yana da wuya. Kada ka partition 10M-row table — kana ƙara operational complexity ba tare da riba ba. Partition lokacin da table yana da girma **kuma** queries suna scoped ta key ta halitta.

Siffar partitioned table

Zaɓar partition strategy

Pruning, indexes, maintenance

Ra'ayin hira

Tambayoyi masu alaƙa