:pg_partman (partition manager)

Step 1: Install the extension and create a 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 will be a partitioned table,
-- using 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, which means a new partition is created daily.
SELECT create_parent(
    p_parent_table := 'public.log_table',
    p_control := 'created_at',
    p_interval := '1 day'
);

After successful execution, 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 created_at value.

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 a retention policy and verify cleanup

Set a data retention policy so that pg_partman automatically cleans up expired data. Configure log_table to retain data for the last two days 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',             -- Set the retention period to 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 have been deleted.
SELECT partition_schemaname, partition_tablename FROM show_partitions('public.log_table', 'DESC');

At this point, you have successfully configured a log table that is partitioned daily and cleaned up automatically.

Scenario 1: Daily partitioned log table with automatic 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 daily, retain data for 30 days, and automatically archive expired data to another schema instead of deleting it directly.

Procedure

1. Create a 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 for daily partitioning.
   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 partitions that are older than 30 days to the archive schema for subsequent 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 entirely older than 30 days, such as audit_logs_p2023_10_01, will be detached from the primary 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 a primary key, such as for user or product tables, and want to separate hot and cold data through partitioning.

Business scenario

Partition the orders table by order_id, with each partition containing 10 million IDs.

Decision advice

The choice of p_interval is critical. You can estimate it based on the growth rate of your 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 a 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 for partitioning 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 a 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 order records.
   -- Assume the current maximum order_id is 100,000,000.
   -- This policy will delete 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 a large existing table to a partitioned table

This scenario is for smoothly migrating a large, existing monolithic table, such as a terabyte-scale table, to a partitioned structure managed by pg_partman.

Business scenario

Migrate a 1 TB sensor_data table to a monthly partitioned table and minimize the impact on online services.

Core tool

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

Procedure

1. Preparations: Create a 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. Execute data migration: 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 setting it to be less than or equal to the partition interval. Here it is set to '1 day', meaning data for each day is processed in one transaction.
   -- p_wait: The waiting time (in seconds) between each batch, 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 will loop until all data in the sensor_data_source table has been migrated. During the migration, pg_partman will automatically create 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 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 when the first partition to create is determined.

    • 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 previous partitions may be created than expected, and not all future partitions may be created. The first run of run_maintenance() will create the missing future partitions. This is due to the need to support custom time intervals. For intervals of 24 hours or more, the setup will proceed as expected.

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

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

Sub-partitioning

Subpartitioning lets you create finer-grained partitions on existing partitions to form a multi-level partition structure, such as partitioning by year and then subpartitioning 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, subpartitioning is usually of limited effectiveness. We recommend that you prioritize the adjustment of the top-level partition interval.

Data retention policy

pg_partman automatically enforces data retention policies 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 explicitly exclude certain partitions, the query optimizer will skip scanning those partitions.

pg_partman provides the ability to add CHECK constraints on non-partition key columns to enhance the effect of 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 older than 30 partition intervals.

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

Template table and attribute inheritance

Because PostgreSQL declarative partitioning has incomplete support for inheriting certain object attributes, such as primary keys or unique indexes on non-partition keys and autovacuum settings, 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 indexes 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 make changes, 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 occurs only at partition creation time and is not automatically applied after changes. You can reapply them 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 supports automatic generation of new sequence values only when data is inserted through the parent table. It is not supported when you insert data directly into partitions.

Automatic maintenance

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

Manual maintenance

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

• CALL run_maintenance_proc(); We recommend that you use this procedure. It performs 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 want to perform maintenance only 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 normally 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
-- for more than 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 a 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 routine partition management, monitoring, and information queries.

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 partition structure or 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 and stores 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 subpartition 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, and premake, have the same meaning as in part_config, but their scope is the current subpartition set.

You do not need to review the definitions of all columns. Essentially, when pg_partman maintains a subpartition 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.