Manage partitioned tables with pg_partman

pg_partman is a powerful PostgreSQL extension that automates the creation and management of time-based and sequence-ID-based partitioned tables. It simplifies partition management by automatically handling tasks like creating new partitions as needed and dropping old ones, which significantly reduces the complexity of database administration.

pg_partman is pre-compiled in SynxDB, so you do not need to install it manually. Simply enable the extension in your database to get started.

For a complete overview of pg_partman’s features and background, see its official GitHub page:

• Project homepage: https://github.com/pgpartman/pg_partman

• Detailed documentation: https://github.com/pgpartman/pg_partman/blob/development/doc/pg_partman.md

Enable the pg_partman extension

To use pg_partman, connect to your database and run the following SQL command to create a dedicated schema and enable the extension. Installing it in a separate schema is recommended for easier management.

CREATE SCHEMA partman;
CREATE EXTENSION pg_partman SCHEMA partman;

Usage example

The following example demonstrates the core workflow of pg_partman by showing how to manage a log table.

1. Create the parent table. Create a parent table to store logs, partitioned by RANGE on the log_time column.

   CREATE TABLE public.logs (
       id serial,
       log_time timestamp NOT NULL,
       message text
   ) PARTITION BY RANGE (log_time);
   

2. Create the partitioning configuration. Use the partman.create_parent function to tell pg_partman how to manage this partitioned table.

   SELECT partman.create_parent(
       p_parent_table := 'public.logs', -- Specify the parent table
       p_control := 'log_time',         -- Specify the control column (partition key)
       p_interval := '10 minutes'       -- Specify the partition interval, e.g., a new partition every 10 minutes
   );
   

3. Configure retention and pre-creation. You can configure a retention policy to have pg_partman automatically delete old partitions. To ensure write performance, you can also configure it to pre-create future partitions.

   Configure these policies by updating the partman.part_config table:

   UPDATE partman.part_config SET
       retention = '1 hour',             -- Retain only the last 1 hour of partitions
       retention_keep_table = false,     -- When partitions expire, drop the corresponding child tables directly
       retention_keep_index = false,     -- Also drop the indexes of expired child tables
       premake = 6                       -- Always pre-create 6 future partitions
   WHERE parent_table = 'public.logs';
   

4. Run partition maintenance. All pg_partman management operations, such as creating new partitions and dropping old ones, are triggered by the run_maintenance() function. You must run this function periodically to apply the configured rules.

   Run it manually once:

   SELECT partman.run_maintenance();
   

   Note

   The run_maintenance() function does not run automatically. In a production environment, you need to set up a scheduled job to call it periodically. It is recommended to use the built-in CREATE TASK feature in SynxDB for automated scheduling. For detailed usage of CREATE TASK, see Automate SQL statement execution.

   For example, create a task to run maintenance every 10 minutes (600 seconds):

   CREATE TASK run_maintenance SCHEDULE '*/10 * * * *' AS $$SELECT partman.run_maintenance()$$;
   

Main features

pg_partman provides a rich set of features to meet various partition management needs, including:

• Time and sequence-based partitioning: Supports partition keys of various types, including timestamp, timestamptz, date, integer, and bigint.

• Automatic partition creation: Pre-creates future partitions based on your configuration to prevent write failures due to non-existent partitions.

• Automatic partition cleanup: Automatically DROPs or DETACHes expired partitions based on the retention policy.

• Multi-level partitioning: Supports sub-partitioning.

• Runtime configuration: Allows you to modify partition policies by updating the configuration table directly, with no downtime required.

• Template table support: Allows you to define uniform indexes, permissions, and other properties for future child partitions.

For detailed usage, parameter descriptions, and more advanced configurations of the features above, refer to the official pg_partman documentation.