在表中添加可为空的列需要超过10分钟的时间。

Question

在表中添加可为空的列需要超过10分钟的时间。

postgresqldatabase-designpostgresql-9.1alter-table

11

我在添加一列到表格上遇到了问题。
我尝试运行了几次，但是在运行超过10分钟后，我决定取消查询，因为锁定时间太长。

ALTER TABLE mytable ADD mycolumn VARCHAR(50);

有用信息：

- PostgreSQL版本：9.1 - 行数：约250K - 列数：38 - 可空列数：32 - 约束数：5（1 PK，3 FK，1 UNIQUE） - 索引数：1 - 操作系统类型：Debian Squeeze 64

我发现有趣的信息，关于PostgreSQL如何管理可空列（通过HeapTupleHeader）。

我的第一个猜测是，因为这个表已经有32个可空列，并且每个8位的MAXALIGN，所以HeapTupleHeader的长度为4字节（未经验证，也不知道如何验证）。

因此，添加一个新的可空列可能需要更新每一行的HeapTupleHeader，以添加一个新的8位MAXALIGN，这可能会导致性能问题。

所以我尝试修改一个可空列（实际上并不可空），以便将可空列的数量减少到31，以检查我的猜测是否正确。

ALTER TABLE mytable ALTER myothercolumn SET NOT NULL;

很不幸，这个修改也花了很长时间，超过了5分钟，所以我也中止了它。

你有没有想法是什么原因导致了这个性能损耗？

- Matthieu Verrecchia

1嗯，我可以告诉你其中的一部分：将列类型更改为不兼容的其他类型实际上会创建一个新的列，复制数据，并将旧列设置为已删除。然而，“SET NOT NULL”并不改变类型，它只是添加了一个约束 - 但是这个约束必须对表进行检查，这需要进行全表扫描。9.4版本通过采用较弱的锁来改善其中一些情况，但仍然相当重量级。 - Craig Ringer

1在怀疑它运行缓慢之前，你需要确保ALTER TABLE不仅仅是在等待锁定。如果你已经检查过，请在问题中提及。 - Daniel Vérité

谢谢Craig和Daniel。当我运行alter命令时，它在pg_stat_activity中显示为等待"true"，我猜这意味着它在等待锁！？这是检查的正确方式吗？顺便说一下，在运行这个alter之前，一切都很顺利，但是几秒钟后，锁的数量增加了。 - Matthieu Verrecchia

请尝试在https://wiki.postgresql.org/wiki/Lock_dependency_information上查询，以获得更好的视图。要么您有未提交的事务，要么这个表的活动非常频繁，使其始终处于繁忙状态。 - Daniel Vérité

可能更适合在dba.SE上。 - Erwin Brandstetter

1个回答

- Erwin Brandstetter · Accepted Answer

这里有几个误解：

空位图（null bitmap）不是堆元组头的一部分。根据文档：引用如下：有一个固定大小的头部（在大多数机器上占用23字节），后面是可选的空位图...

你的32个可为空的列并不可疑，原因有两个：

每一行都会添加空位图，仅当该行中至少有一个实际的NULL值时。可空列没有直接影响，只有实际的NULL值才有影响。如果分配了空位图，则总是完全分配（全部或无）。空位图的实际大小为每列1位，向上取整到下一个字节。根据当前源代码：
空位图在堆元组头部之后进行分配，并且在可选的OID之后跟随行数据。OID或行数据的起始位置由头部中的t_hoff指示。根据注释源代码：：

注意t_hoff必须是MAXALIGN的倍数。

堆元组头部之后有一个空闲字节，占用23个字节。因此，最多包含8列的行的空位图实际上不会增加额外成本。对于表中的第9列，t_hoff再次前进MAXALIGN（通常为8）个字节，以提供另外64列。因此，下一个边界将在72列处。

显示PostgreSQL数据库集群的控制信息（包括MAXALIGN），以Debian机器上典型的Postgres 9.3安装为例：

    sudo /usr/lib/postgresql/9.3/bin/pg_controldata /var/lib/postgresql/9.3/main

我更新了你引用的相关答案中的说明。

撇开这些不谈，即使你的ALTER TABLE语句触发了整个表的重写（很可能是因为改变了数据类型），25万行并不算多，在任何一台还过得去的机器上都只需要几秒钟的时间（除非行的大小异常大）。如果需要10分钟或更长时间，那就表示完全不同的问题。很可能是你的语句在等待对表的锁。

pg_stat_activity中日益增加的条目数量意味着有更多的未完成事务——表上的并发访问（很可能）必须等待操作完成。

一些猜测

检查可能存在的表膨胀情况，尝试进行轻度的VACUUM mytable或者更激进的VACUUM FULL mytable——这两种方式可能会遇到相同的并发问题，因为这两种方式都会获取独占锁。你可以尝试使用pg_repack代替...

我会首先检查索引、触发器、外键或其他约束可能存在的问题，特别是涉及该列的问题。特别是可能涉及到损坏的索引？尝试使用REINDEX TABLE mytable;命令或者在ALTER TABLE之后在同一事务中重新添加它们。

尝试在夜间或负载较轻的时候运行该命令。

一种蛮力方法是停止对服务器的访问，然后再次尝试：

Force drop db while others may be connected in postgresql

如果无法确定问题所在，升级到当前版本或特别是即将发布的9.4版本可能有所帮助。针对大型表和锁定细节已经进行了几项改进。但是，如果您的数据库出现了问题，您应该首先找出问题所在。