PostgreSQL分区管理利器pg_partman使用指南

PostgreSQL分区管理利器pg_partman使用指南

前言

在PostgreSQL数据库管理中，分区表是处理海量数据的有效手段。pg_partman作为PostgreSQL的分区管理扩展，能够自动化处理分区表的创建和维护工作，大幅减轻DBA的工作负担。本文将详细介绍如何使用pg_partman实现不同类型的分区管理。

时间范围分区：每日一个分区

创建分区表

首先需要创建一个已配置分区类型的父表。pg_partman目前仅支持RANGE类型分区（包括时间和ID分区）。注意，不能将非分区表直接转换为分区表的父表。

CREATE SCHEMA IF NOT EXISTS partman_test;

CREATE TABLE partman_test.time_taptest_table (
    col1 int,
    col2 text default 'stuff',
    col3 timestamptz NOT NULL DEFAULT now()
) PARTITION BY RANGE (col3);

CREATE INDEX ON partman_test.time_taptest_table (col3);

处理主键约束

在原生分区中，除非包含分区键，否则不能在分区父表上创建唯一索引（包括主键）。对于基于时间的分区，这通常不可行，因为这会限制每个子表中只能有一个时间戳值。

pg_partman通过使用模板表来管理原生分区不支持的特性。注意，这并不能解决跨分区集的约束强制执行问题。

CREATE TABLE partman_test.time_taptest_table_template (
    LIKE partman_test.time_taptest_table
);

ALTER TABLE partman_test.time_taptest_table_template 
ADD PRIMARY KEY (col1);

创建分区

使用create_parent函数初始化分区管理：

SELECT partman.create_parent(
    p_parent_table := 'partman_test.time_taptest_table',
    p_control := 'col3',
    p_interval := '1 day',
    p_template_table := 'partman_test.time_taptest_table_template'
);

执行后，系统会自动创建按天分区的子表，每个子表都会继承模板表的主键约束。

基于UUIDv7的时间分区

UUIDv7是一种包含时间戳成分的UUID版本，适合作为分区键。

创建分区表

CREATE SCHEMA IF NOT EXISTS partman_test;

CREATE TABLE partman_test.time_taptest_table (
    col1 int,
    col2 text default 'stuff',
    col3 uuid PRIMARY KEY
) PARTITION BY RANGE (col3);

使用UUIDv7编解码器

pg_partman内置了UUIDv7的编解码函数，可以基于UUID中的时间成分进行分区：

SELECT partman.create_parent(
    p_parent_table := 'partman_test.time_taptest_table',
    p_control := 'col3',
    p_interval := '1 day',
    p_time_encoder := 'partman.uuid7_time_encoder',
    p_time_decoder := 'partman.uuid7_time_decoder'
);

由于col3是分区键，主键索引会自动被子分区继承。

基于文本类型的时间分区

当分区键是包含时间信息的文本类型时，需要自定义编解码函数。

创建分区表

假设col3包含格式为INVYYYYMMDD的标识符：

CREATE SCHEMA IF NOT EXISTS partman_test;

CREATE TABLE partman_test.time_taptest_table (
    col1 int,
    col2 text default 'stuff',
    col3 text PRIMARY KEY
) PARTITION BY RANGE (col3);

自定义编解码函数

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
$$;

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
$$;

创建分区

SELECT partman.create_parent(
    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'
);

序列ID分区：每10个ID值一个分区

对于序列ID的分区，可以设置每个分区包含固定范围的ID值。

创建分区表

CREATE SCHEMA IF NOT EXISTS partman_test;

CREATE TABLE partman_test.id_taptest_table (
    col1 bigint,
    col2 text,
    col3 timestamptz DEFAULT now()
) PARTITION BY RANGE (col1);

创建分区

SELECT partman.create_parent(
    p_parent_table := 'partman_test.id_taptest_table',
    p_control := 'col1',
    p_interval := '10',
    p_start_partition := '0'
);

对已有表进行分区

pg_partman支持两种方式将已有表转换为分区表：

离线分区

1. 创建新的分区表结构
2. 将数据从原表迁移到分区表
3. 重命名表交换

在线分区

使用pg_partman的partition_table_proc()函数，可以在最小停机时间内完成分区转换。

撤销分区

如果需要撤销分区设置，可以使用undo_partition()函数：

SELECT partman.undo_partition(
    p_parent_table := 'partman_test.time_taptest_table',
    p_batch_interval := '1 day',
    p_batch_count := 10
);

总结

pg_partman为PostgreSQL分区管理提供了强大的自动化工具，支持多种分区策略和灵活的自定义选项。通过合理配置，可以显著简化分区表的维护工作，提高数据库管理效率。