Automating Quarterly Partition Creation in PostgreSQL

This walkthrough automates quarterly RANGE partition creation for a PostgreSQL 16 ledger table using pg_partman and pg_cron — one of the automated partition creation workflows covered in partitioning implementation patterns & routing. Quarterly boundaries suit financial and accounting workloads: closing a fiscal quarter maps to detaching exactly one partition, and auditors get an immutable table per reporting period.

Prerequisites

Step 1 — Create the partitioned ledger table

pg_partman 5.x manages declaratively partitioned tables, so the parent must exist with PARTITION BY RANGE before the extension takes over. The control column must be NOT NULL — partman refuses to manage a nullable partition key. Boundary choice follows the same rules as any of the range partitioning strategies: partition on the column your queries filter by, which for a ledger is the posting timestamp, not the insertion time.

CREATE TABLE ledger (
  entry_id   BIGINT GENERATED ALWAYS AS IDENTITY,
  account_id BIGINT      NOT NULL,
  posted_at  TIMESTAMPTZ NOT NULL,
  amount     NUMERIC(18,4) NOT NULL,
  memo       TEXT,
  PRIMARY KEY (entry_id, posted_at)
) PARTITION BY RANGE (posted_at);

CREATE INDEX ledger_account_posted_idx ON ledger (account_id, posted_at);

Operational note: The primary key must include posted_at because PostgreSQL enforces uniqueness per partition — every unique constraint on a partitioned table has to contain the partition key. Application code that assumed entry_id alone was unique needs a review before you flip this on.

DBA tip: Create the index on the parent, not on individual children. Indexes declared on the partitioned parent are automatically cloned onto every partition partman creates later, which is the single most common thing teams forget when they script partition creation by hand.

Step 2 — Install pg_partman and register the quarterly partition set

create_parent() registers the table in partman.part_config and immediately creates the current quarter, premake future quarters, and a default partition to catch strays. pg_partman has no literal “quarter” keyword; a 3 months interval starting on a quarter boundary produces exactly calendar quarters. p_premake => 2 keeps two future quarters provisioned at all times, so a missed maintenance run costs you a buffer, not an outage.

CREATE SCHEMA partman;
CREATE EXTENSION pg_partman WITH SCHEMA partman;

SELECT partman.create_parent(
  p_parent_table => 'public.ledger',
  p_control      => 'posted_at',
  p_interval     => '3 months',
  p_premake      => 2,
  p_start_partition => '2026-01-01'
);

Operational note: p_start_partition pins the first boundary to January 1 so children align to calendar quarters (Q1 starts Jan 1, Q2 Apr 1, and so on). Without it, partman anchors intervals to the current date, and you can end up with “quarters” running February-to-May.

DBA tip: Run create_parent() inside a transaction on a quiet connection. It takes an ACCESS EXCLUSIVE lock on ledger while attaching the initial children; on a brand-new table that is instant, but on a table already receiving traffic set lock_timeout = '5s' first so a blocked lock fails fast instead of queuing every write behind it.

Step 3 — Schedule maintenance with pg_cron

partman.run_maintenance_proc() is the engine that keeps the premake buffer full and applies retention. It is a procedure (not a function) so it can commit after each partition set, which prevents one broken set from rolling back work on the others. Schedule it daily — the call is a no-op whenever the buffer is already full, and a daily cadence means a failed run is caught within 24 hours rather than discovered at quarter end.

CREATE EXTENSION pg_cron;

SELECT cron.schedule(
  'partman-maintenance',
  '30 2 * * *',
  $$CALL partman.run_maintenance_proc()$$
);

-- Confirm the job registration
SELECT jobid, jobname, schedule, command FROM cron.job;

Operational note: pg_cron executes jobs in the database named by cron.database_name. If pg_partman lives in a different database than the one pg_cron was installed into, use cron.schedule_in_database() instead, or the job will run — and report success — against the wrong database while your buffer quietly drains.

DBA tip: Alert on cron.job_run_details, not just the PostgreSQL log. A query like SELECT status, return_message FROM cron.job_run_details WHERE jobid = 1 ORDER BY start_time DESC LIMIT 5; surfaces failed maintenance runs with the actual error text, and it is trivially wired into any SQL-capable monitoring agent.

Step 4 — Configure retention: detach and archive old quarters

Ledgers rarely allow hard deletion, so configure partman to detach expired quarters instead of dropping them. With retention_keep_table = true, any quarter older than the retention interval is detached from ledger during maintenance but kept on disk as a standalone table, ready to be dumped to cold storage and then dropped on your schedule — not partman’s.

UPDATE partman.part_config
   SET retention                 = '2 years',
       retention_keep_table      = true,
       infinite_time_partitions  = true
 WHERE parent_table = 'public.ledger';

Operational note: infinite_time_partitions = true forces partman to keep premaking future quarters even during ingestion gaps. Without it, a table that pauses writes (a migration freeze, a seasonal lull) stops getting new partitions, and the first write after the gap lands in the default partition.

DBA tip: Automate the archive of detached quarters with a nightly job on the host, then drop only after the dump verifies:

pg_dump -d ledgerdb --table=public.ledger_p20240101 -Fc \
  -f /archive/ledger_p20240101.dump \
  && psql -d ledgerdb -c 'DROP TABLE public.ledger_p20240101;'

Keep the dump’s pg_restore --list output alongside the file; auditors ask for proof the archive is readable far more often than they ask for the data itself.

Step 5 — Fallback: pure PL/pgSQL for environments without pg_partman

Managed platforms occasionally ship pg_cron but not pg_partman. The same quarterly guarantee fits in one idempotent DO block: compute the current quarter with date_trunc('quarter', ...), then ensure this quarter plus the next two exist. CREATE TABLE IF NOT EXISTS ... PARTITION OF makes re-runs harmless, so you can schedule it daily with pg_cron exactly like the partman procedure in Step 3.

DO $$
DECLARE
  q_from date;
  q_to   date;
  part   text;
BEGIN
  FOR i IN 0..2 LOOP
    q_from := (date_trunc('quarter', now()) + (i * interval '3 months'))::date;
    q_to   := (q_from + interval '3 months')::date;
    part   := format('ledger_p%s', to_char(q_from, 'YYYY"q"Q'));
    EXECUTE format(
      'CREATE TABLE IF NOT EXISTS %I PARTITION OF ledger
         FOR VALUES FROM (%L) TO (%L)',
      part, q_from, q_to
    );
  END LOOP;
END
$$;

Operational note: This block covers creation only — retention, detach, and index verification remain manual. Track detached-quarter cleanup in your runbook, because nothing in this path will ever remind you. If you outgrow single-instance automation entirely, orchestrating the DDL from Apache Airflow centralizes the schedule across many databases.

DBA tip: to_char(q_from, 'YYYY"q"Q') yields names like ledger_p2026q3, which sort correctly and are unambiguous in log output. Resist encoding month numbers into quarterly partition names — ledger_p202607 reads like a monthly partition and will mislead whoever is on call in two years.

Verification

Confirm the partition set is registered with the intended interval, buffer, and retention:

SELECT parent_table, partition_interval, premake, retention, retention_keep_table
FROM partman.part_config
WHERE parent_table = 'public.ledger';
 parent_table  | partition_interval | premake | retention | retention_keep_table
---------------+--------------------+---------+-----------+----------------------
 public.ledger | 3 mons             |       2 | 2 years   | t

Then verify the physical tree: the current quarter, two future quarters, and the default partition should all be attached:

SELECT relid::text AS partition, isleaf,
       pg_get_expr(c.relpartbound, c.oid) AS bounds
FROM pg_partition_tree('public.ledger') t
JOIN pg_class c ON c.oid = t.relid
WHERE isleaf
ORDER BY 1;
    partition     | isleaf |                             bounds
------------------+--------+----------------------------------------------------------------
 ledger_default   | t      | DEFAULT
 ledger_p20260701 | t      | FOR VALUES FROM ('2026-07-01 ...') TO ('2026-10-01 ...')
 ledger_p20261001 | t      | FOR VALUES FROM ('2026-10-01 ...') TO ('2027-01-01 ...')
 ledger_p20270101 | t      | FOR VALUES FROM ('2027-01-01 ...') TO ('2027-04-01 ...')

Finally, assert the default partition stays empty — any row count above zero means a boundary gap: SELECT count(*) FROM ONLY ledger_default; should return 0. Wire that query into monitoring with a warning threshold of 1.

Failure mode table

Failure mode Root cause SRE mitigation
Writes start landing in ledger_default Maintenance never ran: pg_cron missing from shared_preload_libraries, or cron.database_name points at the wrong database Alert on count(*) FROM ONLY ledger_default > 0 and on cron.job_run_details staleness; after fixing, run CALL partman.partition_data_proc('public.ledger'); to move stranded rows into proper quarters
Maintenance errors with “updated partition constraint for default partition would be violated” Rows sitting in the default partition overlap the range of the next quarter partman is trying to create Relocate the rows with partition_data_proc() before re-running maintenance; the error is protective — never drop the default partition to silence it
Disk fills with detached ledger_p* tables retention_keep_table = true detaches quarters but nothing archives or drops them Add the pg_dump-then-DROP archive job from Step 4 to the same schedule as maintenance, and graph the count of unattached ledger_p% relations in pg_class

FAQ

Why use pg_partman instead of a plain pg_cron job with a DO block?

A hand-rolled DO block only creates partitions. pg_partman adds configuration-driven premake buffers, retention with detach-instead-of-drop semantics, template-table index propagation, and a part_config catalog you can monitor with a single query. For one table the DO block in Step 5 is perfectly adequate; across dozens of partition sets the extension pays for itself in reduced drift and simpler auditing.

How many quarters ahead should premake create?

Two premade quarters (a six-month buffer) is a solid production default: it survives a full quarter of missed maintenance runs before writes fall into the default partition. Raising premake beyond three mostly adds empty tables for the planner to consider without meaningful extra safety. If your alerting on cron.job_run_details is trustworthy, two is enough.

Does creating a new quarterly partition lock the ledger table?

CREATE TABLE ... PARTITION OF briefly takes an ACCESS EXCLUSIVE lock on the parent while the new child is registered. No data is scanned, so the lock is held for milliseconds — but it still queues behind any long-running query touching ledger, and everything behind it queues too. Schedule maintenance at a low-traffic hour and set lock_timeout so the DDL fails fast and retries rather than stalling the write path.

Back to Automated Partition Creation Workflows