pg_partman
Overview
| Package | Version | Category | License | Language |
|---|---|---|---|---|
pg_partman | 5.4.3 | OLAP | PostgreSQL | C |
| ID | Extension | Bin | Lib | Load | Create | Trust | Reloc | Schema |
|---|---|---|---|---|---|---|---|---|
| 2510 | pg_partman | No | Yes | No | Yes | No | No | - |
| Related | citus pg_fkpart timescaledb periods emaj pg_cron plproxy temporal_tables |
|---|---|
| Depended By | timeseries |
Version
| Type | Repo | Version | PG Ver | Package | Deps |
|---|---|---|---|---|---|
| EXT | PGDG | 5.4.3 | 1817161514 | pg_partman | - |
| RPM | PGDG | 5.4.3 | 1817161514 | pg_partman_$v | - |
| DEB | PGDG | 5.4.3 | 1817161514 | postgresql-$v-partman | - |
Build
You can build the RPM / DEB packages for pg_partman using pig build:
pig build pkg pg_partman # build RPM / DEB packages
Install
You can install pg_partman directly. First, make sure the PGDG repository is added and enabled:
pig repo add pgdg -u # Add PGDG repo and update cache
Install the extension using pig or apt/yum/dnf:
pig install pg_partman; # Install for current active PG version
pig ext install -y pg_partman -v 18 # PG 18
pig ext install -y pg_partman -v 17 # PG 17
pig ext install -y pg_partman -v 16 # PG 16
pig ext install -y pg_partman -v 15 # PG 15
pig ext install -y pg_partman -v 14 # PG 14
dnf install -y pg_partman_18 # PG 18
dnf install -y pg_partman_17 # PG 17
dnf install -y pg_partman_16 # PG 16
dnf install -y pg_partman_15 # PG 15
dnf install -y pg_partman_14 # PG 14
apt install -y postgresql-18-partman # PG 18
apt install -y postgresql-17-partman # PG 17
apt install -y postgresql-16-partman # PG 16
apt install -y postgresql-15-partman # PG 15
apt install -y postgresql-14-partman # PG 14
Create Extension:
CREATE EXTENSION pg_partman;
Usage
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');
Feedback
Was this page helpful?
Thanks for the feedback! Please let us know how we can improve.
Sorry to hear that. Please let us know how we can improve.