大表字段变更策略

在 1000 万行的表上执行 ALTER TABLE ADD COLUMN NOT NULL 会锁表几分钟，导致全站服务中断。生产环境的 Schema 变更必须用"展开-迁移-切换"三步走策略，实现真正的零停机变更。

为什么 ALTER TABLE 危险

-- ❌ 危险操作（在大表上会锁表很长时间）
ALTER TABLE orders ADD COLUMN notes TEXT NOT NULL DEFAULT '';
-- PostgreSQL 12 之前：重写整张表（表越大越慢，全程锁表）
-- PostgreSQL 12+：ADD COLUMN with DEFAULT 变快了，但 NOT NULL + 无默认值仍需重写
ALTER TABLE orders ALTER COLUMN total_amount TYPE BIGINT;
-- 需要重写整张表（因为数据类型改变）
-- 1000 万行 ≈ 几十秒到几分钟锁表
ALTER TABLE orders DROP COLUMN legacy_field;
-- PostgreSQL 只是标记列为删除（不实际重写），相对安全
-- 但会获取 ACCESS EXCLUSIVE 锁（阻塞所有查询，通常很短暂）

原则一：添加 NULL 列（最安全）

-- ✅ 安全：添加允许 NULL 的列（PostgreSQL 会立即完成，不重写表）
ALTER TABLE orders ADD COLUMN notes TEXT;
-- 注意：PostgreSQL 12 之前，即使有 DEFAULT 也会重写表
-- PostgreSQL 12+：ADD COLUMN with constant DEFAULT 不重写表
-- ✅ 安全：PostgreSQL 12+ 添加有默认值的列
ALTER TABLE orders ADD COLUMN source TEXT DEFAULT 'web';
-- PostgreSQL 12+ 把默认值存在 pg_attrdef，不重写表行
-- 查询时动态计算默认值，直到行被 UPDATE 后才实际写入
-- ❌ 仍然危险（即使是 PostgreSQL 12+）：
ALTER TABLE orders ADD COLUMN seq INTEGER NOT NULL;
-- NOT NULL 无默认值：需要验证所有现有行，可能锁表

原则二：展开-迁移-切换（三步策略）

场景：给大表添加 NOT NULL 列

-- 目标：给 orders 表添加 payment_method TEXT NOT NULL
-- 步骤 1：添加允许 NULL 的列（快，不锁表）
ALTER TABLE orders ADD COLUMN payment_method TEXT;
-- 立即完成
-- 步骤 2：批量回填历史数据（分批，避免长时间锁）
-- 见下方"批量更新"策略
-- 步骤 3：添加 NOT NULL 约束（使用 NOT VALID）
ALTER TABLE orders
ADD CONSTRAINT orders_payment_method_not_null
CHECK (payment_method IS NOT NULL) NOT VALID;
-- NOT VALID：跳过现有行验证，只对新插入的行生效
-- 步骤 4：验证约束（后台运行，不锁表读/写）
ALTER TABLE orders
VALIDATE CONSTRAINT orders_payment_method_not_null;
-- VALIDATE 只需要 SHARE UPDATE EXCLUSIVE 锁（允许并发读写）
-- 步骤 5（可选）：迁移完成后转为真正的 NOT NULL
-- ALTER TABLE orders ALTER COLUMN payment_method SET NOT NULL;
-- 这一步需要扫描所有行，仍有锁风险，通常跳过（CHECK 约束已等效）

批量更新历史数据

-- ❌ 危险：一次性更新所有行（长时间持有锁，写入大量 WAL）
UPDATE orders SET payment_method = 'unknown' WHERE payment_method IS NULL;
-- ✅ 安全：分批更新（每批小事务，避免长时间锁）
DO $$
DECLARE
batch_size INTEGER := 5000;
updated_count INTEGER;
total_updated INTEGER := 0;
BEGIN
LOOP
UPDATE orders
SET payment_method = 'unknown'
WHERE id IN (
SELECT id FROM orders
WHERE payment_method IS NULL
LIMIT batch_size
);
GET DIAGNOSTICS updated_count = ROW_COUNT;
total_updated := total_updated + updated_count;
EXIT WHEN updated_count = 0;  -- 没有更多行需要更新
RAISE NOTICE '已更新 % 行，总计 %', updated_count, total_updated;
-- 小暂停，让其他查询有机会执行（可选）
PERFORM pg_sleep(0.1);
END LOOP;
RAISE NOTICE '批量更新完成，共更新 % 行', total_updated;
END;
$$;

场景：修改列类型

-- 目标：将 orders.user_id 从 INTEGER 改为 BIGINT
-- （INTEGER 上限约 21 亿，user_id 快满了）
-- ❌ 危险：直接 ALTER COLUMN TYPE（重写整张表）
ALTER TABLE orders ALTER COLUMN user_id TYPE BIGINT;
-- ✅ 安全的零停机迁移方案：
-- 步骤 1：添加新列
ALTER TABLE orders ADD COLUMN user_id_new BIGINT;
-- 步骤 2：创建触发器，同步新数据
CREATE OR REPLACE FUNCTION sync_user_id_new()
RETURNS TRIGGER AS $$
BEGIN
NEW.user_id_new := NEW.user_id;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER sync_user_id_trigger
BEFORE INSERT OR UPDATE ON orders
FOR EACH ROW EXECUTE FUNCTION sync_user_id_new();
-- 步骤 3：回填历史数据（分批）
-- UPDATE orders SET user_id_new = user_id WHERE user_id_new IS NULL
-- （见上方批量更新脚本）
-- 步骤 4：验证数据完整性
SELECT COUNT(*) FROM orders WHERE user_id != user_id_new::integer;
-- 应返回 0
-- 步骤 5：在一个快速事务中完成切换（锁时间极短）
BEGIN;
-- 删除触发器
DROP TRIGGER sync_user_id_trigger ON orders;
-- 添加 NOT NULL 约束
ALTER TABLE orders ALTER COLUMN user_id_new SET NOT NULL;
-- 重命名列
ALTER TABLE orders RENAME COLUMN user_id TO user_id_old;
ALTER TABLE orders RENAME COLUMN user_id_new TO user_id;
COMMIT;
-- 步骤 6：验证无误后删除旧列
-- ALTER TABLE orders DROP COLUMN user_id_old;

场景：添加外键约束

-- ❌ 危险：直接 ADD FOREIGN KEY（会扫描全表验证，锁时间长）
ALTER TABLE orders ADD CONSTRAINT fk_orders_users
FOREIGN KEY (user_id) REFERENCES users(id);
-- ✅ 安全：NOT VALID + VALIDATE（两步解锁验证）
-- 步骤 1：添加约束（只锁一瞬间，跳过现有行验证）
ALTER TABLE orders
ADD CONSTRAINT fk_orders_users
FOREIGN KEY (user_id) REFERENCES users(id)
NOT VALID;
-- 步骤 2：后台验证（允许并发读写）
ALTER TABLE orders VALIDATE CONSTRAINT fk_orders_users;

不锁表的索引创建

-- ❌ 阻塞所有写入
CREATE INDEX idx_orders_status ON orders (status);
-- ✅ 不阻塞写入（允许读写并发，建索引时间更长）
CREATE INDEX CONCURRENTLY idx_orders_status ON orders (status);
-- 注意：CONCURRENTLY 不能在事务块中使用
-- 如果中断，会留下 INVALID 索引，需要手动清理：
-- DROP INDEX CONCURRENTLY idx_orders_status;
-- 清理无效索引
SELECT indexname FROM pg_indexes
WHERE schemaname = 'public'
AND NOT EXISTS (
SELECT 1 FROM pg_class
WHERE relname = indexname AND relkind = 'i'
AND NOT pg_index.indisvalid
FROM pg_index WHERE indexrelid = pg_class.oid
);
-- 更直接的方式：
SELECT indexrelid::regclass AS index_name, indisvalid
FROM pg_index
WHERE NOT indisvalid;

Schema 变更检查清单

在执行任何 ALTER TABLE 前，回答以下问题：
1. 表的当前行数是多少？
SELECT COUNT(*) FROM table_name;  -- > 100 万行需要特别谨慎
2. 这个操作会重写表吗？
- ADD COLUMN WITH DEFAULT（PG 12+：不重写）
- ADD COLUMN NOT NULL（无默认值：重写）
- ALTER COLUMN TYPE（重写）
- DROP COLUMN（不重写，但获取 ACCESS EXCLUSIVE）
3. 可以在低峰期执行吗？
如果必须锁表，选择凌晨低流量时段
4. 是否有回滚计划？
- 保留旧列备份
- 准备回滚 SQL
5. 是否通知了相关团队？
变更窗口、预计影响时长

下一节：CREATE INDEX CONCURRENTLY 实战——索引是最常见的生产变更。CONCURRENTLY 关键字让你在不中断业务的情况下建立和删除索引，但有一些陷阱需要注意。