PostgreSQL 单机单表性能分析

2025-04-22

prog

db

/

linux

基于 Hetzner 4c8g 8c16g 两档 arm64 服务器实际基准与社区经验，梳理 PostgreSQL 在单表容量、内存缓存、CPU 并行、顺序/随机磁盘 I/O 等维度的性能瓶颈与调优策略。

实验与估算环境#

机型与基准#

规格	Geekbench 6 单核	Geekbench 6 多核	存储
4c 8 GB	≈ 1100	≈ 3600	NVMe SSD (单盘)
8c 16 GB	≈ 2200	≈ 7200	NVMe SSD (同盘型)

pgbench 填充 scale=500 (~ 75 GB) 与 scale=200 (~ 30 GB) 两组数据进行点查、范围查、顺序扫描与并行聚合测试；并用 fio 跑顺序 128 KiB 与随机 4 KiB 的读写基准。

内存：shared_buffers 与“热数据”上限#

为何推荐 25‑40 % RAM#

PostgreSQL 采用 8 KiB 页并优先走 shared_buffers 与 OS 页缓存；官方 Wiki 建议 shared_buffers ≈ 25 % RAM，再预留一倍以上给 OS 缓存。Tembo 的内存公式显示在 16 GB 机器用 4 GB shared_buffers 能给 100 并发留下 ~88 MB work_mem 空间。

“能装多少行”粗算#

假设行长 200 B，则

可缓存行数 ≈ (shared_buffers + OS cache) / 行长
4c8g: (2 GB + 4 GB) / 200 B ≈ 30 M
8c16g: (4 GB + 12 GB) / 200 B ≈ 60 M

缓存命中时过滤速率可达单核 5‑10 M rows/s；超出该行数后随机 IOPS 迅速成为主瓶颈。

CPU：并行查询与维护#

并行查询收益#

Crunchy Data 基准表明，在 8 核机器把 max_parallel_workers_per_gather 提到 4，可获得 1.7‑2.2× 的聚合加速。Citus 的实验亦指出并行能把 1‑2 M rows/s 的聚合提升至近线性扩张。

autovacuum & checkpoint#

更多 CPU 让 autovacuum 与并行 VACUUM 有余力及时清理；文档建议在大表把 autovacuum_vacuum_scale_factor 下调至 0.07‑0.1 避免膨胀。若 bgwriter 配置不当，会出现写放大与卡顿。

磁盘 I/O：顺序吞吐 vs 随机 IOPS#

PostgreSQL I/O 分布#

组件	块大小	I/O 性质
WAL	16 MiB 段	顺序写
查询/索引	8 KiB (4 KiB FS)	随机读
autovacuum / checkpoint	8 KiB	随机写
COPY / 备份	≥ 1 MiB	顺序读写

AWS EBS 文档强调随机小 I/O 会迅速耗尽 IOPS 额度。Cloudflare fio 测试显示同一 NVMe 顺序 128 KiB 读 14 GB/s，而随机 4 KiB 仅 300‑600 k IOPS (~2 GB/s) ，差距 5‑10 倍。

哪个先瓶颈？#

OLTP — 随机 IOPS 先满；查询延迟与 autovacuum 周期抖动。
备份/导入 — 顺序带宽受限；CPU 得不到喂饱。

容量“经验阈值”对比#

规格	点查/索引安全行数	扫描/聚合拐点	主要瓶颈
4c 8g	≈ 50 M	≈ 30 M	随机 IOPS
8c 16g	80‑120 M	≈ 60 M	随机 IOPS→顺序带宽

数据综合自 Citus 行扫描速率、Cloudflare fio 差异、以及社区 pgbench 与 fio 对比。

调优清单#

postgresql.conf 起步（16 GB RAM）#

shared_buffers      = 4GB
effective_cache_size= 12GB
work_mem            = 16MB
maintenance_work_mem= 1GB
max_worker_processes= 8
max_parallel_workers= 8
max_parallel_workers_per_gather = 4
checkpoint_completion_target = 0.9
autovacuum_vacuum_scale_factor = 0.07

BRIN 索引在 100 M 行以上可将内存占用降到 kB 级。

fio 快速脚本#

fio --name=rand4k --iodepth=32 --rw=randread --bs=4k  --size=4G --numjobs=4 --runtime=60 --time_based
fio --name=seq128k --iodepth=32 --rw=read     --bs=128k --size=4G --numjobs=1 --runtime=60 --time_based

结果对照 pg 负载判断先升级 IOPS 还是带宽。

何时仍需分区或分片？#

单表 > 1 亿行且热点页不再留在缓存；
随机 IOPS 已打满、高延迟；
聚合扫描占主流且顺序带宽不足。

届时可用时间分区 + BRIN 或采用 Citus/Timescale 水平分片。

结语#

4c8g 机型在 30‑50 M 行内仍可保持毫秒级点查；再大必须关注 autovacuum 与 IOPS。
升级到 8c16g 把“安全容量”提升到 ~1 亿行，并借助并行查询显著减少聚合响应时间。
真正的天花板是磁盘随机 IOPS；带宽和 CPU 并不会弥补缓存未命中的碎片读写。
持续监控 pg_stat_bgwriter、pg_stat_io 与 fio 基准，按需在内存 → IOPS → 分区三层次迭代，才是 PostgreSQL 扩容的正确顺序。