PostgreSQL / 系统课时

迁移普通大表到分区结构

迁移普通大表到分区结构

把线上普通大表改成分区表,本质不是一次 ALTER TABLE,而是一次受控的数据迁移。安全路径要把新结构准备、持续增量同步、历史回填、冻结写入、最终校验和切换拆开,确保回填期间的新写入不会丢失,并让每个阶段都有明确的停止或回退条件。

学习目标

  • 规划普通大表迁移到分区表的低风险阶段和切换门禁
  • 实现并验证历史回填期间的持续增量同步
  • 用行数、业务抽样和依赖检查验证迁移结果

先建立新的分区结构

假设现有大表 event_log_old 已在线运行,并按 occurred_at 保留历史数据。迁移时先创建新的分区父表和必要叶子分区,不直接改造原表。示例使用包含分区键的主键,既满足 PostgreSQL 对分区表唯一约束的要求,也为后续幂等回填和增量同步提供冲突键。

sql
CREATE TABLE event_log_new (
    tenant_id bigint NOT NULL,
    event_id bigint NOT NULL,
    occurred_at timestamptz NOT NULL,
    event_type text NOT NULL,
    payload jsonb NOT NULL,
    PRIMARY KEY (tenant_id, event_id, occurred_at)
) PARTITION BY RANGE (occurred_at);

CREATE TABLE event_log_new_2026_08 PARTITION OF event_log_new
FOR VALUES FROM ('2026-08-01 00:00:00+00') TO ('2026-09-01 00:00:00+00');

CREATE TABLE event_log_new_2026_09 PARTITION OF event_log_new
FOR VALUES FROM ('2026-09-01 00:00:00+00') TO ('2026-10-01 00:00:00+00');

CREATE INDEX event_log_new_tenant_time_idx
ON event_log_new (tenant_id, occurred_at DESC);

预期结果

text
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE INDEX

生产环境应按实际历史范围提前创建全部目标分区,并检查是否存在落在规划范围之外的记录,否则后续写入会因找不到可接收该分区键的叶子分区而失败。如果业务不能保证 (tenant_id, event_id, occurred_at) 唯一,应重新设计真实业务唯一键;但在分区表上声明的 PRIMARY KEY 或 UNIQUE 约束仍必须包含全部分区键列。

把迁移拆成可回退阶段

迁移期间需要同时追踪旧表、新分区表、历史回填和持续写入,单靠文字容易遗漏先后关系。稳妥路径是:建立新结构,先启动增量同步,再分批回填历史数据;回填完成后冻结旧表写入,验证最终状态,再在短维护窗口切换表名并保留旧表作为回退对象。

普通大表迁移到分区结构的低风险路径

历史回填只能搬运被查询到的已有数据,不能自动覆盖回填过程中继续进入旧表的新写入。生产环境可以采用应用双写或经过验证的 CDC;为了给出能够在单个 PostgreSQL 数据库中独立验证的完整闭环,本课使用临时行级触发器同步 INSERT、UPDATE 和 DELETE。

启动并验证增量同步

增量同步必须在历史回填开始前启用。对于 UPDATE,如果组成冲突键的列发生变化,需要先删除旧键对应的目标记录,再按新值写入;DELETE 则同步删除目标记录。触发器与原业务事务处于同一事务中,因此原事务回滚时,同步写入也会一起回滚。

sql
CREATE OR REPLACE FUNCTION sync_event_log_to_partitioned()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    IF TG_OP = 'DELETE' THEN
        DELETE FROM event_log_new
        WHERE tenant_id = OLD.tenant_id
          AND event_id = OLD.event_id
          AND occurred_at = OLD.occurred_at;
        RETURN OLD;
    END IF;

    IF TG_OP = 'UPDATE'
       AND (OLD.tenant_id, OLD.event_id, OLD.occurred_at)
           IS DISTINCT FROM
           (NEW.tenant_id, NEW.event_id, NEW.occurred_at) THEN
        DELETE FROM event_log_new
        WHERE tenant_id = OLD.tenant_id
          AND event_id = OLD.event_id
          AND occurred_at = OLD.occurred_at;
    END IF;

    INSERT INTO event_log_new (
        tenant_id, event_id, occurred_at, event_type, payload
    ) VALUES (
        NEW.tenant_id, NEW.event_id, NEW.occurred_at, NEW.event_type, NEW.payload
    )
    ON CONFLICT (tenant_id, event_id, occurred_at)
    DO UPDATE SET
        event_type = EXCLUDED.event_type,
        payload = EXCLUDED.payload;

    RETURN NEW;
END;
$$;

CREATE TRIGGER event_log_migration_sync
AFTER INSERT OR UPDATE OR DELETE ON event_log_old
FOR EACH ROW
EXECUTE FUNCTION sync_event_log_to_partitioned();

预期结果

text
CREATE FUNCTION
CREATE TRIGGER

安装后先在事务中做一次探针写入,确认旧表变更会同步到新表。示例时间必须落在已经创建的目标分区内。

sql
BEGIN;

INSERT INTO event_log_old (
    tenant_id, event_id, occurred_at, event_type, payload
) VALUES (
    1001,
    900001,
    TIMESTAMPTZ '2026-09-07 00:00:00+00',
    'migration_probe',
    '{"source":"migration-test"}'::jsonb
);

SELECT tenant_id, event_id, event_type
FROM event_log_new
WHERE tenant_id = 1001
  AND event_id = 900001
  AND occurred_at = TIMESTAMPTZ '2026-09-07 00:00:00+00';

ROLLBACK;

预期结果

text
BEGIN
INSERT 0 1
 tenant_id | event_id |    event_type
-----------+----------+-----------------
      1001 |   900001 | migration_probe
(1 row)

ROLLBACK

ROLLBACK 会同时撤销旧表写入和触发器产生的新表写入,因此不会留下测试数据。若新表缺行,应在历史回填开始前停止迁移,优先排查触发器、目标分区范围和约束错误。

分批回填历史数据

大表回填应按分区边界或稳定主键范围分批执行,让每批事务大小、WAL 产生量和复制压力保持可控。由于增量触发器已经运行,历史回填使用 ON CONFLICT,避免历史行与同期业务变更同时写入新表时发生重复键冲突。

sql
INSERT INTO event_log_new (
    tenant_id, event_id, occurred_at, event_type, payload
)
SELECT
    tenant_id, event_id, occurred_at, event_type, payload
FROM event_log_old
WHERE occurred_at >= TIMESTAMPTZ '2026-08-01 00:00:00+00'
  AND occurred_at <  TIMESTAMPTZ '2026-09-01 00:00:00+00'
ON CONFLICT (tenant_id, event_id, occurred_at)
DO UPDATE SET
    event_type = EXCLUDED.event_type,
    payload = EXCLUDED.payload;

预期结果

text
INSERT 0 <ROW_COUNT>

<ROW_COUNT> 取决于该批实际数据量。生产回填应记录每批时间范围、处理行数、耗时和失败状态,并持续观察长事务、WAL、磁盘空间、复制延迟与数据库负载。不要用一个覆盖多年数据的超大事务换取表面上的操作简单。

冻结写入并完成最终校验

同步触发器可以覆盖迁移期间的新变更,但切换前仍需要一个明确冻结点。先在应用或入口层暂停旧表的新写入,等待已经开始的写事务结束,再做最终比对。由于本课使用的是本地 AFTER 触发器,触发器操作与原事务一起提交,不存在需要单独等待消费的异步队列。

先按时间范围做行数检查,这是成本较低的第一层验证。

sql
SELECT
    (SELECT count(*)
     FROM event_log_old
     WHERE occurred_at >= TIMESTAMPTZ '2026-08-01 00:00:00+00'
       AND occurred_at <  TIMESTAMPTZ '2026-09-01 00:00:00+00') AS old_count,
    (SELECT count(*)
     FROM event_log_new
     WHERE occurred_at >= TIMESTAMPTZ '2026-08-01 00:00:00+00'
       AND occurred_at <  TIMESTAMPTZ '2026-09-01 00:00:00+00') AS new_count;

预期结果

text
 old_count | new_count
-----------+-----------
 <COUNT>   | <COUNT>
(1 row)

两个计数必须一致,但计数相同不能证明内容完全一致。关键迁移还应按租户、日期或业务状态做聚合比对,并抽样核对真实记录。如果实际采用异步 CDC,则冻结后必须等待消费位点或 LSN 明确到达冻结点,再进入切换阶段。

在短窗口内切换并保留回退

最终切换应发生在写入已经冻结且校验通过之后。通过固定表名访问时,可以在维护窗口内重命名对象;但 ALTER TABLE ... RENAME 需要取得 ACCESS EXCLUSIVE 锁,因此执行前应确认没有长事务或持续访问阻塞切换。

sql
BEGIN;

DROP TRIGGER event_log_migration_sync ON event_log_old;

ALTER TABLE event_log_old RENAME TO event_log_legacy;
ALTER TABLE event_log_new RENAME TO event_log;

COMMIT;

预期结果

text
BEGIN
DROP TRIGGER
ALTER TABLE
ALTER TABLE
COMMIT

切换前还必须盘点视图、外键、触发器、权限、序列、ORM 映射和运维脚本等依赖。数据库中的依赖通常绑定对象 OID,重命名旧表和新表并不会让原先依赖旧表对象的数据库对象自动改为依赖新表,因此必须逐项检查实际指向,并在需要时显式重建依赖。

切换成功后不要立即删除 event_log_legacy。先恢复应用写入,观察查询正确性、写入错误、分区裁剪、索引使用和容量变化;只有业务验收完成且回退窗口结束后,再删除临时同步函数,并进入旧表归档或清理阶段。低风险迁移的关键不是一次性搬得更快,而是让持续增量、历史回填、冻结校验和切换门禁形成可验证的完整闭环。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

迁移普通大表到分区结构

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通