PostgreSQL列表分区相关技术问题咨询

1. 列表分区的分区值数量或分区表数量是否存在限制？ #

PostgreSQL没有文档明确规定的硬性上限，实际限制取决于系统资源（内存、磁盘、元数据存储能力）和性能表现。分区表过多会增加查询计划生成的开销，单个分区的FOR VALUES IN中包含过多值也会增大元数据复杂度，建议根据业务场景合理规划分区数量与分区值范围。

2. 按给定代码创建分区表后，能否通过SQL查询分区值列表？ #

可以通过查询系统目录表提取分区值，以下是两种常用查询方式：

方式一：查看分区定义与名称 #

SELECT 
  pg_get_partition_def(c.oid) AS partition_definition,
  c.relname AS partition_name
FROM pg_class c
JOIN pg_inherits i ON c.oid = i.inhrelid
JOIN pg_class p ON i.inhparent = p.oid
WHERE p.relname = 'part_table' AND c.relname != 'part_default';

方式二：精准提取分区值 #

SELECT 
  regexp_match(pg_get_partition_def(c.oid), 'FOR VALUES IN \((.*?)\)') AS partition_values,
  c.relname AS partition_name
FROM pg_class c
JOIN pg_inherits i ON c.oid = i.inhrelid
JOIN pg_class p ON i.inhparent = p.oid
WHERE p.relname = 'part_table' AND c.relname != 'part_default';

3. 默认分区存在test_3数据时，创建对应分区报错，如何不删除数据完成创建？ #

需要先将默认分区中key_name='test_3'的数据迁移至新分区，再完成分区关联，步骤如下（可包裹在事务中保证数据一致性）：

BEGIN;
-- 创建新的分区表（暂不关联主表）
CREATE TABLE part_test_3 (LIKE part_table INCLUDING ALL);
-- 迁移目标数据
INSERT INTO part_test_3 SELECT * FROM part_default WHERE key_name = 'test_3';
-- 删除默认分区中的对应数据
DELETE FROM part_default WHERE key_name = 'test_3';
-- 将新分区关联到主表
ALTER TABLE part_table ATTACH PARTITION part_test_3 FOR VALUES IN ('test_3');
COMMIT;

4. 是否可以修改分区表的名称或分区值？ #

• 修改分区表名称：支持直接重命名，与普通表操作一致：

ALTER TABLE part_test RENAME TO part_test_new;

• 修改分区值：PostgreSQL不支持直接修改分区关联的取值，需通过迁移数据+重建分区的方式实现，示例步骤（以将part_test的取值从'test'改为'test_new'为例）：

-- 创建对应新值的分区
CREATE TABLE part_test_new PARTITION OF part_table FOR VALUES IN ('test_new');
-- 迁移旧分区数据到新分区
INSERT INTO part_test_new SELECT * FROM part_test;
-- 将旧分区从主表分离
ALTER TABLE part_table DETACH PARTITION part_test;
-- 可选：删除旧分区
DROP TABLE part_test;

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