PolarDB:pg_partman (partition manager)

Step 1: Install the extension and create the parent table

First, install the pg_partman extension in your database. Then, create a parent table for partitioning.

-- 1. Enable the pg_partman extension in the current database.
CREATE EXTENSION pg_partman;

-- 2. Create a parent table to store log data.
-- PARTITION BY RANGE (created_at) specifies that this table is a partitioned table and uses the created_at column for range partitioning.
CREATE TABLE public.log_table (
    id bigint GENERATED BY DEFAULT AS IDENTITY,
    created_at timestamptz NOT NULL,
    message text
) PARTITION BY RANGE (created_at);

Step 2: Initialize the partition set

Call the create_parent() function to register log_table with the pg_partman management system and define the partitioning rules.

-- Call the create_parent function to initialize partitioning.
-- public.log_table: The parent table to manage.
-- created_at: The partition key column.
-- '1 day': The partition interval. A new partition is created every day.
SELECT create_parent(
    p_parent_table := 'public.log_table',
    p_control := 'created_at',
    p_interval := '1 day'
);

After the statement is successfully executed, pg_partman automatically creates partitions for the current day and the next few days. You can run the \d+ public.log_table command to view the created partitions.

Step 3: Insert test data

Insert some data into the parent table log_table. The data is automatically routed to the corresponding partition based on the value of created_at.

INSERT INTO public.log_table (created_at, message)
SELECT generate_series(
     now() - interval '4 days',
     now(),
     interval '1 hour'
 ),
 'This is a log message.';

Step 4: Configure the retention policy and verify cleanup

Set a data retention policy to let pg_partman automatically clean up expired data. Configure log_table to retain only the last two days of data and automatically DROP expired partitions.

-- 1. Update the part_config table to set a retention policy for log_table.
UPDATE part_config
SET
    retention = '2 days',             -- Retention period of 2 days.
    retention_keep_table = false      -- Do not keep the table structure. Directly DROP expired partitions.
WHERE parent_table = 'public.log_table';

-- 2. Manually run the maintenance task to trigger the retention policy.
CALL run_maintenance_proc();

-- 3. Verification: Check the partition list again to confirm that partitions older than 2 days are deleted.
SELECT partition_schemaname, partition_tablename FROM show_partitions('public.log_table', 'DESC');

You have now successfully configured a log table that is partitioned by day and has an automatic cleanup policy.

Scenario 1: Daily partitioned log table with auto-cleanup

This scenario is suitable for data that grows rapidly and has a clear lifecycle, such as logs, audit records, and Internet of Things (IoT) time series data.

Business scenario

Partition the audit_logs table by day, retain data for 30 days, and automatically archive expired data to another schema instead of deleting it directly.

Procedure

1. Create the parent table and initialize the partition set:

   -- Create the parent table.
   CREATE TABLE public.audit_logs (
       log_id uuid NOT NULL,
       event_time timestamptz NOT NULL,
       user_id text,
       details jsonb,
       PRIMARY KEY (event_time, log_id)
   ) PARTITION BY RANGE (event_time);
   
   -- Initialize the partition set to partition by day.
   SELECT create_parent(
       p_parent_table := 'public.audit_logs',
       p_control := 'event_time',
       p_interval := '1 day'
   );

2. Configure the retention and archiving policy: Move old partition tables older than 30 days to the archive schema for later batch analytics or backup.

   -- Create a schema for archiving.
   CREATE SCHEMA IF NOT EXISTS archive;
   
   -- Update the configuration to set the retention policy.
   UPDATE part_config
   SET
       retention = '30 days',          -- Retain for 30 days.
       retention_schema = 'archive'    -- Move to the archive schema after expiration.
   WHERE parent_table = 'public.audit_logs';

   When run_maintenance() runs, any partition table that is completely older than 30 days, such as audit_logs_p2023_10_01, will be detached from the main partition set. Its schema will be changed from public to archive, becoming archive.audit_logs_p2023_10_01.

Scenario 2: Business table partitioned by ID

This scenario is suitable for businesses that use a numeric ID as the primary key for tables such as user tables or product tables and want to separate hot and cold data through partitioning.

Business scenario

Partition the orders table by order_id. Each partition contains 10 million IDs.

Recommendation

The choice of p_interval is critical. Estimate it based on the growth rate of business IDs and a reasonable size for a single partition. If 1 million new orders are added daily, an interval of 10000000 means a new partition is created approximately every 10 days.

Procedure

1. Create the parent table and initialize the partition set:

   -- Create the parent table.
   CREATE TABLE public.orders (
       order_id bigint NOT NULL,
       customer_id bigint,
       order_date timestamptz,
       amount numeric
   ) PARTITION BY RANGE (order_id);
   
   -- Initialize the partition set to partition by ID range.
   -- Note: p_interval must be provided as text, even if it is a number.
   SELECT create_parent(
       p_parent_table := 'public.orders',
       p_control := 'order_id',
       p_interval := '10000000' -- Each partition contains 10 million IDs.
   );

2. Configure the retention policy (optional): If old order data can be archived or deleted, you can configure an ID-based retention policy.

   -- Retain the most recent 50 million orders.
   -- Assume the current maximum order_id is 100,000,000.
   -- This policy deletes all partitions where order_id < 50,000,000.
   UPDATE part_config
   SET
       retention = '50000000',
       retention_keep_table = false
   WHERE parent_table = 'public.orders';

Scenario 3: Migrating an existing large table to a partitioned table

This scenario is for smoothly migrating an existing, massive monolithic table (for example, terabyte-scale) to a partitioned structure managed by pg_partman.

Business scenario

Migrate a 1 TB sensor_data table to a table partitioned by month with minimal impact on online business.

Core tool

The partition_data_proc() procedure. It can migrate data in small batches and independent transactions, which avoids long table locks and transaction backlogs.

Procedure

1. Preparations: Create the parent table and initialize the partition set.

   -- 1. Rename the original table to serve as the data source.
   ALTER TABLE public.sensor_data RENAME TO sensor_data_source;
   
   -- 2. Create a new parent table with the same structure.
   CREATE TABLE public.sensor_data (
       reading_id bigserial,
       device_id text,
       ts timestamptz NOT NULL,
       value double precision
   ) PARTITION BY RANGE (ts);
   
   -- 3. Initialize the partition set.
   SELECT create_parent('public.sensor_data','ts','1 month');

2. Migrate the data: Use partition_data_proc to migrate data in batches from sensor_data_source to the new partitioned table sensor_data.

   -- Call the data migration procedure.
   -- p_source_table: Specifies the source table.
   -- p_interval: The time range of data to process in each batch. We recommend that this be less than or equal to the partition interval. Here it is set to '1 day', meaning one day's worth of data is processed in a single transaction.
   -- p_wait: The waiting time (in seconds) between each batch. This is used to reduce sustained pressure on I/O and CPU.
   CALL partition_data_proc(
       p_parent_table := 'public.sensor_data',
       p_source_table := 'public.sensor_data_source',
       p_interval := '1 day',
       p_wait := 1
   );

   This procedure runs in a loop until all data from the sensor_data_source table is migrated. During the migration, pg_partman automatically creates the required partitions as needed.

3. Final steps: After the migration is complete, verify data consistency. Then, you can safely delete the original data source table sensor_data_source. During the migration, the application can continue to write new data to the new partitioned parent table sensor_data.

Partition type and interval

Partition type

pg_partman is based on PostgreSQL's declarative partitioning and mainly supports the following partitioning methods:

• Range Partitioning: Suitable for continuous data, such as timestamps or serial IDs. This is the most common partition type.

• List Partitioning: Supported only for numeric ID-based partitioning with an interval of 1.

Partition interval (p_interval)

This parameter defines the data range covered by each child partition.

• Time-based: Accepts any valid interval value, such as '1 hour', '1 day', '1 week', or '1 month'.

  Note

  • The minimum supported time interval is 1 second. The upper limit is constrained by the minimum and maximum timestamp values supported by PostgreSQL.

  • When you first run create_parent() to create a partition set, intervals of less than one day are rounded down to determine the first partition to create.

    • Intervals less than 24 hours but greater than 1 minute are rounded down to the nearest hour.

    • Intervals less than 1 minute are rounded down to the nearest minute.

    • The system creates enough partitions to support the current real time. This means that when you run create_parent(), more past partitions than expected may be created, and not all future partitions may be created. The first time you run run_maintenance(), it creates any missing future partitions. This behavior is necessary to support custom time intervals. For intervals of 24 hours or more, the setup proceeds as expected.

  • For intervals of 100 years or more, the extension uses the actual start of the century or millennium to determine partition names and constraint rules. For example, the 21st century and the 3rd millennium began on January 1, 2001 (not 2000). This also means there is no year 0.

• Numeric/ID-based: Accepts an integer representing the range size, but it must be provided as text, such as '1000000'.

Sub-partitioning

Sub-partitioning lets you create finer-grained partitions on existing partitions, forming a multi-level partition structure (such as partitioning by year, then sub-partitioning each year by month). It is mainly used for data organization and lifecycle management, such as archiving a large annual partition on a monthly basis. For performance improvement, sub-partitioning usually has a limited effect. Consider adjusting the interval of the top-level partitions first.

Data retention policy

pg_partman automatically runs a data retention policy through run_maintenance() to manage the lifecycle of old partitions. The core configuration parameters are as follows:

Constraint exclusion

Constraint exclusion is a key feature for improving the query performance of partitioned tables. When the WHERE clause of a query can clearly exclude certain partitions, the query optimizer skips scanning those partitions.

pg_partman provides the ability to add CHECK constraints on non-partition key columns to improve constraint exclusion. For example, for a table partitioned by created_at, if there are frequent queries with WHERE device_id = 'A', you can add a constraint to the device_id column of old partitions where data no longer changes.

The core configuration parameters are as follows:

• constraint_cols: A text array that specifies on which columns to create CHECK constraints. For example, '{device_id, user_id}'.

• optimize_constraint: An integer that defines how old a partition must be before the constraint is applied. The default value of 30 means the constraint is applied to partitions that are 30 partition intervals older than the current one.

• constraint_valid: Adding a constraint to a table may conflict with existing data in the table. It can also cause pg_partman's maintenance operations to take a long time. You can set these constraints to be created as not valid. This allows the constraint creation to be almost instantaneous, but constraint exclusion cannot be used until they are validated. Therefore, by default, constraints are created as valid.

Template table and attribute inheritance

Because PostgreSQL's declarative partitioning has incomplete support for inheriting certain object attributes (such as primary keys/unique indexes on non-partition key columns, autovacuum settings, etc.), pg_partman uses a template table mechanism to ensure that newly created partitions can correctly inherit these attributes. The following table shows how pg_partman manages the inheritance of specific attributes. If an attribute is not listed in the table, it is managed through the parent table.

• Attributes: When you call create_parent(), pg_partman automatically creates a table named parent_table_template. Any ALTER TABLE modifications you make to this template table (such as adding an index or changing autovacuum parameters) will be applied when new partitions are created in the future.

  Note

  Modifications to the template table are not retroactively applied to existing partitions. To modify them, you must manually run ALTER operations on the old partitions.

• Permissions and ownership: By default, permissions and ownership are not inherited. If you enable this feature through pg_partman, note that this inheritance only occurs at partition creation time. Changes are not automatically applied afterward (they can be reapplied with reapply_privileges()). You usually do not need to enable this feature unless you need to access partitions directly. If you do, you can set the inherit_privileges option.

  Note

  If you use the IDENTITY feature to manage sequences, it only supports auto-generation of new sequence values when data is inserted through the parent table. It is not supported when inserting data directly into a partition.

Automated maintenance

The core maintenance tasks of pg_partman (creating new partitions and running retention policies) are performed by the run_maintenance() function or the run_maintenance_proc() procedure. In PolarDB for PostgreSQL, we recommend using the built-in Background Worker to automate this process. It automatically calls run_maintenance_proc() based on the interval you set.

Manual maintenance

In some cases, you may need to manually trigger maintenance, such as when debugging or when you need fine-grained control over a specific table.

• CALL run_maintenance_proc(); We recommend using this procedure. It runs maintenance for all partition sets in part_config where automatic_maintenance is on. It also automatically commits the transaction after each partition set is maintained, which reduces lock contention.

• SELECT run_maintenance('public.my_table'); If you only want to run maintenance on a single partition set, you can call this function and pass the parent table name.

Monitoring and alerting

In a production environment, you need to monitor whether partition maintenance is running correctly to prevent data write failures caused by partitions not being created in time. The maintenance_last_run field in the part_config table records the timestamp of the last successful maintenance run for each partition set. This is a key monitoring metric.

-- Query for all automatically maintained partition sets that have not run successfully in over twice the maintenance interval.
SELECT parent_table
FROM part_config
WHERE
    automatic_maintenance = 'on'
    AND maintenance_last_run < (now() - (SELECT setting::interval * 2 FROM pg_settings WHERE name = 'pg_partman_bgw.interval'));

If this query returns any rows, it means that the maintenance for the corresponding partition set may have been interrupted and requires immediate investigation.

Create partitions

These functions are used to initialize partition sets and define subpartitions.

create_parent(
    p_parent_table text
    , p_control text
    , p_interval text
    , p_type text DEFAULT 'range'
    , p_epoch text DEFAULT 'none'
    , p_premake int DEFAULT 4
    , p_start_partition text DEFAULT NULL
    , p_default_table boolean DEFAULT true
    , p_automatic_maintenance text DEFAULT 'on'
    , p_constraint_cols text[] DEFAULT NULL
    , p_template_table text DEFAULT NULL
    , p_jobmon boolean DEFAULT true
    , p_date_trunc_interval text DEFAULT NULL
    , p_control_not_null boolean DEFAULT true
    , p_time_encoder text DEFAULT NULL
    , p_time_decoder text DEFAULT NULL
)
RETURNS boolean

create_sub_parent(
    p_top_parent text
    , p_control text
    , p_interval text
    , p_type text DEFAULT 'range'
    , p_default_table boolean DEFAULT true
    , p_declarative_check text DEFAULT NULL
    , p_constraint_cols text[] DEFAULT NULL
    , p_premake int DEFAULT 4
    , p_start_partition text DEFAULT NULL
    , p_epoch text DEFAULT 'none'
    , p_jobmon boolean DEFAULT true
    , p_date_trunc_interval text DEFAULT NULL
    , p_control_not_null boolean DEFAULT true
    , p_time_encoder text DEFAULT NULL
    , p_time_decoder text DEFAULT NULL
)
RETURNS boolean

create_partition_time(
    p_parent_table text
    , p_partition_times timestamptz[]
    , p_start_partition text DEFAULT NULL
)
RETURNS boolean

create_partition_id(
    p_parent_table text
    , p_partition_ids bigint[]
    , p_start_partition text DEFAULT NULL
)
RETURNS boolean

Migrate data

These functions are used to migrate historical data or data that has mistakenly entered the default partition into the correct partitions.

partition_data_time(
    p_parent_table text
    , p_batch_count int DEFAULT 1
    , p_batch_interval interval DEFAULT NULL
    , p_lock_wait numeric DEFAULT 0
    , p_order text DEFAULT 'ASC'
    , p_analyze boolean DEFAULT true
    , p_source_table text DEFAULT NULL
    , p_ignored_columns text[] DEFAULT NULL
)
RETURNS bigint

partition_data_id(
    p_parent_table text
    , p_batch_count int DEFAULT 1
    , p_batch_interval bigint DEFAULT NULL
    , p_lock_wait numeric DEFAULT 0
    , p_order text DEFAULT 'ASC'
    , p_analyze boolean DEFAULT true
    , p_source_table text DEFAULT NULL
    , p_ignored_columns text[] DEFAULT NULL
)
RETURNS bigint

partition_data_proc (
    p_parent_table text
    , p_loop_count int DEFAULT NULL
    , p_interval text DEFAULT NULL
    , p_lock_wait int DEFAULT 0
    , p_lock_wait_tries int DEFAULT 10
    , p_wait int DEFAULT 1
    , p_order text DEFAULT 'ASC'
    , p_source_table text DEFAULT NULL
    , p_ignored_columns text[] DEFAULT NULL
    , p_quiet boolean DEFAULT false
)

Maintain partitions

These functions are used for daily partition management, monitoring, and information querying.

run_maintenance(
    p_parent_table text DEFAULT NULL
    , p_analyze boolean DEFAULT false
)
RETURNS void

run_maintenance_proc(
    p_wait int DEFAULT 0
    , p_analyze boolean DEFAULT NULL
)

check_default(
    p_exact_count boolean DEFAULT true
)

show_partitions(
    p_parent_table text
    , p_order text DEFAULT 'ASC'
    , p_include_default boolean DEFAULT false
)
RETURNS TABLE (
    partition_schemaname text
    , partition_tablename text
)

show_partition_name(
    p_parent_table text
    , p_value text
    , OUT partition_schema text
    , OUT partition_table text
    , OUT suffix_timestamp timestamptz
    , OUT suffix_id bigint
    , OUT table_exists boolean
)
RETURNS record

show_partition_info(
    p_child_table text
    , p_partition_interval text DEFAULT NULL
    , p_parent_table text DEFAULT NULL
    , OUT child_start_time timestamptz
    , OUT child_end_time timestamptz
    , OUT child_start_id bigint
    , OUT child_end_id bigint
    , OUT suffix text
)
RETURNS record

Destroy partitions

These functions are used to undo a partitioning structure or to delete old partitions according to a retention policy.

undo_partition(
    p_parent_table text
    , p_target_table text
    , p_loop_count int DEFAULT 1
    , p_batch_interval text DEFAULT NULL
    , p_keep_table boolean DEFAULT true
    , p_lock_wait numeric DEFAULT 0
    , p_ignored_columns text[] DEFAULT NULL
    , p_drop_cascade boolean DEFAULT false
    , OUT partitions_undone int
    , OUT rows_undone bigint)
RETURNS record

undo_partition_proc(
    p_parent_table text
    , p_target_table text DEFAULT NULL
    , p_loop_count int DEFAULT NULL
    , p_interval text DEFAULT NULL
    , p_keep_table boolean DEFAULT true
    , p_lock_wait int DEFAULT 0
    , p_lock_wait_tries int DEFAULT 10
    , p_wait int DEFAULT 1
    , p_ignored_columns text[] DEFAULT NULL
    , p_drop_cascade boolean DEFAULT false
    , p_quiet boolean DEFAULT false
)

drop_partition_time(
    p_parent_table text
    , p_retention interval DEFAULT NULL
    , p_keep_table boolean DEFAULT NULL
    , p_keep_index boolean DEFAULT NULL
    , p_retention_schema text DEFAULT NULL
    , p_reference_timestamp timestamptz DEFAULT CURRENT_TIMESTAMP
)
RETURNS int

drop_partition_id(
    p_parent_table text
    , p_retention bigint DEFAULT NULL
    , p_keep_table boolean DEFAULT NULL
    , p_keep_index boolean DEFAULT NULL
    , p_retention_schema text DEFAULT NULL
)
RETURNS int

part_config

This table is the main configuration center for pg_partman, storing the partitioning policies and maintenance settings for all top-level parent tables.

part_config_sub

This table stores the configuration for subpartitions. Its structure is basically the same as part_config, but with one key difference: it uses the sub_parent column to identify its immediate parent table.

• sub_parent (text): The name of the parent table of the sub-partition set. This table itself is a partition of the top-level parent table.

• Other columns: All other columns in the table (such as partition_interval, retention, premake, etc.) have the same meaning as in part_config, but their scope is the current sub-partition set.

You do not need to review the definitions of all columns here again. Just understand that when pg_partman maintains a sub-partition set, it looks up the configuration row in the part_config_sub table using sub_parent as the key and applies the policy defined in that row.