LabHub
学习 学习路径 课程

改表结构 — 别让发版停掉服务

亲眼看迁移把服务停住

在 LabHub 中继续学习

目标

迁移事故并不是因为操作耗时太久，而是因为等待锁时，后续请求排起长队。

本实验会亲手制造这条队列，几秒钟即可复现。

准备

create table big(id bigserial primary key, name text, n int);
insert into big(name,n) select 'row'||i, i from generate_series(1,300000) i;

测量时间

psql -h 127.0.0.1 -U lab -d labdb
\timing on

创建两个会话

psql -h 127.0.0.1 -U lab -d labdb \
  -c "begin; select count(*) from big; select pg_sleep(8);" &
sleep 1
# 여기서 두 번째 세션
wait

步骤

  1. 添加列的耗时 → 01-addcolumn.txt
  2. 锁类型 → 02-lockmode.txt
  3. 排队效应 → 03-queue.txt
  4. lock_timeout → 04-locktimeout.txt
  5. 创建索引阻塞写入 → 05-index.txt
  6. CONCURRENTLY → 06-concurrently.txt
  7. 查找不可用索引 → 07-invalid.md
  8. 总结 → 08-notes.md

参考

第 3 步就是本实验的核心，其余步骤都是规避方法。

添加带默认值的列是否很慢

创建 30 万行的表，添加带默认值的列，并把耗时记录到 01-addcolumn.txt。

create table big(id bigserial primary key, name text, n int);
insert into big(name,n) select 'row'||i, i from generate_series(1,300000) i;

在 psql 中启用 \timing on 即可显示耗时。运行 alter table big add column status text not null default 'new';。

**结果应为毫秒级。**从 PostgreSQL 11 起，默认值只记录在元数据中；“必须重写整张表”的建议已经过时。

它会获取哪种锁

确认 ALTER TABLE 获取的锁类型，保存到 02-lockmode.txt。

在事务中执行 alter，在提交前查询 pg_locks。

begin;
alter table big add column tmp1 int;
select mode from pg_locks where relation='big'::regclass;
rollback;

应看到 AccessExclusiveLock。这是最强的锁，连读取也会阻塞。

后续请求会排队

按长查询 → ALTER TABLE → 简单 SELECT 的顺序执行，把最后一个 SELECT 也被阻塞的证据保存到 03-queue.txt。

这是本实验的核心。

  1. 在后台运行 begin; select count(*) from big; select pg_sleep(8);
  2. 1 秒后在后台运行 alter table big add column q1 int;
  3. 再过 1 秒运行 set lock_timeout='3s'; select count(*) from big;

第 3 个请求与 ALTER 无关，却仍被阻塞，因为锁请求按顺序处理。

服务看起来完全停止，但数据库指标仍然正常，因此定位原因往往耗时很久。

使用 lock_timeout 阻止排队

在相同情况下为 ALTER 设置 lock_timeout，把只有 ALTER 失败、后续请求不再阻塞的证据保存到 04-locktimeout.txt。

set lock_timeout='2s'; alter table ...。两秒后 ALTER 放弃，队列就会解除。

失败的迁移可以重跑，但已经排队的五分钟无法追回。

不要与 statement_timeout 混淆；后者限制执行时间，这里需要限制的是等待时间。

创建索引会阻塞写入

保持写事务开启并尝试 create index，把阻塞结果保存到 05-index.txt。

一个会话运行 begin; update big set n=n where id=1; select pg_sleep(6);，另一个运行 set lock_timeout='2s'; create index idx_n on big(n);。

CREATE INDEX 获取 SHARE 锁，因此**读取可以继续，写入会被阻塞。**大表可能持续数分钟。

使用 CONCURRENTLY 创建

在相同情况下证明 create index concurrently 能够成功，并把它不能在事务中运行的结果一起保存到 06-concurrently.txt。

即使写事务保持开启也能通过。再运行 begin; create index concurrently ...;，会得到：

ERROR: CREATE INDEX CONCURRENTLY cannot run inside a transaction block

如果迁移工具自动包裹事务，就会在这里失败。两种结果都要保留。

失败的索引会留下

编写并执行从 pg_index 查找不可用索引的查询，把查询与结果保存到 07-invalid.md，并说明为何必须检查。

select indexrelid::regclass, indisvalid from pg_index where not indisvalid;

CONCURRENTLY 中途失败时，会留下 indisvalid = false 的索引，必须删除后重建。

不知道这一点，就会长期排查“索引明明创建了为何不用”。当前结果可以为空，目标是熟练掌握查询。

总结三项原则

在 08-notes.md 中至少写三行：迁移事故的真正原因、为何需要 lock_timeout，以及删除列时为何要拆分部署。

正文必须包含 대기、lock_timeout、배포。