Serial列在PostgreSQL中占用了不成比例的空间

Question

我想创建一个自动递增的id列，该列是而不是主键在PostgreSQL表中。这个表目前只有200多行，包含14列。

SELECT pg_size_pretty(pg_total_relation_size('mytable'));

上面的查询显示mytable占用57gb在磁盘上。在检查df -h(Ubuntu 20.04)后，我目前在磁盘上有30 GB的可用空间

我不明白的是为什么，在尝试创建SERIAL列之后，我完全耗尽了磁盘空间-查询最终永远不会结束。我执行以下命令:

ALTER TABLE mytable ADD COLUMN id SERIAL;

，然后看看我的磁盘空间是如何逐渐耗尽的，直到什么都没有剩下，查询失败。我不是数据库专家，但这没有意义。为什么一个简单的序列化列要占用表本身一半以上的空间，特别是当它不是主键并因此没有索引时?有没有已知的方法来创建这样一个自动递增的id列?

Answer 1

作为概念证明:

create table id_test(pk_fld integer primary key generated always as identity);
--FYI, in Postgres 14+ the overriding system value won't be needed.
--That is a hack around a bug in 13-
insert into id_test overriding system value values (default), (default);
select * from id_test;
pk_fld 
--------
1
2
alter table id_test add column id_fld integer ;
update id_test set id_fld = 0;
alter table id_test alter COLUMN id_fld set not null;
alter table id_test alter COLUMN id_fld add generated always as identity;
update id_test set id_fld = default;
select * from id_test;
pk_fld | id_fld 
--------+--------
1 |      1
2 |      2

基本上，这将过程分解为步骤。显然，这只是一个玩具桌，并不能代表你的设置。我会在测试表上进行测试，测试表是实际表的一个子集，看看磁盘空间消耗会发生什么变化。在更新之后使用VACUUM将行返回到数据库是没有坏处的。

Answer 2

添加serial列就是添加integer列，DEFAULT的值是非恒定的。这将导致PostgreSQL重写表，因为新的列值必须添加到所有现有的行中。因此，PostgreSQL写一个新的表副本，并在完成后丢弃旧的表副本。这将暂时需要原表两倍以上的磁盘空间，这就是为什么会耗尽磁盘空间的原因。

您可以将操作分成几个步骤:

ALTER TABLE mytable ADD id bigint;
CREATE SEQUENCE mytable_id_seq OWNED BY mytable.id;
ALTER TABLE mytable ALTER id SET DEFAULT nextval('mytable_id_seq');

这将不会重写表，并且它将保持现有行不变。这些列的id的值将为NULL。

你可能想要将现有的行更新为NOT NULL，但是要小心:如果你一次更新它们，你也会耗尽磁盘空间，因为在PostgreSQL中，UPDATE会将该行的完整新版本写入表中。您必须批量更新行，并在这些更新之间运行VACUUM。

总之，这是相当恼人和复杂的。所以帮你自己一个忙，增加磁盘空间。那是最简单、最好的解决办法。