Database

pg_partman: partition management

pg_partman is a PostgreSQL extension that enables automated partition management. It removes the need to create and detach partitions for tables partitioned by time or an integer series.

Enable the extension

To enable pg_partman, first enable the extension in your database inside a dedicated schema.


_10
create schema partman;
_10
create extension pg_partman schema partman;

Setting up Partitioning

pg_partman supports partitioning based on time (e.g., by day, month, or year) or by a sequential integer. Setup is performed using the create_parent function.

For time-based partitioning:


_10
select partman.create_parent('schema_name.table_name', 'partition_column', 'time', 'daily');

or for integer-based partitioning:


_10
select partman.create_parent('schema_name.table_name', 'partition_column', 'integer', '1000');

API

  • partman.create_parent: creates a parent table for partitioning
  • partman.run_maintenance: ensures partitions are created and dropped as per the defined settings. This should be run periodically, such as via a cron job scheduled by pg_cron

Example

Let’s set up a simple partitioning example using pg_partman with time-based partitions.


_25
-- Create the table
_25
create table public.foo (
_25
id serial primary key,
_25
data text,
_25
created_at timestamp not null
_25
);
_25
_25
-- Create the parent table
_25
select
_25
partman.create_parent(
_25
'public.foo',
_25
'created_at',
_25
'time',
_25
'daily'
_25
);
_25
_25
-- Configure the partitioned table
_25
update
_25
partman.part_config
_25
set
_25
infinite_time_partitions = true,
_25
retention = '3 months',
_25
retention_keep_table=true
_25
where
_25
parent_table = 'public.foo';

For more information about available configuration, see the pg_partman docs.

Following these statements, partitions for public.foo will be automatically managed as necessary when the partman.run_maintenance() function is called.

Running Maintenance

It is important to call the partman.run_maintenance() procedure frequently to ensure partitions are created and detached according to the partitioned table's configuration.


_10
call partman.run_maintenance_proc();

To automate this task, consider using pg_cron.


_10
create extension pg_cron;
_10
_10
select
_10
cron.schedule('@hourly', $$call partman.run_maintenance_proc()$$);

Resources