PostgreSQL ADD COLUMN DEFAULT NULL 锁和性能

Question

我的 PostgreSQL 9.6 数据库中有一个包含 300 万行的表。该表已经有一个空位图(它有 2 个其他 DEFAULT NULL 字段)。我想向该表添加一个新的 bool 值可为空的列。我坚持这两个陈述之间的区别:

ALTER TABLE my_table ADD COLUMN my_column BOOLEAN;
ALTER TABLE my_table ADD COLUMN my_column BOOLEAN DEFAULT NULL;

我认为这些说法没有区别，但是:

1. 我在文档中找不到任何证据。文档告诉我们，为新列提供 DEFAULT 值会使 PostgreSQL 重写所有元组，但我认为这种情况并非如此，因为默认值为 NULL .
2. 我对该表的副本进行了一些测试，第一条语句(没有 DEFAULT NULL)比第二条语句花费的时间多一点。我不明白为什么。

我的问题是:

1. PostgreSQL 是否会对这两个语句使用相同的锁类型(ACCESS EXCLUSIVE)？
2. 如果我使用 DEFAULT NULL，PostgreSQL 是否会重写所有元组以向每个元组添加 NULL 值？
3. 这两种说法有什么区别吗？

Answer 1

第 2 点 Vao Tsun 的回复有问题。

如果您使用 ALTER TABLE my_table ADD COLUMN my_column BOOLEAN; 它不会重写所有元组，它只是元数据中的更改。

但是如果你使用 ALTER TABLE my_table ADD COLUMN my_column BOOLEAN DEFAULT NULL，它会重写所有的元组，并且它会在长表上永远存在。

文档本身说明了这一点。

When a column is added with ADD COLUMN, all existing rows in the table are initialized with the column's default value (NULL if no DEFAULT clause is specified). If there is no DEFAULT clause, this is merely a metadata change and does not require any immediate update of the table's data; the added NULL values are supplied on readout, instead.

这告诉我们，如果有一个DEFAULT子句，即使它是NULL，它也会重写所有的元组。

这是由于更新子句的性能问题。如果您需要对未重写的元组进行更新，则需要将元组移动到另一个磁盘空间，耗费更多时间。

我自己在 Postgresql 9.6 上对此进行了测试，当时我不得不在一个有 300 多万个元组的表上添加一列。在没有 DEFAULT NULL 的情况下，它持续了 11 毫秒，而在有 DEFAULT NULL 的情况下，它持续了 30 多分钟。