PostgreSQL是读多还是写多？搞清楚性能至少提升50%

PostgreSQL到底是读多还是写多？搞清楚这点，你的数据库性能至少提升50%！

你有没有遇到过这样的情况：数据库突然变慢，CPU飙高，I/O卡成PPT，但你却不知道问题出在哪？其实，很多性能问题的根源，就在于你没搞清楚自己的PostgreSQL到底是“读多”还是“写多”。

今天我们就来彻底讲明白这件事：用SQL查出真实负载，再根据结果精准调优。无论你是创业公司CTO、后端工程师，还是刚入行的DBA，这篇文章都能让你少走三年弯路。

先说结论：绝大多数PostgreSQL数据库都是读多写少：比如常见的电商、内容平台、用户系统，90%以上的操作都是SELECT查询，写入（INSERT/UPDATE/DELETE）占比通常不到10%；但也有例外——比如物联网设备日志采集、金融交易流水、实时监控系统，这些场景下写入压力可能远超读取。

所以，不能一概而论，必须用数据说话。

那怎么判断自己的数据库到底是读多还是写多？别猜！PostgreSQL早就把所有线索藏在了系统表里。我们可以通过两个核心视图：pg_stat_user_tables（记录表级操作统计）和pg_statio_user_tables（记录I/O块读写情况），结合pg_class里的元数据，估算出每个表的“读页数”和“写页数”。

这里有个关键点：在PostgreSQL里，读和写根本不是对等的操作。读取是以8KB的“页”为单位，而且高频数据通常缓存在shared_buffers或操作系统缓存里，根本不用碰磁盘。但写入就复杂多了——每次修改数据，不仅要写WAL（预写日志），还要更新索引、TOAST表（存大字段的溢出表），甚至可能触发全页写（Full Page Write）。更别说后台还有autovacuum在默默清理死元组。所以，一次写操作的实际I/O开销，可能是读操作的几十倍。

正因如此，我们不能简单用“查询次数 vs 修改次数”来判断负载类型。真正要看的是“物理I/O页数”的比例。为此，Crunchy Data的数据库专家设计了一个超实用的SQL查询，能自动帮你分类每个表是“读重”“写重”还是“均衡”。

WITH
ratio_target AS (SELECT 5 AS ratio),
table_list AS (SELECT
 s.schemaname,
 s.relname AS table_name,
 -- Sum of heap and index blocks read from disk (from pg_statio_user_tables)
 si.heap_blks_read + si.idx_blks_read AS blocks_read,
 -- Sum of all write operations (tuples) (from pg_stat_user_tables)
s.n_tup_ins + s.n_tup_upd + s.n_tup_del AS write_tuples,
relpages * (s.n_tup_ins + s.n_tup_upd + s.n_tup_del ) / (case when reltuples = 0 then 1 else reltuples end) as blocks_write
FROM
 -- Join the user tables statistics view with the I/O statistics view
 pg_stat_user_tables AS s
JOIN pg_statio_user_tables AS si ON s.relid = si.relid
JOIN pg_class c ON c.oid = s.relid
WHERE
 -- Filter to only show tables that have had some form of read or write activity
(s.n_tup_ins + s.n_tup_upd + s.n_tup_del) > 0
AND
 (si.heap_blks_read + si.idx_blks_read) > 0
 )
SELECT *,
 CASE
   -- Handle case with no activity
   WHEN blocks_read = 0 and blocks_write = 0 THEN
     'No Activity'
   -- Handle write-heavy tables
   WHEN blocks_write * ratio > blocks_read THEN
     CASE
       WHEN blocks_read = 0 THEN 'Write-Only'
       ELSE
         ROUND(blocks_write :: numeric / blocks_read :: numeric, 1)::text || ':1 (Write-Heavy)'
     END
   -- Handle read-heavy tables
   WHEN blocks_read > blocks_write * ratio THEN
     CASE
       WHEN blocks_write = 0 THEN 'Read-Only'
       ELSE
         '1:' || ROUND(blocks_read::numeric / blocks_write :: numeric, 1)::text || ' (Read-Heavy)'
     END
   -- Handle balanced tables
   ELSE
     '1:1 (Balanced)'
 END AS activity_ratio
FROM table_list, ratio_target
ORDER BY
 -- Order by the most active tables first (sum of all operations)
 (blocks_read + blocks_write) DESC;

这个查询的核心逻辑分三步：

第一步，设定一个“读写比阈值”。比如设为5:1，意思是如果读页数超过写页数的5倍，就算读重；反之则算写重。这个值你可以根据业务调整——如果你的系统对写延迟极其敏感，哪怕10:1也可能算写重。

第二步，计算每个表的实际读写页数。读页数直接从pg_statio_user_tables里取heap_blks_read + idx_blks_read；写页数则通过一个巧妙的估算：用表的总页数（relpages）除以总行数（reltuples），得出“每页平均行数”，再乘以实际写入行数（n_tup_ins + n_tup_upd + n_tup_del），就得到了“大概影响了多少个物理页”。

第三步，对比读写页数，自动打标签。比如结果可能是“9259.5:1 (Write-Heavy)”——注意，这个数字看起来反直觉，其实是写页数远大于读页数，所以被判定为写重。也可能是“Read-Only”或“Balanced”。

举个真实例子：某系统有个audit_logs表，每天写入150万条日志，但几乎没人查。查询结果显示blocks_read=2，blocks_write=18519，直接被标为“写重”。而orders表虽然偶尔有写入，但读取频繁，结果却是“Read-Only”——因为统计期内没有物理I/O读（数据全在缓存里）。

除了这个方法，你还可以用pg_stat_statements扩展来辅助判断。它会统计每条SQL的执行次数和返回行数。比如：

SELECT  
  SUM(CASE WHEN query ILIKE 'SELECT%' THEN rows ELSE 0 END) AS rows_read,  
  SUM(CASE WHEN query ILIKE 'INSERT%' OR query ILIKE 'UPDATE%' OR query ILIKE 'DELETE%' THEN rows ELSE 0 END) AS rows_written  
FROM pg_stat_statements;

这样你能看到“总共读了多少行”“写了多少行”，虽然不如页级统计精准，但胜在直观，适合快速筛查。

搞清楚负载类型后，调优方向就完全不同了。

如果你的系统是写重，重点在降低I/O压力和提升事务吞吐：

- 上NVMe SSD！这是最直接有效的手段。PostgreSQL 18还引入了异步I/O，进一步压榨磁盘性能。
- 别乱建索引。每个索引都要在写入时更新，过度索引会让INSERT慢如蜗牛。定期用pg_stat_user_indexes查unused indexes，大胆删。
- 调整fillfactor（填充因子）。比如设为80%，留出空间给HOT更新（Heap-Only Tuple），避免因索引更新引发额外I/O。
- 优化WAL参数：增大wal_buffers（默认16MB，可试32~64MB），减少fsync次数；调整checkpoint_timeout（比如从5分钟拉到15分钟），避免后台刷脏页时I/O雪崩。
- 考虑拆表或分库。如果单表写入量过大，用时间分区或哈希分片分散压力。

如果你的系统是读重，核心目标是“让数据尽量不碰磁盘”：

- 把shared_buffers调大（通常设为物理内存的25%），effective_cache_size设为内存的50%~75%，让PostgreSQL知道有多少缓存可用。
- 用EXPLAIN ANALYZE揪出慢查询，针对性建复合索引。记住：WHERE、JOIN、ORDER BY用到的字段，优先考虑索引。
- 上读写分离！主库只处理写，多个只读副本扛查询流量。云厂商基本都支持一键创建PostgreSQL只读实例。
- 启用连接池（比如pgBouncer），避免频繁建连消耗资源。

最后划重点：别再凭感觉调数据库了！每周跑一次那个读写分析SQL，把结果存下来做趋势图。你会发现，很多“突发慢查询”其实早有征兆——比如某个表的blocks_write突然飙升，可能意味着新上线的功能在疯狂写日志。

这篇文章的作者来自Crunchy Data，这是全球领先的PostgreSQL商业公司，为NASA、美国国防部、财富500强提供企业级PostgreSQL解决方案。联合作者Elizabeth Christensen是资深数据库架构师，专注PostgreSQL性能优化十余年。他们每天处理的都是PB级数据、百万级QPS的真实战场，所以给出的建议全是实战干货，没有一句空话。

总之，读写负载分析不是DBA的专属技能，而是每个后端开发者都该掌握的基本功。花10分钟跑个SQL，可能省下你三天的救火时间。数据库不玄学，数据说了算！

90%的PostgreSQL性能问题，都源于你没搞清这一点！