PostgreSQL执行删除、创建索引等基础操作出现严重延迟超时

你碰到的DELETE慢、建索引慢是同一个问题引发的连锁故障：

• PostgreSQL 删除主表（你的表"A"）记录时，会强制检查所有关联外键表是否存在引用该主键的记录，避免产生无效外键。如果外键字段没有索引，数据库只能对关联表做全表顺序扫描完成校验。
• 表"D"关联"A"的外键字段没有索引，删除"A"单条记录时，数据库需要扫完D表全量数据做存在性判断，这是DELETE操作超时的直接原因。
• 直接给该外键字段建普通索引超时，是因为默认CREATE INDEX会申请表级排他锁，只要表上存在未提交的长事务、慢查询占着锁，建索引操作就会一直等待，拖到会话过期。

1. 清理阻塞锁 #

先执行下面的SQL查询当前实例中的锁等待关系，把持锁超过10分钟、无业务影响的空闲事务、异常慢查询先杀掉，避免后续操作被阻塞：

-- 查询阻塞链路和对应会话信息
SELECT
    blocked.pid AS blocked_pid,
    blocked.query AS blocked_query,
    blocking.pid AS blocking_pid,
    blocking.query AS blocking_query,
    now() - blocking.query_start AS blocking_duration
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked ON blocked.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks 
    ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
    AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
    AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
    AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
    AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
    AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
    AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
    AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
    AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
    AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking ON blocking.pid = blocking_locks.pid
WHERE NOT blocked_locks.GRANTED;

确认查到的阻塞会话无业务影响后，执行SELECT pg_terminate_backend(对应blocking_pid值);终止会话即可。

2. 无锁创建外键索引 #

不要用默认建索引语法，改用CONCURRENTLY模式创建索引，该模式不会申请表级排他锁，不会阻塞表上正常的读写请求，不会出现锁等待超时问题，仅建索引速度稍慢：

-- 注意：该语句不能在事务块内执行，直接在独立会话运行即可
CREATE INDEX CONCURRENTLY idx_d_a_fk ON D(填写D表关联A表的外键字段名);

索引创建完成后，可以通过查询pg_indexes视图或者psql中执行\d D确认索引存在。

3. 验证操作性能 #

索引建完后再执行表"A"的单条DELETE操作，此时数据库会直接走D表的外键索引完成引用校验，不需要全表扫描，10万行量级的表单条删除耗时会稳定在毫秒级。

• PostgreSQL中所有作为外键的字段必须创建索引，否则不仅主表的主键更新、删除操作会触发全表扫描变慢，外键表的关联查询性能也会受影响。
• 生产环境创建索引一律使用CREATE INDEX CONCURRENTLY语法，避免长时间表锁阻塞正常业务。
• 如果补完D表索引后DELETE操作仍有延迟，依次检查B、C两张表的外键字段是否漏建索引，用同样的方式补建即可。

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