本文记录了笔者对PostgreSQL优化的观点与思路，仅供参考！
PostgreSQL: Documentation: 18: Part III. Server Administration
【TODO】学习官方文档，并用自己的话整理成笔记。
硬件
CPU
内存
硬盘
重点关注硬盘的Q32T16下的性能表现，优先选择该情况下分数较高的硬盘。
需要准备3块硬盘，一块用来安装操作系统，一块用来挂载到数据库目录，一块用来挂载到数据备份目录。
如果有条件的话可以使用RAID10。
系统
操作系统
这里我们使用Debian来运行数据库。
参数配置
文件系统
数据盘和备份盘各自采用整盘单分区策略，创建分区时起始位置不小于 1MiB（2048扇区） 确保物理扇区对齐（4K/8K/16K），文件系统优先选用XFS；系统盘无特殊要求，使用默认的ex4即可。
在 Debian 默认分区方案中，安装程序会在系统盘末尾创建独立 swap 分区。当使用 LVM 或虚拟磁盘（如 LVM thin pool、云平台弹性块存储）时，该 swap 分区将阻碍对系统盘末端的空间扩展，导致扩容操作复杂化。
XFS 还是 EXT4？
根据网上找到的测试数据，XFS各项数据都比较不错。
Bcachefs, Btrfs, EXT4, F2FS & XFS File-System Performance On Linux 6.15 - Phoronix
磁盘分区以及格式化命令参考
# # 安装xfs
# apt-get install xfsprogs
# # 使用fdisk -l 确认要分区的硬盘
# fdisk -l
......
Disk /dev/sdc: 60 GiB, 64424509440 bytes, 125829120 sectors
Disk model: Virtual Disk
Units: sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 4096 bytes
I/O size (minimum/optimal): 4096 bytes / 4096 bytes
# fdisk /dev/sdc # 根据提示进行操作，
# # 格式化成xfs，正在寻找官方是否有推荐配置
# mkfs.xfs /dev/sdc1
meta-data=/dev/sdc1 isize=512 agcount=4, agsize=3932032 blks
= sectsz=4096 attr=2, projid32bit=1
= crc=1 finobt=1, sparse=1, rmapbt=0
= reflink=1 bigtime=1 inobtcount=1 nrext64=0
data = bsize=4096 blocks=15728128, imaxpct=25
= sunit=0 swidth=0 blks
naming =version 2 bsize=4096 ascii-ci=0, ftype=1
log =internal log bsize=4096 blocks=16384, version=2
= sectsz=4096 sunit=1 blks, lazy-count=1
realtime =none extsz=4096 blocks=0, rtextents=0
Discarding blocks...Done.
# # 数据盘
# mkfs.xfs /dev/sdb1
meta-data=/dev/sdb1 isize=512 agcount=4, agsize=1965952 blks
= sectsz=4096 attr=2, projid32bit=1
= crc=1 finobt=1, sparse=1, rmapbt=0
= reflink=1 bigtime=1 inobtcount=1 nrext64=0
data = bsize=4096 blocks=7863808, imaxpct=25
= sunit=0 swidth=0 blks
naming =version 2 bsize=4096 ascii-ci=0, ftype=1
log =internal log bsize=4096 blocks=16384, version=2
= sectsz=4096 sunit=1 blks, lazy-count=1
realtime =none extsz=4096 blocks=0, rtextents=0
Discarding blocks...Done.
# # 确认硬盘uuid，为后续自动挂载做准备
# blkid
# # fstab参考
# PostgreSQL Data
UUID=4de7e829-acc7-40bb-a522-f1b6aa929f72 /var/lib/postgresql xfs defaults,noatime,logbufs=8,logbsize=32k,noquota 0 2
# PostgreSQL Backup
UUID=a3992d35-a9fd-46e9-92c1-f4264063fccf /pg_backup xfs defaults,noatime,logbufs=8,logbsize=32k,noquota 0 2fdisk 部分命令备注
xfs参数备注
按照红帽官方对xfs的说明，默认配置已适用于大多数工作负载。
第 3 章 XFS 文件系统 | 存储管理指南 | Red Hat Enterprise Linux | 7 | Red Hat Documentation
xfs挂载选项
性能调优指南 | Red Hat Enterprise Linux | 7 | Red Hat Documentation
离线安装
部分服务器无法连接互联网，这部分章节记录如何在离线状态下安装Debian、PostgreSQL以及其他需要的软件。
系统配置
参数配置
TODO
日志清理
TODO
数据库配置
参数配置
请将配置单独创建于 conf.d 目录内，切勿直接修改主配置文件。
#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------
# - Connection Settings -
# 此配置 '*' 仅适用于测试环境。
# 为保障安全，请根据实际需求将其修改为指定的 IP 地址，例如：'192.168.1.100'
listen_addresses = '*'
port = 5432
max_connections = 1000
#------------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#------------------------------------------------------------------------------
# - Memory -
# 根据自己的实际情况进行设置，一般默认为系统内存的25%，合理设置该值可以降低读写磁盘的负载，可以通过缓存命中率来判断是否需要调整
shared_buffers = 4GB # min 128kB
# (change requires restart)
# 这里需要根据是否有频繁使用临时文件来判断是否需要增加，合理设置该值可以确保排序或哈希等操作是在内存中进行
work_mem = 64MB # min 64kB
# 建议设置为大于work_men, 合理设置该值可以加快清理和还原数据库转储的性能
maintenance_work_mem = 128MB # min 1MB
#------------------------------------------------------------------------------
# WRITE-AHEAD LOG
#------------------------------------------------------------------------------
# - Archiving -
archive_mode = on
archive_command = 'test ! -f /pg_backup/wal/%f && cp %p /pg_backup/wal/%f'
#------------------------------------------------------------------------------
# REPORTING AND LOGGING
#------------------------------------------------------------------------------
# - What to Log -
log_timezone = 'Asia/Shanghai'
#------------------------------------------------------------------------------
# CLIENT CONNECTION DEFAULTS
#------------------------------------------------------------------------------
# - Locale and Formatting -
timezone = 'Asia/Shanghai'
shared_preload_libraries = 'pg_stat_statements'
# pg_stat_statements.track = none日志清理
systemd通过/usr/bin/pg_ctlcluster去启动pg_ctl，其中包括了日志配置的读取与设置。
使用pg_lsclusters命令可以快速确认目录在什么位置。
在默认配置下，日志文件位于/var/log/postgresql，日志清理由logrotate来进行。
# /etc/logrotate.d/postgresql-common 默认配置
/var/log/postgresql/*.log {
weekly
rotate 10
copytruncate
delaycompress
compress
notifempty
missingok
su root root
}默认配置会保留最多10个归档文件，但是不会清理旧版本的日志，可以通过设置maxage 30来清理超出30天的文件。
常用SQL语句
重置统计数据
-- 会重置pg_stat_database等表的统计数据
SELECT pg_stat_reset();检查数据库缓存命中率
-- 检查shared_buffers是否合理
select
datname,
round(blks_hit::numeric / (blks_hit + blks_read) * 100, 5) AS hit_ratio
FROM pg_stat_database
where blks_hit + blks_read <> 0;
-- 表级缓存命中率
SELECT
relname,
round(heap_blks_hit::numeric / NULLIF(heap_blks_hit + heap_blks_read, 0) * 100, 5) AS hit_ratio
FROM pg_statio_user_tables
ORDER BY hit_ratio ASC NULLS LAST;检查是否频繁使用临时文件
-- 检查work_mem是否合理
SELECT
queryid,
calls,
temp_blks_read,
temp_blks_written
FROM pg_stat_statements
WHERE temp_blks_written > 0
ORDER BY temp_blks_written DESC
LIMIT 10;查看当前正在执行的SQL
SELECT
pid, usename, datname, state, query, backend_start, query_start
FROM pg_stat_activity WHERE state <> 'idle';查看当前持锁情况
SELECT
pg_stat_activity.pid,
pg_stat_activity.query,
pg_locks.locktype,
pg_locks.mode,
pg_locks.granted
FROM pg_stat_activity
JOIN pg_locks ON pg_stat_activity.pid = pg_locks.pid
ORDER BY pg_stat_activity.pid;查看当前参数值
SHOW shared_buffers;
SHOW work_mem;
SHOW maintenance_work_mem;
SHOW effective_cache_size;性能测试
根据 OpenBenchmarking.org 上发布的 PostgreSQL pgbench 基准测试结果，在指定硬件环境下，当配置为 Scaling Factor 1000、并发客户端数 1000、并采用读写混合（Read-Write）模式时，系统达到了约 89,738+/- 1,238 TPS 的事务处理性能。
我自己也进行了一些测试，但由于是在虚拟机环境中运行的，结果仅供参考。
机器1
CPU：Intel(R) Core(TM) i5-10400 CPU @ 2.90GHz * 12
内存：2048M
硬盘：系统盘30G（ext4）、数据盘60G（xfs）、备份盘60G（xfs）
宿主机：Windows Server 2022 Hyper-V
宿主机硬盘：Samsung SSD 980 1TB NTFS
# 2025年7月11日 参数：-c 12 -j 12 -T 1200 -l -M prepared -P 10 scale：10 数据盘：xfs 备份盘：xfs
pgbench (17.4, server 17.5 (Debian 17.5-1.pgdg120+1))
starting vacuum...end.
progress: 10.0 s, 2140.3 tps, lat 5.562 ms stddev 2.434, 0 failed
progress: 20.0 s, 2206.8 tps, lat 5.430 ms stddev 2.331, 0 failed
progress: 30.0 s, 2246.6 tps, lat 5.339 ms stddev 2.313, 0 failed
progress: 40.0 s, 2223.4 tps, lat 5.393 ms stddev 2.322, 0 failed
progress: 50.0 s, 2267.7 tps, lat 5.288 ms stddev 2.285, 0 failed
progress: 60.0 s, 2246.2 tps, lat 5.338 ms stddev 2.415, 0 failed
progress: 70.0 s, 2243.3 tps, lat 5.343 ms stddev 2.344, 0 failed
progress: 80.0 s, 2226.5 tps, lat 5.386 ms stddev 2.280, 0 failed
progress: 90.0 s, 2238.3 tps, lat 5.355 ms stddev 2.309, 0 failed
progress: 100.0 s, 1809.9 tps, lat 6.625 ms stddev 2.940, 0 failed
progress: 110.0 s, 1680.5 tps, lat 7.139 ms stddev 3.358, 0 failed
progress: 120.0 s, 2053.7 tps, lat 5.839 ms stddev 2.911, 0 failed
progress: 130.0 s, 2074.5 tps, lat 5.781 ms stddev 2.815, 0 failed
progress: 140.0 s, 2142.9 tps, lat 5.596 ms stddev 2.811, 0 failed
progress: 150.0 s, 2214.0 tps, lat 5.415 ms stddev 2.351, 0 failed
progress: 160.0 s, 2292.7 tps, lat 5.229 ms stddev 2.199, 0 failed
progress: 170.0 s, 2236.7 tps, lat 5.359 ms stddev 2.290, 0 failed
progress: 180.0 s, 2187.0 tps, lat 5.484 ms stddev 2.405, 0 failed
progress: 190.0 s, 2237.4 tps, lat 5.359 ms stddev 2.291, 0 failed
progress: 200.0 s, 2260.2 tps, lat 5.305 ms stddev 2.306, 0 failed
progress: 210.0 s, 2149.1 tps, lat 5.579 ms stddev 2.607, 0 failed
progress: 220.0 s, 973.0 tps, lat 12.321 ms stddev 12.382, 0 failed
progress: 230.0 s, 1003.6 tps, lat 11.949 ms stddev 8.276, 0 failed
progress: 240.0 s, 851.2 tps, lat 14.100 ms stddev 16.773, 0 failed
progress: 250.0 s, 769.8 tps, lat 15.543 ms stddev 13.223, 0 failed
progress: 260.0 s, 1026.7 tps, lat 11.692 ms stddev 8.489, 0 failed
progress: 270.0 s, 617.4 tps, lat 19.378 ms stddev 24.050, 0 failed
progress: 280.0 s, 924.5 tps, lat 13.021 ms stddev 11.351, 0 failed
progress: 290.0 s, 1100.5 tps, lat 10.900 ms stddev 8.746, 0 failed
progress: 300.0 s, 1096.3 tps, lat 10.938 ms stddev 5.585, 0 failed
progress: 310.0 s, 1142.9 tps, lat 10.492 ms stddev 5.193, 0 failed
progress: 320.0 s, 1205.6 tps, lat 9.948 ms stddev 4.897, 0 failed
progress: 330.0 s, 923.7 tps, lat 12.979 ms stddev 9.896, 0 failed
progress: 340.0 s, 1215.3 tps, lat 9.871 ms stddev 4.829, 0 failed
progress: 350.0 s, 1148.5 tps, lat 10.439 ms stddev 6.006, 0 failed
progress: 360.0 s, 923.3 tps, lat 12.994 ms stddev 11.003, 0 failed
progress: 370.0 s, 1080.0 tps, lat 11.102 ms stddev 6.920, 0 failed
progress: 380.0 s, 1011.7 tps, lat 11.805 ms stddev 9.138, 0 failed
progress: 390.0 s, 992.4 tps, lat 12.137 ms stddev 8.217, 0 failed
progress: 400.0 s, 1208.6 tps, lat 9.920 ms stddev 4.999, 0 failed
progress: 410.0 s, 1014.7 tps, lat 11.820 ms stddev 8.975, 0 failed
progress: 420.0 s, 1135.4 tps, lat 10.563 ms stddev 5.473, 0 failed
progress: 430.0 s, 1236.7 tps, lat 9.695 ms stddev 4.450, 0 failed
progress: 440.0 s, 1150.0 tps, lat 10.428 ms stddev 5.477, 0 failed
progress: 450.0 s, 1128.3 tps, lat 10.628 ms stddev 5.402, 0 failed
progress: 460.0 s, 1201.5 tps, lat 9.982 ms stddev 4.678, 0 failed
progress: 470.0 s, 1190.5 tps, lat 10.075 ms stddev 4.771, 0 failed
progress: 480.0 s, 1150.9 tps, lat 10.416 ms stddev 5.219, 0 failed
progress: 490.0 s, 1025.3 tps, lat 11.698 ms stddev 8.745, 0 failed
progress: 500.0 s, 977.1 tps, lat 12.274 ms stddev 9.297, 0 failed
progress: 510.0 s, 1149.1 tps, lat 10.436 ms stddev 5.320, 0 failed
progress: 520.0 s, 1188.2 tps, lat 10.087 ms stddev 5.747, 0 failed
progress: 530.0 s, 1141.7 tps, lat 10.510 ms stddev 5.354, 0 failed
progress: 540.0 s, 1136.1 tps, lat 10.558 ms stddev 5.346, 0 failed
progress: 550.0 s, 1206.7 tps, lat 9.937 ms stddev 4.902, 0 failed
progress: 560.0 s, 1099.1 tps, lat 10.909 ms stddev 5.742, 0 failed
progress: 570.0 s, 987.2 tps, lat 12.153 ms stddev 9.503, 0 failed
progress: 580.0 s, 992.3 tps, lat 12.081 ms stddev 8.801, 0 failed
progress: 590.0 s, 986.3 tps, lat 12.164 ms stddev 10.079, 0 failed
progress: 600.0 s, 1218.6 tps, lat 9.839 ms stddev 4.800, 0 failed
progress: 610.0 s, 1123.8 tps, lat 10.668 ms stddev 6.602, 0 failed
progress: 620.0 s, 1184.2 tps, lat 10.132 ms stddev 5.131, 0 failed
progress: 630.0 s, 1175.7 tps, lat 10.202 ms stddev 4.960, 0 failed
progress: 640.0 s, 1121.0 tps, lat 10.696 ms stddev 5.958, 0 failed
progress: 650.0 s, 1204.7 tps, lat 9.955 ms stddev 4.715, 0 failed
progress: 660.0 s, 1183.2 tps, lat 10.134 ms stddev 5.110, 0 failed
progress: 670.0 s, 1163.2 tps, lat 10.310 ms stddev 5.208, 0 failed
progress: 680.0 s, 1196.7 tps, lat 10.020 ms stddev 4.805, 0 failed
progress: 690.0 s, 1175.7 tps, lat 10.201 ms stddev 4.895, 0 failed
progress: 700.0 s, 1154.6 tps, lat 10.387 ms stddev 6.167, 0 failed
progress: 710.0 s, 1175.4 tps, lat 10.202 ms stddev 5.321, 0 failed
progress: 720.0 s, 1110.4 tps, lat 10.796 ms stddev 6.986, 0 failed
progress: 730.0 s, 1191.8 tps, lat 10.065 ms stddev 4.929, 0 failed
progress: 740.0 s, 1072.3 tps, lat 11.183 ms stddev 7.825, 0 failed
progress: 750.0 s, 1172.4 tps, lat 10.231 ms stddev 4.944, 0 failed
progress: 760.0 s, 1154.1 tps, lat 10.398 ms stddev 5.131, 0 failed
progress: 770.0 s, 1062.9 tps, lat 11.281 ms stddev 6.821, 0 failed
progress: 780.0 s, 1126.1 tps, lat 10.651 ms stddev 5.006, 0 failed
progress: 790.0 s, 1080.3 tps, lat 11.102 ms stddev 5.806, 0 failed
progress: 800.0 s, 1009.0 tps, lat 11.889 ms stddev 9.782, 0 failed
progress: 810.0 s, 1103.2 tps, lat 10.873 ms stddev 5.231, 0 failed
progress: 820.0 s, 815.3 tps, lat 13.145 ms stddev 43.959, 0 failed
progress: 830.0 s, 677.5 tps, lat 19.586 ms stddev 141.098, 0 failed
progress: 840.0 s, 1066.0 tps, lat 11.228 ms stddev 6.152, 0 failed
progress: 850.0 s, 1037.1 tps, lat 11.581 ms stddev 8.307, 0 failed
progress: 860.0 s, 978.2 tps, lat 12.269 ms stddev 7.811, 0 failed
progress: 870.0 s, 911.7 tps, lat 13.160 ms stddev 12.395, 0 failed
progress: 880.0 s, 1030.6 tps, lat 11.530 ms stddev 7.291, 0 failed
progress: 890.0 s, 1017.0 tps, lat 11.905 ms stddev 8.922, 0 failed
progress: 900.0 s, 1116.6 tps, lat 10.744 ms stddev 6.240, 0 failed
progress: 910.0 s, 1060.3 tps, lat 11.310 ms stddev 6.500, 0 failed
progress: 920.0 s, 1011.9 tps, lat 11.852 ms stddev 7.454, 0 failed
progress: 930.0 s, 1077.8 tps, lat 11.130 ms stddev 5.731, 0 failed
progress: 940.0 s, 1068.5 tps, lat 11.227 ms stddev 7.155, 0 failed
progress: 950.0 s, 983.2 tps, lat 12.196 ms stddev 9.020, 0 failed
progress: 960.0 s, 1108.4 tps, lat 10.819 ms stddev 5.805, 0 failed
progress: 970.0 s, 1089.5 tps, lat 11.012 ms stddev 5.907, 0 failed
progress: 980.0 s, 1008.3 tps, lat 11.897 ms stddev 8.072, 0 failed
progress: 990.0 s, 1109.1 tps, lat 10.812 ms stddev 6.515, 0 failed
progress: 1000.0 s, 1140.9 tps, lat 10.514 ms stddev 7.008, 0 failed
progress: 1010.0 s, 991.4 tps, lat 12.097 ms stddev 7.769, 0 failed
progress: 1020.0 s, 920.5 tps, lat 13.032 ms stddev 68.246, 0 failed
progress: 1030.0 s, 1176.5 tps, lat 10.192 ms stddev 4.977, 0 failed
progress: 1040.0 s, 1112.0 tps, lat 10.785 ms stddev 5.422, 0 failed
progress: 1050.0 s, 736.9 tps, lat 12.665 ms stddev 35.240, 0 failed
progress: 1060.0 s, 645.7 tps, lat 22.705 ms stddev 248.597, 0 failed
progress: 1070.0 s, 1128.1 tps, lat 10.634 ms stddev 7.118, 0 failed
progress: 1080.0 s, 1187.0 tps, lat 10.102 ms stddev 4.931, 0 failed
progress: 1090.0 s, 917.6 tps, lat 13.075 ms stddev 41.871, 0 failed
progress: 1100.0 s, 1083.3 tps, lat 11.074 ms stddev 6.877, 0 failed
progress: 1110.0 s, 1137.5 tps, lat 10.542 ms stddev 5.504, 0 failed
progress: 1120.0 s, 1062.3 tps, lat 11.289 ms stddev 6.597, 0 failed
progress: 1130.0 s, 848.6 tps, lat 11.136 ms stddev 15.285, 0 failed
progress: 1140.0 s, 764.7 tps, lat 19.021 ms stddev 148.252, 0 failed
progress: 1150.0 s, 1101.2 tps, lat 10.892 ms stddev 6.475, 0 failed
progress: 1160.0 s, 1137.5 tps, lat 10.546 ms stddev 6.242, 0 failed
progress: 1170.0 s, 666.5 tps, lat 15.231 ms stddev 72.784, 0 failed
progress: 1180.0 s, 1062.4 tps, lat 13.027 ms stddev 65.336, 0 failed
progress: 1190.0 s, 897.8 tps, lat 13.016 ms stddev 46.869, 0 failed
progress: 1200.0 s, 124.3 tps, lat 99.059 ms stddev 749.082, 0 failed
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 10
query mode: prepared
number of clients: 12
number of threads: 12
maximum number of tries: 1
duration: 1200 s
number of transactions actually processed: 1488616
number of failed transactions: 0 (0.000%)
latency average = 9.667 ms
latency stddev = 33.417 ms
initial connection time = 74.341 ms
tps = 1240.536708 (without initial connection time)
# 不知道为什么，tps为什么会突然降这么多
机器2
CPU：Intel(R) Core(TM) i5-8400 CPU @ 2.80GHz * 6
内存：128M ~ 2048M
硬盘：系统盘30G（ext4）、数据盘30G（xfs，reflink=1）、备份盘60G（xfs，reflink=1)
宿主机：Windows Server 2025 Hyper-V
宿主机硬盘：Samsung SSD 980 1TB NTFS
# 2025年7月11日 参数：-c 6 -j 6 -T 1200 -l -M prepared -P 60 scale：10 数据盘：xfs 备份盘：xfs