PostgreSQL如何不读取海量数据验证约束或附加分区？

我们正在对存储大量带时间戳记录的数据库进行逾期已久的分区转换操作，该时间戳字段已单独创建索引。计划流程是：创建现有表的分区版本，再将现有表作为分区附加到新表中。

为避免附加分区时锁表，按照文档建议先创建NOT VALID约束再验证，执行的SQL如下：

ALTER TABLE foo ADD CONSTRAINT partitioning_constraint 
CHECK (record_timestamp BETWEEN '2000-01-01' AND '2024-11-20') -- 结束日期设为未来某时间
NOT VALID;

COMMIT;

ALTER TABLE foo VALIDATE CONSTRAINT partitioning_constraint; -- 此步骤读取数据耗时极长

但直接查询不符合约束的记录时，因为索引存在，几乎瞬间返回空结果：

SELECT * from foo WHERE record_timestamp < '2000-01-01'; -- 立即返回空行
SELECT * from foo WHERE record_timestamp > '2024-11-20'; -- 立即返回空行

已确认：

• 通过简单SELECT语句验证了约束的有效性（无不符合约束的数据）
• NOT VALID约束已生效，可阻止不符合约束的数据变更

需求：找到无需读取数TB数据即可完成约束验证（或直接附加分区）的方法，生产环境无法承受大规模IO占用。

方法1：利用索引加速约束验证（PostgreSQL 12+） #

PostgreSQL不会自动用现有索引加速VALIDATE CONSTRAINT，但可以通过部分索引绕开全表扫描：

1. 先删除已创建的NOT VALID约束：

ALTER TABLE foo DROP CONSTRAINT partitioning_constraint;

2. 基于现有时间戳索引创建部分索引，覆盖符合约束的所有数据（因已确认无违规数据，此索引会包含全表数据，创建速度极快）：

CREATE INDEX idx_foo_partitioning_check ON foo(record_timestamp)
WHERE record_timestamp BETWEEN '2000-01-01' AND '2024-11-20';

3. 重新创建NOT VALID约束并验证，此时PostgreSQL会利用部分索引完成验证，无需全表扫描：

ALTER TABLE foo ADD CONSTRAINT partitioning_constraint 
CHECK (record_timestamp BETWEEN '2000-01-01' AND '2024-11-20') NOT VALID;

ALTER TABLE foo VALIDATE CONSTRAINT partitioning_constraint;

4. 验证完成后可删除临时部分索引：

DROP INDEX idx_foo_partitioning_check;

方法2：附加分区时直接跳过验证（PostgreSQL 11+） #

如果已确认原表数据完全符合分区约束，可在附加分区时使用NO VALIDATION选项，直接跳过数据扫描：
假设新分区表为foo_partitioned，执行以下命令：

ALTER TABLE foo_partitioned ATTACH PARTITION foo NO VALIDATION;

此操作瞬间完成，且因NOT VALID约束已阻止违规数据写入，不会出现数据一致性问题。

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