pg_partman

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:

Manage child table creation (table, index, granularity)
Retentional Policy to automatically drop partitions
Child Table Inheritance

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

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

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

See https://stackoverflow.com/questions/66475018/install-pg-partman-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
;

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');

Example Guide On Setting Up Native Partitioning

References

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

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.

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

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

SELECT *
FROM partman.part_config
;