pg_partman
pg_partman
pg_partman : Extension to manage partitioned tables by time or ID
Overview
| ID | Extension | Package | Version | Category | License | Language |
|---|---|---|---|---|---|---|
| 2510 | pg_partman | pg_partman | 5.4.3 | OLAP | PostgreSQL | C |
| Attribute | Has Binary | Has Library | Need Load | Has DDL | Relocatable | Trusted |
|---|---|---|---|---|---|---|
--s-d-- | No | Yes | No | Yes | no | no |
| Relationships | |
|---|---|
| Requires | plpgsql |
| Need By | timeseries |
| See Also | citus pg_fkpart timescaledb periods emaj pg_cron plproxy temporal_tables |
Packages
| Type | Repo | Version | PG Major Compatibility | Package Pattern | Dependencies |
|---|---|---|---|---|---|
| EXT | PGDG | 5.4.3 | 18 17 16 15 14 | pg_partman | plpgsql |
| RPM | PGDG | 5.4.3 | 18 17 16 15 14 | pg_partman_$v | - |
| DEB | PGDG | 5.4.3 | 18 17 16 15 14 | postgresql-$v-partman | - |
| Linux / PG | PG18 | PG17 | PG16 | PG15 | PG14 |
|---|---|---|---|---|---|
el8.x86_64 | PGDG 5.4.3 | PGDG 5.4.3 | PGDG 5.4.3 | PGDG 5.4.3 | PGDG 5.4.3 |
el8.aarch64 | PGDG 5.4.3 | PGDG 5.4.3 | PGDG 5.4.3 | PGDG 5.4.3 | PGDG 5.4.3 |
el9.x86_64 | PGDG 5.4.3 | PGDG 5.4.3 | PGDG 5.4.3 | PGDG 5.4.3 | PGDG 5.4.3 |
el9.aarch64 | PGDG 5.4.3 | PGDG 5.4.3 | PGDG 5.4.3 | PGDG 5.4.3 | PGDG 5.4.3 |
el10.x86_64 | PGDG 5.4.3 | PGDG 5.4.3 | PGDG 5.4.3 | PGDG 5.4.3 | PGDG 5.4.3 |
el10.aarch64 | PGDG 5.4.3 | PGDG 5.4.3 | PGDG 5.4.3 | PGDG 5.4.3 | PGDG 5.4.3 |
d12.x86_64 | PGDG 5.4.3 | PGDG 5.4.3 | PGDG 5.4.3 | PGDG 5.4.3 | PGDG 5.4.3 |
d12.aarch64 | PGDG 5.4.3 | PGDG 5.4.3 | PGDG 5.4.3 | PGDG 5.4.3 | PGDG 5.4.3 |
d13.x86_64 | PGDG 5.4.3 | PGDG 5.4.3 | PGDG 5.4.3 | PGDG 5.4.3 | PGDG 5.4.3 |
d13.aarch64 | PGDG 5.4.3 | PGDG 5.4.3 | PGDG 5.4.3 | PGDG 5.4.3 | PGDG 5.4.3 |
u22.x86_64 | PGDG 5.4.3 | PGDG 5.4.3 | PGDG 5.4.3 | PGDG 5.4.3 | PGDG 5.4.3 |
u22.aarch64 | PGDG 5.4.3 | PGDG 5.4.3 | PGDG 5.4.3 | PGDG 5.4.3 | PGDG 5.4.3 |
u24.x86_64 | PGDG 5.4.3 | PGDG 5.4.3 | PGDG 5.4.3 | PGDG 5.4.3 | PGDG 5.4.3 |
u24.aarch64 | PGDG 5.4.3 | PGDG 5.4.3 | PGDG 5.4.3 | PGDG 5.4.3 | PGDG 5.4.3 |
u26.x86_64 | PGDG 5.4.3 | PGDG 5.4.3 | PGDG 5.4.3 | PGDG 5.4.3 | PGDG 5.4.3 |
u26.aarch64 | PGDG 5.4.3 | PGDG 5.4.3 | PGDG 5.4.3 | PGDG 5.4.3 | PGDG 5.4.3 |
Source
pig build pkg pg_partman; # build rpm/debInstall
Make sure PGDG repo available:
pig repo add pgdg -u # add pgdg repo and update cacheInstall this extension with pig:
pig install pg_partman; # install via package name, for the active PG version
pig install pg_partman -v 18; # install for PG 18
pig install pg_partman -v 17; # install for PG 17
pig install pg_partman -v 16; # install for PG 16
pig install pg_partman -v 15; # install for PG 15
pig install pg_partman -v 14; # install for PG 14Create this extension with:
CREATE EXTENSION pg_partman CASCADE; -- requires plpgsqlUsage
pg_partman: Extension to manage partitioned tables by time or ID
pg_partman automates creation and management of both time-based and number-based partition sets
using PostgreSQL’s native declarative partitioning (v5.0+). It handles adding new partitions and
removing old ones per retention policies, with an optional background worker for automatic maintenance.
Create the Extension
CREATE SCHEMA partman;
CREATE EXTENSION pg_partman SCHEMA partman;Create a Time-Based Partition Set
CREATE TABLE public.measurements (
id bigserial,
created_at timestamptz NOT NULL DEFAULT now(),
value numeric
) PARTITION BY RANGE (created_at);
SELECT partman.create_parent(
p_parent_table := 'public.measurements',
p_control := 'created_at',
p_interval := '1 day'
);Create a Serial/ID-Based Partition Set
CREATE TABLE public.events (
id bigserial,
data text
) PARTITION BY RANGE (id);
SELECT partman.create_parent(
p_parent_table := 'public.events',
p_control := 'id',
p_interval := '100000'
);Run Maintenance
Manually trigger partition maintenance (create new partitions, drop expired ones):
SELECT partman.run_maintenance();Or for a specific table:
SELECT partman.run_maintenance(p_parent_table := 'public.measurements');Configure Retention
Update the configuration to set retention policy:
UPDATE partman.part_config
SET retention = '30 days',
retention_keep_table = false
WHERE parent_table = 'public.measurements';Background Worker
Enable automatic maintenance in postgresql.conf:
shared_preload_libraries = 'pg_partman_bgw'
pg_partman_bgw.interval = 3600 -- run every hour (seconds)
pg_partman_bgw.dbname = 'mydb'Migrate Existing Data into Partitions
CALL partman.partition_data_proc('public.measurements');Show Partitions
SELECT * FROM partman.show_partitions('public.measurements');Undo Partitioning
CALL partman.undo_partition_proc('public.measurements');Last updated on