PostgreSQL 迁移
核心原则。 每次迁移都是一对 up/down 文件，down 必须完全撤销 up 的操作。每条迁移在其自身的事务中执行。唯一的例外是：在已有数据且持续写入的表上创建索引 —— 使用 CREATE INDEX CONCURRENTLY 放在单独的无事务迁移中，且每个文件只包含一条命令。
断言检查见 CHECKS.md。
文件格式
migrations/
├── 20250524120000_create_users.up.sql
├── 20250524120000_create_users.down.sql
├── 20250525090000_add_users_phone.up.sql
└── 20250525090000_add_users_phone.down.sql
- 前缀为 UTC 时间
YYYYMMDDHHmmss，名称为 snake_case 的简短描述。 *.up.sql执行变更，*.down.sql回滚变更。两个文件都必须存在。- 已应用的迁移不要修改 —— 编写新的迁移。
类型与约束
| 避免使用 | 推荐写法 | 原因 |
|---|---|---|
bigserial | bigint GENERATED ALWAYS AS IDENTITY | SQL 标准；如果需要插入自定义 id（如导入），使用 BY DEFAULT |
varchar(n) | text NOT NULL CHECK (length(x) <= n) | 长度是数据规则，更容易修改 |
timestamp | timestamptz | 存储时间点，而非“墙上时钟” |
integer 标志位 | boolean | |
json | jsonb | 二进制格式，支持索引和运算符 |
| 来自扩展的 uuid | uuid DEFAULT gen_random_uuid() | PostgreSQL 13 起内置 |
除非是预期长度很长的文本（如描述、评论），否则始终用 CHECK 约束限制文本列的长度。如果最大长度不明确，请询问用户。
| 数据 | 约束 |
|---|---|
| 电子邮件 | CHECK (length(email) <= 320) |
| 姓名 | CHECK (length(name) <= 255) |
| 电话 | CHECK (length(phone) <= 50) |
| slug、代码 | CHECK (length(code) <= 100) |
| URL | CHECK (length(url) <= 2048) |
| 描述 | 无限制，或 CHECK (length(bio) <= 10000) |
表模板：
CREATE TABLE users (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL CHECK (length(name) <= 255),
email text NOT NULL CHECK (length(email) <= 320),
is_active boolean NOT NULL DEFAULT true,
metadata jsonb,
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX ix_users_email ON users (email);
回滚
| up | down |
|---|---|
CREATE TABLE t | DROP TABLE IF EXISTS t |
ALTER TABLE t ADD COLUMN c | ALTER TABLE t DROP COLUMN IF EXISTS c |
CREATE INDEX ix | DROP INDEX IF EXISTS ix |
CREATE INDEX CONCURRENTLY ix | DROP INDEX CONCURRENTLY IF EXISTS ix — 同样无事务 |
CREATE TYPE t AS ENUM (...) | DROP TYPE IF EXISTS t |
ALTER TYPE t ADD VALUE 'v' | PostgreSQL 不支持 DROP VALUE —— 重建类型或保留空 down |
ALTER TABLE t ALTER COLUMN c SET NOT NULL | ALTER TABLE t ALTER COLUMN c DROP NOT NULL |
ALTER TABLE t ADD CONSTRAINT c | ALTER TABLE t DROP CONSTRAINT IF EXISTS c |
down 中使用 IF EXISTS 是为了保证回滚的幂等性。
常见变更
带默认值的列。 使用常量 DEFAULT 时，PostgreSQL 11+ 不会重写整个表，命令几乎是瞬间完成。如果使用 volatile 默认值（如 gen_random_uuid()、clock_timestamp()），表会在排他锁下整体重写 —— 对于大型在线表，先添加无默认值的列，再分批填充。
ALTER TABLE users ADD COLUMN role text NOT NULL DEFAULT 'user' CHECK (length(role) <= 50);
外键 — 立即在引用列上创建索引：PostgreSQL 不会自动创建，没有索引时删除父记录和 join 操作会进行全表扫描。
CREATE TABLE orders (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
user_id bigint NOT NULL REFERENCES users (id),
total numeric NOT NULL
);
CREATE INDEX ix_orders_user_id ON orders (user_id);
枚举值新增。 ALTER TYPE ... ADD VALUE 可以在事务中工作，但新值在提交之前无法使用（错误代码 55P04 unsafe use of new value）。如果迁移器在事务中执行迁移，请将新增值和使用该值（UPDATE、DEFAULT、CHECK）放在不同的迁移中。
ALTER TYPE payment_method ADD VALUE 'bank_transfer';
down：由于没有 DROP VALUE。如果该值无害——保留空的 down 并加注释；否则——重建类型（新建类型、ALTER COLUMN ... TYPE ... USING、删除旧类型）。
在线表上的索引
对于已有数据且仍在写入的表，索引应通过 CREATE INDEX CONCURRENTLY (CIC) 创建。普通 CREATE INDEX 会在构建期间阻塞写入。如果索引与新表在同一迁移中创建，则使用普通 CREATE INDEX。数据库大小不是借口：挂起的事务会在任何大小下破坏新索引（见下文）。
up:
CREATE INDEX CONCURRENTLY ix_orders_customer_id ON orders (customer_id);
down:
DROP INDEX CONCURRENTLY IF EXISTS ix_orders_customer_id;
- 单独的无事务迁移，每个文件一条命令。 在事务块中禁止使用 CIC（“cannot run inside a transaction block”）。单次请求发送多条命令时，PostgreSQL 会将其视为隐式事务，导致 CIC 报相同错误。
- 如果迁移器不支持无事务迁移，不要将 CIC 放在普通迁移中。 告知用户需要这种模式（参见“对迁移器的要求”部分），或者单独执行 CIC 然后手动记录迁移。
- CIC 会等待所有持有旧快照的事务完成，即使是只读取其他表的事务。迁移可能会持续很长时间。
- 应用后验证索引。 CIC 不是事务性的：失败时会留下 INVALID 索引，而迁移本身不会显示这一点。
SELECT c.relname, i.indisvalid, i.indcheckxmin
FROM pg_index i JOIN pg_class c ON c.oid = i.indexrelid
WHERE c.relname = 'ix_orders_customer_id';
indisvalid = false— 执行DROP INDEX CONCURRENTLY IF EXISTS并重新创建，或者使用REINDEX INDEX CONCURRENTLY。- 重试失败的 CIC 迁移。 不使用
IF NOT EXISTS时，在 INVALID 索引上会报“relation already exists”错误。使用IF NOT EXISTS更糟：命令会静默跳过 INVALID 索引，导致迁移“通过”。因此不要在 CIC 中使用IF NOT EXISTS：先移除无效索引，再重试。
索引存在，但计划是 Seq Scan
索引有效，但优化器不使用它——检查 indcheckxmin（使用上面的查询）。true：在索引创建之前启动的事务存活期间，查询不会使用该索引。原因：UPDATE 操作在表中留下了未引用索引的 HOT 版本链。仅 INSERT 不会导致此问题。不要重建索引或查找统计信息问题。查找旧事务：
SELECT pid, xact_start, backend_xmin, state, left(query, 60) AS query
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY xact_start;
等待它们完成或终止（pg_terminate_backend(pid)）——仅在与用户协商后。发布前复现：在一个会话中打开 repeatable read 事务并读取表，在另一个会话中执行 UPDATE 行和 CREATE INDEX；indcheckxmin 将变为 true。
对迁移器的要求
- 每条迁移在其自身的事务中执行，同时记录到已应用迁移日志：失败的迁移完全回滚，已应用的保持不变。
- 防止并行执行（例如 advisory lock）：同时启动的副本不会重复应用相同的迁移。
- 对单独迁移支持无事务模式 —— 在文件中标记（例如，第一行
-- migrate:no-transaction）。此文件作为单条命令发送，绕过事务。代价：应用和日志记录不再是原子操作。如果进程在两者之间崩溃，重试会因“already exists”而失败；检查indisvalid，如果索引有效，手动将迁移记录到日志。 - 无需连接数据库即可生成配对文件。