Automating Partition Creation with pg_partman

The partitioned metrics table was set up by hand with monthly partitions through December. Nobody wrote down that someone would have to create January’s. At 00:00:00 on January 1st every insert began failing with no partition of relation "metrics" found for row, the ingest pipeline backed up, and the on-call engineer created the partition by hand at 00:14. Partitioning moves a table’s maintenance from row-level work (deleting, vacuuming) to calendar-level work (creating future partitions, retiring old ones), and that calendar work must be automated. pg_partman is the standard PostgreSQL extension for it: it creates partitions ahead of time, retires old ones according to a retention policy, and runs as a scheduled maintenance job. This guide sets it up for an existing partitioned table and wires in the monitoring that catches failures before they become outages. It belongs to Partitioning Live Tables Without Downtime.

Symptom / Error Signatures

Without automation, the failure is abrupt:

ERROR:  no partition of relation "metrics" found for row
DETAIL:  Partition key of the failing row contains (recorded_at) = (2027-01-01 00:00:00.041+00).

Softer symptoms come first, if anyone is looking: a shrinking number of future partitions, retention jobs that nobody runs so old partitions accumulate, and hand-written partition DDL scattered through the migration history with inconsistent naming. With pg_partman installed but misconfigured, the symptoms are maintenance that never runs (partman.part_config shows a stale premake horizon) or retention that detaches nothing because retention is unset.

Root Cause Analysis

Declarative partitioning routes each row to the partition whose bounds contain its key; there is no automatic creation. For time-based partitioning, the set of partitions must therefore always extend beyond “now” by a safe margin. pg_partman stores a configuration row per partitioned table in partman.part_config — interval, how many partitions to premake, retention — and its maintenance function (partman.run_maintenance(), or the procedure partman.run_maintenance_proc()) creates missing future partitions and applies retention each time it runs. Maintenance must be scheduled: through pg_partman’s background worker, pg_cron, or an external scheduler.

Partition creation takes a brief lock on the parent, so maintenance itself should run with a lock timeout and be retried; with premake of several intervals, one failed run is harmless because the buffer absorbs it.

Immediate Mitigation

1. If inserts are failing now, create the missing partition by hand. It is fast and takes only a brief lock on the parent.

-- PostgreSQL · migration role · emergency partition for the current interval
SET lock_timeout = '3s';
CREATE TABLE IF NOT EXISTS metrics_p20270101 PARTITION OF metrics
  FOR VALUES FROM ('2027-01-01') TO ('2027-01-02');
-- ROLLBACK PATH: not needed; pg_partman will adopt partitions following its naming convention.

2. Install pg_partman and register the table. On pg_partman 5.x, create_parent takes the control column, interval and premake; it creates the template and any missing partitions.

-- PostgreSQL 14+ · superuser or extension owner · pg_partman 5.x syntax
CREATE SCHEMA IF NOT EXISTS partman;
CREATE EXTENSION IF NOT EXISTS pg_partman SCHEMA partman;
SELECT partman.create_parent(
  p_parent_table => 'public.metrics',
  p_control      => 'recorded_at',
  p_interval     => '1 day',
  p_premake      => 7
);
UPDATE partman.part_config
   SET retention = '90 days', retention_keep_table = true, infinite_time_partitions = true
 WHERE parent_table = 'public.metrics';
-- ROLLBACK PATH: DELETE FROM partman.part_config WHERE parent_table = 'public.metrics';  (partitions remain)

Existing partitions must follow pg_partman’s naming convention for it to manage them; check the documentation for your version, and rename or detach hand-made partitions accordingly.

3. Schedule maintenance. With pg_cron:

-- PostgreSQL · requires pg_cron · hourly maintenance with a lock timeout
SELECT cron.schedule('partman-maintenance', '7 * * * *',
  $$SET lock_timeout = '5s'; CALL partman.run_maintenance_proc()$$);

Permanent Fix / Long-Term Pattern

Treat partition maintenance as production infrastructure with an owner and an alert. Premake enough intervals that several consecutive maintenance failures are harmless — a week of daily partitions, a few months of monthly ones — and alert when the horizon drops below half of that. The horizon check is a simple catalog query on the newest partition’s upper bound:

-- PostgreSQL · read-only · days of partitions remaining ahead of now
SELECT max(upper(bounds)) - now() AS horizon
FROM (
  SELECT tstzrange(
           (regexp_match(pg_get_expr(c.relpartbound, c.oid), 'FROM \(''([^'']+)''\)'))[1]::timestamptz,
           (regexp_match(pg_get_expr(c.relpartbound, c.oid), 'TO \(''([^'']+)''\)'))[1]::timestamptz) AS bounds
  FROM pg_inherits i JOIN pg_class c ON c.oid = i.inhrelid
  WHERE i.inhparent = 'metrics'::regclass
) p;

Start with retention_keep_table = true so retention detaches old partitions without dropping them, verify the behaviour for a few cycles, then switch to dropping — or archive detached tables first, as described in dropping old data with partition detach instead of DELETE. Keep pg_partman’s configuration in version control (the create_parent call and part_config updates as migrations) so environments stay consistent. Add the horizon metric to the dashboards in Migration Observability.

Two details make pg_partman behave well alongside schema migrations. First, changes to the parent — new columns, new indexes, new constraints — propagate to future partitions only if they are applied to the parent (or, for some properties, to pg_partman’s template table); a migration that alters only existing partitions leaves new ones inconsistent. Apply schema changes to the parent and let PostgreSQL cascade them, and check pg_partman’s template table for properties that do not cascade, such as some storage parameters. Second, coordinate maintenance with migrations: a long migration holding a lock on the parent will make maintenance wait or time out. With a generous premake, simply let maintenance fail and retry on its next run rather than raising its lock timeout.

When sizing premake, think in terms of the longest plausible outage of the maintenance job itself — a broken extension upgrade, a failed pg_cron worker, a database restored from backup without its cron schedule. A restored database is the classic case: pg_cron jobs live in the cron schema and may not be restored with the data, so verify the schedule exists after any restore.

Verification Checklist

• partman.part_config contains the table with the intended interval, premake and retention.
• A scheduler runs run_maintenance_proc at least several times per partition interval, with a lock timeout.
• The horizon query reports at least the premake interval ahead, and an alert fires below half of it.
• Retention has been observed detaching (and, once enabled, dropping) the expected partitions.
• pg_partman setup is expressed as migrations so every environment is configured identically.

Frequently Asked Questions

Does pg_partman replace declarative partitioning? No. Modern pg_partman manages native declarative partitions; it automates creating and retiring them. The partitioning itself is PostgreSQL’s.

What happens if maintenance fails for a day? Nothing visible, as long as premake covers the gap: the existing future partitions keep accepting inserts. The horizon alert tells you to fix the maintenance job before the buffer runs out.

Does creating a partition lock the table? It takes a lock on the parent briefly while the new partition is added to the catalog. With a lock timeout and hourly retries, contention with long transactions is harmless.

Can pg_partman manage a table I partitioned by hand? Yes, after registering it with create_parent, provided existing partitions follow its naming convention for the chosen interval. Rename or detach non-conforming partitions first.