如何在RDS PostgreSQL 12.10中自动实现LIST分区至新表？

针对运行时动态出现的分区键值，你可以通过两种主流方式实现LIST分区的自动创建，以下是具体操作步骤：

1. 基础：创建LIST分区主表 #

首先定义以LIST方式分区的主表，假设我们以region字段作为分区键：

CREATE TABLE orders (
    order_id INT,
    region VARCHAR(20),
    order_date DATE,
    amount NUMERIC
) PARTITION BY LIST (region);

2. 方式一：触发器+自定义函数（轻量手动实现） #

通过编写PL/pgSQL函数结合触发器，在插入数据时自动检查并创建对应分区：

步骤1：创建分区检查与创建函数 #

CREATE OR REPLACE FUNCTION create_list_partition_if_not_exists()
RETURNS TRIGGER AS $$
DECLARE
    partition_name TEXT;
    partition_value TEXT;
BEGIN
    partition_value := NEW.region;
    -- 生成合规的分区表名，替换空格为下划线并转为小写
    partition_name := 'orders_' || lower(replace(partition_value, ' ', '_'));
    
    -- 检查目标分区是否已存在
    IF NOT EXISTS (
        SELECT 1 FROM pg_tables 
        WHERE tablename = partition_name AND schemaname = 'public'
    ) THEN
        -- 动态创建分区
        EXECUTE format(
            'CREATE TABLE %I PARTITION OF orders FOR VALUES IN (%L)',
            partition_name,
            partition_value
        );
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

步骤2：给主表绑定触发器 #

CREATE TRIGGER trigger_create_list_partition
BEFORE INSERT ON orders
FOR EACH ROW EXECUTE FUNCTION create_list_partition_if_not_exists();

此后插入包含新region值的数据时，会自动创建对应的分区表。

3. 方式二：使用pg_partman扩展（生产环境推荐） #

RDS PostgreSQL原生支持pg_partman扩展，这是专门管理分区的工具，稳定性和扩展性更强。

步骤1：启用pg_partman扩展 #

CREATE EXTENSION pg_partman;

（需要账号拥有rds_superuser权限，可通过RDS控制台或IAM配置）

步骤2：初始化分区配置 #

SELECT partman.create_parent(
    p_parent_table := 'public.orders',
    p_partition_type := 'list',
    p_control := 'region',
    p_interval := NULL, -- LIST分区无需时间/数值间隔，留空
    p_automatic_maintenance := 'on' -- 开启自动维护
);

步骤3：自动处理新分区键值 #

pg_partman会自动识别新的分区键值，你可以手动触发维护检查，或者配置定时任务：

• 手动触发维护：

SELECT partman.run_maintenance();

• 配置定时任务（需先启用pg_cron扩展，在RDS参数组中开启）：

-- 每分钟运行一次分区维护
SELECT cron.schedule('* * * * *', 'SELECT partman.run_maintenance();');

注意事项 #

• 触发器方式适合低写入量场景，高并发下会有一定性能开销；pg_partman更适合生产环境，支持分区清理、批量维护等特性。
• 分区命名需符合PostgreSQL表名规范，避免特殊字符。
• 操作前请在测试环境验证，确保新键值插入时分区正常创建。

内容的提问来源于stack exchange，提问作者Mohit Singla