`pg_partman` and automated maintenance: the operational piece that makes partitioning sustainable

Capsule description

So far you've learned how to decide when to partition (capsule 02), apply the 3 types (capsules 03-05), and verify that pruning works (capsule 06). What's missing is the critical operational ingredient: who creates next month's partitions? who drops the ones from 18 months ago per the retention policy? who detects if the default partition started growing?.

Without automation, someone on the team has to remember. That person goes on vacation, forgets, and the June 1st inserts start failing (or land in the default partition, which grows unchecked). The operation becomes a "dirty area" nobody wants to touch and everybody is afraid to break. Partitioning gets abandoned in production more often from operational pain than from design errors.

This capsule teaches you pg_partman, the extension that automates the full partition lifecycle: creating future partitions with a configurable lead time, dropping old ones per the retention policy, health monitoring, and migrating existing tables to partitioned ones. You'll walk away able to configure pg_partman for an events table partitioned by month with 18 months of retention, schedule it to run automatically, and monitor that it's actually doing its job.

By the end you'll be able to take the ticket "we partitioned events 6 months ago and nobody created the new partitions, the inserts are going to the default partition" and solve it at the root by installing pg_partman instead of writing your own fragile cron job.

Mental model: pg_partman is your partitions' "gardener"

Think of a partitioned table as a garden. Without maintenance:

• New plants (future partitions) that should be sown before the season aren't sown. The harvest (inserts) falls to the ground (default partition) and gets lost or hidden.
• Dead plants (old partitions beyond retention) take up space, consume resources, and slow down the care of the rest.
• Weeds (data in the default partition that shouldn't be there) grow unchecked.

pg_partman is the automatic gardener: it periodically reviews the garden's state, sows ahead of time, drops what's past its cycle, and reports anomalies.

┌──────────────────────────────────────────────────────────────────┐
│                  Cron / pg_cron / scheduler                      │
│                  calls partman.run_maintenance()                 │
│                  every hour (or whatever you configure)          │
└─────────────────────────────┬────────────────────────────────────┘
                              │
                              ▼
              ┌────────────────────────────────┐
              │     pg_partman                 │
              │     (PostgreSQL extension)     │
              └───────────┬────────────────────┘
                          │
            ┌─────────────┼─────────────────────┐
            ▼             ▼                     ▼
    ┌──────────────┐ ┌──────────────┐   ┌──────────────────┐
    │  Create      │ │  Drop        │   │  Report          │
    │  future      │ │  old         │   │  anomalies       │
    │  partitions  │ │  partitions  │   │  (default        │
    │  (premake)   │ │  (retention) │   │   partition with │
    │              │ │              │   │   data)          │
    └──────────────┘ └──────────────┘   └──────────────────┘
            │             │                     │
            ▼             ▼                     ▼
   events_2026_06    events_2024_11    Log: "default has
   events_2026_07    events_2024_12     5234 rows, investigate"
   events_2026_08    (DROP)

Three ideas to internalize:

1. pg_partman doesn't run by itself. It's an extension that provides functions (partman.run_maintenance(), partman.create_parent(), etc.). You need something to call them: typically pg_cron (another extension), an OS cron, or an external scheduler (Airflow, k8s CronJob).

2. The configuration lives in a table in the partman schema. pg_partman maintains metadata about each partitioned table it manages: what granularity, how many future partitions to create (premake), the retention policy, and what to do with the old ones (drop, or detach + archive).

3. It doesn't replace partition pruning or the decisions from capsule 02. pg_partman is the operations layer. It assumes you already decided to partition and picked the type. Its job is keeping it healthy in production.

This explains why it's the mandatory piece, not an optional one, in production: without pg_partman (or a robust equivalent), partitioning degrades within 6-12 months from lack of maintenance.

Comparison: pg_partman vs custom scripts + cron

Before seeing the setup, it's worth understanding why the "I'll write my own cron" option is the most common trap.

Option A: SQL scripts + OS cron

It's what the team does when it "doesn't want to add another dependency":

# /etc/cron.d/create_events_partition
# On the first day of each month, create next month's partition
0 0 1 * * postgres psql -d mydb -c "CREATE TABLE events_$(date -d '+1 month' '+%Y_%m') PARTITION OF events FOR VALUES FROM ('$(date -d '+1 month' '+%Y-%m-01')') TO ('$(date -d '+2 month' '+%Y-%m-01')');"

Why it fails in practice:

1. A single point of failure: if the cron doesn't run on the 1st (server down, DST change, bash error), there's no retry. The partition doesn't exist on the 2nd and the inserts fall into the default.
2. No idempotency: running it twice can fail with "table already exists" or create inconsistent state.
3. No automatic retention: you have to write another script for DROP PARTITION with the "older than 18 months" logic. And another to monitor the default.
4. No recovery if you fall behind: if you discover in August that no partitions have been created since June, you need an ad-hoc script to recover.
5. Bash + SQL is fragile: any change of schema, retention, or granularity requires editing the script and testing it somewhere else.
6. No centralized metadata: which tables are partitioned? with what retention? who owns them? It lives in docs (or in the head of whoever wrote it).

Option B: pg_partman

SELECT partman.create_parent(
    p_parent_table => 'public.events',
    p_control => 'created_at',
    p_type => 'range',
    p_interval => '1 month',
    p_premake => 3
);

UPDATE partman.part_config
SET retention = '18 months',
    retention_keep_table = false
WHERE parent_table = 'public.events';

Why it works in production:

1. Idempotent: run_maintenance() can run every hour. If the partition already exists, it does nothing.
2. Tolerant of delays: if it didn't run for a week, the next run creates the missing partitions.
3. Full lifecycle: creates future ones + drops old ones + monitors the default in a single call.
4. Declarative configuration: the parameters live in partman.part_config. Changing retention from 18 to 24 months is an UPDATE, not editing bash.
5. The community maintains it: bug fixes, support for new PostgreSQL versions, edge cases already discovered by others.
6. Auditable: functions to list the state, see which partitions exist, and what's pending a drop.

The team gains weeks/month of operations by adopting pg_partman over maintaining custom scripts. It's the difference between "we partitioned in Q1, by Q3 nobody touches it and it works" vs "we partitioned in Q1, by Q3 someone must remember the cron, and by Q5 it breaks because we changed servers".

Installation and initial setup

pg_partman is a PostgreSQL extension. Available in most distributions (Debian/Ubuntu via postgresql-XX-partman, RHEL/CentOS via the PGDG repos, the official PostgreSQL Docker image).

Step 1: install the extension

# Ubuntu/Debian (assuming PG 16)
sudo apt-get install postgresql-16-partman

# RHEL/CentOS
sudo yum install pg_partman_16

# Docker (in your Dockerfile, if you use the official postgres image)
RUN apt-get update && apt-get install -y postgresql-16-partman

In the cloud:

• AWS RDS: pg_partman isn't natively supported in standard RDS. Available in Aurora PostgreSQL since version 11.
• GCP Cloud SQL: supported since PG 13.
• Azure Database for PostgreSQL: supported.
• Supabase / Neon: check the current list of allowed extensions.

Step 2: create the schema and enable the extension

-- Create a dedicated schema for pg_partman
CREATE SCHEMA partman;

-- Create the extension in that schema
CREATE EXTENSION pg_partman SCHEMA partman;

-- Verify the installation
SELECT * FROM pg_extension WHERE extname = 'pg_partman';

Expected output:

 extname    | extversion
------------+------------
 pg_partman | 5.2.4

The version matters far more than it looks. pg_partman 5.0 was a hard cut: it removed inheritance-based (trigger-based) partitioning and changed the signature of create_parent(). Almost all the material you'll find online is from 4.x and does not run on 5.x. If you copy a snippet and get a strange error about parameters, the first thing to check is the version.

Step 3: setting up the partitioned events table (recap)

Assuming you already have the events table partitioned from capsule 03:

CREATE TABLE events (
    id          BIGSERIAL,
    user_id     BIGINT NOT NULL,
    post_id     BIGINT,
    event_type  TEXT NOT NULL,
    metadata    JSONB DEFAULT '{}'::jsonb,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

For now you only have the parent table. Let's let pg_partman create the partitions.

Step 4: register the table with partman.create_parent()

SELECT partman.create_parent(
    p_parent_table => 'public.events',
    p_control => 'created_at',
    p_type => 'range',
    p_interval => '1 month',
    p_premake => 3,
    p_start_partition => '2025-11-01'
);

Parameters explained:

• p_parent_table: the parent table's full name (schema.table).
• p_control: the partition key column (created_at).
• p_type: the partitioning strategy. Only two valid values: 'range' or 'list'. It defaults to 'range', so in this case you could omit it.
• p_interval: the granularity. '1 month', '1 day', '1 hour', '15 minutes' are all valid.
• p_premake: how many future partitions to keep created. 3 means "always have 3 future months". The default is 4, and there's a CHECK (premake > 0): you cannot set it to 0.
• p_start_partition: when to start creating partitions from. Useful for historical data.

⚠️ If you find tutorials with p_type => 'native', they're from pg_partman 4.x. In 5.x that value no longer exists and the call blows up:

ERROR:  native is not a valid partitioning type for pg_partman

5.0 removed inheritance (trigger-based) partitioning, so "native" stopped being a necessary distinction: everything is declarative now. The p_type parameter came to mean something else — the strategy, range or list — and many old tutorials are still circulating unupdated.

After running it:

\dt events*

You'll see:

 events                | partitioned table
 events_p2025_11       | table              ← created by pg_partman
 events_p2025_12       | table              ← created by pg_partman
 events_p2026_01       | table              ← created by pg_partman
 events_p2026_02       | table              ← created by pg_partman
 events_p2026_03       | table              ← created by pg_partman
 events_p2026_04       | table              ← created by pg_partman
 events_p2026_05       | table              ← created by pg_partman (current month)
 events_p2026_06       | table              ← premake
 events_p2026_07       | table              ← premake
 events_p2026_08       | table              ← premake
 events_default        | table              ← created automatically

pg_partman created the partitions from November 2025 through August 2026 (current month + 3 premake). The name events_p2026_05 with the p prefix is pg_partman's convention (p = "partition").

Step 5: configure retention

UPDATE partman.part_config
SET retention = '18 months',
    retention_keep_table = false,
    retention_keep_index = false
WHERE parent_table = 'public.events';

Parameters:

• retention: the interval after which old partitions get removed. '18 months' means "partitions whose range is older than 18 months".
• retention_keep_table = false: drop the table completely. If it were true, it would be a detach (the table remains standalone, useful for archiving manually before dropping).
• retention_keep_index = false: also drop the indexes. Only applies when retention_keep_table = true.

Step 6: run the first maintenance

SELECT partman.run_maintenance(p_parent_table => 'public.events');

This call:

1. Checks that the next 3 future partitions exist (premake). If any are missing, it creates them.
2. Checks whether there are old partitions beyond the retention (18 months). If so, it drops them.
3. Reports the result.

To run it across all registered tables:

SELECT partman.run_maintenance();

For scheduled maintenance, prefer the PROCEDURE:

CALL partman.run_maintenance_proc();

The difference isn't cosmetic. run_maintenance() is a function: it runs entirely inside a single transaction, so it holds locks until the end and, if it fails on table number 8, the work of the previous 7 gets rolled back. run_maintenance_proc() is a procedure: it can COMMIT between tables, releases locks earlier, and what's done stays done. With many partitioned tables, that difference is what keeps a nightly maintenance run from blocking the database.

Scheduling automatic maintenance

So far you've run run_maintenance() manually. In production you need it to execute periodically. There are 3 options; choose based on your stack.

Option 1: pg_cron (recommended for self-hosted)

pg_cron is another PostgreSQL extension that provides a native scheduler inside the DB. It pairs perfectly with pg_partman.

# Install pg_cron (similar to pg_partman)
sudo apt-get install postgresql-16-cron

-- Enable the extension
CREATE EXTENSION pg_cron;

-- Schedule run_maintenance every hour
SELECT cron.schedule(
    'pg_partman_maintenance',  -- job name
    '0 * * * *',               -- cron expression: every hour on minute 0
    $$CALL partman.run_maintenance_proc();$$
);

-- Verify it's scheduled
SELECT * FROM cron.job WHERE jobname = 'pg_partman_maintenance';

Advantage: everything inside the DB. If the DB is running, the scheduler is running. It doesn't depend on OS cron or external processes.

Limitation: requires installing pg_cron. Some managed services support it (RDS, Cloud SQL); others don't.

Option 2: OS cron

If you can't install pg_cron:

# /etc/cron.d/pg_partman_maintenance
0 * * * * postgres psql -d mydb -c "CALL partman.run_maintenance_proc();"

Advantage: works in any deployment.

Disadvantage: if the DB server is on a different host from the cron, you have to connect remotely. If the cron host goes down, maintenance stops with no automatic alert.

Option 3: Kubernetes CronJob

For cloud-native environments:

apiVersion: batch/v1
kind: CronJob
metadata:
  name: pg-partman-maintenance
spec:
  schedule: "0 * * * *"
  jobTemplate:
    spec:
      template:
        spec:
          containers:
          - name: psql
            image: postgres:16
            command:
            - psql
            - -h
            - postgres-service
            - -U
            - postgres
            - -d
            - mydb
            - -c
            - "CALL partman.run_maintenance_proc();"
            env:
            - name: PGPASSWORD
              valueFrom:
                secretKeyRef:
                  name: postgres-secret
                  key: password
          restartPolicy: OnFailure

Advantage: integrates with k8s monitoring (alerts if the job fails, automatic retries, centralized logs).

Disadvantage: requires k8s. Higher initial setup complexity.

Recommended frequency

• p_premake = 3 and monthly partitions: running every 6 hours or once a day is fine. You don't need it hourly.
• p_premake = 7 and daily partitions: run every hour.
• Hourly partitions: run every 15 minutes.

If you're not sure, every hour is safe for all cases. The overhead of a run_maintenance() that has nothing to do is very low (~50ms).

Migrating an existing table: partition_data_proc

A typical case: you already have events_old with 50M rows, unpartitioned. You want to migrate to a partitioned table with pg_partman managing the lifecycle. Capsule 08 covers the full zero-downtime technique, but pg_partman provides a key piece: batch-by-batch migration that doesn't block.

Step 1: create the new partitioned table

CREATE TABLE events (
    id          BIGSERIAL,
    user_id     BIGINT NOT NULL,
    post_id     BIGINT,
    event_type  TEXT NOT NULL,
    metadata    JSONB DEFAULT '{}'::jsonb,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

-- Register with pg_partman from the first historical month
SELECT partman.create_parent(
    p_parent_table => 'public.events',
    p_control => 'created_at',
    p_type => 'range',
    p_interval => '1 month',
    p_premake => 3,
    p_start_partition => '2025-05-01'  -- 1 year back (assuming data since May 2025)
);

Step 2: configure retention

UPDATE partman.part_config
SET retention = '18 months',
    retention_keep_table = false
WHERE parent_table = 'public.events';

Step 3: load the historical data in batches

pg_partman provides partman.partition_data_proc() to move data from a source table into the partitioned one in chunks:

CALL partman.partition_data_proc(
    p_parent_table => 'public.events',
    p_source_table => 'public.events_old',
    p_interval     => '1 day',   -- how much data each batch moves (in units of the control column)
    p_loop_count   => 100,       -- how many batches per call (NULL = until the source is empty)
    p_wait         => 1          -- seconds to wait between batches (int, not float)
);

⚠️ Watch the parameter names: p_batch does NOT exist. It's the most common error when copying from old tutorials. The real 5.x signature is (p_parent_table, p_loop_count, p_interval, p_lock_wait, p_lock_wait_tries, p_wait, p_order, p_source_table, p_ignored_columns, p_quiet). And p_wait is an int, not a decimal.

Note also that the batch isn't measured in rows, but in the interval of the control column: p_interval => '1 day' means "move one day of data per batch", not "move N rows". That's an important conceptual difference — the actual batch size depends on how many rows you have per day.

What it does:

1. Takes the next p_interval of data (here, one day) from events_old.
2. Inserts it into events (PostgreSQL routes it to the correct partitions).
3. Deletes those rows from events_old.
4. Waits p_wait seconds (avoiding saturating the DB).
5. Repeats p_loop_count times (or until the source is empty if it's NULL).

Why it works in production:

• It doesn't block the app: new inserts keep going to events_old (or to events if you already did the switch). Each batch is transactional but short.
• Resumable: if it gets interrupted, running it again continues where it left off.
• Configurable: you can tune p_interval, p_loop_count, and p_wait based on the DB's load.

Step 4: switch the app (capsule 08 covers the details)

Once the batch migration is complete, the app starts writing to events. Capsule 08 covers the mechanics of the zero-downtime switch with dual-write or table renaming.

Monitoring and observability

pg_partman doesn't alert you on its own. You need monitoring to confirm it's working.

Query 1: the state of the managed tables

SELECT
    parent_table,
    control,
    partition_interval,
    premake,
    retention,
    automatic_maintenance
FROM partman.part_config;

Output:

 parent_table   | control    | partition_interval | premake | retention   | automatic_maintenance
----------------+------------+--------------------+---------+-------------+----------------------
 public.events  | created_at | 1 mon              |       3 | 18 mons     | on

Query 2: monitor the last run_maintenance execution

The column that tells you when maintenance last ran is maintenance_last_run:

SELECT
    parent_table,
    maintenance_last_run,
    NOW() - maintenance_last_run AS time_since_last_run
FROM partman.part_config
WHERE parent_table = 'public.events';

 parent_table  |     maintenance_last_run      | time_since_last_run
---------------+-------------------------------+---------------------
 public.events | 2026-07-14 09:00:00.123456+00 | 00:42:11.5

⚠️ Don't look for last_partition_check or last_partition: they don't exist. They're columns from old tutorials (or straight-up invented). The real columns of partman.part_config in 5.x include: parent_table, control, partition_interval, partition_type, premake, automatic_maintenance, retention, retention_schema, retention_keep_table, retention_keep_index, template_table, jobmon, infinite_time_partitions, and maintenance_last_run. If in doubt, look at all of them:

SELECT column_name FROM information_schema.columns
WHERE table_schema = 'partman' AND table_name = 'part_config'
ORDER BY ordinal_position;

Query 3: alert if the default partition grows

This is the critical alert:

SELECT count(*) AS rows_in_default
FROM events_default;

Configure an alert in your monitoring system (Prometheus, Datadog, CloudWatch):

• Warning: if > 100 rows.
• Critical: if > 1000 rows.

Rows in the default partition indicate: (a) pg_partman didn't create the future partitions in time, (b) data with invalid created_at, (c) a bug in the app.

Query 4: list a table's partitions

SELECT * FROM partman.show_partitions('public.events');

Output:

 partition_schemaname | partition_tablename
----------------------+---------------------
 public               | events_p2025_11
 public               | events_p2025_12
 ...
 public               | events_p2026_08

⚠️ show_partitions() returns exactly two columns: partition_schemaname and partition_tablename. Nothing else. There is no child_start_time or child_end_time — if you've seen those columns in some blog, they're not from this function.

Each partition's bounds don't come from pg_partman but from PostgreSQL's catalog. If you need to know what range each child covers (for "do I have enough future partitions?" monitoring), the source is pg_class:

SELECT
    c.relname                                   AS partition,
    pg_get_expr(c.relpartbound, c.oid)          AS bounds
FROM pg_class parent
JOIN pg_inherits i   ON i.inhparent = parent.oid
JOIN pg_class c      ON c.oid = i.inhrelid
WHERE parent.relname = 'events'
ORDER BY c.relname;

    partition    |                                    bounds
-----------------+------------------------------------------------------------------------------
 events_default  | DEFAULT
 events_p2026_07 | FOR VALUES FROM ('2026-07-01 00:00:00+00') TO ('2026-08-01 00:00:00+00')
 events_p2026_08 | FOR VALUES FROM ('2026-08-01 00:00:00+00') TO ('2026-09-01 00:00:00+00')

Useful for manually auditing which partitions exist and how far they go.

Query 5: on-disk size per partition

SELECT
    schemaname || '.' || tablename AS partition,
    pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS size
FROM pg_tables
WHERE tablename LIKE 'events_p%'
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC;

To see whether any partition grew abnormally.

Why does this matter in real work?

1. It's the difference between "I partitioned and it works in production for 2 years" and "I partitioned and it broke after 6 months". Without automation, someone has to remember to maintain the partitions. That person leaves, gets sick, changes priorities. pg_partman eliminates that human point of failure.

2. It reduces partitioning's operational time from hours/month to zero. Without pg_partman: someone writes scripts, tests them, schedules them, monitors them, repairs them when they fail. A realistic estimate: 2-4 hours/month per partitioned table. With pg_partman: a 30-minute initial setup, then auto-managed.

3. It saves you from writing and maintaining fragile bash/SQL. All the complexity of "create next month's partition", "drop the old ones per retention", "handle edge cases (DST changes, 31-day vs 30-day months)" is already solved and tested by the community. Your code doesn't have to reinvent it.

4. It makes auditing and compliance easier. "What are our retention policies?" is answered with SELECT * FROM partman.part_config. For a SOC2 audit or similar compliance, having the policy expressed in centralized metadata is real value.

5. Connection with guide #13 (audit logs). Guide #13 introduced audit_logs and mentioned retention as a practice. pg_partman is the tool that materializes that retention in production for partitioned tables. If your audit_logs is partitioned by month with 5 years of retention, pg_partman takes care of creating the new month and dropping the one from 5 years + 1 day ago.

Traps and common mistakes

Mistake 1 (conceptual): assuming pg_partman runs by itself after you install the extension

Symptom: you install pg_partman, register events, don't schedule run_maintenance(). Four months pass. The future partitions were never created. Inserts fall into the default.

Why it happens: pg_partman is a library of functions, not a daemon. It doesn't run on its own. It needs pg_cron, an OS cron, a k8s CronJob, or another scheduler to call run_maintenance() periodically.

How to tell: check:

-- Is there a scheduled job?
SELECT * FROM cron.job WHERE command LIKE '%run_maintenance%';
-- If it's empty and you didn't use OS cron, there's no scheduler.

How to fix it: schedule the maintenance (previous section). It's the step people forget most.

Mistake 2 (practical): premake too low and queries fail at the month boundary

Symptom: you configured p_premake = 1. On the 30th at 23:59 everything works. On the 1st of the next month at 00:01 the inserts fail or fall into the default because the new month's partition doesn't exist (or it does exist but the next one doesn't).

Why it happens: premake = 1 means "always have 1 future partition created". On the 1st of the new month, the "now" partition is the one that used to be "future". If maintenance hasn't run yet that day, there's no other future one. If your inserts arrive with created_at = NOW() + INTERVAL '1 hour' (a different timezone, scheduling), they can fall outside.

How to tell: monitor the default partition around month boundaries.

How to fix it: premake = 3 or more. A safety margin. If your maintenance runs every hour, premake 3 gives you 3 months of buffer. If it runs daily, premake 5-7. If it runs weekly (not recommended), premake 4 minimum.

Mistake 3 (conceptual): retention drops data with no backup

Symptom: you configured retention = '18 months' and retention_keep_table = false. After 18 months, the old partitions get removed. Compliance asks for the data for an audit, but it no longer exists.

Why it happens: retention_keep_table = false means "full DROP". There's no archiving, no automatic backup.

How to tell: document the decision. If the data is needed for compliance, pure retention is NOT enough.

How to fix it:

Option 1: retention_keep_table = true. The partition gets DETACHed (it remains standalone) instead of DROPped. You take care of archiving it:

UPDATE partman.part_config
SET retention = '18 months',
    retention_keep_table = true  -- detach, not drop
WHERE parent_table = 'public.events';

After the DETACH, you pg_dump each detached table and archive it (S3, Glacier, etc.). Then you can DROP TABLE once you've confirmed the archive.

Option 2: a longer retention + a parallel archiving script. You set retention to "5 years" but a parallel cron quarterly archives anything >18 months to cold storage.

Option 3: only apply retention after having a confirmed backup. A more complex operational setup, but it's the right thing when the data is critical.

Mistake 4 (practical): partition_data_proc runs in a very long transaction

Symptom: you run partition_data_proc to migrate 50M rows. After 30 minutes it hasn't finished and you notice it's holding a lock that's blocking other operations.

Why it happens: partition_data_proc is a procedure, not a function. Internally it uses per-batch transactions (not the whole migration in one transaction), but it can still consume significant connections and resources.

How to prevent it:

-- Small batch and a generous wait: slower, but gentle on the DB
CALL partman.partition_data_proc(
    p_parent_table => 'public.events',
    p_source_table => 'public.events_old',
    p_interval     => '6 hours',  -- small batch — more overhead but shorter locks
    p_wait         => 2           -- wait 2s between batches
);

With 50M rows spread over ~12 months, a p_interval of 6 hours is ~1460 batches. At 2 s of waiting each, that's ~50 min of waiting alone, plus the time of each batch. Acceptable during off-hours.

Tune p_interval by looking at how many rows fall inside that interval, not the number of batches: if you have 10M rows/day, '6 hours' is 2.5M rows per batch — far too many. Drop it to '15 minutes'.

Mistake 5 (conceptual): assuming pg_partman fixes a bad partitioning design

Symptom: you partitioned users by hash of id (capsule 05's case). pg_partman complains when registering: "hash partitioning doesn't support retention" or "premake doesn't apply to hash".

Why it happens: pg_partman is designed primarily for range partitioning with a time-based lifecycle: create future partitions, drop old ones. For list partitioning it supports limited operations. For hash partitioning, it supports very little — because hash has no natural "old" or "future".

How to tell: check pg_partman's documentation for the type you're using. Hash partitioning managed by pg_partman is a limited feature.

How to fix it: for hash and many list cases, you don't need pg_partman — because there's no lifecycle to automate. The partitions get created at the start and don't change. You only need monitoring (the default partition for list, distribution for hash).

pg_partman is the tool for range partitioning with retention. For the other cases, its value is limited.

Exercises

Exercise 1: full setup for an audit_logs table

Configure pg_partman for an audit_logs table partitioned by created_at:

• Granularity: monthly.
• Retention: 5 years (compliance).
• Premake: 6 future months.
• Maintenance: every 6 hours with pg_cron.
• Removed partitions must be DETACHed (not DROPped) for manual archiving.

-- 1. Create the parent table
CREATE TABLE audit_logs (
    id            BIGSERIAL,
    actor_id      BIGINT,
    action        TEXT NOT NULL,
    resource_type TEXT NOT NULL,
    resource_id   BIGINT,
    payload       JSONB,
    ip_address    INET,
    created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

-- 2. Indexes that propagate
CREATE INDEX audit_logs_actor_id_created_at_idx
    ON audit_logs (actor_id, created_at DESC);
CREATE INDEX audit_logs_resource_idx
    ON audit_logs (resource_type, resource_id, created_at DESC);

-- 3. Register with pg_partman
SELECT partman.create_parent(
    p_parent_table => 'public.audit_logs',
    p_control => 'created_at',
    p_type => 'range',
    p_interval => '1 month',
    p_premake => 6,
    p_start_partition => '2021-05-01'  -- 5 years back (assuming today is May 2026)
);

-- 4. Configure retention with DETACH (not DROP)
UPDATE partman.part_config
SET retention = '60 months',          -- 5 years = 60 months
    retention_keep_table = true,      -- DETACH, not DROP (for manual archiving)
    retention_keep_index = true,      -- Keep the indexes on the detached ones
    automatic_maintenance = 'on'
WHERE parent_table = 'public.audit_logs';

-- 5. Schedule maintenance every 6 hours with pg_cron
SELECT cron.schedule(
    'audit_logs_partman_maintenance',
    '0 */6 * * *',                    -- every 6 hours on minute 0
    $$CALL partman.run_maintenance_proc();$$
);

-- 6. Validate
SELECT * FROM partman.part_config WHERE parent_table = 'public.audit_logs';
SELECT * FROM cron.job WHERE jobname = 'audit_logs_partman_maintenance';
SELECT * FROM partman.show_partitions('public.audit_logs') LIMIT 10;

-- 7. External archiving setup (a separate process, not part of the SQL)
-- When a partition gets DETACHed, you should:
-- a) Detect it (query: tables with the audit_logs_p* prefix that are no longer partitions of audit_logs)
-- b) pg_dump the table
-- c) Upload the dump to S3/Glacier
-- d) DROP TABLE after confirming the archive

-- Query to detect detached partitions pending archival:
-- SELECT tablename FROM pg_tables
-- WHERE tablename LIKE 'audit_logs_p%'
--   AND tablename NOT IN (
--     SELECT partition_tablename FROM partman.show_partitions('public.audit_logs')
--   );

Exercise 2: detect and resolve "future partitions not created"

Your monitoring alerts: on the 28th of the month, inserts into events started failing with the error no partition of relation "events" found for row. Investigate and resolve.

-- 1. See which partitions exist
SELECT * FROM partman.show_partitions('public.events');

-- Example output (the problem):
--  partition_schemaname | partition_tablename
-- ----------------------+---------------------
--  public               | events_p2026_05
--
-- It's May 28 and the LAST partition is May's.
-- There's no future one: on June 1st the inserts have nowhere to land.

-- 2. Check the configuration
SELECT premake, automatic_maintenance, maintenance_last_run
FROM partman.part_config
WHERE parent_table = 'public.events';

-- A problematic example:
--  premake | automatic_maintenance | maintenance_last_run
-- ---------+-----------------------+------------------------
--        4 | off                   | 2026-04-15 10:00:00+00

-- 3. Check whether run_maintenance is scheduled
SELECT * FROM cron.job WHERE command LIKE '%run_maintenance%';
-- (empty — there's no job)

-- 1. Urgently create the missing partitions
--    (p_partition_times is TIMESTAMPTZ[], not TIMESTAMP[])
SELECT partman.create_partition_time(
    p_parent_table => 'public.events',
    p_partition_times => ARRAY[
        '2026-06-01'::TIMESTAMPTZ,
        '2026-07-01'::TIMESTAMPTZ,
        '2026-08-01'::TIMESTAMPTZ
    ]
);

-- 2. If there's data in the default belonging to the new partitions, move it.
--    WATCH THE ORDER: first you PULL the rows out of the default, and only then
--    can you create the partition (see capsule 04). If the default still holds rows
--    that would belong to the new partition, the CREATE fails with:
--      ERROR: updated partition constraint for default partition would be violated by some row
--    Since the partitions were already created in step 1, it means the default did
--    NOT have June/July/August rows. If it had, step 1 would have failed and you'd
--    have to drain the default first.
BEGIN;
CREATE TEMP TABLE rows_to_move ON COMMIT DROP AS
    SELECT * FROM events_default;
DELETE FROM events_default;
INSERT INTO events SELECT * FROM rows_to_move;
COMMIT;

-- 3. Enable automatic maintenance
UPDATE partman.part_config
SET premake = 3,
    automatic_maintenance = 'on'
WHERE parent_table = 'public.events';

-- 4. Schedule with pg_cron (assuming it's installed)
SELECT cron.schedule(
    'events_partman_maintenance',
    '0 * * * *',                     -- every hour
    $$CALL partman.run_maintenance_proc();$$
);

-- 5. Run maintenance now to confirm it works
SELECT partman.run_maintenance(p_parent_table => 'public.events');

-- 6. Verify
SELECT * FROM partman.show_partitions('public.events');
-- You should see May + June + July + August (premake 3 = 3 months ahead of the current one)

-- Create a view for monitoring
-- Note: the future partitions do NOT come from show_partitions() (which only gives
-- schema + name). The bounds live in the catalog, in pg_class.relpartbound.
CREATE OR REPLACE VIEW partman_health AS
SELECT
    pc.parent_table,
    pc.premake,
    pc.maintenance_last_run,
    EXTRACT(EPOCH FROM (NOW() - pc.maintenance_last_run)) / 3600 AS hours_since_last_run,
    (
        SELECT count(*)
        FROM pg_class parent
        JOIN pg_inherits i ON i.inhparent = parent.oid
        JOIN pg_class c    ON c.oid = i.inhrelid
        WHERE parent.relname = split_part(pc.parent_table, '.', 2)
          AND c.relpartbound IS NOT NULL
          AND pg_get_expr(c.relpartbound, c.oid) <> 'DEFAULT'
          -- the child's lower bound is later than now => it's a future partition
          AND substring(
                pg_get_expr(c.relpartbound, c.oid) from $re$FROM \('([^']+)'$re$
              )::timestamptz > NOW()
    ) AS future_partitions_count
FROM partman.part_config pc;

-- Your monitoring (Datadog/Prometheus/CloudWatch) reads this view periodically
-- Alert if:
--   - hours_since_last_run > 6 (maintenance lagging)
--   - future_partitions_count < premake (future ones missing)

Exercise 3: compare approaches — pg_partman vs a custom script

Your lead asks "do we really need pg_partman or can we do it with a bash script in cron?". Write a mini-comparison of 5-7 points.

Exercise 4: configure archiving to S3 before dropping

Your retention for events is 12 months. Compliance requires that removed partitions be archived to S3 before final deletion. Write the operational plan (not the full code, just the flow).

UPDATE partman.part_config
SET retention = '12 months',
    retention_keep_table = true,    -- DETACH, keeps the table
    retention_keep_index = false,   -- Drop the indexes (to reduce the backup size)
    automatic_maintenance = 'on'
WHERE parent_table = 'public.events';

# pseudocode
async def archive_detached_partitions():
    # 2.1 Detect detached partitions pending archival
    # A detached partition is: a table with the events_p* prefix that doesn't appear
    # in partman.show_partitions('public.events')

    detached = await find_detached_partitions("events")

    for partition in detached:
        # 2.2 pg_dump the partition
        dump_file = f"/tmp/{partition}.sql"
        run_command(f"pg_dump --table={partition} mydb > {dump_file}")

        # 2.3 Compress
        run_command(f"gzip {dump_file}")
        compressed = f"{dump_file}.gz"

        # 2.4 Upload to S3
        s3_key = f"archives/events/{partition}.sql.gz"
        upload_to_s3(compressed, bucket="my-archive-bucket", key=s3_key)

        # 2.5 Verify the upload (checksums)
        if verify_s3_upload(bucket, s3_key, compressed):
            # 2.6 Drop the original table
            await db.execute(f"DROP TABLE {partition}")
            log.info(f"Archived and dropped: {partition}")
        else:
            alert(f"Upload failed for {partition}, NOT dropping")

# k8s CronJob — daily at 3 AM
apiVersion: batch/v1
kind: CronJob
metadata:
  name: archive-events-partitions
spec:
  schedule: "0 3 * * *"
  jobTemplate:
    spec:
      template:
        spec:
          containers:
          - name: archiver
            image: myorg/archive-job:latest
            env:
            - name: DATABASE_URL
              valueFrom:
                secretKeyRef: ...
            - name: S3_BUCKET
              value: "my-archive-bucket"

# If compliance asks for a specific month's data:
# 1. Download from S3
aws s3 cp s3://my-archive-bucket/archives/events/events_p2024_06.sql.gz /tmp/

# 2. Decompress
gunzip /tmp/events_p2024_06.sql.gz

# 3. Restore into a temporary DB (not prod)
psql -d temp_audit_db < /tmp/events_p2024_06.sql

# 4. Run the compliance queries
# 5. Clean up the temp DB

Exercise 5: identify a case where pg_partman isn't the tool

Your team has 4 partitioned tables. For each one, decide whether pg_partman applies:

a) events partitioned by created_at monthly, 18-month retention. b) users partitioned by hash of id with modulus 8. No retention. c) tenant_data partitioned by list of tenant_id with 50 stable tenants. No time-based retention, but occasionally a tenant is offboarded. d) metrics_minutely partitioned by recorded_at daily, 30-day retention.

SELECT partman.create_parent(
    p_parent_table => 'public.metrics_minutely',
    p_control => 'recorded_at',
    p_type => 'range',
    p_interval => '1 day',
    p_premake => 7
);

UPDATE partman.part_config
SET retention = '30 days',
    retention_keep_table = false
WHERE parent_table = 'public.metrics_minutely';

-- Maintenance every 30 minutes (fine granularity demands more frequency)
SELECT cron.schedule(
    'metrics_partman_maintenance',
    '*/30 * * * *',  -- every 30 minutes
    $$CALL partman.run_maintenance_proc();$$
);

Exercise 6: monitoring and alerts for pg_partman

Design the 4 minimum alerts you should configure in your monitoring system for a partitioned table managed by pg_partman.

-- Query for Datadog/Prometheus/CloudWatch
SELECT count(*) AS rows_in_default
FROM events_default;

SELECT EXTRACT(EPOCH FROM (NOW() - maintenance_last_run)) / 3600 AS hours_since_last_run
FROM partman.part_config
WHERE parent_table = 'public.events';

SELECT
    parent_table,
    premake AS configured_premake,
    future_partitions_count AS actual_future_partitions
FROM partman_health
WHERE parent_table = 'public.events';

WITH partition_sizes AS (
    SELECT
        tablename,
        pg_total_relation_size(schemaname || '.' || tablename) AS bytes
    FROM pg_tables
    WHERE tablename LIKE 'events_p%'
)
SELECT
    tablename,
    bytes,
    bytes / (SELECT avg(bytes) FROM partition_sizes) AS ratio_to_avg
FROM partition_sizes
WHERE bytes > (SELECT avg(bytes) * 2 FROM partition_sizes);

# datadog.yaml — postgres integration
postgres:
  init_config:
  instances:
    - host: postgres-host
      port: 5432
      database: mydb
      username: monitoring_user
      password: ENC[...]

      custom_queries:
        - metric_prefix: postgres.partman
          query: |
            SELECT
              'events' as table_name,
              (SELECT count(*) FROM events_default) AS default_rows,
              (SELECT EXTRACT(EPOCH FROM (NOW() - maintenance_last_run)) / 3600
               FROM partman.part_config
               WHERE parent_table = 'public.events') AS hours_since_run
          columns:
            - name: table_name
              type: tag
            - name: default_rows
              type: gauge
            - name: hours_since_run
              type: gauge

# alerts.yaml
alerts:
  - name: "pg_partman: events_default growing"
    query: "max(last_5m):avg:postgres.partman.default_rows{table_name:events} > 1000"
    message: "events_default has {{value}} rows. Investigate immediately."

  - name: "pg_partman: maintenance lagging"
    query: "max(last_30m):avg:postgres.partman.hours_since_run{table_name:events} > 6"
    message: "pg_partman maintenance not run for {{value}} hours."

Summary and next step

In this capsule you learned the operational piece that makes partitioning sustainable in production:

• pg_partman is the automatic "gardener" that creates future partitions (premake), drops old ones (retention), and reports anomalies. Without it (or a robust equivalent), partitioning degrades from lack of maintenance.

• The setup is 30 minutes vs hours/month of operations with custom scripts. Installation + create_parent() + UPDATE part_config + scheduling run_maintenance() with pg_cron or equivalent.

• Version 5.x broke compatibility with the tutorials. p_type no longer accepts 'native' (only 'range' or 'list'), partition_data_proc has no p_batch (it's p_loop_count + p_interval), and part_config tracks the last run in maintenance_last_run (there is no last_partition_check). If you copy a snippet from the internet, check the version first.

• show_partitions() only returns schema and table name. The partition bounds live in PostgreSQL's catalog (pg_class.relpartbound), not in pg_partman. Any monitoring of "do I have enough future partitions?" has to go to the catalog.

• pg_partman solves the time-based lifecycle (range with retention). For hash without retention and for list without a cycle, its value is marginal or nil.

• partition_data_proc is the tool for migrating data from an unpartitioned table to a partitioned one in batches that don't block. It's complementary to the full zero-downtime technique covered in capsule 08.

• 4 mandatory minimum alerts: the default partition has rows, maintenance is lagging, future partitions are missing, a partition grew abnormally. Without monitoring, the problems stay silent until production breaks.

• Connection with guide #13: audit_logs retention (mentioned in #13 as a practice) is materialized in production with pg_partman. The extension is the piece that enforces the retention policies in an automated way.

Before moving on, you should be able to:

• Install pg_partman, register a table with create_parent(), configure retention, and schedule maintenance.
• Distinguish when pg_partman applies (range + retention) vs when it doesn't (hash, stable list).
• Design the 4 minimum alerts for production.
• Argue technically why to adopt pg_partman instead of custom scripts.

Next capsule — Project: partition events with 50M rows using a zero-downtime migration. You've reached the module's close. You're going to apply everything you learned to a real table: events with 50M rows, unpartitioned, in production with live traffic. You'll use the decision from capsule 02 (partition or not?), the syntax from capsule 03 (range by month), the verification from capsule 06 (EXPLAIN before/after), pg_partman from this capsule (maintenance), and the zero-downtime technique from guide #13 (without taking the app down). The deliverable is a PR with before/after benchmarks, and the rubric lets you self-assess. It's the project that gives you the module's portfolio-worthy material.

Resources

1. pg_partman — GitHub repository — source code, README, function documentation. The main entry point.
2. pg_partman — Documentation — detailed documentation of every function, configuration, and use case.
3. pg_partman — Installation Guide — a step-by-step howto for the initial setup.
4. Crunchy Data — Auto Maintenance with pg_partman — a pragmatic overview with real cases and operational tips.
5. pg_cron — GitHub repository — the scheduler extension that pairs perfectly with pg_partman.
6. Citus Data — Time-series Database with pg_partman — a real case combining it with sharding (advanced, out of scope, but good context).
7. AWS RDS — pg_partman support in Aurora PostgreSQL — availability and limitations in the managed service.
8. Crunchy Data — Production-grade partition management — best practices for serious deployments.

Module 4 — Advanced PostgreSQL for Backend Guide

Next capsule: The module project — partition events with 50M rows, zero-downtime migration, and before/after benchmarks.