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.
_10create schema partman;_10create 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:
_10select partman.create_parent('schema_name.table_name', 'partition_column', 'time', 'daily');
or for integer-based partitioning:
_10select 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_25create table public.foo (_25 id serial primary key,_25 data text,_25 created_at timestamp not null_25);_25_25-- Create the parent table_25select_25 partman.create_parent(_25 'public.foo',_25 'created_at',_25 'time',_25 'daily'_25 );_25_25-- Configure the partitioned table_25update_25 partman.part_config _25set_25 infinite_time_partitions = true,_25 retention = '3 months', _25 retention_keep_table=true _25where_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.
_10call partman.run_maintenance_proc();
To automate this task, consider using pg_cron
.
_10create extension pg_cron;_10_10select_10 cron.schedule('@hourly', $$call partman.run_maintenance_proc()$$);
Resources
- Official pg_partman documentation