PostgreSQL中ALTER TABLE添加列耗时过长问题求助

你遇到的这个情况确实挺头疼——明明终止了持有锁的进程，ALTER操作还是慢得离谱。咱们一步步来拆解可能的原因和排查方法：

1. 确认是否还有其他隐藏的锁或活跃事务 #

虽然你终止了PID 17977，但可能还有其他进程在访问目标表xxx，持有相关锁导致ALTER无法继续。可以执行以下查询来排查：

-- 查看所有与xxx表相关的活跃进程和锁
SELECT 
    pid,
    usename,
    datname,
    query,
    state,
    wait_event_type,
    wait_event
FROM pg_stat_activity
WHERE relname = 'xxx' AND state <> 'idle';

如果结果里还有其他非空闲的进程（比如长时间运行的SELECT、UPDATE），那这些进程可能持有AccessShareLock，会阻塞ALTER操作（因为ALTER需要ExclusiveLock）。你可以针对性地终止这些进程，或者等它们完成。

另外，也可以再次检查锁的详细情况，确认ALTER进程的等待对象：

-- 查看ALTER进程的锁等待情况
SELECT
    a.pid AS alter_pid,
    a.query AS alter_query,
    l.locktype,
    l.mode,
    l.granted,
    b.pid AS blocking_pid,
    b.query AS blocking_query
FROM pg_stat_activity a
JOIN pg_locks l ON a.pid = l.pid
LEFT JOIN pg_stat_activity b ON l.pid = b.pid
WHERE a.query LIKE '%ALTER TABLE xxx%' AND NOT l.granted;

这个查询能直接告诉你ALTER进程在等哪个锁，以及哪个进程在阻塞它。

2. 检查表的大小和PostgreSQL版本（是否需要重写表） #

PostgreSQL中ALTER TABLE ADD COLUMN的性能取决于几个关键因素：

• PostgreSQL版本：11及以后的版本，添加可空且无默认值的列是元数据操作，几乎瞬间完成；但如果列是非空或者有默认值，仍然需要重写整个表。
• 表的大小：如果表有几千万甚至上亿条数据，重写表的过程会非常耗时，即使没有锁，也会因为IO和CPU的限制变慢。

你可以先检查表的大小：

SELECT pg_size_pretty(pg_total_relation_size('xxx')) AS table_total_size;

如果表很大，那ALTER操作慢可能是正常的重写过程，这时候可以考虑用一些在线DDL工具（比如pg_repack）来减少锁时间，但如果已经在执行了，最好不要强行终止，避免数据损坏。

3. 检查被终止进程的回滚是否完成 #

当你用pg_terminate_backend终止PID 17977后，如果这个进程之前有未提交的大事务，PostgreSQL需要回滚这个事务，这个过程可能会占用大量资源，导致ALTER操作被延迟。

可以通过以下查询查看是否有正在回滚的事务：

SELECT 
    pid,
    usename,
    datname,
    state,
    xact_start,
    query
FROM pg_stat_activity
WHERE state = 'idle in transaction (aborted)';

如果看到相关进程处于这个状态，那需要等回滚完成后，ALTER操作才会继续。

4. 排查系统资源瓶颈 #

即使没有锁和事务问题，系统资源不足也会导致ALTER操作变慢：

• IO瓶颈：用iostat -x 1查看磁盘的读写利用率，如果%util接近100%，说明磁盘IO跟不上。
• CPU瓶颈：用top查看CPU使用率，如果PostgreSQL进程占用了大量CPU，可能是重写表时的计算压力。
• 内存瓶颈：用free -h查看内存使用情况，如果内存不足导致频繁换页，也会拖慢操作。

5. 检查autovacuum进程的影响 #

有时候autovacuum进程会对目标表进行清理操作，尤其是如果表之前有大量的删除或更新，autovacuum可能在持有锁，导致ALTER操作等待。可以查询autovacuum的状态：

SELECT 
    pid,
    usename,
    datname,
    query,
    state
FROM pg_stat_activity
WHERE query LIKE '%autovacuum:%' AND relname = 'xxx';

如果有autovacuum在运行，可以暂时暂停autovacuum（不建议长期关闭，完成后记得开启）：

ALTER TABLE xxx SET (autovacuum_enabled = false);
-- 完成ALTER后再开启
ALTER TABLE xxx SET (autovacuum_enabled = true);

总的来说，先从锁和阻塞进程入手排查，再确认表重写的必要性，最后检查系统资源和后台进程的影响，一步步来应该能找到问题所在。

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