解锁 7 大技巧，避免你的 Postgres 挂掉

永远不要添加带默认值的列

从 PostgreSQL 11 开始，添加带默认值的列不再重写表。本博文中的其他提示仍然有效！

PostgreSQL 的黄金法则是：在生产中向表添加列时，千万不要指定默认值。

添加列会对表加上锁，从而阻塞读写。如果添加的列有默认值，PostgreSQL 会重写整个表，为每一行填写默认值，这对大型表来说可能需要数小时。在此期间，所有查询都会阻塞，数据库将不可用。

不要这样做：

-- 阻塞读写一直到完全重写（以小时计）
ALTER TABLE items ADD COLUMN last_update timestamptz DEFAULT now();

改用这个方法：

-- 阻塞查询，更新，插入，删除直到 catalog 被更新 (毫秒计)
ALTER TABLE items ADD COLUMN last_update timestamptz;
-- 查询，插入可以执行，一些更新和删除在重写表时，会被阻塞
UPDATE items SET last_update = now();

或者更好的办法是，通过小批量更新来避免长时间阻塞更新和删除，例如：

do {
  numRowsUpdated = executeUpdate(
    "UPDATE items SET last_update = ? " +
    "WHERE ctid IN (SELECT ctid FROM items WHERE last_update IS NULL LIMIT 5000)",
    now);
} while (numRowsUpdate > 0);

这样，您就可以在尽量不影响用户的情况下添加和填充新列。

译者按

在 Bytebase 中对应的是这条 SQL 审核规则：

当心锁队列，使用 lock timeouts（锁超时）

在 PostgreSQL 中，每个锁都有一个队列。如果事务 B 试图获取一个已经被事务 A 持有的有冲突的锁，那么事务 B 将会在锁队列中等待。现在有趣的是：如果另一个事务 C 加入，它不仅需要检查与事务 A 的冲突，还需要检查与事务 B 以及锁队列中其他所有事务的冲突。

这意味着即使你的 DDL 命令可以非常快速地运行，它也可能在队列中等待很长时间，因为需要等待其他查询完成，并且在它之后启动的查询将会被它阻塞。

如果表上可能长时间运行 SELECT 查询，就不要这样做：

ALTER TABLE items ADD COLUMN last_update timestamptz;

而应该这样做：

SET lock_timeout TO '2s'
ALTER TABLE items ADD COLUMN last_update timestamptz;

通过设置 lock_timeout 参数，如果 DDL 命令因为等待锁而阻塞查询超过 2 秒，该命令将会失败。这样做的缺点是 ALTER TABLE 可能不会成功，但可以稍后再试。在开始 DDL 命令之前，建议先查询 pg_stat_activity，查看是否有长时间运行的查询。

CONCURRENTLY（并行地）创建索引

PostgreSQL 的另一条黄金法则是：始终并行地创建索引。
在大型数据集上创建索引可能需要数小时甚至数天，而常规的 CREATE INDEX 命令会在命令执行期间阻止所有写入操作。虽然不会阻塞 SELECT，但这仍然很糟糕，而且还有更好的方法：CREATE INDEX CONCURRENTLY。
不要这样做：

-- 阻塞所有写
CREATE INDEX items_value_idx ON items USING GIN (value jsonb_path_ops);

而应这样做：

-- 只阻塞其他 DDL 操作
CREATE INDEX CONCURRENTLY items_value_idx ON items USING GIN (value jsonb_path_ops);

并发地创建索引有一个缺点。如果出了问题，它不会回滚，而是留下一个未完成（invalid）的索引。如果出现这种情况，不用担心，只需运行 DROP INDEX CONCURRENTLY items_value_idx，然后再尝试创建一次即可。

译者按

在 Bytebase 中对应的是这条 SQL 审核规则：

尽可能晚地获取高级别的锁

当需要运行命令获取表上高级别的锁时，应尽量在事务的较晚阶段执行，以允许查询尽可能长时间地进行。

例如，如果要完全替换表的内容。不要这样做：

BEGIN;
-- 阻塞读写:
TRUNCATE items;
-- 长时间操作:
\COPY items FROM 'newdata.csv' WITH CSV 
COMMIT;

取而代之的是，将数据加载到新表中，然后替换旧表：

BEGIN;
CREATE TABLE items_new (LIKE items INCLUDING ALL);
-- 长时间操作:
\COPY items_new FROM 'newdata.csv' WITH CSV
-- 阻塞读写:
DROP TABLE items;
ALTER TABLE items_new RENAME TO items;
COMMIT;

有一个问题，我们没有从一开始就阻止写入，因此当我们删除旧的 items 表时，它可能已经发生了变化。为了防止出现这种情况，我们可以显式锁表，阻止写入，但不阻止读取：

BEGIN;
LOCK items IN EXCLUSIVE MODE;
...

有时，自己动手加锁更为保险。

添加主键并尽量减少加锁

在表中添加主键通常是个好主意。例如，当你想使用逻辑复制迁移数据库时。

Postgres 让使用 ALTER TABLE 创建主键变得非常简单，但在为主键建立索引的过程中（如果表很大，可能需要很长时间），所有查询都会被阻塞。

ALTER TABLE items ADD PRIMARY KEY (id); -- 长时间阻塞查询

幸运的是，你可以先使用 CREATE UNIQUE INDEX CONCURRENTLY 完成所有繁重的工作，然后使用唯一索引作为主键，这是一种快速操作。

CREATE UNIQUE INDEX CONCURRENTLY items_pk ON items (id); -- 会很长，但不会阻塞查询
ALTER TABLE items ADD CONSTRAINT items_pk PRIMARY KEY USING INDEX items_pk;  -- 会阻塞查询，但很快

通过将创建主键分解为两个步骤，几乎不会对用户造成影响。

永远不要使用 VACUUM FULL

Postgres 的用户体验有时会让人吃惊。虽然 VACUUM FULL 听起来像是清除数据库灰尘的命令，但更合适的命令应该是：请冻结我的数据库数小时。

PLEASE FREEZE MY DATABASE FOR HOURS;

VACUUM FULL 会将整个表重写到磁盘上，这可能需要数小时或数天的时间，并且在重写过程中会阻止所有查询。虽然 VACUUM FULL 有一些有效的使用情况，例如表以前很大，但现在变小了，仍占用大量空间，但这极可能不是你面临的情况。

虽然你应该调整 AUTO VACUUM 设置并使用索引来加快查询速度，但你可能有时需要运行 VACUUM，而不是 VACUUM FULL。

通过重排指令避免死锁

如果你已经使用 PostgreSQL 有一段时间了，你很可能见过类似的错误：

ERROR:  deadlock detected
DETAIL:  Process 13661 waits for ShareLock on transaction 45942; blocked by process 13483.
Process 13483 waits for ShareLock on transaction 45937; blocked by process 13661.

当并发事务以不同顺序获取相同锁时，就会出现这种情况。例如，一个事务发出以下命令。

BEGIN;
UPDATE items SET counter = counter + 1 WHERE key = 'hello'; -- 在 hello 上加锁
UPDATE items SET counter = counter + 1 WHERE key = 'world'; -- 一边阻塞 hello，一边等着 world
END;

与此同时，另一个事务可能会发出相同的命令，但顺序不同。

BEGIN
UPDATE items SET counter = counter + 1 WHERE key = 'world'; -- 在 world 上加锁
UPDATE items SET counter = counter + 1 WHERE key = 'hello';  -- 一边阻塞 world，一边等着 hello
END;

如果这些事务同时运行，它们很可能会互相卡住，永远无法完成。Postgres 会在一秒钟左右后识别出这种情况，并取消其中一个事务，让另一个事务完成。出现这种情况时，你应该检查一下自己的应用程序，看看能否让事务始终按照相同的顺序进行。如果两个事务都先修改 hello，再修改 world，那么第一个事务就会在抢到其他锁之前阻塞第二个事务的 hello 锁。

希望这些建议对您有所帮助。如果您有什么建议，请随时留言！

译者按

优秀的数据库仍然需要「正确」的使用姿势，但是人工检查总会疏漏。好在 Bytebase 落地了 SQL 审核（并在社区版本中全面开放），将规范强制为约束，自动化检查每一条 SQL，为团队安全使用数据库保驾护航。🛡️🎉

---

💡 更多资讯，请关注 Bytebase 公号：Bytebase