1.永远不要添加带默认值的列
从 PostgreSQL 11 开始,添加带默认值的列不再重写表。本博文中的其他提示仍然有效!
PostgreSQL 的黄金法则是:在生产中向表添加列时,千万不要指定默认值。
添加列会对表加上锁,从而阻塞读写。如果添加的列有默认值,PostgreSQL 会重写整个表,为每一行填写默认值,这对大型表来说可能需要数小时。在此期间,所有查询都会阻塞,数据库将不可用。
不要这样做:
改用这个方法:
或者更好的办法是,通过小批量更新来避免长时间阻塞更新和删除,例如:
这样,您就可以在尽量不影响用户的情况下添加和填充新列。
译者按,在 Bytebase 中对应的是这条 SQL 审核规则:
图片
2.当心锁队列,使用 lock timeouts (锁超时)
在 PostgreSQL 中,每个锁都有一个队列。如果事务 B 试图获取一个已经被事务 A 持有的有冲突的锁,那么事务 B 将会在锁队列中等待。现在有趣的是:如果另一个事务 C 加入,它不仅需要检查与事务 A 的冲突,还需要检查与事务 B 以及锁队列中其他所有事务的冲突。
这意味着即使你的 DDL 命令可以非常快速地运行,它也可能在队列中等待很长时间,因为需要等待其他查询完成,并且在它之后启动的查询将会被它阻塞。
如果表上可能长时间运行 SELECT 查询,就不要这样做:
而应该这样做:
通过设置 lock_timeout 参数,如果 DDL 命令因为等待锁而阻塞查询超过 2 秒,该命令将会失败。这样做的缺点是 ALTER TABLE 可能不会成功,但可以稍后再试。在开始 DDL 命令之前,建议先查询 pg_stat_activity,查看是否有长时间运行的查询。
3.CONCURRENTLY (并行地)创建索引
PostgreSQL 的另一条黄金法则是:始终并行地创建索引。
在大型数据集上创建索引可能需要数小时甚至数天,而常规的 CREATE INDEX 命令会在命令执行期间阻止所有写入操作。虽然不会阻塞 SELECT,但这仍然很糟糕,而且还有更好的方法:CREATE INDEX CONCURRENTLY。
不要这样做:
而应这样做:
并发地创建索引有一个缺点。如果出了问题,它不会回滚,而是留下一个未完成(invalid)的索引。如果出现这种情况,不用担心,只需运行 DROP INDEX CONCURRENTLY items_value_idx,然后再尝试创建一次即可。
译者按,在 Bytebase 中对应的是这条 SQL 审核规则:
图片
4.尽可能晚地获取高级别的锁
例如,如果要完全替换表的内容。不要这样做:
取而代之的是,将数据加载到新表中,然后替换旧表:
有一个问题,我们没有从一开始就阻止写入,因此当我们删除旧的 items
表时,它可能已经发生了变化。为了防止出现这种情况,我们可以显式锁表,阻止写入,但不阻止读取:
有时,自己动手加锁更为保险。
5.添加主键并尽量减少加锁
在表中添加主键通常是个好主意。例如,当你想使用逻辑复制迁移数据库时。
Postgres 让使用 ALTER TABLE 创建主键变得非常简单,但在为主键建立索引的过程中(如果表很大,可能需要很长时间),所有查询都会被阻塞。
幸运的是,你可以先使用 CREATE UNIQUE INDEX CONCURRENTLY 完成所有繁重的工作,然后使用唯一索引作为主键,这是一种快速操作。
通过将创建主键分解为两个步骤,几乎不会对用户造成影响。
6.永远不要使用 VACUUM FULL
Postgres 的用户体验有时会让人吃惊。虽然 VACUUM FULL 听起来像是清除数据库灰尘的命令,但更合适的命令应该是:请冻结我的数据库数小时。
虽然你应该调整 AUTO VACUUM 设置并使用索引来加快查询速度,但你可能有时需要运行 VACUUM,而不是 VACUUM FULL。
7.通过重排指令避免死锁
如果你已经使用 PostgreSQL 有一段时间了,你很可能见过类似的错误:
当并发事务以不同顺序获取相同锁时,就会出现这种情况。例如,一个事务发出以下命令。
与此同时,另一个事务可能会发出相同的命令,但顺序不同。
如果这些事务同时运行,它们很可能会互相卡住,永远无法完成。Postgres 会在一秒钟左右后识别出这种情况,并取消其中一个事务,让另一个事务完成。出现这种情况时,你应该检查一下自己的应用程序,看看能否让事务始终按照相同的顺序进行。如果两个事务都先修改 hello,再修改 world,那么第一个事务就会在抢到其他锁之前阻塞第二个事务的 hello 锁。