Skip to content
Optimal Lab

Databases · interactive

Table partitioning

An orders table holds 120 million rows: 10 million a month. Partitioning by month stores each month as its own physical table behind one name. Queries that filter on the partition key skip the rest, and old months drop in an instant.

SELECT … WHERE created_at > now() - interval '7 days'

orders_nov

10M rows

orders_dec

10M rows

orders_jan

10M rows

orders_feb

10M rows

orders_mar

10M rows

orders_apr

10M rows

orders_may

10M rows

orders_jun

10M rows

orders_jul

10M rows

orders_aug

10M rows

orders_sep

10M rows

orders_oct

10M rows

Partitions touched
1 of 12
Rows involved
10M
Cost
fast

10,000,000 rows per month

Partition pruning: only October can contain the last 7 days, so 11 partitions are skipped without being read.

Operation

Table

Step 2 of 4

  1. 1

    One logical table, many physical ones

    Rows are routed to a partition by the key: a date range, a list of values or a hash.

    PARTITION BY RANGE (created_at)

  2. 2

    Partition pruning

    Filter on the partition key and the planner skips partitions that can't match.

    Smaller indexes per partition, too.

  3. 3

    Drop old data instantly

    Detaching or dropping a partition is a metadata change. Deleting millions of rows is hours of I/O and bloat.

    The most common reason to partition.

  4. 4

    Queries without the key get worse

    They must visit every partition. Pick a key your most important queries filter on, and don't partition small tables.

    Partitioning isn't sharding: it's still one server.

In production

Tools and technologies

Large time-series tables are almost always partitioned.

Not tied to one language — use these names when you search docs or design a system.