Postgres Autovacuum是否阻塞表读写？调优方案可行性咨询

我们有一项通过REST API插入百万级记录的任务，发现插入数百万条记录后服务挂起并停止响应。怀疑是PostgreSQL数据库问题，遂提取AWS RDS日志中的相关内容如下：

2022-09-08 19:17:58 UTC::@:[26730]:LOG:  automatic vacuum of table "xxx.xxx.edges": index scans: 1
    pages: 0 removed, 2659041 remain, 0 skipped due to pins, 321741 skipped frozen
    tuples: 4680884 removed, 48758705 remain, 657299 are dead but not yet removable, oldest xmin: 458583508
    index scan needed: 268594 pages from table (10.10% of total) had 4843221 dead item identifiers removed
    index "edges_pkey": pages: 398515 in total, 0 newly deleted, 0 currently deleted, 0 reusable
    index "edges_start_node_id_idx": pages: 289505 in total, 0 newly deleted, 0 currently deleted, 0 reusable
    index "edges_end_node_id_idx": pages: 263812 in total, 0 newly deleted, 0 currently deleted, 0 reusable
    index "edges_bounding_box_gix": pages: 675160 in total, 250 newly deleted, 456 currently deleted, 206 reusable
    index "edges_edge_type_idx": pages: 329144 in total, 7630 newly deleted, 7755 currently deleted, 7755 reusable
    index "edges_source_road_type_idx": pages: 73470 in total, 49 newly deleted, 49 currently deleted, 43 reusable
    I/O timings: read: 38956.761 ms, write: 1681.906 ms
    avg read rate: 57.004 MB/s, avg write rate: 29.287 MB/s
    buffer usage: 5317313 hits, 2531220 misses, 1300474 dirtied
    WAL usage: 2268022 records, 1302892 full page images, 3546499081 bytes
    system usage: CPU: user: 153.21 s, system: 10.87 s, elapsed: 346.90 s

疑问列表 #

• 日志中system usage: CPU: user: 153.21 s, system: 10.87 s, elapsed: 346.90 s是否表示autovacuum总耗时约346秒？
• 表在执行autovacuum和analyze期间是否会禁止读写操作？
• 若确实存在阻塞，计划调整参数让autovacuum在数据变更较小时运行并分配更多资源，缩短每次清理时长以减少阻塞，该方案是否可行？

问题解答 #

1. 关于autovacuum耗时
   是的，elapsed: 346.90 s就是本次autovacuum操作从开始到结束的总耗时，约347秒。这里的user和system CPU时间是操作实际占用的CPU处理时间，因为autovacuum可能会因为I/O等待、系统调度等原因处于空闲状态，所以总耗时会大于CPU时间之和。

2. 关于autovacuum/analyze对读写的影响
   PostgreSQL的autovacuum和analyze操作默认是非阻塞的：

• Autovacuum清理死元组时，只会获取表的共享锁，不会阻止正常的SELECT/INSERT/UPDATE/DELETE操作；
• Analyze操作仅读取表数据收集统计信息，同样不会阻塞读写。
  但需要注意，如果autovacuum需要清理大量死元组，可能会占用较多系统资源（如I/O、CPU），间接影响业务操作的性能；另外，当存在长时间未提交的事务时，autovacuum无法清理这些事务可见的死元组，可能导致死元组堆积，反而影响查询性能。

3. 关于调整autovacuum参数的方案可行性
   该方案是可行的，具体可以从以下几个方向调整：

• 提前触发autovacuum：降低autovacuum_vacuum_threshold和autovacuum_vacuum_scale_factor，让autovacuum在死元组数量较少时就启动，避免单次清理大量数据；
• 增加autovacuum资源：提高autovacuum_work_mem（增大清理时可用内存，减少I/O）、autovacuum_max_workers（允许同时运行更多autovacuum进程），调整autovacuum_vacuum_cost_delay和autovacuum_vacuum_cost_limit，减少autovacuum的主动延迟，让它更快完成清理；
• 针对性配置表参数：如果只有特定表（比如本次的edges表）有大量写入，可以单独给该表设置autovacuum参数（如ALTER TABLE edges SET (autovacuum_vacuum_scale_factor = 0.01);），避免影响其他表。

调整参数时要结合实际业务负载测试，避免autovacuum占用过多资源反而影响正常业务。

内容的提问来源于stack exchange，提问作者GeauxEric