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 is an extension to manage native/trigger-based partitions. This extension can help you with:
- Manage child table creation (table, index, granularity)
- Retentional Policy to automatically drop partitions
- Child Table Inheritance
- Currently pg_partman only supports the RANGE type of partitioning (both for time & id) — 2021 Oct 8
- Cannot create unique indexes (including primary keys) on native-partitioned parent unless they include the partition key
Install on macOS
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 ;
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 ) ;
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()$$) ;
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');
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.
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.
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 ;