DAY 45 W7 · 大促备战：优化实战与工程化

历史数据归档：分区表

两年攒下的订单里 90% 是不会再查的历史数据。你把 orders 按月分区，最老的分区直接归档下线。

学 45 min
练 60 min
盘 15 min
共 120 分钟

学 · 45 min

  1. 01声明式分区：RANGE / LIST / HASH 三种

    场景两年 24 个月的订单挤在一张表里--按月切开后，每月一张「小表」。

    PG 10+ 的声明式分区：父表带 partition by range/list/hash，子表带 for values ... 定义归属。range 按值区间（时间分区本命）；list 按枚举值（按城市、按租户）；hash 散列均衡（无明显查询边界时摊数据）。对查询和 DML 而言父子表是一张表，写入自动路由到子分区。

    create table orders_p (
  id uuid not null,
  created_at timestamp not null,
  ...
  primary key (id, created_at)          -- 分区键必须进主键
) partition by range (created_at);

create table orders_p_2026_08 partition of orders_p
  for values from ('2026-08-01') to ('2026-09-01');

    易错分区键必须是主键 / 唯一键的一部分--「按月分区但主键只有 id」建不起来，这是第一个坑。

  2. 02分区裁剪的触发条件与验证方法

    场景分区表凭什么快：查询条件包含分区键时，别的分区碰都不碰。

    「分区裁剪」= 优化器按 WHERE 里的分区键排除无关分区。常量条件在计划期裁剪（explain 直接只列一个分区）；「参数化条件」要到执行期裁剪（计划里显示全部分区但实际只扫一个，看 analyze 的 actual）。验证永远用 explain analyze 的 actual 行数。

    explain select count(*) from orders_p
where created_at >= '2026-08-01' and created_at < '2026-09-01';
-- Subplans Only / 只出现一个分区 = 裁剪生效

-- 条件不含分区键 = 全分区扫（24 个分区全摸一遍）
explain select count(*) from orders_p where total_amount > 1000;

    易错对分区键套函数（date_trunc(created_at)）会让裁剪失效--和索引失效同一个道理。

  3. 03分区键选择原则，以及分区带来的代价

    场景「该不该分区、按什么分」的决策框架。

    选键原则：高频查询条件里出现的那一列（时间是最常见的）。代价照单全收：① 跨分区查询 / 不含分区键的查询反而变慢；② 全局唯一约束做不了（唯一键必须含分区键）；③ 分区键定了一般改不了；④ 运维复杂度（几百个子表的管理）。索引能解决的性能问题不要用分区解决。

    -- 分区数量也要克制：几百个分区后规划开销开始可感知
select count(*) from pg_inherits
where inhparent = 'orders_p'::regclass;

    易错为了「显得专业」上分区、按 hash 分 16 区但查询从不带分区键 = 白白放大了复杂度。

  4. 04CREATE INDEX CONCURRENTLY 不锁写

    场景大促前要在百万行表上补索引--普通建索引期间全表写入被锁。

    普通 create index 拿排他锁，建完才放行写入；concurrently 分两趟扫表、不阻塞 DML，代价是耗时更长、不能在事务里跑。生产大表加索引的默认姿势。

    -- 普通版：锁写直到完成
create index idx_created on orders_p (created_at);

-- 并发版：写入不受阻（另开窗口验证）
create index concurrently idx_created on orders_p (created_at);
-- 别忘了：不能包在 begin...commit 里

    易错concurrently 中途失败会留下 invalid 索引（占空间不可用），要 drop 掉重建：查 pg_index 的 indisvalid 列。

  5. 05大批量更新为什么要分批提交

    场景一条 UPDATE 把 200 万行历史数据打标--事务日志爆炸、复制延迟、长锁。

    单个巨型事务的三重罪：WAL 洪峰（磁盘和复制跟不上）、锁持有全程（别人排队）、失败回滚代价同样是巨型。分批：每批 1 万行、循环提交，每批影响可控，失败从断点重跑。注意批间会有别的事务插进来，过滤条件要幂等（每批重新筛选还没处理的行）。

    do $$
declare n int := 1;
begin
  while n > 0 loop
    with 批 as (
      select id from orders_p
      where status is null
      limit 10000
      for update skip locked
    )
    update orders_p o
    set status = 0
    from 批 where 批.id = o.id;
    get diagnostics n = row_count;   -- 没得改了就停
  end loop;
end $$;

    易错「一次更新所有行」的失败重试等于再来一遍全量；分批 + 幂等筛选才有断点续跑能力。

练 · 60 min

  1. 把 orders 按月做 RANGE 分区，迁移数据
    参考答案

    第一个坑就是主键：分区键 created_at 不进主键，建表直接报错。超出所有分区范围的行 insert 会报错，线上可以再补一个 default 分区兜底。

    -- ① 分区父表：结构同 orders，但主键必须含分区键
create table orders_p (
  id           uuid not null,
  user_id      uuid not null,
  status       integer,
  total_amount numeric(10,2),
  created_at   timestamp not null,
  paid_at      timestamp,
  primary key (id, created_at)          -- 分区键必须进主键
) partition by range (created_at);

-- ② 数据跨两年多，用循环把逐月分区建齐
do $$
declare
  d date := date_trunc('month', current_date) - interval '24 months';
begin
  while d <= date_trunc('month', current_date) loop
    execute format(
      'create table orders_p_%s partition of orders_p for values from (%L) to (%L)',
      to_char(d, 'YYYY_MM'), d, d + interval '1 month');
    d := d + interval '1 month';
  end loop;
end $$;

-- ③ 迁移（练习库 100 万行一次搬可接受；线上要走双写或停写窗口）
insert into orders_p (id, user_id, status, total_amount, created_at, paid_at)
select id, user_id, status, total_amount, created_at, paid_at
from orders;

-- ④ 核对行数一致后切换
select (select count(*) from orders) as 老表,
       (select count(*) from orders_p) as 新表;
-- 一致后：drop table orders; alter table orders_p rename to orders;
  2. 用 EXPLAIN 验证带时间条件的查询只扫了一个分区
    参考答案

    常量条件在计划期就裁剪（explain 里只见一个分区）；应用传参的参数化条件是执行期裁剪，计划里列全部分区但只有真扫的分区有 actual 耗时--验证永远看 actual。对分区键套函数会让裁剪失效。

    explain analyze select count(*)
from orders_p
where created_at >= timestamp '2026-08-01'
  and created_at <  timestamp '2026-09-01';
-- 计划里只出现 1 个分区 = 裁剪生效

explain analyze select count(*) from orders_p where total_amount > 1000;
-- 条件不含分区键：25 个月分区全部被摸一遍
  3. 用 CONCURRENTLY 在大表上建索引，另开窗口验证写入不被阻塞
    参考答案

    预期：窗口 B 的 insert 立刻返回，不排队。若 concurrently 中途失败会留下占空间但不可用的 invalid 索引：查 pg_index 的 indisvalid 列，drop 掉重建。

    -- 窗口 A：建索引（concurrently 不能包在 begin...commit 里）
create index concurrently idx_orders_p_created on orders_p (created_at);

-- 窗口 B：建索引期间持续写入，验证不被阻塞
insert into orders_p (id, user_id, status, total_amount, created_at)
values (gen_random_uuid(), '00000000-0000-0000-0000-000000000042',
        2, 99.00, timestamp '2026-08-29 10:00:00');
  4. 写一个每次更新 1 万行、循环提交的批量更新脚本
    参考答案

    每批的筛选条件是幂等的（status is null 的行越改越少），中途失败从断点重跑不会重复处理。本库脏数据只有几百行，一批就跑完--重点是掌握「循环 + 提交 + 幂等筛选」的结构。

    -- 逐批 commit 要用过程（DO 块里不能 commit）
create or replace procedure fix_status_batch()
language plpgsql as $$
declare
  n int := 1;
begin
  while n > 0 loop
    with 批 as (
      select id from orders_p
      where status is null
      order by id
      limit 10000
      for update skip locked
    )
    update orders_p o
    set status = 0
    from 批 where 批.id = o.id;
    get diagnostics n = row_count;   -- 本批影响行数，0 就停
    commit;                          -- 每批一提交：锁、WAL、回滚代价都可控
  end loop;
end $$;

call fix_status_batch();
  5. 把最老的一个分区 DETACH 出来归档
    参考答案

    detach 只是改元数据，秒级完成、对父表影响极小。之后子表是独立普通表：先 \copy 导出核对，再 drop--老数据从主查询路径彻底消失，文件留档。这就是「归档硬删」对软删除的胜利。

    -- 先看分区清单，确认最老的那个
select c.relname as 分区
from pg_inherits i
join pg_class c on c.oid = i.inhrelid
where i.inhparent = 'orders_p'::regclass
order by 1
limit 1;

alter table orders_p detach partition orders_p_2024_09;
-- detach 后它变成一张普通表，数据原样还在

-- 核对行数后归档下线
\copy (select * from orders_p_2024_09) to 'orders_2024_09.csv' csv header
drop table orders_p_2024_09;
过关标准 能说出分区的两个好处和两个代价（如跨分区查询变慢、分区键不能随便改）。