Partitioning a table

Archiving took Tiffin's orders table from 200 million rows down to about 80 million, roughly the last year of orders at 200,000 to 250,000 a day. Good, but the business is still growing, and next year that number will be back over 100 million. More importantly, the nightly archive job is a slow, careful dance of batched deletes.

There is a way to keep one big table logically while storing it as many small ones underneath. Queries don't change. Archiving becomes one command. And it all happens inside the same database.

One table, many pieces

Table partitioning splits one large table into smaller physical tables, called partitions, based on the value of a column. The application still sees one table.

For Tiffin, the obvious column is created_at. Each month of orders gets its own partition:

%% caption: One logical table, stored as one physical table per month. Every row lives in exactly one partition.
flowchart TD
  O["orders<br/>the table the app sees"]:::hot --> A[("orders_2026_08<br/>August")]:::store & B[("orders_2026_09<br/>September")]:::store & C[("orders_2026_10<br/>October")]:::store

When the app inserts an order on 18 October, the database puts the row in orders_2026_10. When it reads an order, the database figures out which partitions could possibly hold the answer and looks only there.

This is all one database on one machine. That's the key difference from sharding, which splits data across many databases. We'll get to sharding later in this module. Partitioning is the cheap, local version.

What it looks like in Postgres

Postgres has had built-in partitioning since version 10. You declare the parent table and say how it's split:

CREATE TABLE orders (
  id            bigint      NOT NULL,
  customer_id   bigint      NOT NULL,
  restaurant_id bigint      NOT NULL,
  status        text        NOT NULL,
  total_paise   integer     NOT NULL,
  created_at    timestamptz NOT NULL,
  PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

CREATE TABLE orders_2026_10 PARTITION OF orders
  FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');

CREATE TABLE orders_2026_11 PARTITION OF orders
  FOR VALUES FROM ('2026-11-01') TO ('2026-12-01');

Two details are easy to miss:

• The primary key includes created_at. In Postgres, a unique constraint on a partitioned table must include the partition column, because each partition only checks uniqueness inside itself. So the key is (id, created_at), not just id. If your IDs are generated uniquely anyway (and they should be), this is mostly harmless. The catch: a foreign key that points at orders (from order_items, say) now has to carry both columns too.
• Partitions must exist before rows arrive. If 1 November comes and orders_2026_11 doesn't exist, inserts fail. So a scheduled job creates next month's partition a few weeks ahead. Tools like the pg_partman extension do this for you.

Indexes you create on the parent are created on every partition automatically. Each partition gets its own, smaller index.

Why it helps

Queries skip partitions they don't need

The restaurant tablet asks for today's orders:

SELECT id, status, total_paise
FROM orders
WHERE restaurant_id = 4812
  AND created_at >= '2026-10-18'
  AND created_at <  '2026-10-19';

Postgres sees the created_at filter and knows only orders_2026_10 can match. It never opens the other partitions. This is called partition pruning. Instead of searching one index over 80 million rows, it searches one over about 7.5 million.

The hot partition stays in memory

This month's partition and its indexes are a few gigabytes. They're read and written constantly, so they stay in RAM. Old partitions sit on disk and nobody disturbs them. The working-set problem from the first lesson mostly goes away, because hot and cold rows are no longer mixed in the same pages.

Archiving becomes almost free

Remember the 7-hour batched delete? With monthly partitions, archiving last year's October looks like this:

ALTER TABLE orders DETACH PARTITION orders_2025_10;
-- export orders_2025_10 to Parquet, verify it, then:
DROP TABLE orders_2025_10;

Dropping a table takes milliseconds, whether it holds 1 row or 10 million, because the database deletes its files instead of its rows. No dead rows, no huge log, no replica lag. Detaching needs a brief lock on the parent table, so on Postgres 14+ use DETACH PARTITION ... CONCURRENTLY to avoid stalling live queries. This alone is often the reason teams partition.

Maintenance gets smaller

VACUUM, index rebuilds and statistics updates now run on one partition at a time. Old partitions that never change hardly need any maintenance at all.

Choosing how to split

Range by time is the most common choice for data like orders, events and logs, because almost every query is about recent data and old data gets archived by age. But there are other kinds:

The rule for picking the column: choose the column that most of your important queries filter on. If your hot queries all have a time filter, partition by time. If they all have a city filter and never a time filter, partitioning by time will make them slower, because every query now visits every partition.

The costs

Partitioning is cheap, not free.

• Queries without the partition column touch every partition. One query scanning 36 partitions is slower than the same query on one table with a good index. Check your slow-query log before you partition.
• Too many partitions hurt. Planning a query over thousands of partitions costs real time. Monthly or weekly is usually right. Daily partitions for ten years is 3,650 tables, which is too many for most workloads.
• Unique constraints must include the partition key. You can't enforce "email is unique" across a table partitioned by time. Keep tables that need global uniqueness unpartitioned.
• Migrating an existing table is work. You can't flip a switch on an 80-million-row table. The usual path is to create a new partitioned table, write new rows to it, copy old rows across in batches, then swap names. Do it before the table is huge, not after.

Partitioning versus sharding

People mix these up, so here's the difference side by side:

Partitioning does not give you more CPU, more memory or more write throughput. If the primary can't keep up with writes, partitioning won't save it. What it gives you is a big table that behaves like a small one. For many apps, that's all they need for years.