Most systems I have worked on end up with a handful of tables that only ever grow. Event history, audit trails, request logs, anything append-only. None of them are interesting individually, and together they eventually dominate the maintenance budget: vacuum takes longer every month, index rebuilds turn into scheduled events, and a query that only wants last week has to reason about years of rows to prove it.
Declarative partitioning is the fix, and PostgreSQL has been good at it since 12. The parts worth writing down are how to get an existing table into it without downtime, and how to stop hand-managing it afterwards.
Partition on the Column You Actually Filter
Range partitioning by time is the obvious choice for append-only data, and the partition key has to appear in your WHERE clauses or you gain nothing. Partition by occurred_at while every query filters on account_id and each one still touches every partition.
CREATE TABLE events (
id bigint GENERATED ALWAYS AS IDENTITY,
account_id integer NOT NULL,
occurred_at timestamptz NOT NULL,
event_type smallint NOT NULL,
PRIMARY KEY (id, occurred_at)
) PARTITION BY RANGE (occurred_at);Note the primary key. Every unique constraint on a partitioned table has to include the partition key, so a plain PRIMARY KEY (id) is off the table. If another table carries a foreign key pointing at that id, you have a migration problem to solve before you start, and it is much better to find that now.
Migrating Without Downtime
The approach that works is to leave the existing table alone and attach it as a partition, which turns a rewrite into a metadata change:
ALTER TABLE events_old
ADD CONSTRAINT events_old_range
CHECK (occurred_at >= '2020-01-01' AND occurred_at < '2026-08-01') NOT VALID;
ALTER TABLE events_old VALIDATE CONSTRAINT events_old_range;
ALTER TABLE events ATTACH PARTITION events_old
FOR VALUES FROM ('2020-01-01') TO ('2026-08-01');The two-step constraint is the whole trick. Adding it NOT VALID takes a brief lock and skips the scan. VALIDATE CONSTRAINT then does the scan under a weaker lock that readers and writers work around. By the time ATTACH PARTITION runs, Postgres already knows every row satisfies the bound and skips the verification scan that would otherwise hold ACCESS EXCLUSIVE for the length of a full sequential read. Skip the constraint and the attach does that scan itself, with the table locked.
Detaching Beats Deleting
This is the payoff that justifies the whole exercise, and it is easy to undersell. Aging out old data from a monolithic table means DELETE, and a DELETE of ten million rows does not free anything. It writes ten million dead tuples, generates WAL for every one, leaves the indexes bloated, and hands autovacuum a large cleanup job that competes with your live traffic. The table on disk stays exactly as large as it was.
With partitions, the same operation is a catalog change:
ALTER TABLE events DETACH PARTITION events_2025_07 CONCURRENTLY;No row is touched. No WAL is generated for the data. Nothing needs vacuuming afterwards. The CONCURRENTLY form avoids blocking readers while it happens, and the detached table is now an ordinary standalone table you can archive, dump somewhere cheap, or drop outright. That last distinction matters more than it sounds: detaching is reversible and gives you a table you can still query, while DELETE is a one-way trip that costs far more to perform.
Let pg_partman Run It
Something has to create next month's partition before next month arrives, and the failure mode of forgetting is an insert erroring out at midnight on the first. Hand-rolled cron jobs work until the person who wrote them moves on. pg_partman is the right answer here, and it handles both ends: creating partitions ahead of time and retiring old ones on a policy.
CREATE SCHEMA partman;
CREATE EXTENSION pg_partman WITH SCHEMA partman;
SELECT partman.create_parent(
p_parent_table := 'public.events',
p_control := 'occurred_at',
p_interval := '1 month',
p_premake := 4
);Note that pg_partman 5 changed this signature from the 4.x examples still floating around, and p_type now takes range or list rather than the old native. The p_premake value is how many partitions to keep created ahead, so four months of runway on a monthly interval.
Retention is a column in the extension's own config table:
UPDATE partman.part_config
SET retention = '12 months',
retention_keep_table = true
WHERE parent_table = 'public.events';The default for retention_keep_table is true, which detaches expired partitions and leaves them as standalone tables rather than dropping them. That default is the right one, and it is the same argument as above: you get the space accounting and the maintenance relief immediately, and the data is still sitting there if somebody asks for it next week. Set it to false only when you are certain, or leave it true and have a separate job that dumps and drops detached tables on its own schedule.
Maintenance runs either from the bundled background worker, by adding pg_partman_bgw to shared_preload_libraries, or from a scheduled call:
CALL partman.run_maintenance_proc();Confirm Pruning Actually Happens
Partitioning silently does nothing if the planner cannot prune, so check rather than assume:
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) FROM events
WHERE account_id = 4211
AND occurred_at >= now() - interval '30 days';You want a small number of partitions in the plan, and ideally Subplans Removed when the bound is only known at execution time. The thing that bites people is a partition key wrapped in a function or compared against a different type, which defeats pruning with no warning at all. A timestamptz column compared against a timestamp literal is the classic version.
What It Costs
Partition count matters. Planning time grows with the number of partitions, and daily partitions across five years is 1,800 relations the planner considers before it prunes anything. Monthly is usually right for history, weekly if the volume genuinely warrants it. Start coarse, since merging later is harder than splitting.
The payoff is that maintenance becomes proportional to the working set instead of the archive. Vacuum runs against the current partition. The old ones sit frozen and untouched until the day they get detached, which costs nothing.
