不使用表锁添加新列？

Question

在我的项目中，有一个表格拥有23百万条记录和6个字段被索引了。

之前我尝试为Thinking Sphinx搜索添加增量列，但这会导致整个数据库锁定一个小时。之后，当文件被添加后，我尝试重建索引，但是以下查询导致数据库锁定大约4小时：

"update user_messages set delta = false where delta = true"

为了使服务器正常运行，我从数据库转储中创建了一个新的数据库，并将其提升为数据库，这样服务器就可以启动。

现在我想在没有表锁的情况下向我的表中添加增量列，这是否有可能？并且一旦添加了列delta，为什么会在运行索引重建命令时执行上述查询并阻止服务器运行那么长时间？

附：我使用Postgres和ika db模型在Heroku上运行。

- Gul

Answer 1

Postgres 11或更高版本

自Postgres 11以来，只有易失的默认值需要重写表。 手册：

添加具有易失DEFAULT的列或更改现有列的类型将需要重写整个表及其索引。

加粗是我的强调。 false是不可变的。因此，只需添加带有DEFAULT false的列即可。 超级快速，完成任务：

ALTER TABLE tbl ADD column delta boolean DEFAULT false;

对于Postgres 10或更早版本，或者对于易变的DEFAULT

添加一个没有DEFAULT或DEFAULT NULL的新列通常不会强制重写表格，而且非常便宜。只有在实际写入值时才会创建新行。但是，引用手册：

添加带有DEFAULT子句的列或更改现有列的类型将需要重写整个表及其索引。

在PostgreSQL中，UPDATE会写入一行的新版本。你的问题没有提供所有信息，但那可能意味着要写入数百万个新行。

在进行UPDATE时，如果表的大部分内容都受到影响并且可以独占地锁定表，请在进行大规模UPDATE之前删除所有索引，并在之后重新创建它们。这样更快。手册中相关的建议。

如果您的数据模型和可用磁盘空间允许，CREATE一个新表，并在一个事务中：DROP旧表，然后RENAME新表。相关：

• 在大型表格中填充新列的最佳方法？

在后台创建新表时：一次性对同一行应用所有更改。重复的更新会创建新的行版本，并留下死元组。

如果由于约束条件而无法删除原始表，则另一种快速的方法是构建一个临时表，TRUNCATE原始表并批量INSERT新行 - 如果这有助于性能，则进行排序。所有操作在一个事务中完成。类似这样：

BEGIN

SET temp_buffers = 1000MB;  -- or whatever you can spare temporarily

-- write-lock table here to prevent concurrent writes - if needed
LOCK TABLE tbl IN SHARE MODE;    

CREATE TEMP TABLE tmp AS
SELECT *, false AS delta
FROM   tbl;                -- copy existing rows plus new value
-- ORDER BY ???            -- opportune moment to cluster rows

-- DROP all indexes here

TRUNCATE tbl;              -- empty table - truncate is super fast

ALTER TABLE tbl ADD column delta boolean DEFAULT FALSE; -- NOT NULL?

INSERT INTO tbl
TABLE tmp;                 -- insert back surviving rows.

-- recreate all indexes here

COMMIT;

- Erwin Brandstetter

---

在执行 INSERT SELECT 和 TRUNCATE 之间的时间间隔内插入的行会丢失吗？ - origaminal

@origaminal：在我刚刚更新代码之前，他们可能已经迷失了方向。（与表锁有关的详细问题一直存在链接。）请考虑更新版本。最重要的是，请考虑针对Postgres 11或更高版本添加的新闻。 - Erwin Brandstetter

Answer 2

你可以添加另一张只有一列的表，这样就不会出现长时间锁定的情况。当然，应该再添加一列，作为第一列的外键。

对于索引，你可以使用“CREATE INDEX CONCURRENTLY”，它不会在这个表上使用太重的锁 http://www.postgresql.org/docs/9.1/static/sql-createindex.html。

- Szymon Lipiński