PostgreSQL 的隐形锁开销：一条查询为何会锁住表上的所有索引

2026-08-18 29 预计阅读时间: 1 分钟
来源: postgr.es AI 摘要 Original link

Disclaimer: This article is an AI-assisted summary. Read it together with the original source when precision matters. The summary may omit context, version differences, or edge cases and is not official documentation.

预计阅读时间：8 分钟

在 PostgreSQL 中，查询是否使用某个索引，与查询是否需要锁住这个索引，并不是一回事。一个容易被忽略的事实是：查询访问一张表时，会为该表的每个索引取得锁，即使最终执行计划完全没有使用其中大部分索引。

这意味着索引不仅消耗磁盘、缓存和写入吞吐，还会占用共享锁表中的条目。对索引数量很多、分区很多或并发事务很长的系统，这种开销可能从“几乎不可见”突然变成容量问题。

执行计划没有使用，不等于没有加锁

假设一张表有 16 个索引，而查询最终只选择顺序扫描。直觉上，这条查询似乎只需要处理表本身；但 PostgreSQL 在规划和执行查询时，需要防止相关关系对象被并发删除或修改，因此会锁住表及其索引。

这里的锁通常不是“阻止其他查询读取”的行锁，而是关系级锁。普通 SELECT 获取的锁可以与大多数日常读写并存，但会与某些 DDL 操作冲突。例如，当一个长事务持续持有这些锁时，试图删除索引或修改表结构的会话可能一直等待。

真正值得注意的是数量：

  • 一张表的索引越多，一次访问需要登记的关系锁越多。
  • 一条语句连接多张宽索引表时，锁条目会继续累积。
  • 分区表会把问题放大，因为查询可能涉及多个分区及其本地索引。
  • 锁通常要到事务结束才释放，因此长事务会延长占用时间。

因此，“这个索引没有出现在 EXPLAIN 中”只能说明执行器没有用它查找数据，不能说明它没有锁成本。

用两个会话观察全部索引锁

可以这样实践。下面的实验适用于测试环境，会创建一张表和 16 个索引。请勿直接在生产数据库中运行。

先在会话 A 中执行：

DROP TABLE IF EXISTS lock_demo;

CREATE TABLE lock_demo (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    tenant_id integer NOT NULL,
    payload text NOT NULL DEFAULT ''
);

DO $$
BEGIN
    FOR i IN 1..16 LOOP
        EXECUTE format(
            'CREATE INDEX lock_demo_payload_%s_idx ON lock_demo ((payload || %L))',
            i,
            i::text
        );
    END LOOP;
END
$$;

INSERT INTO lock_demo (tenant_id, payload)
SELECT n % 10, 'row-' || n
FROM generate_series(1, 1000) AS n;

BEGIN;
SELECT pg_backend_pid() AS session_a_pid;
SELECT count(*) FROM lock_demo;

记下 session_a_pid，并保持事务打开。这个查询很可能采用顺序扫描，可以确认执行计划：

EXPLAIN (ANALYZE, COSTS OFF)
SELECT count(*) FROM lock_demo;

随后在会话 B 中，将下面的 12345 替换为会话 A 的 PID：

SELECT
    c.relname,
    c.relkind,
    l.mode,
    l.granted
FROM pg_locks AS l
JOIN pg_class AS c ON c.oid = l.relation
WHERE l.pid = 12345
  AND c.relname LIKE 'lock_demo%'
ORDER BY c.relkind, c.relname;

结果中不仅会出现 lock_demo，还会出现主键索引和刚创建的 16 个索引。它们没有参与 count(*) 的数据定位，却仍然拥有关系锁。

实验完成后，在会话 A 中释放事务，再清理对象：

ROLLBACK;
DROP TABLE lock_demo;

为什么“16 个索引”会演变成系统容量问题

PostgreSQL 使用共享内存维护锁信息。max_locks_per_transaction 很容易被误解成“每个事务最多只能拿这么多锁”，但它更接近共享锁表的容量规划参数：系统按预期事务数和平均锁对象数准备空间，单个事务可能超过这个平均值，只要共享池仍有空间。

风险通常来自乘法，而不是某一张表：

并发事务数 × 每个事务访问的表/分区数 × 每个关系的索引数

例如，一个批处理事务访问 40 个分区，每个分区有 12 个索引，仅关系对象这一层就可能产生数百个锁。多个这样的事务并发运行时，锁表压力会迅速上升。与此同时，未提交事务还可能阻塞索引维护和表结构变更。

可以用下面的查询寻找当前持有关系锁最多的后端：

SELECT
    l.pid,
    a.usename,
    a.state,
    a.xact_start,
    count(*) AS relation_locks,
    left(a.query, 100) AS current_query
FROM pg_locks AS l
JOIN pg_stat_activity AS a ON a.pid = l.pid
WHERE l.locktype = 'relation'
GROUP BY l.pid, a.usename, a.state, a.xact_start, a.query
ORDER BY relation_locks DESC
LIMIT 20;

还可以盘点用户表的索引数量，优先检查异常值：

SELECT
    schemaname,
    tablename,
    count(*) AS index_count
FROM pg_indexes
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
GROUP BY schemaname, tablename
ORDER BY index_count DESC, schemaname, tablename
LIMIT 30;

这些查询只能提供线索。锁数量高不一定代表故障，大型迁移、分区维护和批处理本来就可能持有许多锁；关键是结合事务时长、DDL 等待和业务并发判断。

索引治理比单纯调大参数更重要

遇到锁容量压力时，直接提高 max_locks_per_transaction 可能暂时缓解问题，但它需要更多共享内存，并且通常要求重启数据库。更重要的是，这个参数无法消除过量索引、过细分区和长事务带来的持续成本。

落地时可以按以下顺序检查：

  1. 统计每张热点表及每个分区的索引数量。
  2. 结合 pg_stat_user_indexes 检查长期很少扫描的索引，但不要仅凭扫描次数直接删除约束索引或低频关键索引。
  3. 检查功能重叠的单列索引、复合索引和表达式索引。
  4. 缩短批处理事务，避免在事务中等待外部 API、人工确认或慢速文件处理。
  5. 在压测环境复现高并发和 DDL 操作，再评估是否调整锁相关配置。

索引从来不是免费的。除了每次写入都要维护它之外，每次查询还可能为它占用一个关系锁。把索引数量视为并发与运维容量的一部分，才能避免“查询根本没用这个索引，为什么它仍然影响系统”这类意外。

# PostgreSQL # 数据库

相关推荐