PostgreSQL / 技术文章

PostgreSQL 批量归档拖慢线上库怎么排查

PostgreSQL 批量归档一旦与在线流量争抢 I/O、WAL、锁、CPU 或连接,就会表现为接口延迟上升、复制延迟扩大、检查点变重,甚至磁盘快速吃紧。下面给 DBA、SRE 与平台工程师一条从“确认是不是归档造成的”到“定位瓶颈并安全止损”的排查路径,最终能够把慢点归因到读取、锁等待、写删、WAL/复制或存储压力,而不是只盯着归档任务本身。

先确认:线上变慢是否真的与归档同频

第一步不是立刻调小批次,而是把业务性能劣化与归档批次对齐。至少同时记录归档批次的开始时间、结束时间、处理行数、源端读取耗时、目标端写入耗时、源端删除耗时,以及同期业务延迟、活跃会话、复制延迟和磁盘利用率。

先看当前正在执行什么、在等什么:

sql
SELECT
    pid,
    usename,
    application_name,
    state,
    wait_event_type,
    wait_event,
    now() - query_start AS query_age,
    left(query, 160) AS query
FROM pg_stat_activity
WHERE datname = current_database()
  AND state <> 'idle'
ORDER BY query_start;

预期结果是能把归档会话与业务会话区分开,并看到归档会话究竟处于 CPU 执行状态,还是等待 Lock、IO、WAL、Client 等事件。若业务延迟只在归档批次运行期间升高,并在暂停后明显恢复,才有充分理由继续沿归档链路排查。

不要把 state = 'active' 直接理解成“正在消耗 CPU”。活跃会话也可能正在等待某类资源,wait_event_type 与 wait_event 才是继续分流的关键。:contentReference[oaicite:0]{index=0}

把一个批次拆成读取、等待、写入和删除

批量归档通常包含“选一批数据 → 写入归档目标 → 校验 → 删除源数据 → 提交”。只看整批耗时会掩盖真正的慢点,因此要把阶段拆开。

如果读取阶段越来越慢,先检查归档条件能否稳定走索引,以及批次边界是否导致扫描范围越来越大。对实际使用的筛选 SQL 执行:

sql
EXPLAIN (ANALYZE, BUFFERS, WAL)
SELECT id, tenant_id, occurred_at, payload
FROM event_history
WHERE occurred_at < TIMESTAMP '2026-01-01 00:00:00'
ORDER BY occurred_at, id
LIMIT 5000;

重点看实际扫描行数与返回行数的比例、是否出现大范围顺序扫描、shared read 是否异常放大,以及排序是否落到临时文件。归档条件若是 occurred_at < cutoff,常见的稳妥索引方向是让时间边界和稳定主键共同参与定位,例如 (occurred_at, id),避免每一批都重复从大范围历史数据中筛选。

如果查询本身很快,但整批耗时仍高,就继续判断是在等待锁、写归档目标、删除源表,还是提交与 WAL 刷盘阶段变慢。一个有效的任务日志应该至少把这些阶段分开计时,而不是只记录“本批耗时 18 秒”。

锁等待:归档没有锁全表,也可能拖住业务

小批量 DELETE 仍会获取行级锁,并可能与同时更新相同行的业务事务竞争;归档使用的 DDL、分区操作或某些维护操作还可能需要更强的表级锁。出现 wait_event_type = 'Lock' 时,要直接还原等待者与阻塞者,而不是先调数据库参数。

sql
SELECT
    blocked.pid AS blocked_pid,
    blocked.application_name AS blocked_app,
    blocker.pid AS blocker_pid,
    blocker.application_name AS blocker_app,
    now() - blocked.query_start AS blocked_for,
    left(blocked.query, 120) AS blocked_query,
    left(blocker.query, 120) AS blocker_query
FROM pg_stat_activity AS blocked
JOIN pg_stat_activity AS blocker
  ON blocker.pid = ANY (pg_blocking_pids(blocked.pid))
ORDER BY blocked.query_start;

预期结果是明确谁在等谁。如果归档任务阻塞业务,应优先暂停归档或结束当前可安全回滚的归档批次;如果业务长事务反过来卡住归档,则需要评估该业务事务的年龄、已执行操作和回滚成本,再决定是否终止。不要为了“让归档继续跑”而机械杀掉线上事务。

还要关注事务边界。一次归档 50 万行并在最后统一提交,与每 5000 行提交一次,对持锁时间、WAL 峰值和失败回滚成本完全不同。批次大小应该由线上可承受的资源预算决定,而不是由“任务尽快结束”决定。

WAL 与复制:主库还能跑,不代表归档没有制造压力

大量 INSERT 到归档表、DELETE 源表、索引维护以及可能出现的 full-page image 都会制造 WAL。主库业务延迟上升时,应同时看 WAL 生成速度和备库追赶情况,尤其是归档批次期间复制延迟是否持续扩大。

sql
SELECT
    application_name,
    state,
    sync_state,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS replay_lag_bytes,
    write_lag,
    flush_lag,
    replay_lag
FROM pg_stat_replication
ORDER BY application_name;

pg_stat_replication 按 WAL sender 展示连接中的备库状态,可用于观察 write、flush、replay 进度。:contentReference[oaicite:1]{index=1}

再看实例累计 WAL 指标,并在固定时间间隔采样差值,而不是只读一个累计数字:

sql
SELECT
    wal_records,
    wal_fpi,
    pg_size_pretty(wal_bytes) AS wal_bytes,
    stats_reset
FROM pg_stat_wal;

如果归档开始后 WAL 速率陡升、备库 replay lag 持续扩大,且业务写延迟同步恶化,说明问题已经从“归档 SQL 慢”升级为“归档改变了整个写入与复制压力”。此时止损动作通常是减小批次、增加批次间隔、降低并发,必要时暂停归档等待备库追平,而不是继续提高归档并发。

复制槽也要检查。失联或消费过慢的槽可能持续保留 WAL;槽的 restart_lsn 表示仍可能被消费者需要的最老 WAL 位置。:contentReference[oaicite:2]{index=2}

sql
SELECT
    slot_name,
    slot_type,
    active,
    restart_lsn,
    wal_status,
    safe_wal_size
FROM pg_replication_slots
ORDER BY slot_name;

I/O、临时文件与检查点:找到“磁盘忙”的来源

归档读取历史数据会增加读 I/O,写归档目标和删除源数据会制造脏页与 WAL,排序或哈希内存不足还可能产生临时文件。线上库表现为“所有 SQL 都变慢”时,要确认是否发生了共享存储争抢,而不是只优化某一条归档 SQL。

数据库级别可以先观察临时文件和块读写累计值:

sql
SELECT
    datname,
    blks_read,
    blks_hit,
    temp_files,
    pg_size_pretty(temp_bytes) AS temp_bytes,
    blk_read_time,
    blk_write_time
FROM pg_stat_database
WHERE datname = current_database();

blk_read_time 和 blk_write_time 依赖 I/O timing 相关统计配置;不要把零值直接解释成“没有磁盘耗时”。更有价值的是在归档开始前后做差值,并与主机磁盘吞吐、延迟、队列长度同时对齐。

如果已经部署 pg_stat_statements,可以进一步找出归档 SQL 的累计执行时间、读取块、临时块和 WAL 量。该扩展可以统计语句执行、块 I/O 与 wal_bytes 等指标。:contentReference[oaicite:3]{index=3}

sql
SELECT
    calls,
    round(total_exec_time::numeric, 1) AS total_ms,
    round(mean_exec_time::numeric, 1) AS mean_ms,
    rows,
    shared_blks_read,
    shared_blks_dirtied,
    temp_blks_written,
    pg_size_pretty(wal_bytes::bigint) AS wal_bytes,
    left(query, 140) AS query
FROM pg_stat_statements
WHERE query ILIKE '%event_history%'
ORDER BY total_exec_time DESC
LIMIT 20;

若 pg_stat_statements 未启用,不应在线上故障窗口里临时重启数据库只为获取它;先用现有系统视图和主机监控完成止损,之后再补齐长期观测能力。

用“暂停—缩批—限速—复测”完成止损与归因

排障最终要落到一个可验证的控制回路,而不是停在“可能是 I/O”这种结论上。建议按影响半径从小到大执行:先暂停新批次,观察业务延迟、WAL 速率和复制延迟是否恢复;再以更小批次恢复;随后拉开批次间隔;最后才考虑调整并发、索引或归档架构。

可以给每个归档批次设置明确门禁:

text
RUNNING
  -> 业务延迟超阈值      -> PAUSED
  -> 复制延迟超阈值      -> PAUSED
  -> 磁盘余量低于安全线  -> PAUSED
  -> 单批耗时持续恶化    -> REDUCE_BATCH
  -> 全部门禁通过        -> NEXT_BATCH

恢复任务时,每次只改变一个变量并观察多个批次。例如把 20000 行降到 5000 行后,如果业务延迟恢复但总归档吞吐仍可接受,说明主要矛盾是单事务资源峰值;如果缩批无效而增加批次间隔有效,更像持续 I/O 或 WAL 带宽争抢;如果归档会话长时间处于 Lock 等待,就应回到阻塞链与数据访问冲突,而不是继续调批次。

最终的故障记录至少要留下四类证据:哪个归档阶段变慢、对应的等待或资源指标、采取了什么止损动作、复测后哪些指标恢复。这样下一次归档性能回退时,可以直接从证据链定位,而不是重新从“是不是数据库太忙”开始猜。

目录与阅读设置

记录一个想法

PostgreSQL 批量归档拖慢线上库怎么排查

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

登录哪都通