PostgreSQL partitioning for logs and metrics instead of every table

Partitioning enters a project in one of two ways. The measured way starts from queries that filter by time on tables too big to vacuum comfortably, and it ends with a layout that prunes and drops in milliseconds. The reflex way starts from a table size threshold, partitions everything above it, and ends six months later with queries that scan every partition, a planner slowed by thousands of children, and an application full of workarounds. The difference between the two is not the feature, which is the same declarative partitioning in both cases, it is the understanding of what the queries actually ask. This article builds the measured version: the physical mechanics, the query pattern that justifies the change, time range partitioning for logs and metrics, retention without mass deletes, the index and key mechanics per partition, and the ceiling on partition count.

What partitioning actually changes in the physical layout

Declarative partitioning, native since PostgreSQL 10, turns one logical table into a parent whose rows physically live in child tables. The partition key declared with PARTITION BY decides the home of every row, the child tables declare the ranges they own, and the parent stays the query interface for the whole structure:

CREATE TABLE events (
    id bigserial,
    created_at timestamptz NOT NULL,
    payload jsonb,
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2026_09 PARTITION OF events
    FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');

The PRIMARY KEY line already shows the price of admission. A primary key or unique constraint on a partitioned table must include all the partition key columns, because the server enforces uniqueness per physical structure and cannot see the whole key space from any single child. The bare id key the application assumed for years has to become a compound of id and created_at, and any code that relied on the single column key must follow. Finding this constraint out before a migration, not during it, is the cheapest part of any partitioning project.

The query pattern that decides the whole strategy

Pruning is the mechanism that makes partitioning worth anything: the planner examines the WHERE clause against each partition's range and removes the partitions that cannot contain matching rows, at plan time for literal conditions and at execution time for parameterized ones. Whether pruning happens at all is decided by the queries, and the honest audit is a look at pg_stat_statements sorted by execution time.

The textbook beneficiary is the time window query on log and metric tables, because those tables are nearly always queried by time:

SELECT count(*) FROM events
WHERE created_at >= now() - interval '1 day'
  AND payload->>'level' = 'error';

The planner prunes to the one or two partitions whose ranges intersect the last day, and the query touches a fraction of the data. The anti pattern is the query that filters by something other than the partition key:

SELECT count(*) FROM events
WHERE payload->>'customer' = 'acme';

Nothing prunes, every partition is scanned, and the plan is worse than the single table it replaced, because now the scan opens a structure per partition instead of one. This contrast is the entire decision: tables whose dominant access is by time, such as logs, metrics and event streams, partition well, while tables navigated by primary key, such as users, orders and accounts, almost never do. Partitioning an OLTP table that everyone reaches by id turns every lookup into a routing step and delivers nothing in return.

Range partitioning by time for the tables that earn it

For logs and metrics, range partitioning on a time column is the standard shape. Monthly partitions suit most write volumes, and the rule is to create them ahead of time rather than on the day the insert fails:

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

An insert whose created_at falls outside every existing partition raises an error, and the safety net is the default partition, available since PostgreSQL 11, which collects the rows no other partition claims:

CREATE TABLE events_default PARTITION OF events DEFAULT;

The default partition is a buffer, not a home: rows that land there reveal a missing range, and creating the missing partition later requires a scan of the default to prove no overlap, which is cheap while the partition is small and painful while it is not. Monitoring the default partition's row count is therefore part of operating the layout, and a growing default is the early warning that partition creation stopped running.

For tables without time semantics that simply outgrew one machine's write rate, hash partitioning, also available since PostgreSQL 11, spreads rows across a fixed set of children by a hash of the key. It distributes load and keeps individual structures smaller, but it never enables the instant retention that time ranges do, which is why it is the rarer choice.

Retention as detach and drop instead of mass DELETE

The single strongest argument for partitioning time series tables is retention. On a single table, deleting old data is a small disaster: a DELETE over millions of rows is a long transaction, it marks rows dead but leaves the space unreclaimed, it hands autovacuum hours of work, it bloats indexes, and it streams all that churn to every replica. On a partitioned layout the same policy is two commands:

ALTER TABLE events DETACH PARTITION events_2023_09;
DROP TABLE events_2023_09;

Detaching is a catalog change measured in milliseconds, and dropping a partition is a file level operation that touches no live rows at all. What used to be a weekend maintenance window becomes a Monday cron job. PostgreSQL 14 added DETACH PARTITION CONCURRENTLY, which performs the detach without blocking reads and writes on either side, the finishing touch for layouts that cannot afford even brief locks.

The detach step also opens an archival path that DELETE never had: a detached partition is an ordinary table that can be renamed, dumped, moved to slower storage, or kept readable for auditors before it is finally dropped. A policy of detaching each month, archiving to cold storage, and dropping after a grace period gives the compliance team its guarantee and the database its space back, without a single row level delete anywhere in the process.

Indexes constraints and the per partition mechanics

Everything on a partitioned table exists per partition, and the server manages most of it transparently. An index created on the parent cascades an index into every existing child, and future partitions inherit the definition on creation:

CREATE INDEX idx_events_payload ON events USING gin (payload);

produces one GIN index per partition, each maintained only for the rows it holds. The same locality applies to the earlier lesson about uniqueness: a unique index must include the partition key, and global uniqueness on any other column is impossible to declare, which matters for tables that grew up with a unique email or a unique external reference. The application has to enforce that constraint itself, or the partition key has to join the unique columns.

The per partition view also explains the performance character. Each child is a complete, ordinary table with its own statistics, its own indexes, and its own size, so vacuuming old partitions is fast because they are small, and the hot partition's working set fits in memory because it holds only the current window. Autovacuum, the constant adversary of large append only tables, becomes a routine that finishes because each structure it must clean is a fraction of the whole.

Planning cost and the practical partition count

Partitioning is not free at plan time. Every statement against the parent enumerates the children, and a layout with thousands of partitions pays that enumeration on every prepared statement and every plan cache miss. The planner has grown better at handling large numbers of children with every release, but the practical guidance has held for years: dozens of partitions are invisible, hundreds are fine, and thousands need a deliberate benchmark before anyone commits.

The arithmetic for time series is friendly at monthly granularity. A five year retention with monthly partitions is sixty structures, which the planner handles without complaint. Daily partitions over the same five years are around eighteen hundred, which is where plan time becomes measurable and where many teams settle on weekly or monthly ranges instead, accepting a larger retention unit to keep the child count modest.

The creation and retirement of all those partitions is automation work, and the community standard is pg_partman, an extension that creates future partitions on a schedule, retires expired ones, and keeps the default partition empty. Hand written cron plus a stored procedure works too, and several managed PostgreSQL services ship their own tooling, but the operating rule is the same: partition lifecycle is a scheduled, monitored job, not a human memory task, because the failure mode is inserts erroring against a missing range.

The decision checklist before partitioning anything

The honest path from here to a partitioned layout runs through the queries first and the table size second. The checklist that prevents both the reflex and the missed opportunity is short:

1. Audit the top statements in pg_stat_statements and confirm they filter by the candidate partition key;
2. Check the table is genuinely large, because a few million rows on modern hardware vacuum and index comfortably without partitioning;
3. Verify every unique constraint and primary key can include the partition key without breaking application assumptions;
4. Start with monthly ranges and shrink them only when a single partition itself becomes the maintenance problem;
5. Automate creation, monitoring of the default partition and retention from day one, not after the first missed month;
6. Re-run the real queries against the partitioned layout in staging and compare execution plans, not just wall clock timings.

Partitioning is a physical answer to a query pattern. The tables that answer "which time window" on every access, the logs, metrics and events of the world, earn the layout because pruning makes their dominant queries cheap and detachment makes their retention instant. The tables that answer "which row by key" earn nothing but routing overhead, and the discipline is telling one from the other with the query log before touching a single DDL statement.

Storage tiering and the monitoring that keeps partitions honest

Partitioning composes with tablespaces, and the composition is the cheapest archival tier in the PostgreSQL toolbox. A tablespace on slow, large disks, or on network storage, becomes the destination for the partitions that are read rarely:

CREATE TABLESPACE archive_ts LOCATION '/archive/pg';
ALTER TABLE events_2023_09 SET TABLESPACE archive_ts;

The move is a physical copy at the storage layer, it preserves the logical structure, and the planner does not care: queries still prune and read the old partition when asked, just from the slower device. A common design is three tiers on one logical table, hot months on local SSD, the year on the bulk array, and detached years exported or dropped, with a scheduled job that moves each partition down the tiers on its birthday.

The operation that makes the whole layout trustworthy is monitoring, and the two checks that matter are row counts in the default partition and partition coverage ahead of the current date. A default partition growing is the early warning that creation stopped, and a missing future range is the early warning before inserts begin failing:

SELECT count(*) FROM events_default;
SELECT count(*) FROM pg_inherits
WHERE inhparent = 'events'::regclass;

Both checks are one line each, both belong on the alerting dashboard, and together they replace the two failure modes of hand run partition management with two numbers nobody has to remember to look at.