我有一个在生产环境中运行的postgres数据库，其中有几个包含数十亿行的表，我想给每个表添加一列，并给用户表添加一个外键。假设数据已经填充，我想通过CREATE INDEX CONCURRENTLY添加外键约束和索引。但是我不清楚这在生产环境中如何执行，同时有写事务发生，具体来说，索引创建过程是需要等待所有事务结束后才一次性创建索引，还是逐步创建索引？
我担心表的大小是否会导致生产中的长表锁。
我有一个在生产环境中运行的postgres数据库，其中有几个包含数十亿行的表，我想给每个表添加一列，并给用户表添加一个外键。假设数据已经填充，我想通过CREATE INDEX CONCURRENTLY添加外键约束和索引。但是我不清楚这在生产环境中如何执行，同时有写事务发生，具体来说，索引创建过程是需要等待所有事务结束后才一次性创建索引，还是逐步创建索引？
我担心表的大小是否会导致生产中的长表锁。
1条答案
按热度按时间uxh89sit1#
1.向现有表中添加列会导致ACCESS EXCLUSIVE锁。当您不使用新内容更新记录时，以NULL开始，这可以非常快，因为不需要重写表。
1.创建一个外键也需要一个ACCESS EXCLUSIVE锁。这也可以非常快，只需使用NOT VALID来推迟验证。无论如何，验证都是毫无意义的，一切都是NULL。
1.然后，您对表进行更新，一次更新所有记录或更新大量记录。这将需要一段时间，并且您更新的所有记录都将使用ROW EXCLUSIVE锁进行锁定。这意味着没有其他进程可以更新或删除这些记录。阅读当前数据和插入新数据都没有问题。
1.完成所有更新后，可以并发创建索引
1.最后验证外键约束。这需要一个SHARE UPDATE EXCLUSIVE锁。
使用此方法对生产的影响应该是最小的。注意在更新过程中可能会产生额外的磁盘空间，以及之后可能需要运行的真空分析。
您应该在一个测试环境中使用可比较的数据库负载和大小来测试它，这也会给予您一些关于它需要多长时间的估计。