pg_partman 是一款用于创建和管理基于时间和基于序列的表分区集的插件，旨在简化基于时间或数字/ID 的表分区管理。

创建与删除插件

创建插件

CREATE EXTENSION pg_partman;

删除插件

DROP EXTENSION pg_partman;

使用插件

基于时间分区

以下是一个基于时间分区的示例，通过该示例可以快速了解插件能力。

创建分区表

-- 先创建分区表
CREATE TABLE test_schema.time_partition_table(
col1 int,
col2 text default 'stuff',
col3 timestamptz NOT NULL DEFAULT now()
)PARTITION BY RANGE (col3);
-- 创建非唯一索引
CREATE INDEX ON test_schema.time_partition_table(col3);

test_db=> -- 查看表结构
test_db=> \d+ test_schema.time_partition_table
Partitioned table "test_schema.time_partition_table"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+--------------------------+-----------+----------+---------------+----------+-------------+--------------+-------------
col1 | integer | | | | plain | | |
col2 | text | | | 'stuff'::text | extended | | |
col3 | timestamp with time zone | | not null | now() | plain | | |
Partition key: RANGE (col3)
Indexes:
"time_partition_table_col3_idx" btree (col3)
Number of partitions: 0

创建模板表

-- 创建模板表用于 properties 继承
CREATE TABLE test_schema.time_partition_table_template (LIKE test_schema.time_partition_table);
-- 为模板创建主键用于后续 properties 继承
ALTER TABLE test_schema.time_partition_table_template ADD PRIMARY KEY (col1);

test_db=> \d+ test_schema.time_partition_table_template
Table "test_schema.time_partition_table_template"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+--------------------------+-----------+----------+---------+----------+-------------+--------------+-------------
col1 | integer | | not null | | plain | | |
col2 | text | | | | extended | | |
col3 | timestamp with time zone | | not null | | plain | | |
Indexes:
"time_partition_table_template_pkey" PRIMARY KEY, btree (col1)
Access method: heap

创建子表

-- 为分区表创建子表
SELECT test_schema.create_parent(
p_parent_table := 'test_schema.time_partition_table',
p_control := 'col3',
p_interval := '1 day',
p_template_table := 'test_schema.time_partition_table_template'
);
\d+ test_schema.time_partition_table

test_db=> \d+ test_schema.time_partition_table
Partitioned table "test_schema.time_partition_table"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+--------------------------+-----------+----------+---------------+----------+-------------+--------------+-------------
col1 | integer | | | | plain | | |
col2 | text | | | 'stuff'::text | extended | | |
col3 | timestamp with time zone | | not null | now() | plain | | |
Partition key: RANGE (col3)
Indexes:
"time_partition_table_col3_idx" btree (col3)
Partitions: test_schema.time_partition_table_p20250413 FOR VALUES FROM ('2025-04-13 00:00:00+08') TO ('2025-04-14 00:00:00+08'),
test_schema.time_partition_table_p20250414 FOR VALUES FROM ('2025-04-14 00:00:00+08') TO ('2025-04-15 00:00:00+08'),
test_schema.time_partition_table_p20250415 FOR VALUES FROM ('2025-04-15 00:00:00+08') TO ('2025-04-16 00:00:00+08'),
test_schema.time_partition_table_p20250416 FOR VALUES FROM ('2025-04-16 00:00:00+08') TO ('2025-04-17 00:00:00+08'),
test_schema.time_partition_table_p20250417 FOR VALUES FROM ('2025-04-17 00:00:00+08') TO ('2025-04-18 00:00:00+08'),
test_schema.time_partition_table_p20250418 FOR VALUES FROM ('2025-04-18 00:00:00+08') TO ('2025-04-19 00:00:00+08'),
test_schema.time_partition_table_p20250419 FOR VALUES FROM ('2025-04-19 00:00:00+08') TO ('2025-04-20 00:00:00+08'),
test_schema.time_partition_table_p20250420 FOR VALUES FROM ('2025-04-20 00:00:00+08') TO ('2025-04-21 00:00:00+08'),
test_schema.time_partition_table_p20250421 FOR VALUES FROM ('2025-04-21 00:00:00+08') TO ('2025-04-22 00:00:00+08'),
test_schema.time_partition_table_default DEFAULT

test_db=> -- 查看子表，父表和模板表的索引都在子表中成功创建
test_db=> \d+ test_schema.time_partition_table_p20250419
Table "test_schema.time_partition_table_p20250419"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+--------------------------+-----------+----------+---------------+----------+-------------+--------------+-------------
col1 | integer | | not null | | plain | | |
col2 | text | | | 'stuff'::text | extended | | |
col3 | timestamp with time zone | | not null | now() | plain | | |
Partition of: test_schema.time_partition_table FOR VALUES FROM ('2025-04-19 00:00:00+08') TO ('2025-04-20 00:00:00+08')
Partition constraint: ((col3 IS NOT NULL) AND (col3 >= '2025-04-19 00:00:00+08'::timestamp with time zone) AND (col3 < '2025-04-20 00:00:00+08'::timestamp with time zone))
Indexes:
"time_partition_table_p20250419_pkey" PRIMARY KEY, btree (col1)
"time_partition_table_p20250419_col3_idx" btree (col3)
Access method: heap

非分区表转换成分区表

以下是一个以离线方式将非分区表转换成分区表的示例。

构造测试数据

-- 创建普通表用于后续测试
CREATE TABLE test_schema.original_table(
col1 bigint not null,
col2 text not null,
col3 timestamptz DEFAULT now(),
col4 text
);
CREATE INDEX ON test_schema.original_table(col1);
-- 构造部分数据
INSERT INTO test_schema.original_table (col1, col2, col3, col4) VALUES (generate_series(1,100000), 'stuff'||generate_series(1,100000), now(), 'stuff');

数据迁移

-- 以下步骤开始迁移
-- 1. 重命名待迁移表
ALTER TABLE test_schema.original_table RENAME to old_nonpartitioned_table;

-- 2. 新建与原表同名分区表，并创建子表
CREATE TABLE test_schema.original_table(
col1 bigint not null,
col2 text not null,
col3 timestamptz DEFAULT now(),
col4 text
) PARTITION BY RANGE (col1);
CREATE INDEX ON test_schema.original_table (col1);
SELECT test_schema.create_parent(
p_parent_table := 'test_schema.original_table',
p_control := 'col1',
p_interval := '10000'
);
\d+ test_schema.original_table

-- 3. 调用函数partition_data_proc迁移数据
CALL test_schema.partition_data_proc(
p_parent_table := 'test_schema.original_table',
p_loop_count := 200,
p_interval := '1000',
p_source_table := 'test_schema.old_nonpartitioned_table'
);

数据校验

test_db=> -- 检查数据
test_db=> SELECT count(*) FROM test_schema.old_nonpartitioned_table;
count
-------
0
(1 row)

test_db=> SELECT count(*) FROM test_schema.original_table;
count
--------
100000
(1 row)

test_db=> \d+ test_schema.original_table
Partitioned table "test_schema.original_table"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+--------------------------+-----------+----------+---------+----------+-------------+--------------+-------------
col1 | bigint | | not null | | plain | | |
col2 | text | | not null | | extended | | |
col3 | timestamp with time zone | | | now() | plain | | |
col4 | text | | | | extended | | |
Partition key: RANGE (col1)
Indexes:
"original_table_col1_idx1" btree (col1)
Partitions: test_schema.original_table_p0 FOR VALUES FROM ('0') TO ('10000'),
test_schema.original_table_p10000 FOR VALUES FROM ('10000') TO ('20000'),
test_schema.original_table_p100000 FOR VALUES FROM ('100000') TO ('110000'),
test_schema.original_table_p20000 FOR VALUES FROM ('20000') TO ('30000'),
test_schema.original_table_p30000 FOR VALUES FROM ('30000') TO ('40000'),
test_schema.original_table_p40000 FOR VALUES FROM ('40000') TO ('50000'),
test_schema.original_table_p50000 FOR VALUES FROM ('50000') TO ('60000'),
test_schema.original_table_p60000 FOR VALUES FROM ('60000') TO ('70000'),
test_schema.original_table_p70000 FOR VALUES FROM ('70000') TO ('80000'),
test_schema.original_table_p80000 FOR VALUES FROM ('80000') TO ('90000'),
test_schema.original_table_p90000 FOR VALUES FROM ('90000') TO ('100000'),
test_schema.original_table_default DEFAULT

test_db=> SELECT count(*) FROM test_schema.original_table_p10000;
count
-------
10000
(1 row)

pg_partman_bgw 后台工作进程

pg_partman_bgw 是一个 Background Worker 进程，自动定期执行 run_maintenance()，遍历 part_config 表里所有需要维护的分区表，对每个表按需补建未来分区或按需清理过期分区，无需人工干预或外部定时任务。

pg_partman_bgw 关键参数

参数名	是否必须	使用说明
pg_partman_bgw.dbname	必须	后台进程要连接的目标数据库。可以设置多个数据库，用逗号分隔。
pg_partman_bgw.role	必须	后台进程连接目标数据库使用的角色名。
pg_partman_bgw.interval	推荐	后台进程的执行间隔（秒），默认 3600（1 小时）。

验证 pg_partman_bgw 正常工作

AI 原生 BaaS 平台 Supabase 版