Divide one table without leaving one machine.

01 · What partitioning is

One logical table, several physical ones.

Partitioning splits one logical table into several physical tables, divided along a column you choose. That column is the partition key.

An orders table partitioned by year becomes orders_2024, orders_2025, orders_2026 on disk — while staying a single orders table to your application. You do not change your queries. Inserts route themselves to the right piece.

CREATE TABLE orders (
    id          bigserial,
    customer_id bigint      NOT NULL,
    order_date  timestamptz NOT NULL,
    amount      numeric,
    PRIMARY KEY (id, order_date)
) PARTITION BY RANGE (order_date);

CREATE TABLE orders_2026 PARTITION OF orders
    FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');

02 · Why not just an index?

They solve different problems.

Lesson 03 showed an index finding a row in three page reads. So the obvious objection: if an index already loads the required content quickly, why split the table at all? Is partitioning not just a more disruptive version of the same idea?

An index answers "where are the rows?" It is a lookup structure, and it makes finding cheap. What it cannot do is change the fact that the table still contains a billion rows — that a DELETE must still process every matching row individually, or that VACUUM still has the whole table to work through.

Partitioning changes how much data is involved in the operation itself. Not how quickly you locate rows, but how much the database has to scan, lock, log, or maintain in order to do the work.

The primary benefit, stated plainly: partitioning reduces how much data the database must scan or manage for a given operation. A query for 2026 orders can inspect only the orders_2026 partition instead of the entire table — this is partition pruning. And old data can be removed by dropping an entire partition rather than deleting millions of rows individually.

In short: partitioning makes very large tables faster and easier to manage when queries and maintenance operations align with the partition key.

03 · Where it pays

Five operations that change shape entirely.

1. Deleting old data

Imagine a one-billion-row event table with a two-year retention period.

DELETE FROM events
WHERE event_date < DATE '2024-01-01';

Even if the index finds the rows efficiently, the database must still delete millions of rows individually, update every relevant index, generate substantial WAL, manage locks and dead rows, and later reclaim the storage.

With monthly partitions, the same outcome:

DROP TABLE events_2023_12;

The database removes the whole partition as a storage object — typically much faster, and generating far less transactional work. This is often the strongest practical reason to partition. Note that the index was never the bottleneck here; finding the rows was the cheap part.

2. Large range scans and aggregations

SELECT customer_id, SUM(amount)
FROM orders
WHERE order_date >= DATE '2026-01-01'
  AND order_date <  DATE '2026-02-01'
GROUP BY customer_id;

If this returns a large portion of January’s rows, an index may not help much — reading many rows through an index involves expensive scattered reads, one fetch at a time.

With monthly partitioning, the database can:

• Open only the January partition
• Ignore all other partitions
• Sequentially scan the relevant partition
• Potentially process partitions in parallel

Which gives the principle underneath both cases so far: indexes are strongest when selecting a small number of rows; partition pruning is useful when selecting a large number of rows from a limited data range. They are not competing answers to one question — they answer two different ones.

3. Archiving data

An old partition can be detached from the active table:

ALTER TABLE orders DETACH PARTITION orders_2024;

Once detached it can be exported, compressed, moved to cheaper storage, attached to an archive table, or backed up separately. Doing any of that through individual indexed rows is far more expensive.

4. Bulk loading

New data can be loaded into a separate table, validated and indexed there, and only then attached as a partition:

Load data  →  validate it  →  build indexes  →  attach partition

The import does not interfere with the active table while it runs, and the switch at the end is a metadata operation.

5. Localized maintenance

Instead of maintaining one enormous table, the database can operate on one partition at a time — rebuilding an index, updating statistics, vacuuming or reclaiming storage, checking data integrity.

A job that never fits in a maintenance window becomes a series of jobs that do, and partitions covering closed periods often need no maintenance at all.

04 · When it fits, and when it does not

Everything above depends on one condition.

Pruning only happens if the operation aligns with the partition key. A query without order_date in its WHERE clause gives the planner nothing to eliminate, so it reads every partition — while also paying extra planning cost and opening every partition’s indexes. That is worse than not partitioning at all.

So choose the key from the filter your expensive queries and maintenance jobs already use, not from what looks tidy in the schema.

Do not partition when

• Your queries do not carry the key. Partition by date but look up mostly by customer_id, and every lookup fans out across all partitions.
• You need a unique constraint on another column. Uniqueness is enforced per partition — which is why the example’s primary key is (id, order_date). A plain unique email is not possible on a date-partitioned table.
• The table is not large. Below a few million rows an index wins outright, and partitioning only adds planning overhead.
• The key might need to change. There is no ALTER TABLE ... SET PARTITION KEY; changing it means rewriting the table.

On strategy: use RANGE for anything time-shaped, which is the common case, and LIST for a small fixed set of categories. Treat HASH carefully — it spreads rows evenly but destroys pruning, because consecutive dates land on unrelated partitions. It is for balancing load, not for skipping work.

Then verify rather than assume. Run EXPLAIN (ANALYZE, BUFFERS) and look for Partitions removed by pruning. If every partition still appears in the plan, your query and your key disagree.

Full behaviour, including which expressions permit pruning, is in the table partitioning documentation.

05 · What it does not fix

Partitioning reorganises a table. It does not add a machine.

Same buffer pool, same write-ahead log, same CPU and disk. Every commit still queues behind the same log it did before.

So if writes are what ran out — the server saturated accepting new rows rather than answering questions — partitioning has nothing to offer, because sixty pieces do not give a machine a second core.

Partitioning is a way of reading and managing less. It is not a way of doing more.

When you need the second thing, you are no longer splitting the table — you are splitting the machine. That is sharding, and it costs considerably more than a CREATE TABLE.

The Old Dragon “You built gates within one keep, and the keep grew lighter. But it is still one keep, keeper, and one gatekeeper.”