向Postgres表添加一个空列是否会导致锁定？

Question

我记得在某处读到过，在postgres数据库运行 ALTER TABLE foo ADD COLUMN baz text 不会造成读写锁。设置默认值会导致锁定，但允许使用空默认值可以避免锁定。

然而我在文档中找不到相关说明。有没有人能指出一个明确说明这是真实情况的地方？

- jpadvo

Answer 1

在表级锁文档中提到了不同类型的锁以及它们的用途。例如，Postgres 11的 ALTER TABLE 可以获取 SHARE UPDATE EXCLUSIVE 、 SHARE ROW EXCLUSIVE 或 ACCESS EXCLUSIVE 锁。

Postgres 9.1到9.3声称支持上述三种锁中的两种，但实际上对所有此命令的变体都强制执行了 Access Exclusive 锁。这个限制在Postgres 9.4中被取消，但是由于设计原因 ADD COLUMN 仍然是 ACCESS EXCLUSIVE。

通过源代码很容易检查，因为有一个函数专门用于建立在各种情况下需要的锁级别：在src/backend/commands/tablecmds.c中的AlterTableGetLockLevel。

---

关于锁所持续的时间，一旦获取:

• 当列的默认值为NULL时，添加该列应该非常快，因为它不需要重写表：它只是在目录中进行更新。
• 当列具有非NULL默认值时，这取决于PostgreSQL版本：对于11或更高版本，没有立即重写所有行，因此它应该与NULL情况一样快。但是对于10或更旧版本，整个表将被重写，因此根据表的大小，它可能会相当昂贵。

- Daniel Vérité

---

太棒了 - 我从来没有想过去查看源代码，即使我知道该去哪里找，也不会知道如何查找。令人惊讶的易读性。我正在浏览它，看看是否能找到证据表明它只是为添加可空列而进行的短暂锁定，但我不太确定。也许在ATExecAddColumn函数中有一些线索？https://github.com/postgres/postgres/blob/master/src/backend/commands/tablecmds.c#L4383 - jpadvo

Answer 2

添加新的空列将只会在很短时间内锁定表格，因为不需要重写磁盘上的所有数据。而添加带有默认值的列需要 PostgreSQL 制作所有行的新版本并将它们存储到磁盘上。在此期间，表格将被锁定。

因此，当您需要向大型表中添加带有默认值的列时，建议先添加 null 值，然后分批更新所有行。这样可以避免对硬盘的高负载，并允许自动清理执行其工作，以免使表格大小翻倍。

- alexius

Answer 3

http://www.postgresql.org/docs/current/static/sql-altertable.html#AEN57290

“添加一个非空默认值的列或更改现有列的类型将需要重写整个表和索引。”

因此，文档只指定了何时不会重写表格。总会有一个锁定，但如果不需要重写表，则锁定时间很短。

- Leo

---

警告：在9.4及以上版本中，添加一个带有DEFAULT子句的列或更改现有列的类型将需要重写整个表及其索引。 - Michail Nikolaev