postgres declare partitioning pg_partman pg_slice - ghdrako/doc_snipets GitHub Wiki

Partition Manager(pg_partman)

It is simple and straight-forward time partitioning.

• https://github.com/pgpartman/pg_partman/blob/master-old/doc/pg_partman.md
• https://github.com/pgpartman/pg_partman
• https://github.com/pgpartman/pg_partman/blob/master/doc/pg_partman.md
• https://www.percona.com/blog/partitioning-in-postgresql-with-pg_partman-serial-based-trigger-based/
• https://www.percona.com/blog/postgresql-partitioning-made-easy-using-pg_partman-timebased/
• https://medium.com/@golaneduard1/declarative-partitioning-in-postgresql-migrating-and-automating-with-pg-partman-and-pg-cron-b6d978abb507

With declarative partitioning, much of the partitioning management is automated, but for example, creating new partitions still requires manual intervention—unless you're using tools like pg_partman. Pg_partman helps to automate the creation and management of partitioned tables and partitions through a SQL API. Although new partitions aren’t added and removed automatically, this can be managed by adding another extension like pg_cron to schedule jobs.

A few examples:

• It’s essential to ensure that the necessary partitions have been created when ingesting data to avoid a No Partition of Relation Found for Rowerror, which may block your writes.
• If your workload involves sporadic or irregular data ingestions, you’ll need to ensure you aren't creating excessive, unnecessary partitions, as they could degrade query performance and lead to table bloat.
• You must ensure that there are no gaps or overlaps between partitions, especially when dealing with manual partition modifications.
• If you want to implement a retention policy to regularly drop old partitions regularly, you'll need to set this up.
• If you need to alter the schema of your tables, such as adding or dropping columns, you'll often have to handle these changes manually to ensure they propagate correctly to all partitions.

The pg_partman extension provides automation for creating and managing partitions based on predefined criteria, such as date or numeric ranges.

CREATE SCHEMA partman;
CREATE EXTENSION pg_partman WITH SCHEMA partman;
CREATE SCHEMA mk;      ------- create user your schema
CREATE TABLE mkt.sales(
        sale_id        int,
        sales_cout     int,
        prod           char(20),
        create_dt      timestamp,
        CONSTRAINT pk_mkt_event PRIMARY KEY (sale_id, create_dt)
) PARTITION BY RANGE (create_dt);
---Create Interval partition: The default is for 7 days 
SELECT partman.create_parent('mkt.sales', 'create_dt','1 day');
 create_parent
---------------
 t

CREATE TABLE public.events (
   id               character varying(100),
   name             character varying(200),
   creation_date    timestamp without time zone,
  PRIMARY KEY(id,creation_date)
 ) PARTITION BY RANGE (creation_date);

CREATE SCHEMA partman;

 CREATE EXTENSION pg_partman WITH SCHEMA partman;

 SELECT partman.create_parent(
    p_parent_table => 'public.events',
    p_control => 'creation_date',
    p_type => 'range',
    p_interval => '1 month',
    p_premake => 1);

 UPDATE partman.part_config
  SET infinite_time_partitions = true,
      retention = '24 months',
      retention_keep_table=true
  WHERE parent_table = 'public.events';

The parameters are as follows:

• p_parent_table – The parent partitioned table. This table must already exist and be fully qualified, including the schema.
• p_control – The column on which the partitioning is to be based. The data type must be an integer or time-based.
• p_type – The type is either 'range' or 'list'.
• p_interval – The time interval or integer range for each partition. Example values include 1 day, 1 hour, and so on.
• p_premake – The number of partitions to create in advance to support new inserts.
• infinite_time_partitions = true, – Configures the table to be able to automatically create new partitions without any limit.
• retention = '24 months', – Configures the table to have a maximum retention of 24 months.
• retention_keep_table=true – Configures the table so that when the retention period is due, the table isn't deleted automatically. Instead, partitions that are older than the retention period are only detached from the parent table.

NOTE Be aware of parameter retention_keep_table if you want or not to be removed old partitions.

CREATE EXTENSION pg_cron;

SELECT cron.schedule_in_database(
    'create-partitions-workshops',
    '@monthly',
    $$CALL partman.run_maintenance_proc()$$,
    'workshops');