With pg_partman 5.5 managing the monthly clicks table in PostgreSQL, what do create_partition(), premake, the default table and run_maintenance_proc() do, and what must you monitor?
answer
- parent must already be declarative
- renamed from create_parent
- four partitions of runway
- check_default() and maintenance_last_run
- worker role changed in 5.5
basics
~20 spg_partman automates declarative partitions: create_partition() registers an existing partitioned table, premake (default 4) keeps future partitions ready, a default partition catches strays, and run_maintenance_proc() or its background worker creates and retires partitions. Monitor default-table rows and maintenance failures.
solid answer
~40 sThe parent must already be `PARTITION BY RANGE (clicked_at)`; `SELECT partman.create_partition('public.clicks', 'clicked_at', '1 month');` registers it (5.4 renamed it from `create_parent()`, kept for compatibility), briefly takes `ACCESS EXCLUSIVE`, and creates the current and `premake` (default 4) future children, a default partition and a template table. Maintenance comes from `CALL partman.run_maintenance_proc();` on a scheduler, which commits per set, or from the background worker: `pg_partman_bgw` in `shared_preload_libraries`, `pg_partman_bgw.dbname`, `pg_partman_bgw.interval` 3600 seconds. In 5.5 `pg_partman_bgw.role` defaults to `partman_maintainer`, so the worker errors until that role exists. Retention detaches rather than drops unless `retention_keep_table` is false. Monitor `partman.check_default()` for rows in the default, moving them with `partition_data_proc()`, and `part_config.maintenance_last_run`, which 5.5 sets to NULL when a set's maintenance fails.
code
sql · 13 linesCREATE SCHEMA partman;
CREATE EXTENSION pg_partman SCHEMA partman;
SELECT partman.create_partition('public.clicks', 'clicked_at', '1 month');
UPDATE partman.part_config
SET retention = '13 months', retention_keep_table = false
WHERE parent_table = 'public.clicks';
CALL partman.run_maintenance_proc();
SELECT * FROM partman.check_default();
SELECT parent_table, maintenance_last_run FROM partman.part_config;go deeper
Know that pg_partman creates future partitions and retires old ones for a table that is already declaratively partitioned, and that its main entry point is create_partition().
Explain premake, the default and template tables, retention_keep_table, and the difference between run_maintenance(), run_maintenance_proc() and the background worker.
Run it in production: alert on check_default() rows and stale maintenance_last_run, move stray rows with partition_data_proc(), and handle the 5.5 worker-role change before upgrading.
Decide whether a third-party extension or in-house automation owns partition lifecycle, weighing upgrade cadence, security fixes and who can change part_config.
## What pg_partman is pg_partman is a third-party extension that automates **creating and retiring partitions** of PostgreSQL's declaratively partitioned tables. Since 5.0.1 it uses native declarative partitioning only (minimum PostgreSQL 14); the old trigger-based mode is gone. It supports time- and number-based `range` partitioning, plus `list` when the interval is 1. It does not partition an ordinary table for you: the parent must already be declared with `PARTITION BY`. ## Setting up the clickstream set 1. Install it in its own schema: `CREATE SCHEMA partman; CREATE EXTENSION pg_partman SCHEMA partman;`. 2. Create `clicks` as `PARTITION BY RANGE (clicked_at)` and put indexes, constraints and privileges on the parent, so they propagate to children. 3. Register it: `SELECT partman.create_partition('public.clicks', 'clicked_at', '1 month');`. The parent name must be schema-qualified. `create_partition()` replaced `create_parent()` in 5.4; the old name still exists for backward compatibility. 4. The call briefly takes `ACCESS EXCLUSIVE` on the parent (it moves no data), creates the current and future children, a **default partition** and a **template table**, and writes a row to `partman.part_config`. ## The settings that matter | Setting | Default | Meaning | |---|---|---| | `premake` | 4 | children kept ready ahead of the current one | | `p_default_table` (setup parameter) | true | create a default partition for stray rows | | `retention` | NULL | keep everything; set e.g. `'13 months'` to retire old children | | `retention_keep_table` | true | retention **detaches** old children instead of dropping them | | `infinite_time_partitions` | false | no new time partitions while no new data arrives | | `ignore_default_data` | true | maintenance ignores default-table rows when deciding what to create | | `automatic_maintenance` | 'on' | include this set in a plain maintenance run or the worker | | `detach_before_drop` | false | new in 5.5: detach before dropping, for sets referenced by foreign keys | Two of these interact in a way that surprises people. Future children are planned from the **newest data** in the set, and with `infinite_time_partitions` false pg_partman stops making new time partitions while no new rows arrive. If ingestion pauses for longer than the `premake` runway, the first rows after it resumes can land in the default and then have to be moved with `partition_data_proc()`; set `infinite_time_partitions` to true for a set whose ingestion can legitimately go quiet. The default of 4 was chosen with the optional pg_jobmon extension in mind, which raises an alert after three consecutive failed runs, so a failing job is reported before the runway is used up. ## Running maintenance - `run_maintenance()` is a function that maintains every set with `automatic_maintenance` on, or only the set you pass. - `CALL partman.run_maintenance_proc();` is a procedure that commits after each set, which reduces lock contention when many sets are maintained. - The **background worker** simply runs `run_maintenance_proc()` on a timer. Add `pg_partman_bgw` to `shared_preload_libraries` (restart required), set `pg_partman_bgw.dbname`, and optionally `pg_partman_bgw.interval` (default 3600 seconds). - **5.5 breaking change:** `pg_partman_bgw.role` now defaults to `partman_maintainer`. If it was never set and that role does not exist with the needed privileges, the worker fails. The project recommends a dedicated non-superuser role. - Also in 5.5, an error in one set no longer stops maintenance of the others: it logs a warning and sets that set's `maintenance_last_run` to NULL. ## What to monitor - **Rows in the default**: `SELECT * FROM partman.check_default();` returns a row for each default table that contains data. Any row means `premake` fell behind or clients send bad timestamps. Move valid rows with `CALL partman.partition_data_proc('public.clicks');`, which creates the needed child and moves data in committed batches. Left alone, those rows block core PostgreSQL from creating the partition they belong to. - **Maintenance freshness**: `maintenance_last_run` in `part_config`; an old timestamp or NULL means the job or worker is failing. - **Runway**: `partman.show_partitions('public.clicks')` lists children; the newest should stay months ahead of now. - **Template caveats**: properties core cannot propagate, such as `UNLOGGED` on children in PostgreSQL 18 or a unique index on a non-partition column, come from the template and apply only to children created afterwards. Such a unique index is enforced per child only, which the bundled `check_unique_constraint.py` script audits.
- check_default() reports rows in clicks_default. How do you fix it with pg_partman?Run `CALL partman.partition_data_proc('public.clicks');`. It creates the children those rows belong to and moves them out of the default in committed batches, with `p_loop_count` and `p_wait` to throttle. Then find out why they arrived: `premake` too small, maintenance not running, or bad client timestamps.
- Retention is set to 13 months, but old clicks partitions are still in the database. Why?`retention_keep_table` defaults to true, so retention only detaches expired children and leaves them as standalone tables. Set it to false to drop them, or set `retention_schema` to move them elsewhere. If other tables reference `clicks` by foreign key, 5.5's `detach_before_drop` is also needed.
- After upgrading to pg_partman 5.5 the background worker logs errors. What changed?`pg_partman_bgw.role` now defaults to `partman_maintainer`, an arbitrary role name chosen so the worker stops running as a superuser by accident. Create that role with ownership of the partition sets and rights in their schemas, or set the parameter to another non-superuser role; it takes effect on reload.
- pg_partman 5.x routes rows into child tables with insert triggers.
- create_partition() converts an ordinary table into a partitioned one in place.
- Setting retention makes pg_partman drop old partitions by default.
- The background worker runs as a superuser unless you configure a role.
- A unique index on the template table guarantees uniqueness across the whole partition set.
Assumes pg_partman 5.5 on PostgreSQL 18: create_partition() replaced create_parent() in 5.4, and 5.5 changed the background worker's default role and failure handling.