PostgreSQL 13添加分区时仍触发ACCESS EXCLUSIVE锁与全表扫描问题

云原生阿岚

2026-8-4

PostgreSQL 13 ATTACH PARTITION 仍触发全表扫描并持有ACCESS EXCLUSIVE锁问题

问题详情

我使用PostgreSQL 13，按照官方文档说明，执行ATTACH PARTITION前为待附加表创建匹配分区规则的CHECK约束，应该可以跳过全表扫描，且不会长时间持有ACCESS EXCLUSIVE锁。但实际操作中，创建带CHECK约束的新分区、导入约1亿条符合约束的数据后，执行附加操作时，主分区表tasks仍被锁至无法插入数据，同时触发了全表扫描。当前tasks无默认分区。

分区表定义

CREATE TABLE IF NOT EXISTS tasks
(
    task_time timestamp(6) with time zone not null,
    task_sp_time timestamp(6) with time zone,
    task_org_id text not null,
    build_id text,
    unit_id  text,
    unit_req numeric(12,2),
    ... 30 columns truncated ...,
    constraint tasks_pkey1
        primary key (task_org_id, task_time)
)
partition by RANGE(task_time);

操作流程

创建空分区表tasks_partitions.tasks_20230111；
为该表添加CHECK约束tmp_20230111，限定task_time的范围；
从已分离的旧默认分区导入约1亿条符合约束的数据；
执行ALTER TABLE tasks ATTACH PARTITION tasks_partitions.tasks_20230111 FOR VALUES FROM ('2023-01-11 00:00:00+00') TO ('2023-01-12 00:00:00+00');附加分区。

观测现象

附加操作执行期间，tasks表完全无法处理插入请求。通过以下查询语句，观测到新分区及关系468140上持有ACCESS EXCLUSIVE锁：

SELECT a.datname,
       l.relation::regclass,
       l.transactionid,
       l.mode,
       l.GRANTED,
       l.usename,
       a.query,
       a.query_start,
       age(now(), a.query_start) AS "age",
       a.pid
FROM pg_stat_activity a
JOIN pg_locks l ON l.pid = a.pid
ORDER BY a.query_start;

原因分析与解决办法

核心问题点

CHECK约束与分区范围未严格匹配
PostgreSQL仅在CHECK约束的范围与ATTACH PARTITION指定的分区范围完全一致时，才会跳过全表扫描。例如若CHECK约束定义为task_time >= '2023-01-11'，但分区规则是FROM '2023-01-11' TO '2023-01-12'，数据库会判定可能存在超出分区范围的数据，必须执行全表验证。同时需注意RANGE分区为左闭右开规则，约束的边界条件需与分区规则完全对齐（如使用>=和<而非<=）。
CHECK约束未完成数据验证
若先导入数据再添加CHECK约束，且添加时使用了NOT VALID参数，该约束并未验证现有数据是否符合规则，PostgreSQL在附加时仍会触发全表扫描。即便未使用NOT VALID，若添加约束后修改过数据，也可能导致约束验证状态失效。
ACCESS EXCLUSIVE锁持有时间被拉长
即便满足跳过扫描的条件，ATTACH PARTITION仍会对主分区表持有短时间的ACCESS EXCLUSIVE锁，但一旦触发全表扫描，锁的持有时间会大幅延长，直接阻塞业务写入。

解决步骤

严格对齐CHECK约束与分区范围
若分区规则为FROM '2023-01-11 00:00:00+00' TO '2023-01-12 00:00:00+00'，CHECK约束需按以下方式定义：
```
ALTER TABLE tasks_partitions.tasks_20230111 
ADD CONSTRAINT tmp_20230111 
CHECK (task_time >= '2023-01-11 00:00:00+00' AND task_time < '2023-01-12 00:00:00+00');
```
确保时间的时区、精度、边界条件与分区规则完全一致。
确认CHECK约束已验证所有数据
执行以下查询，查看约束的convalidated状态是否为true：
```
SELECT conname, convalidated 
FROM pg_constraint 
WHERE conrelid = 'tasks_partitions.tasks_20230111'::regclass;
```
若状态为false，需执行ALTER TABLE tasks_partitions.tasks_20230111 VALIDATE CONSTRAINT tmp_20230111;完成验证后，再执行附加操作。
调整操作顺序（可选优化）
若能控制操作流程，建议先创建CHECK约束，再导入数据。数据库会在导入时自动校验每条数据符合约束，后续附加时无需扫表，锁的持有时间也会极短。
排查长事务阻塞
附加操作前，检查主表是否存在长事务（如长时间未提交的插入、更新操作），这类事务会导致ATTACH PARTITION的锁等待，间接拉长锁的持有时间。可通过你提供的锁查询语句排查此类阻塞。

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

PostgreSQL 13添加分区时仍触发ACCESS EXCLUSIVE锁与全表扫描问题

问题详情 #

分区表定义 #

操作流程 #

观测现象 #

原因分析与解决办法 #

核心问题点 #

解决步骤 #