PostgreSQL ADD COLUMN DEFAULT NULL 锁定和性能。

Question

PostgreSQL ADD COLUMN DEFAULT NULL 锁定和性能。

38

我在我的PostgreSQL 9.6数据库中有一张有300万行的表。这个表已经有了一个空位图(它有另外两个DEFAULT NULL字段)。我想给这个表添加一个新的布尔可空列。我困扰于这两个语句之间的区别：

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

我认为这些语句没有区别，但是：

我在文档中找不到任何证据。文档告诉我们为新列提供DEFAULT值将使PostgreSQL重写所有元组，但我不认为对于这种情况是真的，因为默认值是NULL。
我在这个表的副本上运行了一些测试，第一个语句（没有DEFAULT NULL）比第二个语句花费了更多的时间。我不明白为什么。

我的问题是：

PostgreSQL会为这两个语句使用相同的锁类型（ACCESS EXCLUSIVE）吗？
如果我使用DEFAULT NULL，PostgreSQL会重写所有元组以添加NULL值吗？
这两个语句之间有什么区别吗？

- Ivan Kalita

https://dba.stackexchange.com/questions/60965/does-postgresql-optimize-adding-columns-with-non-null-defaults - gavenkoa

2个回答

18

https://www.postgresql.org/docs/current/static/sql-altertable.html

是的-相同的ACCESS EXCLUSIVE，没有默认值为NULL或没有默认值（统计信息，“选项”，约束条件，集群可能需要更少的限制，但不添加列）。

请注意，每个子表单所需的锁级别可能不同。除非明确说明，否则将持有ACCESS EXCLUSIVE锁定。当列出多个子命令时，将保留任何子命令所需的最严格的锁。

否-在选择结果中将附加NULL。

使用ADD COLUMN添加列时，表中的所有现有行都使用该列的默认值进行初始化（如果未指定DEFAULT子句，则为NULL）。如果没有DEFAULT子句，则这只是元数据更改，并不需要立即更新表的数据；添加的NULL值在读取时提供。

否-据我所知没有区别。只是元数据更改（我认为这是用不同语义表示的一种情况）

编辑-演示：

db=# create table so(i int);
CREATE TABLE
Time: 9.498 ms
db=# insert into so select generate_series(1,10*1000*1000);
INSERT 0 10000000
Time: 13899.190 ms
db=# alter table so add column nd BOOLEAN;
ALTER TABLE
Time: 1025.178 ms
db=# alter table so add column dn BOOLEAN default null;
ALTER TABLE
Time: 13.849 ms
db=# alter table so add column dnn BOOLEAN default true;
ALTER TABLE
Time: 14988.450 ms
db=# select version();
                                                    version
----------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.6.1 on x86_64-apple-darwin15.6.0, compiled by Apple LLVM version 8.0.0 (clang-800.0.42.1), 64-bit
(1 row)

最后为了避免猜测，这是数据类型特定的:

db=# alter table so add column t text;
ALTER TABLE
Time: 25.831 ms
db=# alter table so add column tn text default null;
ALTER TABLE
Time: 13.798 ms
db=# alter table so add column tnn text default 'null';
ALTER TABLE
Time: 15440.318 ms

- Vao Tsun

1

正如其他答案中所评论的那样，从PG 11开始就有很大的差异。至于您的测量数据，我已经在PG 10上执行了多次相同的操作，'default null'版本有时需要很长时间，有时则非常快... - Yann Vo

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- leo begher · Accepted Answer

在Postgres 10或更早版本（如OP的9.6）中，任何DEFAULT子句，即使是DEFAULT NULL，都需要对表进行完全重写，对于大型表来说可能需要很长时间^[1]。

根据文档的说明：

当使用ADD COLUMN添加列时，表中的所有现有行都会使用列的默认值进行初始化（如果未指定DEFAULT子句，则为NULL）。如果没有DEFAULT子句，这只是一个元数据更改，不需要立即更新表的数据；添加的NULL值在读取时提供。

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

我亲自在Postgresql 9.6上进行了测试，当我需要在一个有3亿多行数据的表中添加一列时。没有使用DEFAULT NULL时，耗时11毫秒，而使用DEFAULT NULL时，耗时超过30分钟。

这在Postgres 11中已经改变了，Vao Tsun的回答只适用于这些情况，根据Postgres 11的文档：

当使用ADD COLUMN添加列并指定一个非易失性的DEFAULT时，该默认值在语句执行时进行计算，并将结果存储在表的元数据中。该值将用于所有现有行的该列。如果未指定DEFAULT，则使用NULL。在任何情况下，都不需要对表进行重写。

因此，只要DEFAULT是一个静态值或非易失性表达式，就不需要重写表。

[1]: 无论300万行数据是否庞大都是有争议的。