How do you partition a single table with billions of rows?

Question

IT Interview Practice · Accepted Answer

**Split the giant table into smaller physical child tables by a partition key, so each query touches only the partitions it needs (partition pruning) instead of scanning billions of rows.** A table with 5B order rows still behaves like one logical table, but the engine skips 99% of it when your `WHERE` filters on the partition key.

## The shape of a 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)
```

## Choosing a partition strategy

- **Range** — by date/id ranges. Best for time-series (orders, logs, events); recent data stays hot and old ranges are easy to drop.
- **Hash** — even spread across N partitions by `hash(key) % N`. Use when there's no natural range and you want balanced load (e.g. `user_id`).
- **List** — explicit buckets (`region IN ('EU','US')`). Good for tenant or 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** only works if the query filters on the **partition key**. Filter on something else and you scan every partition — the #1 mistake.
- **Local index** = one index per child (default in Postgres); cheap to build and drop with the partition. **Global index** (Oracle) spans all partitions — helps unique lookups across partitions but must be rebuilt when you drop one.
- **Maintenance** becomes O(partition): archive old data with `DROP TABLE orders_2019` (instant, no giant `DELETE`), and `ATTACH`/`DETACH` for rolling windows.

## Interview perspective

The interviewer probes whether you understand that **partitioning is not indexing** — it reduces the data scanned, an index reduces rows examined within a scan; you often need both. Good answer names range/hash/list; great answer stresses that **the partition key must match your dominant query filter**, warns that a wrong key kills pruning, and mentions cheap archival via `DROP`.

**Trade-off:** cross-partition queries (no key filter, or unique constraints across partitions) get slower and unique enforcement is harder. Don't partition a 10M-row table — you add operational complexity for no gain. Partition when the table is huge **and** queries are naturally scoped by the key.

The shape of a partitioned table

Choosing a partition strategy

Pruning, indexes, maintenance

Interview perspective

Related questions