pg_partman

Work In Progress

pg_partman (PostgreSQL Partition Manager)

Current version 2021 Oct 8: v4.6.0. Supports PostgreSQL 9.6+ The project highly recommends using PostgreSQL 11+ and Native Partitioning. AWS RDS (PostgreSQL 12.5+) and AWS Aurora (PostgreSQL 12.6+) has pg_partman pre-installed.

pg_partman is an extension to manage native/trigger-based partitions. This extension can help you with:

For PostgreSQL Partitions, see Native Partitioning and Trigger-based Partitioning.

Heads up

Install

https://github.com/pgpartman/pg_partman#installation

Install on macOS

See https://stackoverflow.com/questions/66475018/install-pg-partman-on-macos.

Enable pg_partman

Install pg_partman extension to a schema partman, so when you use its functions, it is clear it is coming from pg_partman.

CREATE SCHEMA partman
;

CREATE EXTENSION pg_partman SCHEMA partman
;

CREATE ROLE partman WITH LOGIN
;
GRANT ALL ON SCHEMA partman TO partman
;
GRANT ALL ON ALL TABLES IN SCHEMA partman TO partman
;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA partman TO partman
;
GRANT EXECUTE ON ALL PROCEDURES IN SCHEMA partman TO partman
;
GRANT ALL ON SCHEMA partman TO partman
;

Usage

create_parent function

SELECT partman.create_parent(
  p_parent_table => 'public.events', -- public is schema of your table
  p_control => 'created_at',
  p_type => 'native',
  p_interval=> 'daily',
  p_premake => 30 -- How many number of partitions in advance for new ingestion
)
;

run_maintenance_proc function

CREATE EXTENSION pg_cron;

UPDATE partman.part_config
SET infinite_time_partitions = true, -- auto create partition even if no new data coming in
    retention = '6 months', -- partition has retention for 6 months
    retention_keep_table = true -- dont auto delete partition, only detach from parent
WHERE parent_table = 'public.events'
;

-- check daily for above configured policy
SELECT cron.schedule('@daily', $$CALL partman.run_maintenance_proc()$$)
;

The partition_data_proc() can handle moving the data out of the default.

Check how many rows in default partition

SELECT * FROM partman.check_default(p_exact_count := true)
;

We cannot add a child table to a partition set if that new child table's constraint covers data that already exists in the default.

Fill gap between partitions immediately:

SELECT * FROM partman.partition_gap_fill('public.table');

Tutorials

Example Guide On Setting Up Native Partitioning

References

FAQs

What does create_parent do?

It paritions your table, pre make a default partition + few child partitions.

Why I can’t create child partition?

If you have data in the default partition, then you cannot create a new child partition if that new child partition's constraint matches data in the default. This is a PostgreSQL feature that attempt to create a new child partition that contains data tries to be attached, an error will be thrown.

The default partition has an constraint that is the opposite of all child partitions’s constraint. When we are creating a new child partition, PostgreSQL sees we already have the data in the default partition, so it prevent us adding a table that has the same constraint.

Use partition_data_proc to move data out from default partition into the proper child partitions. It moves data to a temp table, creates the child partition, then move data from temp to child partition.

partition_data_proc locks our table?

This locks your table when adding/removing child partitions. The rows being moved will also be locked because they're being transferred between tables.

Queries

Get pg_partman schmea

SELECT nspname
FROM pg_catalog.pg_namespace n,
     pg_catalog.pg_extension e
WHERE e.extname = 'pg_partman'
  AND e.extnamespace = n.oid
;

List your pg_partman config

SELECT *
FROM partman.part_config
;