pg_partman Documentation

Overview

pg_partman is a PostgreSQL extension designed to simplify managing time-based or number/ID-based table partitioning.

Key Requirements & Compatibility

• PostgreSQL Version: Minimum version 14 is required (as of version 5.0.1).
• Partitioning Method: Uses built-in declarative partitioning. Trigger-based partitioning is no longer supported.
• Partitioning Types:
  • Ranged Partitioning: Supported for time- and number-based intervals.
  • List Partitioning: Supported for number-based partitioning when the interval is 1.
  • Numeric/Decimal Support: Experimental support for numeric/decimal values in number-based partitioning is available (as of version 5.1), but the interval must remain an integer.

Data Handling & Default Partitions

• A default partition is automatically created for all partition sets to catch data falling outside existing child boundaries.
• Use check_default() to monitor data in the default table.
• Use the partition_data_* family of functions to move data from the default table into valid child partitions.
• Warning: Future child table creation is based on current partition set data and ignores data in the default table by default. It is recommended to set a high premake value to ensure the expected data range is covered.

If your partition key is a TEXT column containing timestamp information (e.g., an identifier like INV20240815), you must define custom encoder and decoder functions.

1. Create an encoder function that takes a timestamptz and returns the formatted text identifier.
2. Create a decoder function that takes the text identifier and returns a timestamptz.
3. Call partman.create_partition passing these function names to p_time_encoder and p_time_decoder.

-- 1. Define Encoder
CREATE FUNCTION public.encode_timestamp(p_timestamp timestamptz, OUT encoded text)
    RETURNS text
    LANGUAGE plpgsql STABLE
    AS $$
BEGIN
    SELECT concat('INV', to_char(p_timestamp, 'YYYYMMDD')) INTO encoded;
END
$$;

-- 2. Define Decoder
CREATE FUNCTION public.decode_timestamp(p_str text, OUT ts timestamptz)
    RETURNS TIMESTAMPTZ
    LANGUAGE plpgsql STABLE
    AS $$
BEGIN
    SELECT substr(p_str, 4) INTO ts;
END
$$;

-- 3. Initialize Partitioning
SELECT partman.create_partition(
    p_parent_table := 'partman_test.time_taptest_table'
    , p_control := 'col3'
    , p_interval := '1 day'
    , p_time_encoder := 'public.encode_timestamp'
    , p_time_decoder := 'public.decode_timestamp'
);

Offline partitioning involves moving data from an existing non-partitioned table to a new partitioned table. This method is recommended when you have foreign keys pointing TO the table being partitioned, as it requires recreating the foreign key relationship.

Steps:

1. Rename the original table: Rename your current table so the new partitioned table can take its original name.
2. Create the new partitioned parent: Create a new table using PARTITION BY and ensure it has the same properties (privileges, constraints, defaults, indexes) as the original.
3. Initialize partitions: Use partman.create_partition() to set up the initial partition set.
4. Migrate data: Use partman.partition_data_proc() to move data from the old table to the new one in controlled batches.
5. Cleanup: Run VACUUM ANALYZE on the new parent table and the old source table after migration.

-- 1. Rename original table
ALTER TABLE public.original_table RENAME to old_nonpartitioned_table;

-- 2. Create new partitioned parent
CREATE TABLE public.original_table (
    col1 bigint not null
    , col2 text not null
    , col3 timestamptz DEFAULT now()
    , col4 text
) PARTITION BY RANGE (col1);

CREATE INDEX ON public.original_table (col1);

-- 3. Initialize partitions
SELECT partman.create_partition(
    p_parent_table := 'public.original_table'
    , p_control := 'col1'
    , p_interval := '10000'
);

-- 4. Migrate data in batches
CALL partman.partition_data_proc(
    p_parent_table := 'public.original_table'
    , p_loop_count := 200
    , p_interval := '1000'
    , p_source_table := 'public.old_nonpartitioned_table'
);

-- 5. Cleanup
VACUUM ANALYZE public.original_table;

To automate the creation of child tables and the execution of retention policies, you must run maintenance tasks regularly. You can use a scheduled job (like cron) or the PostgreSQL Background Worker (BGW).

Options for running maintenance:

• run_maintenance() (Function): Runs maintenance for all partition sets where automatic_maintenance is set to true in part_config.
  • Use p_parent_table to run maintenance for a specific table only (overriding automatic_maintenance settings).
  • Use p_analyze (boolean) to run an ANALYZE on the parent table if new child tables were created. This is necessary for effective constraint exclusion/partition pruning.
  • Use p_jobmon (boolean) to control logging via the pg_jobmon extension.
• run_maintenance_proc() (Procedure): Similar to the function, but it commits after each partition set's maintenance is finished. This is highly recommended for large numbers of partition sets to reduce transaction contention.
  • Use p_wait (int) to specify how many seconds to wait between each partition set's maintenance run.

To migrate a weekly partition set using the old IYYYwIW format to the new YYYYMMDD format, follow these steps:

1. Generate and run rename SQL: Use a query to generate ALTER TABLE ... RENAME TO ... statements. You must adjust the substring lengths based on your parent table name length + 2 (to account for _p).
2. Update configuration: Change the datetime_string in partman.part_config to 'YYYYMMDD'.
3. Test maintenance: Temporarily increase premake and set infinite_time_partitions = true, then run partman.run_maintenance() to verify new partitions follow the new naming convention.
4. Revert settings: Restore premake and infinite_time_partitions to their original values.

-- 1. Generate rename statements (Adjust '20' and '21' based on parent table name length + 2)
SELECT format(
    'ALTER TABLE %I.%I RENAME TO %I;'
    , n.nspname
    , c.relname
    , substring(c.relname from 1 for 20) || to_char(to_timestamp(substring(c.relname from 21), 'IYYY"w"IW'), 'YYYYMMDD')
)
FROM pg_inherits h
JOIN pg_class c ON h.inhrelid = c.oid
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE h.inhparent::regclass = 'your_schema.your_parent_table'::regclass
AND c.relname NOT LIKE '%_default'
ORDER BY c.relname;

-- 2. Update config
UPDATE partman.part_config SET datetime_string = 'YYYYMMDD';

-- 3. Test
UPDATE partman.part_config SET premake = premake+1, infinite_time_partitions = true;
SELECT partman.run_maintenance('your_schema.your_parent_table');