运行ALTER TABLE…ADD列可能会产生副作用，可能会在生产中造成严重问题。更改数据结构是一个重要的问题，而且经常发生，因此了解实际情况非常重要。

让我们深入了解如何运行ALTER TABLE…ADD列，以避免由于锁定或大量I/O而影响操作。

开始使用ALTER TABLE…ADD COLUMN

下面的语句创建了一个简单的表，该表将用作我们计划运行的操作的测试虚拟表：

test=# CREATE TABLE t_sample AS
SELECT a1::int
FROM generate_series(1, 100000000) AS a1;
SELECT 100000000
Time: 56272,917 ms (00:56,273)

初始表的大小约为3.4 GB，如下表所示：

test=# \d+
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+----------+-------+-------+-------------+---------------+---------+-------------
public | t_sample | table | hs | permanent | heap | 3458 MB |
(1 row)

到目前为止，这是我们所期望的。但是如果添加了列呢？它如何影响数据库？

在PostgreSQL中向表中添加列

我们必须在这里解决各种情况。最简单的方法是添加没有任何默认值的列：

test=# ALTER TABLE t_sample ADD COLUMN a2 int;
ALTER TABLE
Time: 11,472 ms

这里重要的一点是：这个操作非常快，因为它所做的只是在系统目录上运行一些魔术。我们确实需要一个完整的表锁，但这是一个非常短的锁，因为PostgreSQL实际上并不将此列写入磁盘。系统列知道磁盘上应该还有一列–因为没有任何列，所以引擎可以安全地假设该值确实为空。

test=# ALTER TABLE t_sample ADD COLUMN a3 int DEFAULT 10;
ALTER TABLE
Time: 3,339 ms

同样，该操作非常快速，不会影响我们所需的存储量，因为此附加列首先不会将其写入磁盘：

test=# \d+
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+----------+-------+-------+-------------+---------------+---------+-------------
public | t_sample | table | hs | permanent | heap | 3458 MB |
(1 row)

引擎知道应该有更多的列。但是，如果在一行中找不到任何内容，PostgreSQL将知道该值应该是什么。在这种情况下，它可以预期值为10。同样，不需要持久化该条目。

但是，如果要添加到列中的值不再是常量，则情况开始发生变化。

ALTER TABLE和random（）

test=# ALTER TABLE t_sample
ADD COLUMN a4 int DEFAULT random()*1000;
ALTER TABLE
Time: 65233,954 ms (01:05,234)

random（）不产生确定性值。但是，表的内容应该是静态的——不能动态更改。因此，我们必须将数据写入磁盘。请注意，操作需要一分钟以上。虽然这并不多，但也意味着表暂时被锁定。让数百个或可能数千个连接等待此操作可能会严重损害可用性，并导致从达到连接限制到超时的各种问题。因此，必须避免大量的表锁定。

由于必须将附加列写入磁盘，表的大小已增长到4 GB以上。

test=# \d+
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+----------+-------+-------+-------------+---------------+---------+-------------
public | t_sample | table | hs | permanent | heap | 4223 MB |
(1 row)

请注意，这里的大小不是主要问题，锁定通常是主要问题。

ALTER TABLE … DROP COLUMN …

一旦添加了列，我们还需要查看如果要删除它们会发生什么。与addcolumn一样，dropcolumn命令尽量避免重写表。因此，DROP COLUMN只需将该列标记为已删除：

test=# ALTER TABLE t_sample DROP COLUMN a3;
ALTER TABLE
Time: 8,148 ms
test=# \d+
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+----------+-------+-------+-------------+---------------+---------+-------------
public | t_sample | table | hs | permanent | heap | 4223 MB |
(1 row)

表的大小不变，因为物理列仍然存在。然而，我们看不到它，空间将在稍后被回收。ALTER TABLE…DROP COLUMN…因此（通常）不会导致重大的锁定问题，除非由于极高的并发性，您很难获得短锁。

如果您想了解更多关于PostgreSQL的信息，请参阅Michał关于数据规范化实用示例的文章。

查看我们的CYBERTEC Migrator，从Oracle迁移到PostgreSQL：了解CYBERTEC Migrater

作者相关

Hans-Jürgen Schönig

原文标题：POSTGRESQL: ALTER TABLE … ADD COLUMN … DONE RIGHT
原文作者：Hans-Jürgen Schönig
原文链接：https://www.cybertec-postgresql.com/en/postgresql-alter-table-add-column-done-right/