Table Partitioning

Partitioning splits one logical table into smaller physical tables (partitions) by a key — usually a date range. Queries that filter on the key skip irrelevant partitions entirely (partition pruning).

The biggest win is maintenance: dropping last year's data becomes an instant DROP TABLE of one partition instead of a slow DELETE of millions of rows. Indexes created on the parent are created on every partition.

Partition only when tables are genuinely large (tens of millions of rows) or have a clear retention policy. Always include a default partition, or create future partitions ahead of time, so inserts never fail.

partitioning.sqlSQL

CREATE TABLE attendance (
    student_id bigint  NOT NULL,
    day        date    NOT NULL,
    present    boolean NOT NULL,
    PRIMARY KEY (student_id, day)
) PARTITION BY RANGE (day);

CREATE TABLE attendance_2025 PARTITION OF attendance
    FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');
CREATE TABLE attendance_2026 PARTITION OF attendance
    FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');
CREATE TABLE attendance_default PARTITION OF attendance DEFAULT;

INSERT INTO attendance (student_id, day, present)
SELECT s, d::date, (s + extract(doy FROM d)::int) % 9 <> 0
FROM generate_series(1, 300) AS s,
     generate_series(date '2025-01-06', date '2026-11-27', interval '1 day') AS d
WHERE extract(isodow FROM d) < 6;

ANALYZE attendance;

-- Only attendance_2026 is scanned
EXPLAIN SELECT count(*) FROM attendance WHERE day BETWEEN '2026-03-01' AND '2026-03-31';

SELECT tableoid::regclass AS partition, count(*)
FROM attendance
GROUP BY 1
ORDER BY 1;

-- Retire a whole year instantly
ALTER TABLE attendance DETACH PARTITION attendance_2025;
DROP TABLE attendance_2025;

Runs in your browser · PostgreSQL

Key points

Partition large tables by the key you filter and expire data by — usually time.
Partition pruning skips partitions that can't match the WHERE clause.
Detach/drop a partition to delete old data instantly; keep a default partition.

Exercise

Partition a payments_archive table by month for 2026 using a loop in a DO block to create the 12 partitions. Insert a year of generated payments and use EXPLAIN to confirm a one-month query reads a single partition.

Show solution

Try the exercise yourself first — then compare your approach with this one.

A DO block loops over the twelve months and creates each partition with format + EXECUTE. After loading a year of generated payments, EXPLAIN shows that a one-month query touches a single partition.

monthly-partitions.sqlSQL

CREATE TABLE payments_archive (
    id         bigint      NOT NULL,
    student_id bigint      NOT NULL,
    amount     numeric(12,2) NOT NULL,
    paid_at    timestamptz NOT NULL
) PARTITION BY RANGE (paid_at);

DO $$
DECLARE
    month_start date;
BEGIN
    FOR m IN 1..12 LOOP
        month_start := make_date(2026, m, 1);
        EXECUTE format(
            'CREATE TABLE payments_archive_2026_%s PARTITION OF payments_archive FOR VALUES FROM (%L) TO (%L)',
            lpad(m::text, 2, '0'), month_start, month_start + interval '1 month'
        );
    END LOOP;
END;
$$;

INSERT INTO payments_archive (id, student_id, amount, paid_at)
SELECT g, 1 + g % 500, 10000 + (g % 30) * 5000,
       timestamptz '2026-01-01' + (g % 365) * interval '1 day'
FROM generate_series(1, 100000) AS g;

ANALYZE payments_archive;

EXPLAIN SELECT sum(amount) FROM payments_archive
WHERE paid_at >= '2026-06-01' AND paid_at < '2026-07-01';
-- Only payments_archive_2026_06 is scanned

SELECT count(*) AS partitions FROM pg_inherits WHERE inhparent = 'payments_archive'::regclass;   -- 12

Runs in your browser · PostgreSQL

Key points

Exercise

Check your understanding