Partitioning and Query Tuning

Indexes make finding rows fast. They do nothing about a table that is simply enormous: backups that take hours, a vacuum that never finishes, and a retention policy that means deleting 200 million old rows without hurting anyone.

Partitioning splits one logical table into many physical ones. Queries still see a single table, and the database quietly works on only the pieces it needs.

Declarative partitioning#

You declare the parent table with a partition key, then create the pieces.

CREATE TABLE events (
    event_id    BIGINT GENERATED ALWAYS AS IDENTITY,
    occurred_at TIMESTAMPTZ NOT NULL,
    customer_id INTEGER     NOT NULL,
    kind        TEXT        NOT NULL,
    payload     JSONB,
    PRIMARY KEY (event_id, occurred_at)
) PARTITION BY RANGE (occurred_at);

CREATE TABLE events_2024_01 PARTITION OF events
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
CREATE TABLE events_2024_02 PARTITION OF events
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');
CREATE TABLE events_default PARTITION OF events DEFAULT;

 events (parent, holds no rows itself)
   |
   +-- events_2024_01   Jan 2024 rows
   +-- events_2024_02   Feb 2024 rows
   +-- events_default   anything that matches no other partition

Inserts into events are routed to the right child automatically. The bounds are half-open: the FROM value is included, the TO value is not, so months chain with no gaps and no overlaps.

One rule catches everyone: the primary key and every unique constraint must include the partition key. A unique index is enforced per partition, and the database cannot see across them, so uniqueness is only guaranteed when the key narrows to one partition. That is why occurred_at is part of the primary key above.

The three strategies#

Time-based range partitioning is by far the most common, and the most useful.

Partition pruning#

The payoff for queries: the planner skips partitions that cannot contain matching rows.

EXPLAIN SELECT * FROM events
WHERE occurred_at >= '2024-02-10' AND occurred_at < '2024-02-20';

 Seq Scan on events_2024_02 events
   Filter: ((occurred_at >= '2024-02-10') AND (occurred_at < '2024-02-20'))

Only February was touched. January and the default partition do not appear.

The catch is that pruning only works when the query filters on the partition key. A query by customer_id alone must visit every partition, so each partition needs its own index on that column. Create the index on the parent and PostgreSQL builds it on every partition, including those you add later.

CREATE INDEX ON events (customer_id);

Retention without pain#

This is the best reason to partition. Deleting a year of old rows from a single table means a huge, slow DELETE, then dead tuples, then vacuum. With partitions it is a metadata operation:

ALTER TABLE events DETACH PARTITION events_2024_01;
DROP TABLE events_2024_01;           -- or archive it first

Instant, no bloat, no long locks. Pair it with a scheduled job that creates next month's partition ahead of time, because an insert that matches no partition and has no default partition fails.

When partitioning is the wrong tool#

• The table is not that big. A rough guide: consider it once a table passes tens of millions of rows or its size outgrows memory. Below that, a good index is simpler and faster.
• Queries do not filter on the partition key. You get the overhead of many tables and none of the pruning.
• Too many partitions. Thousands of tiny ones make planning slower. Partition monthly or weekly, not by the minute.
• It was meant to speed up a slow query. Partitioning helps maintenance and large scans. For point lookups, indexes win.

A tuning checklist#

Query tuning is a process, not a list of tricks. Measure, change one thing, measure again.

1. Find the slow queries with data. Enable the pg_stat_statements extension. It records every normalized query with call counts and total time, and the biggest total is where your effort pays most.
2. Read the plan with EXPLAIN (ANALYZE, BUFFERS). Find the node where time and rows go.
3. Compare estimates with reality. A large mismatch calls for ANALYZE, or extended statistics.
4. Fix the cause, usually from the list below.

The usual causes, with fixes.

Fetching more than you need. SELECT * pulls every column, defeats index-only scans and ships wide rows over the network. Name the columns.

Predicates that cannot use an index. Make them sargable:

WHERE TO_CHAR(order_date, 'YYYY-MM') = '2024-04'                 -- scans everything
WHERE order_date >= '2024-04-01' AND order_date < '2024-05-01'   -- uses the index

Deep OFFSET pagination. OFFSET 1000000 reads and discards a million rows. Use keyset pagination (Module 7).

Counting to test existence. SELECT COUNT(*) ... > 0 counts everything. EXISTS stops at the first row.

Too many round trips. The N+1 pattern, one query for the list and one per item, is the most common cause of slow applications. Fetch everything in one joined query or one WHERE id = ANY(...).

Row-by-row writes. A thousand single-row inserts are a thousand commits. Insert many rows per statement, or use COPY.

UNION where UNION ALL is correct. The sort is wasted.

DISTINCT hiding a join problem. Fix the join.

Stale statistics and dead tuples. Check that autovacuum is keeping up (pg_stat_user_tables, columns n_dead_tup and last_autovacuum).

Missing foreign key indexes. Deletes on the parent scan the whole child table.

An index that exists but is not chosen. See the previous lesson's list.

5. Verify. Re-run the same EXPLAIN ANALYZE and compare. Also check that you did not slow down the writes.

Resist configuration tuning (shared_buffers, work_mem) until the queries and indexes are right. It rarely rescues a bad query, and it is easy to starve the whole server with a large work_mem multiplied by many connections.

Common mistakes#

Partitioning a small table. Complexity with no benefit.

Forgetting the partition key in the primary key. The table cannot even be created.

Not creating future partitions. Inserts start failing at midnight on the first of the month.

Queries without the partition key in the filter. Every partition is scanned.

Tuning without measuring. You change three things at once and learn nothing.

Practice#

1. Create the partitioned events table with two monthly partitions and a default one. Insert rows across three months and check which partition each lands in (SELECT tableoid::regclass, * FROM events).
2. Run EXPLAIN for a one-week date range and confirm only one partition is scanned. Then query by customer_id only and compare.
3. Detach and drop the oldest partition, and confirm its rows are gone.
4. Take a slow query from your own work, or one against big_orders, and apply the checklist: record the plan before, make one change, and record the plan after.
5. Write a query that checks existence the slow way (COUNT(*) > 0) and the fast way (EXISTS) on big_orders, and compare the timings.