我需要向一个高流量的大型 PostgreSQL 表(大约 2TB)添加主键。这是一项关键操作,我正在寻找有关如何有效地完成该操作的指导。
我已经尝试过以下步骤:
-- Step 1: Add id identity column
ALTER TABLE users
ADD COLUMN id BIGINT GENERATED ALWAYS as IDENTITY;
-- Step 2: Add unique index on (id, user_id) concurrently
CREATE UNIQUE INDEX CONCURRENTLY users_id_user_id_idx
ON users (id, user_id);
-- verify that step 2 is completed
-- Step 3: Add primary key
ALTER TABLE users
ADD CONSTRAINT users_pkey PRIMARY KEY USING INDEX users_id_user_id_idx;
我面临两个问题:
表完全锁定在“步骤 1”本身上。
我知道这是预料之中的,但如果有任何选择可以避免这种情况,请提出建议。
我收到以下错误,
错误:无法扩展文件“base/16401/90996”:设备上没有剩余空间提示:检查可用磁盘空间。
但600GB
我的服务器上还有剩余的存储空间。
由于表将被锁定在“第 1 步”,并且如果没有选项可以避免这种情况,我可以利用停机时间id
先添加列,然后运行其他两个脚本。
我不知道这是否可以解决存储错误。
请提供任何建议,以便我能够以尽可能少的停机时间添加 PK。
PostgreSQL v14.6
为什么?
您的步骤 1 需要的空间远远超过 600 GB(暂时)。该表大约有 2 TB。
bigint
至少必须有尽可能多的可用空间(减去可能的膨胀,再加上每行新列 8 个字节),因为该更改迫使 Postgres 重写整个表。最大限度地减少阻塞并最大限度地减少总存储需求
相反,请按以下顺序执行:
小提琴
添加一个没有默认值的可为空的列,因此它最初
id
是这样的。null
这样,Postgres 就可以应付微小的元数据更改。没有表重写,没有阻塞。
我会将 PK 列命名为“user_id”,而不喜欢广泛使用、非描述性且高度重复的名称“id”。但保留“id”以与问题保持一致。
手动创建
SEQUENCE
:使列“拥有”序列:
添加列默认值,该列仅在新行中生效。
看:
null
以总大小(或其他大小)的 1% 左右的批量更新预先存在的行(仍包含值)。在单独的事务中,允许自动清理启动并标记死行以供重用。这样,表就不会增长太多,600 GB 就足够了。自从Postgres 12中添加了SQL程序,我们就可以
COMMIT
在匿名代码块中。假设有一个timestamptz
列users.inserted_at
(最好有一个索引!),这样的东西可以工作:或者,在客户端中循环,并
VACUUM users;
在迭代之间运行以确保空间得到重用。(VACUUM
不能在事务内运行。)看:
最终,所有旧行都被更新。
现在创建唯一索引
CONCURRENTLY
,以避免阻塞插入。与您的步骤 2 类似,但仅限于(id)
:我看不出加入
user_id
PK 的充分理由。如果您需要它进行仅索引扫描,请考虑使用INCLUDE (user_id)
. 但这并不总是有益的。看:现在使用唯一索引添加新的 PK,而不阻止插入(您的步骤 3):
这也将隐式设置列
NOT NULL
。最后,使用Peter Eisentraut 的函数
upgrade_serial_to_identity(tbl regclass, col name)
将 转换serial
为IDENTITY
列。作为超级用户:或者坚持
serial
PK,也许就足够了。有关的: