PostgreSQL 表膨胀排查：任务队列表从 24GB 回到 7GB 的复盘
任务队列表慢下来时，我一开始盯着索引看了很久。
这个表每天都会插入新任务，也会频繁更新任务状态。正常情况下，消费者每次拉 100 条待处理任务，接口耗时在 20ms 到 50ms 之间。后来预发环境压测时，同样的查询开始抖到 700ms 以上，偶尔还会超过 1s。
数据库是 PostgreSQL 15.6，表结构做过简化，大概是这样：
CREATE TABLE send_job (
id BIGSERIAL PRIMARY KEY,
tenant_id BIGINT NOT NULL,
status SMALLINT NOT NULL,
run_at TIMESTAMPTZ NOT NULL,
retry_count INT NOT NULL DEFAULT 0,
payload JSONB NOT NULL,
locked_at TIMESTAMPTZ,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
消费者拉任务的 SQL 如下：
SELECT
id,
tenant_id,
payload,
retry_count
FROM send_job
WHERE tenant_id = 18
AND status = 0
AND run_at <= now()
ORDER BY run_at ASC, id ASC
LIMIT 100
FOR UPDATE SKIP LOCKED;
表上已经有针对这个查询的索引：
CREATE INDEX idx_send_job_pending
ON send_job (tenant_id, status, run_at, id);
这个索引从查询形态上看没有明显问题。tenant_id 和 status 是等值条件，run_at 接排序和范围，id 用来保证同一时间下顺序稳定。执行计划也没有额外排序。
但慢查询还是出现了。
执行计划看起来没跑偏，但 buffer 很高
先看慢的时候的执行计划。
EXPLAIN (ANALYZE, BUFFERS)
SELECT
id,
tenant_id,
payload,
retry_count
FROM send_job
WHERE tenant_id = 18
AND status = 0
AND run_at <= now()
ORDER BY run_at ASC, id ASC
LIMIT 100
FOR UPDATE SKIP LOCKED;
脱敏后的结果如下：
Limit (cost=0.43..412.92 rows=100 width=912)
(actual time=38.214..842.773 rows=100 loops=1)
Buffers: shared hit=18342 read=6129 dirtied=47
-> LockRows (cost=0.43..283721.50 rows=68812 width=912)
(actual time=38.212..842.748 rows=100 loops=1)
Buffers: shared hit=18342 read=6129 dirtied=47
-> Index Scan using idx_send_job_pending on send_job
(cost=0.43..283033.38 rows=68812 width=912)
(actual time=38.176..840.931 rows=100 loops=1)
Index Cond: ((tenant_id = 18)
AND (status = 0)
AND (run_at <= now()))
Buffers: shared hit=18294 read=6129
Planning Time: 0.318 ms
Execution Time: 842.831 ms
表面看是 Index Scan，索引条件也对上了，返回行数只有 100。问题出在 Buffers 上：为了拿 100 行，读了两万多个 buffer。
这种执行计划很容易误导人。它没有明显的 Seq Scan，也没有 Sort，看起来像是索引用上了。但 PostgreSQL 顺着索引找数据时，仍然可能踩到大量已经失效的行版本和膨胀的数据页。最终表现就是扫描路径没变，I/O 和 CPU 都上去了。
我当时没有继续改索引，而是先看表和索引的体积。
先确认是不是表膨胀
查表大小时，我一般会把表、索引、总大小拆开看。
SELECT
pg_size_pretty(pg_relation_size('public.send_job')) AS table_size,
pg_size_pretty(pg_indexes_size('public.send_job')) AS indexes_size,
pg_size_pretty(pg_total_relation_size('public.send_job')) AS total_size;
当时输出是：
table_size | indexes_size | total_size
------------+--------------+-----------
24 GB | 11 GB | 35 GB
这个大小明显不正常。任务表的活跃数据只有 120 万行左右，payload 虽然是 JSONB，但大多数任务内容不大。按历史估算，整张表加索引应该在 6GB 到 8GB 之间。
再看统计信息：
SELECT
relname,
n_live_tup,
n_dead_tup,
last_vacuum,
last_autovacuum,
vacuum_count,
autovacuum_count,
analyze_count,
autoanalyze_count
FROM pg_stat_user_tables
WHERE relname = 'send_job';
输出大概是这样：
relname | n_live_tup | n_dead_tup | last_vacuum | last_autovacuum | vacuum_count | autovacuum_count
----------+------------+------------+-------------+-------------------------------+--------------+------------------
send_job | 1248310 | 6839204 | | 2026-01-14 03:18:22.491+08 | 0 | 11
n_dead_tup 是估算值，不适合当成精确数字，但方向已经很明显。死元组数量比活跃行多了几倍，autovacuum 跑过，却没有跟上更新速度。
如果库里可以安装扩展，我还会用 pgstattuple 再确认一次。这个扩展会扫描表，生产环境要避开高峰期。
CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT
table_len,
tuple_count,
tuple_percent,
dead_tuple_count,
dead_tuple_percent,
free_percent
FROM pgstattuple('public.send_job');
结果更直观：
table_len | tuple_count | tuple_percent | dead_tuple_count | dead_tuple_percent | free_percent
-------------+-------------+---------------+------------------+--------------------+-------------
25818447872 | 1270192 | 21.84 | 6924418 | 46.73 | 24.61
这时基本可以判断：查询慢不是因为索引方向错了，而是表和索引里堆了太多过期版本。索引仍然能用，但每次沿着索引走，都会绕进大量无效行版本和分散的数据页。
UPDATE 频繁的表很容易被撑大
PostgreSQL 的 UPDATE 不是在原位置直接覆盖一行。
一条任务从待处理变成处理中，会更新一次：
UPDATE send_job
SET status = 1,
locked_at = now(),
updated_at = now()
WHERE id = $1;
处理中变成成功，又会更新一次：
UPDATE send_job
SET status = 2,
updated_at = now()
WHERE id = $1;
失败重试还会更新 retry_count、run_at、status。
在 MVCC 机制下，每次更新都会产生新的行版本，旧版本要等没有事务再需要它时，才能被 VACUUM 清理。清理之后，空间通常会留在表文件内部，后续插入或更新可以复用。磁盘上的表文件不会因为普通 VACUUM 立刻缩小。
这个机制本身没问题，问题出在任务队列表这种写法上：
insert -> update status -> update retry_count -> update locked_at -> update status -> delete/archive
同一行生命周期里会经历多次更新。任务量上来后，死元组产生速度比 autovacuum 清理速度更快，表文件就会一路涨。
这个表还有一个放大因素：payload 是 JSONB。虽然状态更新没有改 payload，但行版本变多以后，堆表和索引都会承压。任务表越热，这个问题越明显。
autovacuum 为什么没跟上
PostgreSQL 默认的 autovacuum 触发阈值大致可以按这个公式理解：
autovacuum_vacuum_threshold
+
autovacuum_vacuum_scale_factor * 表行数
默认 autovacuum_vacuum_scale_factor 通常是 0.2。对于 100 多万行的表，要积累 20 多万条变更后才容易触发一次 vacuum。对普通业务表可能够用，对任务队列这种高频更新表偏保守。
先看当前表有没有单独配置过 autovacuum 参数：
SELECT
relname,
reloptions
FROM pg_class
WHERE oid = 'public.send_job'::regclass;
当时输出是空的：
relname | reloptions
----------+------------
send_job |
也就是完全吃实例默认配置。
再看 autovacuum 进程有没有被长事务拖住。长事务会保留很早之前的快照，VACUUM 即使跑了，也不能清理那些仍可能被旧快照看到的行版本。
SELECT
pid,
usename,
state,
now() - xact_start AS xact_age,
wait_event_type,
wait_event,
left(query, 160) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start ASC
LIMIT 10;
这次还真查到一个后台导出任务，事务开了两个多小时：
pid | usename | state | xact_age | wait_event_type | query
------+---------+--------+----------+-----------------+-------------------------------
9182 | report | idle | 02:17:43 | Client | BEGIN; SELECT ... FROM ...
这个连接处于 idle in transaction 状态。它不一定是唯一原因，但会让 VACUUM 清理效果变差。后面我们把报表导出改成了分页短事务，避免长时间挂着一个快照。
先让 VACUUM 追上来
这种问题不要一上来就 VACUUM FULL。
VACUUM FULL 会重写整张表，能释放磁盘空间，但需要更强的锁，业务表在线上直接跑风险很高。我的处理顺序是先让普通 VACUUM 把可回收空间标记出来，让后续写入能够复用这些空间，再安排索引重建和表重组。
低峰期先跑一次：
VACUUM (ANALYZE, VERBOSE) public.send_job;
执行时可以观察进度：
SELECT
pid,
relid::regclass AS table_name,
phase,
heap_blks_total,
heap_blks_scanned,
heap_blks_vacuumed,
index_vacuum_count,
num_dead_tuples
FROM pg_stat_progress_vacuum
WHERE relid = 'public.send_job'::regclass;
普通 VACUUM 跑完后，磁盘上的 table_size 没有明显下降，这个结果正常。它主要让表内部空间变成可复用状态，同时更新统计信息。真正能立刻感受到的是后续写入不再继续疯狂扩表，查询 buffer 也开始下降。
再查一次统计信息：
SELECT
n_live_tup,
n_dead_tup,
last_vacuum,
last_autovacuum,
vacuum_count,
autovacuum_count
FROM pg_stat_user_tables
WHERE relname = 'send_job';
输出变成：
n_live_tup | n_dead_tup | last_vacuum | last_autovacuum | vacuum_count | autovacuum_count
------------+------------+-------------------------------+-------------------------------+--------------+------------------
1261044 | 318902 | 2026-01-15 01:42:11.029+08 | 2026-01-14 03:18:22.491+08 | 1 | 11
n_dead_tup 降下来了，但这只是止血。要避免几天后复发，还要调整 autovacuum。
给热表单独调 autovacuum
实例级参数不适合为了一个热表改得太激进。这个库里还有不少普通表，如果全局降低 scale factor，autovacuum 会更频繁地扫很多不需要重点照顾的表。
这次直接给 send_job 单独设置存储参数：
ALTER TABLE public.send_job SET (
autovacuum_vacuum_scale_factor = 0.02,
autovacuum_vacuum_threshold = 5000,
autovacuum_analyze_scale_factor = 0.01,
autovacuum_analyze_threshold = 3000,
autovacuum_vacuum_cost_limit = 2000,
autovacuum_vacuum_cost_delay = 2
);
调整后的含义大概是：死元组达到几万级别时就尽早触发，而不是等到几十万甚至更多。ANALYZE 也更积极一些，避免统计信息落后导致执行计划误判。
可以用下面的 SQL 检查是否生效：
SELECT
relname,
reloptions
FROM pg_class
WHERE oid = 'public.send_job'::regclass;
输出类似：
relname | reloptions
----------+----------------------------------------------------------------------------------------
send_job | {autovacuum_vacuum_scale_factor=0.02,autovacuum_vacuum_threshold=5000,...}
这个值不能照抄到所有表。写入很少的维表不需要这么激进；超大表可能还要配合分区；更新频率极高的表，还要考虑业务写法能不能减少 UPDATE 次数。
任务队列表比较特殊，它的状态变化频繁，而且查询只关心少量待处理任务。对它来说，宁愿 autovacuum 更早介入，也不要让死元组堆到影响消费者拉取。
索引膨胀也要单独处理
表膨胀处理完后，慢查询有明显改善，但还没有回到最初水平。再看索引大小：
SELECT
indexrelid::regclass AS index_name,
pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
idx_scan,
idx_tup_read,
idx_tup_fetch
FROM pg_stat_user_indexes
WHERE relid = 'public.send_job'::regclass
ORDER BY pg_relation_size(indexrelid) DESC;
输出里 idx_send_job_pending 已经涨到了 8GB 多：
index_name | index_size | idx_scan | idx_tup_read | idx_tup_fetch
----------------------+------------+----------+--------------+--------------
idx_send_job_pending | 8192 MB | 1843921 | 982183411 | 128318122
send_job_pkey | 2741 MB | 2420011 | 241920034 | 241880191
普通 VACUUM 可以清理索引里的无效条目，但索引文件本身也可能变得很松散。这个索引是任务拉取的关键路径，后面单独做了并发重建：
REINDEX INDEX CONCURRENTLY idx_send_job_pending;
这里也要注意，REINDEX ... CONCURRENTLY 不能放在事务块里执行。它比普通 REINDEX 更适合在线业务，但执行时间会更长，也会带来额外 I/O。线上跑之前要确认磁盘空间足够。
重建后再看：
index_name | index_size
----------------------+-----------
idx_send_job_pending | 2380 MB
这一步对任务拉取接口的改善很明显。因为消费者每次都走这个索引，索引层级和页面数量降下来后，buffer 读也跟着下降。
已经涨大的表文件怎么收回来
普通 VACUUM 解决的是复用问题，不负责把表文件压回去。
这次表从 24GB 涨到 24GB，VACUUM 后仍然接近 24GB，只是内部空洞可以继续复用。如果磁盘空间紧张，或者表已经膨胀到影响缓存命中率，就需要安排表重组。
常见选择有三个。
VACUUM FULL public.send_job; 可以直接压缩表文件，但会重写整张表，锁影响比较大。业务高峰期不能随便跑。
pg_repack 可以在线重组表和索引，锁时间短很多，但需要安装扩展，也需要额外磁盘空间。我们这次最终用的是这个方案。
还有一种更土但可控的做法：新建影子表，分批搬迁数据，然后在维护窗口切表。这个方案代码和运维步骤更多，适合对扩展安装比较敏感的环境。
重组前后对比大概是这样：
| 指标 | 处理前 | VACUUM 后 | 重组后 |
|---|---|---|---|
| 表大小 | 24GB | 23GB | 5.1GB |
| 索引大小 | 11GB | 10GB | 2.4GB |
| 总大小 | 35GB | 33GB | 7.5GB |
| 拉取任务耗时 | 700ms ~ 1s | 180ms ~ 260ms | 25ms ~ 45ms |
| 单次查询 buffer | 2 万多个 | 5000 左右 | 200 左右 |
重组不是每天都要做。真正重要的是前面的 autovacuum 参数和业务写法调整。只要死元组不再长期堆积，后面表文件大小会稳定很多。
业务写法也要一起改
数据库参数只能兜底，任务队列表本身也要减少无意义更新。
原来的消费者每次尝试拉任务时，都会更新 locked_at 和 updated_at。有些任务被多个消费者竞争时，会产生更多更新。后来把任务领取逻辑改成只更新真正拿到的记录：
WITH picked AS (
SELECT id
FROM send_job
WHERE tenant_id = 18
AND status = 0
AND run_at <= now()
ORDER BY run_at ASC, id ASC
LIMIT 100
FOR UPDATE SKIP LOCKED
)
UPDATE send_job j
SET status = 1,
locked_at = now(),
updated_at = now()
FROM picked
WHERE j.id = picked.id
RETURNING
j.id,
j.tenant_id,
j.payload,
j.retry_count;
这段 SQL 把“选择任务”和“锁定任务”放在同一次数据库操作里完成，减少应用层来回调用。它不能消除 UPDATE 带来的新版本，但能减少重复领取和状态抖动。
另外，成功任务不再长时间留在主表里。保留最近 3 天的数据，超过时间后转存到历史表：
CREATE TABLE send_job_history
(LIKE send_job INCLUDING ALL);
归档时分批处理，避免一次性删太多：
WITH moved AS (
DELETE FROM send_job
WHERE status = 2
AND updated_at < now() - interval '3 days'
ORDER BY id
LIMIT 5000
RETURNING *
)
INSERT INTO send_job_history
SELECT *
FROM moved;
PostgreSQL 的 DELETE ... ORDER BY ... LIMIT 不能直接这样写。实际落地时要通过 CTE 先选 ctid 或主键，再删除：
WITH rows AS (
SELECT id
FROM send_job
WHERE status = 2
AND updated_at < now() - interval '3 days'
ORDER BY id
LIMIT 5000
),
moved AS (
DELETE FROM send_job j
USING rows r
WHERE j.id = r.id
RETURNING j.*
)
INSERT INTO send_job_history
SELECT *
FROM moved;
这个批量大小要按库的写入压力调。5000 只是当时环境下比较稳的值，不能直接当成通用标准。
复盘下来最有用的几个检查点
这类问题以后再遇到，我会按固定顺序查。
先看慢 SQL 的执行计划，重点看 BUFFERS，不要只看有没有走索引。
再看表和索引体积：
SELECT
pg_size_pretty(pg_relation_size('public.send_job')) AS table_size,
pg_size_pretty(pg_indexes_size('public.send_job')) AS indexes_size,
pg_size_pretty(pg_total_relation_size('public.send_job')) AS total_size;
接着看死元组和 autovacuum 记录：
SELECT
n_live_tup,
n_dead_tup,
last_autovacuum,
autovacuum_count
FROM pg_stat_user_tables
WHERE relname = 'send_job';
然后查长事务：
SELECT
pid,
state,
now() - xact_start AS xact_age,
left(query, 120) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start ASC
LIMIT 10;
如果确认是热表，就给这张表单独调 autovacuum，不要急着改全局参数。
如果索引也膨胀明显，再考虑：
REINDEX INDEX CONCURRENTLY idx_send_job_pending;
如果表文件已经大到必须释放磁盘，再安排 pg_repack、VACUUM FULL 或影子表切换。
总结
这次任务队列表变慢，表面上是一个查询耗时问题，实际排查落到了 MVCC、死元组、autovacuum 和索引膨胀上。
PostgreSQL 里的热更新表很容易出现这个问题，尤其是任务队列、订单状态表、消息投递表、库存流水表这类高频更新场景。索引设计正确只能保证查询路径不跑偏，不能自动解决死元组堆积。
我后来给这类表都加了三条约束：状态更新尽量少，完成数据尽快归档，autovacuum 按表单独配置。这样做之后，任务拉取接口重新稳定在几十毫秒，表大小也没有继续失控。
下一篇我会写 PostgreSQL 分页查询优化，重点放在 OFFSET 深分页为什么越来越慢，以及用游标分页、复合排序键和索引顺序把查询稳定下来。
浙公网安备 33010602011771号