Unapartition vipi jedwali moja lenye rows bilioni?

Question

IT Interview Practice · Accepted Answer

**Gawanya jedwali kubwa kuwa physical child tables ndogo kwa partition key, ili kila query iguse partitions inazohitaji tu (partition pruning) badala ya kuscan rows bilioni.** Jedwali lenye order rows 5B bado hutenda kama jedwali moja la kimantiki, lakini engine huruka 99% yake wakati `WHERE` yako inachuja kwa partition key.

## Umbo la jedwali lililopartitioniwa

```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)
```

## Kuchagua partition strategy

- **Range** — kwa date/id ranges. Bora kwa time-series (orders, logs, events); data ya hivi karibuni hubaki hot na ranges za zamani ni rahisi kudondosha.
- **Hash** — usambazaji sawa kuvuka N partitions kwa `hash(key) % N`. Tumia wakati hakuna natural range na unataka balanced load (mfano `user_id`).
- **List** — buckets za wazi (`region IN ('EU','US')`). Nzuri kwa tenant au region isolation.

```sql
-- Postgres: range partition kwa mwezi, prune kwa 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');

-- Hii huscan orders_2025_01 PEKEE, si jedwali zima
SELECT sum(amount) FROM orders
 WHERE created_at >= '2025-01-01' AND created_at < '2025-02-01';
```

## Pruning, indexes, maintenance

- **Partition pruning** hufanya kazi tu ikiwa query inachuja kwa **partition key**. Chuja kwa kitu kingine na unascan kila partition — kosa #1.
- **Local index** = index moja kwa kila child (default katika Postgres); nafuu kujenga na kudondosha na partition. **Global index** (Oracle) hufunika partitions zote — husaidia unique lookups kuvuka partitions lakini lazima ijengwe upya unapodondosha moja.
- **Maintenance** huwa O(partition): archive data ya zamani kwa `DROP TABLE orders_2019` (papo hapo, hakuna `DELETE` kubwa), na `ATTACH`/`DETACH` kwa rolling windows.

## Mtazamo wa mahojiano

Mhojiwa huchunguza kama unaelewa kuwa **partitioning si indexing** — hupunguza data iliyoscankiwa, index hupunguza rows zilizochunguzwa ndani ya scan; mara nyingi unahitaji zote mbili. Jibu zuri hutaja range/hash/list; jibu bora sana husisitiza kuwa **partition key lazima ilingane na dominant query filter yako**, huonya kuwa key mbaya huua pruning, na hutaja archival nafuu kupitia `DROP`.

**Trade-off:** cross-partition queries (hakuna key filter, au unique constraints kuvuka partitions) huwa polepole na utekelezaji wa unique ni mgumu zaidi. Usipartition jedwali la 10M-row — unaongeza operational complexity bila faida. Partition wakati jedwali ni kubwa **na** queries zimescope kwa key kiasili.

Umbo la jedwali lililopartitioniwa

Kuchagua partition strategy

Pruning, indexes, maintenance

Mtazamo wa mahojiano

Maswali yanayohusiana