PostgreSQL新增分区耗时过长，默认分区索引未生效求解

问题根源 #

PostgreSQL在创建新的范围分区时，会自动检查default分区中是否存在属于新分区范围的数据。即使为default分区的timestamp字段创建了索引，优化器可能因统计信息过时、内部查询逻辑未触发索引扫描等原因，依然选择全表扫描，导致IO激增、创建分区耗时极久。

解决方案 #

1. 手动迁移数据后再创建分区 #

先将default分区中属于目标新分区范围的数据迁移到新分区，再完成分区创建，避免系统自动扫描全表：

-- 1. 提前创建目标分区
CREATE TABLE partitioned_table_20240601 PARTITION OF partitioned_table
FOR VALUES FROM ('2024-06-01 00:00:00') TO ('2024-06-02 00:00:00');

-- 2. 迁移对应范围的数据
INSERT INTO partitioned_table_20240601
SELECT * FROM partitioned_table_default
WHERE timestamp >= '2024-06-01 00:00:00' AND timestamp < '2024-06-02 00:00:00';

-- 3. 从default分区删除已迁移的数据
DELETE FROM partitioned_table_default
WHERE timestamp >= '2024-06-01 00:00:00' AND timestamp < '2024-06-02 00:00:00';

此方法彻底避免了创建分区时的全表扫描，适合数据量较大的场景。

2. 临时禁用顺序扫描强制使用索引 #

如果必须直接创建分区，可临时禁用当前会话的顺序扫描，迫使优化器使用索引：

-- 临时禁用顺序扫描
SET enable_seqscan = off;

-- 创建目标分区
CREATE TABLE partitioned_table_20240601 PARTITION OF partitioned_table
FOR VALUES FROM ('2024-06-01 00:00:00') TO ('2024-06-02 00:00:00');

-- 恢复顺序扫描设置
SET enable_seqscan = on;

注意：此操作仅对当前会话生效，不要全局禁用顺序扫描，避免影响其他查询的执行计划。

3. 更新统计信息 #

如果索引未被使用是因为统计信息过时，先更新default分区的统计信息，再尝试创建分区：

ANALYZE partitioned_table_default;

优化器会基于最新的统计信息选择更优的执行计划，可能会触发索引扫描。

4. 拆分default分区（数据量极大时） #

若default分区数据量过大，可先将其拆分为多个子分区或普通表，再逐步挂载为正式分区：

-- 1. 将default分区从主表分离，转为普通表
ALTER TABLE partitioned_table DETACH PARTITION partitioned_table_default;

-- 2. 创建对应时间段的分区并迁移数据
CREATE TABLE partitioned_table_hist_2023 PARTITION OF partitioned_table
FOR VALUES FROM ('2023-01-01 00:00:00') TO ('2024-01-01 00:00:00');

INSERT INTO partitioned_table_hist_2023
SELECT * FROM partitioned_table_default WHERE timestamp < '2024-01-01 00:00:00';

-- 3. 重复上述步骤拆分其他时间段，最后将剩余数据重新挂载为default分区（或对应新分区）
ALTER TABLE partitioned_table ATTACH PARTITION partitioned_table_default DEFAULT;

5. 修复自动化分区管理 #

修复原cron任务，确保提前创建未来的分区（如提前7天），同时定期清理旧分区，避免数据再次堆积到default分区。也可使用pg_partman这类工具自动化分区生命周期管理，减少人为故障风险。

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