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.

pg_partman configuration to partition set, one maintenance run A row in part_config declares quarterly intervals, a premake of four and a retention of eight quarters with retention_keep_table false. run_maintenance_proc reads that row, creates any missing future quarters up to the premake horizon, and drops quarters older than the retention window. The resulting set shows eight retained quarters, the current one, and four pre-created ones. part_config row partition_interval = '3 months' premake = 4 retention = '8 months' retention_keep_table = false infinite_time_partitions = true after the run 2 quarters dropped 8 quarters retained 1 current quarter 4 quarters pre-created 13 child tables total run_maintenance_proc() called by pg_cron every hour dropped by retention 8 retained quarters — queried current premake = 4 ahead premake is a headroom setting, not a performance setting: it is how long the system survives with run_maintenance stopped. Four quarters of headroom means a broken pg_cron has a year before inserts start hitting the default partition. 
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.

Detach, archive, verify, drop — the safe retention order An expiring quarter is first detached concurrently so the parent table keeps serving. The detached table is then dumped to object storage and its row count and checksum are compared with the source. Only after the verification passes is the table dropped. A failed verification leaves the detached table in place, which is recoverable; dropping first is not. 1. DETACHCONCURRENTLYparent stays online 2. ARCHIVEpg_dump → S3compressed, versioned 3. VERIFYcount + md5 matchthe gate before deletion 4. DROP TABLEspace returnedonly after step 3 passes verification failed → leave the table detached and alert; nothing is lost A detached table still occupies disk. Set the alert on "detached tables older than 7 days" or the archive step silently becomes a leak. DETACH CONCURRENTLY (PostgreSQL 14+) avoids the ACCESS EXCLUSIVE lock on the parent that plain DETACH takes — the difference between a sub-second operation and a queue of blocked queries behind an idle transaction. 
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.

Choosing a scheduler when extensions are not available If the platform allows extensions, pg_partman plus pg_cron is the least code. If only pg_cron is allowed, call a hand-written PL/pgSQL function on a schedule. If neither extension is available, keep the same function and drive it from an external scheduler or the deploy pipeline. In all three branches the DDL function is identical, so migrating between them costs nothing. CREATE EXTENSION pg_partman allowed? yes → pg_partman + pg_cron, ~15 lines of config pg_cron allowed on its own? yes → cron.schedule() calls your PL/pgSQL function no → external timer or deploy hook calls the same function no Write the DDL as a plain SQL function first and choose the trigger second. Managed platforms change their extension allow-lists more often than your schema changes, and this shape survives that without touching the partition logic. 
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