在线模式变更

概念定义

改建表结构曾经意味着全程锁表。在线方案解耦：先在带外建新结构，把实时变更流入其中，最后一瞬切换。重建慢慢磨，业务流量照跑。

工作机制

MySQL InnoDB 原生在线 DDL 拷贝行并回放行日志；gh-ost 改读 binlog 而不用触发器，并按副本延迟自节流。PostgreSQL 多数操作只在提交时取 MVCC 级锁，CONCURRENTLY 建索引避开独占锁。最终换名或提交仍需短暂独占窗口。

使用场景

任何大到无法在锁窗口内重建的表。要预算：影子副本的磁盘、副本延迟余量、节流阈值，以及回滚预案——切换点就是难以回头的时刻。

常见误区

“在线”不等于零影响：吞吐会降、副本会滞后、磁盘几乎翻倍。切换点的元数据锁会排在任何长事务后面，不先清理就会冻结全表；忽略主键热点的分块拷贝会打爆单页。部分 MySQL 变更已有 instant 算法，先查版本里哪些操作仍是全量重建。

代码示例

import { pgTable, text, integer, timestamp, index } from 'drizzle-orm/pg-core';
import { sql } from 'drizzle-orm';

// 初始 schema（drizzle/0000_init.sql 由 drizzle-kit generate 产出）
const invoices = pgTable('invoices', {
  id: integer('id').primaryKey(),
  customer: text('customer').notNull(),
  createdAt: timestamp('created_at').defaultNow(),
});

// 增量迁移：新增可空列 → 回填 → 收紧约束
const ADD_COLUMN = sql`
  ALTER TABLE invoices
    ADD COLUMN due_date timestamp;
`;

const BACKFILL = sql`
  UPDATE invoices
    SET due_date = created_at + interval '30 days'
    WHERE due_date IS NULL;
`;

const TIGHTEN = sql`
  ALTER TABLE invoices
    ALTER COLUMN due_date SET NOT NULL;
`;

async function applySteps(db, steps: ReturnType<typeof sql>[]) {
  for (const step of steps) {
    await db.execute(step);
  }
  await db.execute(sql`
    CREATE INDEX IF NOT EXISTS invoices_due_date_idx
      ON invoices (due_date);
  `);
}

console.log('three-step migration prepared: add, backfill, tighten');

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    phone VARCHAR(20),
    hire_date DATE NOT NULL DEFAULT CURRENT_DATE,
    salary NUMERIC(10, 2) NOT NULL,
    department_id INTEGER NOT NULL,
    manager_id INTEGER,
    employment_status VARCHAR(20) NOT NULL DEFAULT 'active',

    -- Unique constraint
    CONSTRAINT uk_employees_email UNIQUE (email),

    -- Check constraints for data validation
    CONSTRAINT chk_salary_positive CHECK (salary > 0),
    CONSTRAINT chk_salary_range CHECK (salary <= 1000000),
    CONSTRAINT chk_hire_date CHECK (hire_date <= CURRENT_DATE),
    CONSTRAINT chk_status_valid CHECK (
        employment_status IN ('active', 'on_leave', 'terminated', 'suspended')
    ),
    CONSTRAINT chk_phone_format CHECK (
        phone IS NULL OR phone ~ '^\+?[0-9]{10,15}$'
    ),

    -- Foreign key constraints
    CONSTRAINT fk_employee_department
        FOREIGN KEY (department_id)
        REFERENCES departments(department_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,

    CONSTRAINT fk_employee_manager
        FOREIGN KEY (manager_id)
        REFERENCES employees(employee_id)
        ON DELETE SET NULL
) TABLESPACE pg_default;

-- Add a deferred constraint
ALTER TABLE employees
    ADD CONSTRAINT chk_no_self_reference
    CHECK (manager_id IS NULL OR manager_id != employee_id);