Engineering Atlas › System Design Fundamentals

Splitting tables with partitioning

15 min read · last reviewed 2026-10-17

Open in the interactive app: practice, quiz, mock interview →

In one minute

How it works

Why split a table at all

A single 4 TB events table has one heap, one set of deep B-tree indexes, and one autovacuum job that takes hours to walk it. Split it into one partition per month and a query for last week opens one or two partitions, each partition's indexes fit in memory, and retention becomes DROP TABLE events_2025_09 instead of a billion-row DELETE. The Postgres manual puts it neatly: partitioning stands in for the upper levels of an index, so the heavily used parts are more likely to fit in memory (Postgres docs).

The rule that assigns a row to a partition is a pure function of the partition key. Every benefit and every problem of partitioning comes from how well that key matches the access pattern.

Horizontal strategies

Range partitioning gives each partition a contiguous interval of the key: [2025-09-01, 2025-10-01) or user_id from A to F. Range scans touch only overlapping partitions, and splitting or dropping by range is natural. The cost: a monotonically increasing key (a timestamp, an auto-increment ID) sends every insert to the last partition.

Hash partitioning assigns a row to hash(key) mod N. Writes spread evenly, but a range query must visit all N partitions, and changing N moves most rows, so systems fix N large up front or hash into splittable ranges of a hash space (see the consistent hashing lesson).

List partitioning maps explicit values to partitions, such as region IN ('eu-west', 'eu-central') for data residency.

Composite partitioning layers two rules, for example list by region, then range by month (sub-partitioning in Postgres).

Vertical partitioning

Vertical partitioning moves columns. Moving a 6 KB profile_json blob out of users into user_profiles keeps the hot table narrow, so more rows fit per 8 KB page. Columnar stores take this to the limit by storing every column separately.

Static versus dynamic partitions

In Postgres or MySQL you declare partitions, and a job or an extension like pg_partman creates next month's partition ahead of time. Distributed stores manage them for you: CockroachDB starts each table as one range, splits it when it passes range_max_bytes (512 MiB by default) or its load crosses a threshold, and merges neighbours smaller than range_min_bytes (128 MiB) back together (CockroachDB docs). Bigtable splits tablets at about 100 to 200 MB by default, and HBase does the same with regions, so the layout follows the real key distribution instead of a day-one guess (Bigtable paper). Managed stores also cap what one partition can absorb: a DynamoDB partition is designed for at most 3,000 read units and 1,000 write units per second, however much capacity the table has in total (AWS docs).

Local and global secondary indexes

A local secondary index covers one partition; a global one spans all partitions and is itself partitioned by the indexed column. Local indexes are cheap and drop with their partition, but a lookup by another column probes every partition. Global indexes answer it in one probe but make each write touch two partitions, needing a distributed transaction or an asynchronous index. Postgres has only local indexes, which is why a unique constraint on a partitioned table must include all the partition key columns (Postgres docs).

Down the stack

Layer 1The contract

A partitioned table promises the same SQL results as an unpartitioned one; what changes is cost. A query whose WHERE clause constrains the partition key with constants or parameters is promised to touch only matching partitions (pruning). A query that does not constrain it is promised correctness, not speed: it scans every partition. Uniqueness is only guaranteed within what one index covers, so global uniqueness on a non-key column is not part of the contract in Postgres. In a distributed store the contract adds routing: any node can accept any key and will forward it to the current owner, with a retry if the range moved mid-request.

Layer 2The mechanism

Postgres stores the partitioning scheme in pg_partitioned_table (strategy and key columns) and each child's bounds in pg_class.relpartbound. On INSERT, tuple routing builds a PartitionDispatch for the parent and binary-searches the sorted bounds to find the child, recursing for sub-partitions. Planning-time pruning compares WHERE clauses against the same bounds; execution-time pruning handles values known only at run time, such as prepared statement parameters or the inner side of a nested loop, and shows up in EXPLAIN ANALYZE as Subplans Removed: N.

CockroachDB treats the whole database as one sorted key-value map cut into ranges. Range descriptors (start key, end key, replica locations) live in a two-level index: meta1 points to meta2 ranges, which point to data ranges, meta1's location is gossiped, and every node caches meta2 entries (CockroachDB docs). Load-based splitting now measures CPU by default: with kv.allocator.load_based_rebalancing.objective set to cpu (the default), a range becomes a split candidate above kv.range_split.load_cpu_threshold (500 ms of CPU per second); the older kv.range_split.load_qps_threshold of 2,500 applies only when the objective is qps (CockroachDB settings). Either way, it samples per-key load and picks a split key that roughly halves it; the split only writes new descriptors, because the data is already sorted in place. A single dominant key cannot be split, since one key lives in exactly one range. If that key carries a moderate share, say 60%, CockroachDB can still isolate it in its own range, but at 99% there is nothing useful to split (CockroachDB docs).

Layer 3The machine

Each Postgres partition is a separate relation with its own heap files, visibility map and indexes. DROP TABLE on a partition writes a few catalog rows and unlinks files: milliseconds, and nearly no WAL. Deleting the same 200 million rows with DELETE writes a WAL record per tuple, several GB, and leaves dead tuples for vacuum. Partitioning also bounds index size: B-tree fan-out of a few hundred keeps depth at 3 or 4 levels either way, but a 10 GB per-partition index keeps its hot pages in shared buffers where a 900 GB one cannot, so a lookup costs about one random read instead of several.

The overheads show up in the lock manager and planner. A query that cannot prune takes an AccessShareLock on every partition and every index. Before PostgreSQL 18 each backend had only 16 fast-path lock slots, so a query over 50 partitions with 3 indexes each spilled into the shared lock table and contended on its 16 lock_manager LWLocks under concurrency. Postgres 18 sizes the fast-path arrays from max_locks_per_transaction, so the default of 64 gives 64 slots, up to a cap of 16,384 (Postgres commit). Note also that partitions pruned at executor start-up are still locked. Planning time also grows with the number of unpruned partitions, and each session loads metadata for every partition it touches into its own memory. The manual says the planner handles up to a few thousand partitions fairly well only if typical queries prune all but a few (Postgres docs), so thousands of tiny partitions are slower than dozens of reasonable ones.

Why it's built this way

  1. Memory is finite and random reads are slow, so smaller partitions keep each index small enough to stay hot.
  2. Deleting rows one by one costs WAL and vacuum work proportional to the rows, while dropping a file costs a constant, so retention is aligned with partition boundaries.
  3. Most queries filter on the same one or two columns, so choosing one of them as the partition key lets the planner skip most of the data with a cheap comparison against sorted bounds.
  4. Data distribution is unknown and changes over time, so distributed stores split and merge dynamically by size and load instead of trusting a fixed layout.
  5. Splitting by load needs per-key statistics, which are expensive, so they are sampled only after a range is already hot.

Diagram

flowchart TD
  Q["INSERT / SELECT with key"] --> R{"Partition bounds (sorted)"}
  R -->|"key < 2025-09-01"| P1["events_2025_08"]
  R -->|"2025-09-01 to 2025-10-01"| P2["events_2025_09"]
  R -->|"key >= 2025-10-01"| P3["events_2025_10 (hot)"]
  P3 --> L{"QPS > threshold?"}
  L -->|yes| S["Sample keys, split at load median"]
  S --> P3a["range A"]
  S --> P3b["range B"]
  P1 --> D["Retention: DROP partition"]

Worked example

A SaaS audit log takes 3,000 inserts per second, about 260 million rows a day at 300 bytes each, roughly 78 GB a day before indexes. Retention is 90 days, so the table holds about 7 TB.

Unpartitioned, the nightly retention job deletes 260 million rows: tens of GB of WAL for replicas to replay, then 78 GB of dead tuples for vacuum. Partitioned by day, retention is DROP TABLE audit_2025_07_18.

Queries are "events for tenant X in the last 24 hours". With daily range partitions on created_at, the planner opens one or two partitions of 78 GB each instead of 7 TB, and an index on (tenant_id, created_at) inside each partition is about 10 GB rather than 900 GB. Ninety daily partitions is a comfortable count; hourly partitions would give 2,160, and a dashboard query over 30 days would lock and plan 720 partitions.

The catch: all 3,000 inserts per second land in today's partition. One Postgres server absorbs that, but in a distributed store it puts every write on one range and one node. Prefixing the key with a hash bucket, (hash(tenant_id) % 16, created_at), trades 16-way scatter on reads for 16-way spread on writes.

In the wild

Trade-offs

OptionStrengthsWeaknessesChoose when
RangeRange scans prune well, cheap retention, natural splitsSequential keys create a hot last partitionTime series, ordered scans, retention by age
HashEven write spread, no hot tailRange queries hit every partition; changing N moves dataPoint lookups by ID, write-heavy, no range scans
ListExplicit placement, data residencySkew if one value dominates; new values need new partitionsSmall fixed set of categories or regions
CompositeCombines placement and pruningMore partitions, more planning and opsRegion residency plus time retention
VerticalNarrow hot rows, fewer pages per lookupJoins to reassemble; two writes per updateWide rows with rarely read columns

Pitfalls in production

Leadership lens

Check yourself

  1. A table is range-partitioned by month on created_at. Why is SELECT * FROM t WHERE id = 42 slow, and what are two fixes?

    Show answer

    The query has no constraint on the partition key, so it probes every partition's index. Add a created_at bound from the caller, or encode the partition in the ID (for example a time-ordered ID) so the key can be derived.

  2. Why does dropping a partition generate almost no WAL while deleting the same rows generates gigabytes?

    Show answer

    DROP changes a few catalog rows and unlinks files, a constant amount of work. DELETE logs a change per tuple and per index entry, and leaves dead tuples for vacuum.

  3. A CockroachDB range is at 10,000 QPS but load-based splitting never splits it. What is the likely cause?

    Show answer

    Most of the load is on one key, and a single key cannot be split across ranges. The fix is in the data model, such as spreading that key's writes over several keys.

Sources and further study

Primary

Systems docs

Books

Other curricula

Watch

Free resources

Open in the interactive app: practice, quiz, mock interview →