纲要Background Writer进程概述核心职责定期将shared_buffers中的脏页写入Page Cache设计目标分摊Checkpoint的 I/O 压力减少后端进程Backend主动刷脏核心参数bgwriter_delaybgwriter_lru_maxpagesbgwriter_lru_multiplierbgwriter_flush_after监控视图pg_stat_bgwriterPG 16 及更早buffers_cleanbuffers_backendmaxwritten_cleanpg_stat_ioPG 16pg_stat_checkpointerPG 17PG 17 的重大变更buffers_backend与buffers_backend_fsync从pg_stat_bgwriter中移除可观测性向pg_stat_io迁移多语言示例Node.jspgGopgxPythonpsycopg2JavaJDBC调优策略与最佳实践一、Background Writer 的设计背景与核心职责PostgreSQL 使用shared_buffers作为共享缓冲区缓存所有数据页的读写都经过这一层。Checkpoint进程负责将shared_buffers中的所有脏页刷入磁盘但若两次Checkpoint间隔较长且写负载较高累积的大量脏页会在单一Checkpoint瞬间触发巨大的 I/O 突发I/O Spike直接影响查询响应时间。为解决这一问题PostgreSQL 引入了独立的Background Writer后台写进程。其核心职责包括周期性扫描shared_buffers以固定间隔唤醒扫描缓冲区中的脏页。将脏页写入Page CacheBackground Writer仅将数据写入操作系统Page Cache实际的fsync落盘由Checkpoint进程负责。为Checkpoint分摊压力提前将部分脏页写出Checkpoint执行时可跳过已写入的数据块从而减少 I/O 突发。减少后端进程主动刷脏当shared_buffers空闲缓冲区不足时后端进程需自行选择脏页淘汰并刷盘称为buffers_backend。Background Writer若能及时释放缓冲区可显著降低后端进程的查询延迟。定期扫描脏页fsyncBackend 主动刷脏用户查询Backend Processshared_buffersBackground WriterPage CacheCheckpoint磁盘shared_buffers 不足二、Background Writer 核心参数详解以下参数均在postgresql.conf中配置修改后需重新加载pg_reload_conf()或SELECT pg_reload_conf();或重启生效。2.1bgwriter_delay含义Background Writer两次活动轮次之间的延迟时间毫秒。默认值200ms。说明每轮中Background Writer会写入若干脏页由后续参数控制然后休眠bgwriter_delay毫秒后重复。若某次扫描未发现任何脏页进程将进入更长的休眠。注意许多系统上睡眠延迟的有效分辨率仅为 10 毫秒设置为非 10 的倍数将向上取整。调优建议写入密集型工作负载可适当降低该值如100ms使Background Writer更频繁地工作分散 I/O 负载。2.2bgwriter_lru_maxpages含义每轮中Background Writer最多写入的缓冲区数量。默认值100个缓冲区。说明设置为0将禁用背景写入Checkpoint活动不受影响。该参数与bgwriter_lru_multiplier共同决定每轮实际写入量。调优建议I/O 能力充足的系统可适当提高该值如200或更高让Background Writer每轮处理更多脏页。2.3bgwriter_lru_multiplier含义每轮写入脏页数量的乘数因子。默认值2.0。说明每轮写入量基于“最近几轮中后端进程所需的新缓冲区数量”的平均值乘以该乘数。设置1.0代表“准时制”策略——恰好写入预测所需的数量更大的值提供应对突发需求的缓冲更小的值则有意将写入工作留给后端进程。调优建议若buffers_backend过高可适当提高bgwriter_lru_multiplier如2.5或3.0使Background Writer更积极地清理脏页。若 I/O 资源紧张可降低该值以减少背景写带来的额外 I/O 开销。2.4bgwriter_flush_after含义Background Writer写入超过此字节数后强制操作系统将写入下发到底层存储。默认值512kBLinux。说明该参数控制Background Writer调用posix_fadvise等接口的频率避免Page Cache中积累过多脏页。三、监控视图与指标解读3.1pg_stat_bgwriterPG 16 及更早该视图始终包含单行全局统计数据。核心字段如下字段类型说明buffers_checkpointbigintCheckpoint期间写入的缓冲区数buffers_cleanbigintBackground Writer写入的缓冲区数maxwritten_cleanbigintBackground Writer因写入过多而停止清理扫描的次数buffers_backendbigint后端进程直接写入的缓冲区数buffers_backend_fsyncbigint后端进程执行fsync的次数buffers_allocbigint分配的缓冲区数-- 查询 Background Writer 相关统计PG 16 及更早SELECTbuffers_clean,buffers_backend,buffers_backend_fsync,maxwritten_clean,buffers_allocFROMpg_stat_bgwriter;指标解读buffers_clean应远大于buffers_backend表明大多数刷脏工作由Background Writer完成。若buffers_backend过高说明后端进程频繁主动刷脏应调优Background Writer参数使其更积极地工作。maxwritten_clean过高说明Background Writer每轮写入量达到bgwriter_lru_maxpages上限可考虑提高该值。3.2pg_stat_ioPG 16PG 16 引入了pg_stat_io视图按后端类型、对象类型和 I/O 上下文细粒度统计 I/O 操作。这是 PostgreSQL 可观测性的一次重大提升。-- 查询后端进程直接写入的统计PG 16SELECTbackend_type,object,context,writes,write_bytesFROMpg_stat_ioWHEREbackend_typeclient backendORbackend_typebackground writer;3.3pg_stat_checkpointerPG 17PG 17 将Checkpoint相关统计从pg_stat_bgwriter中剥离独立为pg_stat_checkpointer视图。-- PG 17 查询 Checkpoint 统计SELECTcheckpoints_timed,checkpoints_req,checkpoint_write_time,checkpoint_sync_time,buffers_checkpointFROMpg_stat_checkpointer;四、PG 17 的重大变更buffers_backend的移除与可观测性演进4.1 变更内容PostgreSQL 17 对pg_stat_bgwriter视图进行了重要重构移除的字段buffers_backend和buffers_backend_fsync。迁移路径这些信息现已通过pg_stat_io视图提供。Checkpoint 统计独立checkpoints_timed、checkpoints_req、checkpoint_write_time、checkpoint_sync_time、buffers_checkpoint迁移至pg_stat_checkpointer。4.2 为什么buffers_backend是“误导人的指标”原素材中提到的“buffers_backend是一个误导人的指标”其根本原因在于buffers_backend不仅统计普通后端进程因缓冲区不足而主动刷脏的次数还统计了autovacuum等后台进程的写入操作。这意味着该指标混杂了多种来源的写入无法准确反映“查询后端因Background Writer工作不及时而被迫刷脏”的真实情况。PG 17 通过pg_stat_io的backend_type字段实现了精确区分运维人员可按需过滤-- PG 17 精确查询 client backend 的主动写入SELECTwritesASbackend_direct_writes,write_bytesASbackend_direct_write_bytesFROMpg_stat_ioWHEREbackend_typeclient backendANDcontextnormal;4.3 版本兼容建议PostgreSQL 版本pg_stat_bgwriter可用字段后端写入统计来源≤ 16buffers_backend,buffers_backend_fsyncpg_stat_bgwriter≥ 17移除buffers_backendpg_stat_io若需编写跨版本兼容的监控脚本可先检查 PostgreSQL 主版本号再选择对应的查询语句。五、多语言示例以下示例演示如何通过不同编程语言连接 PostgreSQL查询Background Writer相关统计信息。所有示例均使用原生驱动并附有对应的 PostgreSQL 原生 SQL 注释。5.1 Node.jspg库import{Client}frompg;asyncfunctiongetBgWriterStats(){constclientnewClient({host:localhost,port:5432,database:postgres,user:postgres,password:your_password,});awaitclient.connect();try{// 查询 pg_stat_bgwriterPG 16 及更早// SELECT buffers_clean, buffers_backend, maxwritten_clean// FROM pg_stat_bgwriter;constresawaitclient.query(SELECT buffers_clean, buffers_backend, maxwritten_clean FROM pg_stat_bgwriter);console.log(Background Writer Stats:);console.log(buffers_clean:${res.rows[0].buffers_clean});console.log(buffers_backend:${res.rows[0].buffers_backend});console.log(maxwritten_clean:${res.rows[0].maxwritten_clean});}finally{awaitclient.end();}}getBgWriterStats().catch(console.error);5.2 Gopgx库packagemainimport(contextfmtloggithub.com/jackc/pgx/v5)funcmain(){conn,err:pgx.Connect(context.Background(),postgres://postgres:your_passwordlocalhost:5432/postgres)iferr!nil{log.Fatal(err)}deferconn.Close(context.Background())// 查询 pg_stat_bgwriterPG 16 及更早// SELECT buffers_clean, buffers_backend, maxwritten_clean// FROM pg_stat_bgwriter;varbuffersClean,buffersBackend,maxwrittenCleanint64errconn.QueryRow(context.Background(), SELECT buffers_clean, buffers_backend, maxwritten_clean FROM pg_stat_bgwriter ).Scan(buffersClean,buffersBackend,maxwrittenClean)iferr!nil{log.Fatal(err)}fmt.Printf(Background Writer Stats:\n)fmt.Printf( buffers_clean: %d\n,buffersClean)fmt.Printf( buffers_backend: %d\n,buffersBackend)fmt.Printf( maxwritten_clean: %d\n,maxwrittenClean)}5.3 Pythonpsycopg2库importpsycopg2defget_bgwriter_stats():connpsycopg2.connect(hostlocalhost,port5432,databasepostgres,userpostgres,passwordyour_password)try:withconn.cursor()ascur:# 查询 pg_stat_bgwriterPG 16 及更早# SELECT buffers_clean, buffers_backend, maxwritten_clean# FROM pg_stat_bgwriter;cur.execute( SELECT buffers_clean, buffers_backend, maxwritten_clean FROM pg_stat_bgwriter )rowcur.fetchone()print(Background Writer Stats:)print(f buffers_clean:{row[0]})print(f buffers_backend:{row[1]})print(f maxwritten_clean:{row[2]})finally:conn.close()if__name____main__:get_bgwriter_stats()5.4 JavaJDBCimportjava.sql.Connection;importjava.sql.DriverManager;importjava.sql.ResultSet;importjava.sql.Statement;publicclassBgWriterStats{publicstaticvoidmain(String[]args){Stringurljdbc:postgresql://localhost:5432/postgres;Stringuserpostgres;Stringpasswordyour_password;try(ConnectionconnDriverManager.getConnection(url,user,password);Statementstmtconn.createStatement()){// 查询 pg_stat_bgwriterPG 16 及更早// SELECT buffers_clean, buffers_backend, maxwritten_clean// FROM pg_stat_bgwriter;Stringsql SELECT buffers_clean, buffers_backend, maxwritten_clean FROM pg_stat_bgwriter ;try(ResultSetrsstmt.executeQuery(sql)){if(rs.next()){System.out.println(Background Writer Stats:);System.out.println( buffers_clean: rs.getLong(buffers_clean));System.out.println( buffers_backend: rs.getLong(buffers_backend));System.out.println( maxwritten_clean: rs.getLong(maxwritten_clean));}}}catch(Exceptione){e.printStackTrace();}}}5.5 多语言对比维度Node.js (pg)Go (pgx)Python (psycopg2)Java (JDBC)驱动pgpgx/v5psycopg2org.postgresql.Driver连接方式连接字符串 / 对象DSN 连接字符串关键字参数JDBC URL查询接口client.query()QueryRow()/Query()cursor.execute()Statement.executeQuery()结果处理rows[0].columnScan(var)fetchone()[idx]rs.getLong(column)资源管理手动end()defer Close()try-finallytry-with-resources类型安全弱类型JS强类型弱类型Python强类型适用场景快速原型、Web 后端高性能微服务数据科学、自动化运维企业级应用六、调优策略与最佳实践6.1 判断Background Writer是否“懒惰”通过对比buffers_clean与buffers_backend可判断Background Writer的工作效率健康状态buffers_cleanbuffers_backend表明刷脏工作主要由Background Writer完成。需调优状态buffers_backend接近或超过buffers_clean表明后端进程频繁主动刷脏应考虑降低bgwriter_delay如100ms提高唤醒频率。提高bgwriter_lru_maxpages如200增加每轮写入量。提高bgwriter_lru_multiplier如2.5或3.0使Background Writer更积极。6.2 注意 PG 17 的监控变更若使用 PG 17 及以上版本使用pg_stat_io替代buffers_backend监控后端写入。使用pg_stat_checkpointer监控Checkpoint相关指标。编写监控脚本时需做版本适配。6.3Checkpoint与Background Writer的协同Background Writer的目标是减少而非消除Checkpoint的 I/O 压力。过度激进的Background Writer可能增加整体 I/O 负载因为同一脏页可能被多次写出。调优时需在“减少Checkpoint突发”与“控制背景写额外开销”之间取得平衡。七、官方文档PostgreSQL 官方文档 - Background Writerhttps://www.postgresql.org/docs/current/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-BACKGROUND-WRITERPostgreSQL 官方文档 - pg_stat_bgwriterhttps://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-BGWRITER-VIEWPostgreSQL 官方文档 - pg_stat_iohttps://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-IO-VIEWPostgreSQL 官方文档 - pg_stat_checkpointerhttps://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-CHECKPOINTER-VIEW八、参考链接Understanding the PostgreSQL Background Writer - FastwareThe pg_stat_checkpointer view in Postgres 17 - pganalyzePostgreSQL 17: pg_stat_bgwriter column changes - Munin适配方案PostgreSQL BGWriter 与 Checkpoint 性能调优 - AustinDatabases总结Background Writer是 PostgreSQL 中负责将shared_buffers脏页定期写入Page Cache的后台进程其核心价值在于为Checkpoint分摊 I/O 压力并减少后端进程主动刷脏带来的查询延迟。关键调优参数包括bgwriter_delay、bgwriter_lru_maxpages、bgwriter_lru_multiplier和bgwriter_flush_after需根据buffers_clean与buffers_backend的比例进行动态调整。PG 17 将buffers_backend从pg_stat_bgwriter中移除并迁移至pg_stat_io同时将Checkpoint统计独立为pg_stat_checkpointer显著提升了可观测性的精细度。多语言 Demo 展示了 Node.js、Go、Python、Java 四种主流技术栈访问pg_stat_bgwriter的实践方式便于运维与开发团队快速集成监控能力。