PostgreSQL在线DDL

PostgreSQL在线DDL

目录

PostgreSQL 的“在线 DDL”并没有像 MySQL 5.6 之后那样“一键 ONLINE”的语法，而是“有的语句天然不阻塞业务，有的语句需要借助技巧或第三方工具才能做到零停机”。下面把生产环境常用的做法一次性梳理给你，按“无需工具 → 需要工具 → 无法在线只能停机”三个层次归纳：

1️.无需第三方工具即可“在线”执行的 DDL

（对业务读写几乎无阻塞）

操作 语法示例 注意事项
添加可空字段 ALTER TABLE t ADD COLUMN c1 int; 只要不给默认值、不设 NOT NULL，就只需在系统目录加一条记录，瞬间完成。
添加默认值且可空 ALTER TABLE t ADD COLUMN c2 int DEFAULT 0; 9.4+ 版本已优化：会先把默认值放到系统目录，不立即改每一行，查询时动态填充，因此同样秒级完成。
删除字段 ALTER TABLE t DROP COLUMN c1; 仅标记“已删除”，不会立即清理数据，不会长时间锁表。
创建/删除普通索引 CREATE INDEX CONCURRENTLY idx ON t(c1);
DROP INDEX CONCURRENTLY idx;		 必须带 CONCURRENTLY 关键字，否则会锁表；不能放在显式事务里。
创建/删除 UNIQUE / PK 索引 CREATE UNIQUE INDEX CONCURRENTLY idx ON t(c1); 同上，但需保证无重复值。
重命名表 / 列 / 索引 ALTER TABLE t RENAME TO new_t; 只改元数据，瞬间完成。

2.需要借助技巧或第三方工具才能“在线”执行的 DDL

（业务可读写，但过程较长，需要 DBA 干预）

操作 推荐做法 关键点
改列数据类型
（int → bigint、varchar(32)→varchar(64) 等）		 1. 加影子列
2. 双写触发器
3. 分批回填数据
4. 切换主键/索引
5. 删除旧列		 阿里云、GitHub、Instagram 都用过该套路；可写成脚本自动化。
添加带 NOT NULL 的列 先加可空列 → 填数据 → 再 ALTER SET NOT NULL；或参考上一条影子列方案。 直接 ADD COLUMN ... NOT NULL DEFAULT xxx 会重写全表并长时间锁表。
变更主键 影子列 + CREATE UNIQUE INDEX CONCURRENTLY + 交换主键。 切换时需要在极短窗口拿 ACCESS EXCLUSIVE 锁，业务几乎无感知。
分区 / 合并分区 PostgreSQL 11+ 的 DETACH/ATTACH PARTITION 已大幅降低锁时间，但仍需要短暂锁；超大表可结合逻辑复制或触发器影子表方案。
一键式工具 • pg-osc（Ruby 版，类似 gh-ost）
• pg_repack（C 版，支持重建表、索引）
• pt-osc 思路的 postgres_online_schema_change		 工具内部做“影子表 + 触发器 + 交换”流程，官方文档会给出磁盘空间、主键、外键等限制。

3.目前仍无法在线完成，必须停机或接受长锁的 DDL

（需要业务低峰期或维护窗口）

操作 原因
减小列长度（varchar(64)→varchar(32)） 需要检查已有数据是否超长，必须重写表。
修改列顺序 系统列位置固定，只能通过重写表实现。
删除被外键引用的列 需同时更新引用端，锁级联。
改列为 NOT NULL 且无默认值 需要全表扫描验证 NULL，锁时间长。

实战小结（DBA 常用 checklist）

  • 先跑 \d+ 表名 或 pg_stat_activity 确认当前无长事务。
  • 能用 CONCURRENTLY 就用；不能用的先评估数据量。
  • 超 1 亿行的大表改结构，优先考虑 pg-osc 或 影子列 + 触发器 方案。
  • 任何 DDL 前先在测试库跑一遍，记录耗时、锁等待、磁盘空间。
  • 预留至少 1.5 倍表大小的磁盘空间做影子表 / 临时索引。

一句话总结：

PostgreSQL 的“在线 DDL”没有统一开关，而是“语法特性 + 手工影子表 + 第三方工具”的组合拳；日常 80% 需求可以用 ADD COLUMN 可空、CREATE INDEX CONCURRENTLY 解决，剩余 20% 用 pg-osc 或 影子列脚本 基本都能做到零停机。

posted @ 2026-05-18 13:50  数据库小白（专注）  阅读(26)  评论(0)    收藏  举报