postgresql -- 性能参数&视图

PostgreSQL 的默认配置目标是“在任何环境下都能启动”，而非“在任何环境下都能高性能运行”。

1. shared_buffers —— 数据库的缓存心脏

shared_buffers = 16GB          # 物理内存的 25%
huge_pages = try               # 优先申请大页，不足时回退普通页

作用：PostgreSQL 最重要的共享内存区域，用于缓存数据页。与 Oracle 的 Buffer Cache 作用类似，但 PG 依赖操作系统页缓存作为二级缓存，构成独特的分层缓存架构。

配置原则：

• 起点：物理内存的 25%

• 读多写少、热数据集较大 → 可逐步调高至 30–40%，配合 pg_buffercache 监控实际使用率

• 写入密集型负载 → 建议保持 25%，将更多内存留给操作系统页缓存以优化写入缓冲

大页配置说明：

Linux 专用数据库服务器建议配置 HugePages，可显著降低 TLB 压力。容器环境（Docker / Kubernetes）需额外验证 HugePages 支持情况，部分容器运行时默认未开启大页。

# 查看大页状态
cat /proc/meminfo | grep Huge
# 须确保 HugePages_Total > 0 且 HugePages_Free > 0
# 若 HugePages_Total = 0，需在操作系统层面配置，如 sysctl vm.nr_hugepages

与 MySQL 的架构差异——为何不能设为 75%？

PostgreSQL 采用双层缓存架构：shared_buffers + 操作系统页缓存。当数据从磁盘读取时，会同时缓存在两个层级。若 shared_buffers 设置过大，同一数据页可能重复驻留两份，造成严重的内存浪费。

MySQL InnoDB 通常将更多内存集中分配给 Buffer Pool，因此常见经验值为物理内存的 60%～ 75%。但是否使用操作系统页缓存取决于 innodb_flush_method 等配置。在 Linux + O_DIRECT 场景下可基本绕过 OS Cache，而默认配置下仍会存在一定程度的双重缓存。

-- 查看表缓存命中率，若已稳定在健康阈值以上，继续增大 shared_buffers 收益有限
SELECT
    sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read))::float AS table_cache_hit_ratio
FROM pg_statio_user_tables;

-- 索引缓存命中率同样关键——命中率下降说明索引块正被频繁换出，可能需调大缓存
SELECT
    sum(idx_blks_hit) / (sum(idx_blks_hit) + sum(idx_blks_read))::float AS index_cache_hit_ratio
FROM pg_statio_user_indexes;

缓存命中率健康阈值（参考值）：

2. work_mem —— 排序和哈希操作的内存

work_mem = 16MB                # 全局保守值

作用：指定单个排序、哈希操作（如 ORDER BY、DISTINCT、HASH JOIN）所能使用的最大内存。一旦超过此阈值，操作将溢出到磁盘临时文件，性能显著下降。

核心认知：

• 每个操作独立分配一块内存，非每条 SQL 共用

• 一个复杂查询可能包含多个排序节点和多个 Hash Join 节点，内存占用呈倍数叠加

• 极端理论峰值 ≈ work_mem × 并发连接数 × 每连接操作数，但实际 OLTP 场景下多数查询不触发排序/哈希分配，真实峰值远低于此理论值

-- 检查是否在使用临时文件——排序或哈希操作内存不足的标志
SELECT datname, temp_files, temp_bytes
FROM pg_stat_database
WHERE datname = current_database();
-- 若 temp_files 持续高速增长，说明 work_mem 不足

正确策略：全局守小，按需放大

-- 对分析型用户单独调大
ALTER USER analyst SET work_mem = '256MB';

-- 对特定大查询会话临时调大
SET work_mem = '512MB';
SELECT ...;  -- 执行大查询
RESET work_mem;  -- 用完立刻恢复，避免影响同会话后续轻量操作

3. maintenance_work_mem 与 autovacuum_work_mem

maintenance_work_mem

maintenance_work_mem = 512MB   # VACUUM、CREATE INDEX、REINDEX 等使用

注意：自 PostgreSQL 9.4 起，autovacuum worker 实际受 autovacuum_work_mem 控制，默认未设置时回退到此值。手工执行的维护操作会足额分配。

-- 手动对大表创建索引时临时调高
SET maintenance_work_mem = '4GB';
CREATE INDEX CONCURRENTLY idx_large ON large_table(col);
RESET maintenance_work_mem;

autovacuum_work_mem —— 大表维护的隐藏瓶颈

autovacuum_work_mem = 1GB      # PG 9.4+ 可用，大表环境建议单独设置

大型表 VACUUM 时，若 autovacuum_work_mem 过小（默认回退到 maintenance_work_mem），可能导致：

• 索引清理需要更多轮次

• VACUUM 总耗时显著拉长

• 死元组长时间无法回收

对于 TB 级别大表，单独设置 autovacuum_work_mem 通常比继续提高 maintenance_work_mem 更有效。

-- 查看各表的 VACUUM 历史和死元组情况
SELECT schemaname, relname, n_dead_tup, last_vacuum, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

4. effective_cache_size —— 告知优化器可用缓存总量

effective_cache_size = 48GB    # 物理内存的 75%

作用：不分配任何物理内存，仅作为提示值影响查询优化器的计划选择。

effective_cache_size 表示优化器认为可用于缓存数据页的总缓存容量，包括：

• PostgreSQL shared_buffers

• 操作系统页缓存（Page Cache）

实用取值方法：

• 专用数据库服务器：建议设置为物理内存的 50%～ 75%

• 混合部署服务器：应扣除其他服务实际占用内存后估算

不要简单根据当前时刻 free -h 的 buff/cache 数值直接计算，因为该值会随系统运行状态动态变化。

IO 与查询规划参数

5. random_page_cost —— 随机 IO 代价系数（SSD 必须修改）

# NVMe SSD（推荐保守起步）
random_page_cost = 1.5
# SATA SSD
random_page_cost = 1.5–2.0
# HDD（默认 4.0）
random_page_cost = 4.0

作用：告知优化器随机磁盘访问相对于顺序访问的代价倍数。顺序访问的基准值 seq_page_cost 默认为 1.0。

为何 SSD 环境必须调低？默认值 4.0 是机械硬盘时代的经验值。在 NVMe SSD 上，随机读取与顺序读取的延迟差距极小，沿用默认值会导致优化器倾向于选择全表扫描而放弃更优的索引扫描。

⚠️ 必须执行的验证步骤：

-- 1. 先更新目标表的统计信息
ANALYZE target_table;

-- 2. 对比两种计划的真实执行性能
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;
-- 重点观察 actual time 与 buffers 的实际读/命中情况，而非仅看计划形状

风险提示：NVMe 环境下设为 1.0 过于激进，会使优化器认为随机访问与顺序访问代价完全一致。建议从 1.5 起步，结合压测结果逐步微调。

6. effective_io_concurrency —— 并发 IO 能力

# NVMe SSD
effective_io_concurrency = 100
# SATA SSD
effective_io_concurrency = 50–100
# HDD
effective_io_concurrency = 2

作用：控制 Bitmap Heap Scan 节点可同时发起的异步 IO 请求数量。

对于高性能企业级 NVMe，实际生产环境中经常设置 200、256 甚至 300。具体取值应结合存储队列深度、内核异步 IO 能力和实际压测结果来持续验证。设置超过硬件最大队列深度时，超出部分会被内核排队，表现为收益递减但通常不会造成负面拥堵。

-- 若业务负载中 Bitmap Heap Scan 极少出现，调优此参数收益不大
SELECT schemaname, relname, seq_scan, idx_scan
FROM pg_stat_user_tables
WHERE seq_scan > 0;

WAL 与 Checkpoint 参数

7. wal_buffers —— WAL 写入缓冲区

wal_buffers = -1    # 自动计算，通常无需修改

wal_buffers = -1 时，PostgreSQL 自动计算规则为：

wal_buffers = shared_buffers / 32

同时受以下硬性限制：

• 最小值：64KB

• 最大值：16MB

因此，只有在 shared_buffers 极小（约小于 2MB）时，才会退化为最小值 64KB。现代生产环境通常保持默认值即可。

-- 在大事务或逻辑复制等场景中，监控 WAL 写入情况
SELECT wal_records, wal_fpi, wal_bytes FROM pg_stat_wal;
-- 结合系统层监控综合判断：
-- iostat：观察磁盘写入延迟与队列深度
-- vmstat：观察 IO 阻塞进程数量
-- PG16+ 可使用 pg_stat_io 获得更精细的 WAL 写入统计

手动调大 wal_buffers（如 32–64MB）应在有明确监控证据的前提下进行，无依据的调大只会浪费内存。

8. checkpoint 相关参数 —— 平衡写入平滑度与恢复时间

checkpoint_timeout = 10min
checkpoint_completion_target = 0.9
max_wal_size = 根据 WAL 产生速率计算
min_wal_size = 4GB
checkpoint_warning = 30s        # 默认值，建议保留

PostgreSQL 默认已启用 checkpoint_warning = 30s，生产环境建议保留该默认值。若两次检查点间隔小于 30 秒，PostgreSQL 将输出警告日志。

核心权衡：

• 间隔较长 → 脏页写入更平滑，IO 尖峰更少，但崩溃恢复时间更长

• 间隔较短 → IO 尖峰频繁，但崩溃恢复更快

max_wal_size 的计算方法：

-- 先估算 WAL 产生速率
SELECT
    pg_wal_lsn_diff(pg_current_wal_lsn(), '0/0') / 1024.0 / 1024 / 1024 AS wal_gb_since_start;
-- 或持续监控 pg_stat_wal 的 wal_bytes 增量

经验上，max_wal_size 应明显大于一个 checkpoint 周期内产生的 WAL 量。实际生产环境通常预留 1.5 ～ 3 倍缓冲空间，避免 WAL 容量提前触发 checkpoint。例如：预估一个周期产生 20GB WAL，则 max_wal_size 可设为 30 ～ 60GB。

⚠️ max_wal_size 设置过小，会导致 WAL 频繁触及上限而强制触发检查点，使 checkpoint_timeout 设置失效。

DBA 必查监控项：

-- PG16+ 推荐使用 pg_stat_io，可精细区分各类 IO 来源
SELECT backend_type, io_context, writes, fsyncs
FROM pg_stat_io
WHERE backend_type IN ('background writer', 'checkpointer')
ORDER BY writes DESC;

-- pg_stat_bgwriter 主要用于兼容历史监控体系
SELECT checkpoints_timed, checkpoints_req,
       checkpoint_write_time, checkpoint_sync_time
FROM pg_stat_bgwriter;

重点观察：如果 checkpoints_req 持续增长，说明 WAL 提前达到 max_wal_size，当前 checkpoint_timeout 实际上并未生效。此时应优先扩大 max_wal_size，而非继续提高 checkpoint_timeout。

checkpoint_completion_target = 0.9
-- 将脏页刷新分散到检查点间隔的 90% 时间内完成，避免最后时刻集中刷盘引发 IO 尖峰

配合 bgwriter 平滑写入曲线：

bgwriter_delay = 200ms            # 默认值，后台写入进程轮询间隔
bgwriter_lru_maxpages = 200       # 每轮最大刷盘页数，从默认 100 适度调大

bgwriter_lru_maxpages 决定脏页在被检查点集中刷写前由后台写入进程先行清理的速度。若 checkpoint_completion_target = 0.9 仍不足以平滑 IO，适当调高此值可进一步分散写入压力。

连接与并行参数

9. max_connections —— 连接数非越大越好

max_connections = 200

PostgreSQL 采用进程模型：每个连接 fork 一个独立进程，资源开销远大于线程模型的数据库。

连接数过高的代价：

• 进程上下文切换开销急剧上升

• 每连接额外占用 5–10MB 内存（含连接私有缓存）

• 锁竞争与快照管理开销增加

最佳实践：

# 数据库端：保守设置
max_connections = 100–300

# 前端：部署 PgBouncer 连接池

PgBouncer 配置示例：

pool_mode = transaction           # 事务级复用，兼容性最佳
default_pool_size = 20–50        # 按实际数据库承载能力调整，非固定值
max_client_conn = 1000            # 客户端到 PgBouncer 的最大连接数

通过连接池将数千客户端请求收敛为少量数据库连接，吞吐量不降反升。

10. parallel workers —— 并行查询

max_parallel_workers_per_gather = 2    # PG 14+ 默认值，通常无需修改
max_parallel_workers = 8
max_parallel_maintenance_workers = 4

关键关系：

• max_parallel_workers：全局并行 worker 进程池上限

• max_parallel_workers_per_gather：单个查询最多可从池中获取的 worker 数量

若系统中通常有 3–4 个查询同时请求并行执行，则 max_parallel_workers 至少应为 per_gather × 预期并发数，否则 worker 池会被耗尽，后续查询降级为串行执行。

验证并行是否实际生效：

EXPLAIN (ANALYZE, VERBOSE) SELECT ...;
-- 在计划节点中出现 "Workers Planned: X" 且 "Workers Launched: X" 且 X > 0，说明并行生效

11. synchronous_commit —— 写入持久性与性能的平衡

synchronous_commit = on        # 默认值，保证 ACID 的持久性

作用：控制事务提交时，是否需要等待 WAL 刷新到磁盘后才向客户端返回成功。

⚠️ off 或 remote_write 不影响数据最终持久化，只影响事务返回客户端的时机。off 模式下，崩溃时最多可能丢失 3 × wal_writer_delay（默认 200ms，即 600ms）窗口内的事务。

# 对延迟敏感的批量写入场景可全局设置
synchronous_commit = off

也可按会话级别精细控制：

SET synchronous_commit = off;
-- 执行批量导入或日志写入
RESET synchronous_commit;

JIT：OLTP 环境下需谨慎评估的“优化”

jit = on                        # PG 11+ 默认启用

JIT（Just-In-Time 编译）主要提升聚合计算、分析型查询、数据仓库场景的性能。

PG14 ～ 17 中 JIT 默认开启。对于高频短 SQL、高并发 OLTP 场景，JIT 编译开销可能超过直接执行解释器计划的代价，往往收益有限。

建议通过 pg_stat_statements 和 EXPLAIN ANALYZE 观察实际收益后决定，不要仅因为是 OLTP 就直接关闭：

1. 首先监控高耗时 SQL 的执行计划，观察 JIT 编译耗时占比

2. 如果确认 JIT 开销大于收益，可全局关闭：

jit = off

1. 若为混合负载，可会话级按需启用：

SET jit = on;
-- 执行分析型大查询
RESET jit;

pg_stat_statements：比 auto_explain 更重要的扩展

shared_preload_libraries = 'pg_stat_statements, auto_explain'

生产环境定位性能问题的优先级：

不要直接开启大量 auto_explain 日志，否则可能造成日志膨胀，尤其在 OLTP 高并发场景下。

-- 启用 pg_stat_statements
CREATE EXTENSION pg_stat_statements;

-- 快速定位 Top 5 高耗时 SQL
SELECT queryid, calls, mean_exec_time, total_exec_time, query
FROM pg_stat_statements
ORDERBY total_exec_time DESC
LIMIT5;

-- 定位消耗临时文件最多的 SQL
SELECT queryid, calls, temp_blks_written, query
FROM pg_stat_statements
ORDERBY temp_blks_written DESC
LIMIT5;

pg_stat_statements 是生产环境日常巡检和应急排查的第一入口，DBA 应确保该扩展始终启用。

PostgreSQL 16/17 IO 监控体系：pg_stat_io

PG16 开始引入 pg_stat_io 视图。相比 pg_stat_bgwriter 和 pg_stat_database，它可以更精细地观察各类 IO 操作的统计信息，已逐渐成为现代 PostgreSQL 调优的核心监控视图：

SELECT * FROM pg_stat_io;

pg_stat_io 的关键分类维度：

常用查询示例：

-- 按后端类型汇总 IO 读写量
SELECT backend_type,
       sum(reads) AS total_reads,
       sum(writes) AS total_writes,
       sum(extends) AS total_extends,
       sum(fsyncs) AS total_fsyncs
FROM pg_stat_io
GROUPBY backend_type
ORDERBY total_writes DESC;

-- 查看 autovacuum 的 IO 活动
SELECT io_context, reads, writes, extends, fsyncs
FROM pg_stat_io
WHERE backend_type = 'autovacuum worker';

-- 查看 WAL 写入统计
SELECT io_object, io_context, writes, fsyncs
FROM pg_stat_io
WHERE io_object = 'wal';

它能清晰展示 shared buffer 命中、WAL 写入、Checkpoint 写入、Backend 写入、Autovacuum 写入等各类 IO 的分布情况，帮助 DBA 在优化 IO 参数时做到有的放矢。pg_stat_bgwriter 建议作为兼容历史监控体系的补充视图。

5 个常见误区

误区一：shared_buffers 设为物理内存的 75%

❌ 错误：将 MySQL InnoDB 配置经验直接照搬到 PostgreSQL。

✅ 正解：PostgreSQL 采用双层缓存模型。64GB 内存服务器设为 16–24GB 更为合理。可借助 pg_buffercache 查看实际缓存使用率来精准调优。

误区二：不加区分地全局调小 autovacuum 参数

autovacuum_vacuum_scale_factor = 0.2  # 默认：20% 的表行变化才触发

一张 1000 万行的表需要 200 万行变更才会触发 VACUUM，这对大表过于迟缓。但全局调至 0.02 又会使小表被过度 VACUUM，浪费 IO 资源。

✅ 正解：全局保留默认值，仅对大表单独设置

ALTER TABLE large_table SET (
    autovacuum_vacuum_scale_factor = 0.01,
    autovacuum_analyze_scale_factor = 0.005,
    autovacuum_vacuum_cost_delay = 0      -- 详见下方注意事项
);

-- 对超级大表推荐使用阈值替代百分比
ALTER TABLE huge_table SET (
    autovacuum_vacuum_threshold = 50000,
    autovacuum_vacuum_scale_factor = 0
);

关于 autovacuum_vacuum_cost_delay = 0：

仅建议用于专用数据库服务器、SSD/NVMe 环境且已完成压测验证的场景。在共享存储或 IO 资源紧张的环境下，过于激进的 VACUUM 可能导致业务查询抖动、Checkpoint 延迟、磁盘队列积压。生产环境需谨慎评估。

误区三：work_mem 全局设置过大

❌ 错误：全局设置 256MB，以为"越大性能越好"。

✅ 正解：work_mem 是每个操作的上限，一个查询中多个排序/联接操作会叠加。全局保守设置 8–16MB，对特定用户或会话按需调大，用完即恢复。

误区四：max_connections 设得越大越"安全"

❌ 错误：为应对峰值，直接设置 2000 个连接。

✅ 正解：实测 2000 连接下的吞吐量远低于 200 连接 + PgBouncer 池化方案。控制数据库端连接数在 100–300，由连接池管理客户端突发流量。

误区五：看到 Seq Scan 就盲目加索引

❌ 错误：执行计划中出现全表扫描（Seq Scan）就直接建索引。

✅ 正解：

1. 先执行 ANALYZE 确保统计信息是最新的

2. 使用 EXPLAIN (ANALYZE, BUFFERS) 查看实际执行时间与 IO 情况

3. 若查询返回表中超过 20% 的行，Seq Scan 通常比索引回表更快

关于 20% 阈值的重要说明：20% 只是经验值。真实决策受 random_page_cost、effective_cache_size、数据分布、SSD/HDD 类型、表聚簇程度等因素共同影响。不要机械套用固定比例，而应以实际执行时间为准。

1. 关注 Buffers: shared hit 比例——若大部分是内存命中且执行时间可接受，保持现状

不要以计划形状替代实际性能数据作为判断依据。

十、调优方法论

参数调优永远排在 SQL 优化、索引设计、表结构设计之后。

如果一个 SQL 原本需要扫描 1 亿行数据，无论如何调整 shared_buffers、work_mem、random_page_cost，都无法获得数量级性能提升。

生产环境中，80% 以上的性能问题来自 SQL 与数据模型设计，而非 postgresql.conf 参数本身。

调优五步法

⚠️ 严禁一次性修改多个参数，出现问题后无法定位根因。

监控指标速查表

生产环境必备检查清单

安全底线（必须设置）

# 1. 空闲事务超时 —— 防止长时间空闲事务导致表膨胀
idle_in_transaction_session_timeout = 300000  # 5 分钟（毫秒）

# 2. 语句超时 —— 防止慢查询长期占用资源拖垮系统
statement_timeout = 300000  # 5 分钟（毫秒），按业务最长语句时间调整

# 3. 锁超时 —— 防止 DDL/DML 被长时间阻塞
lock_timeout = 60000  # 60 秒（毫秒）

这三个超时参数是生产环境必须配置的安全底线。未提交的事务中空闲的连接会持有锁并阻止 VACUUM 清理死元组，是生产环境最常见的表膨胀根源。

完整的 postgresql.conf 参考

# ============================================================
# PostgreSQL 14–17 生产环境配置参考（64GB 内存，NVMe SSD）
# ⚠️ 所有数值须结合自身负载通过压测验证
# ⚠️ shared_preload_libraries 中多个库以逗号分隔
# ============================================================

# ---------- 内存 ----------
shared_buffers = 16GB
huge_pages = try
work_mem = 16MB
maintenance_work_mem = 512MB
autovacuum_work_mem = 1GB
effective_cache_size = 48GB

# ---------- IO 代价 ----------
random_page_cost = 1.5
effective_io_concurrency = 100

# ---------- 连接 ----------
max_connections = 200

# ---------- WAL 与 Checkpoint ----------
wal_buffers = -1
checkpoint_timeout = 10min
checkpoint_completion_target = 0.9
max_wal_size = 60GB          # 须根据实际 WAL 产生速率计算
min_wal_size = 4GB
checkpoint_warning = 30s     # 默认值，建议保留

# ---------- 后台写入 ----------
bgwriter_delay = 200ms
bgwriter_lru_maxpages = 200

# ---------- 并行 ----------
max_parallel_workers_per_gather = 2
max_parallel_workers = 8
max_parallel_maintenance_workers = 4

# ---------- JIT ----------
jit = on                      # 建议通过 pg_stat_statements 评估后决策

# ---------- 写入持久性 ----------
synchronous_commit = on       # 允许少量数据丢失的场景可设为 off

# ---------- 超时（安全底线） ----------
idle_in_transaction_session_timeout = 300000
statement_timeout = 300000
lock_timeout = 60000

# ---------- 日志与扩展 ----------
log_min_duration_statement = 1000
shared_preload_libraries = 'pg_stat_statements, auto_explain'
auto_explain.log_min_duration = 1000
auto_explain.log_analyze = on
auto_explain.log_buffers = on

调优效果验证

# 初始化基准测试数据
pgbench -i -s 100 testdb

# 运行基准测试（32 客户端，8 线程，持续 5 分钟）
pgbench -c 32 -j 8 -T 300 testdb

# 对比调优前后的 TPS（含连接建立开销）和延迟分布

总结

PostgreSQL 没有万能参数 ，需根据具体机器配置，应用业务特点进行调整。

== PG18  的优化 ==

1. 连接池:别让 PostgreSQL 当保姆

症状: TPS 卡住不动,CPU 却很闲,延迟抖动厉害。

解决方案:

• 把 max_connections 控制在合理范围(200-400)

• 在 PostgreSQL 前面加 PgBouncer,用 transaction 模式

为什么有效: PostgreSQL 擅长执行查询,不擅长管理成千上万个空闲连接。先用连接池,再谈其他优化。

2. Autovacuum 调优:比那些神奇参数更重要

监控指标:

• 查看 pg_stat_user_tables 的 n_dead_tup

• 用 pg_total_relation_size vs pg_relation_size 检查膨胀

关键配置:

autovacuum_vacuum_scale_factor = 0.05autovacuum_analyze_scale_factor = 0.05autovacuum_max_workers = 6autovacuum_naptime = '10s'maintenance_work_mem = '2GB'

为什么有效: 及时的统计信息和更少的表膨胀,让每个查询计划都更聪明。

3. Checkpoint 和 WAL:让 I/O 变得可预测

平滑延迟的配置:

wal_compression = lz4max_wal_size = '8GB'checkpoint_timeout = '15min'synchronous_commit = on

为什么有效: 更大、更平缓的 checkpoint 和压缩的 WAL 减少了写入突刺。如果你用的是 NVMe,这个改动能明显降低 P99 延迟。

4. work_mem:更快的排序,更少的临时文件

经验法则: 按并发的排序/关联来设置,不是为单个查询设置。从 64MB 开始测试。

判断依据: 用 EXPLAIN (ANALYZE, BUFFERS) 看是否有临时文件溢出,如果有就提高 work_mem。

5. 并行查询:在该用的地方用

读取场景配置:

max_parallel_workers_per_gather = 4max_parallel_workers = 8max_worker_processes = 16

何时启用: 大范围扫描和聚合查询。

何时关闭: 大量微小语句的 OLTP — 并行启动成本可能超过收益。

6. JIT:分析场景开,OLTP 场景关

简单规则:

• 微查询的 OLTP → JIT 关闭

• 复杂表达式的分析 → JIT 开启(默认),实测验证

jit = onjit_above_cost = 200000  # OLTP 场景可以调高

7. 缓存三角:共享缓存、OS 缓存、有效缓存

配置示例:

shared_buffers = '8GB'        # RAM 的 25% 是安全起点effective_cache_size = '24GB' # 给规划器的提示,包含 OS 缓存

为什么重要: 合理的大小能告诉查询规划器,索引计划是否真的能保持热度。

8. 索引维护:比硬件升级更有效

反复验证有效的做法:

• 为热点读取添加覆盖索引(INCLUDE 列)

• 对时间追加表(日志、事件)使用 BRIN 索引

• 如果删除旧数据,按时间分区 — VACUUM 变得轻松

• 对倾斜谓词大胆使用部分索引

9. 查询优化:最后 20% 的工作,感觉像 80% 的提升

三个快速修复:

• 把 SELECT * 替换为具体列名

• 在应用层消灭意外的 N+1 查询

• 对重复查询使用预编译语句

10. 存储和内核:设置一次,长期受益

Linux 默认值检查清单:

• 文件系统:xfs 或 ext4,在 NVMe 上都很稳

• I/O 调度器:快速 SSD 用 none

• 大页:关闭透明大页,保证可预测性

• CPU 调速器:专用数据库节点用 performance

PostgreSQL 18 的实际改进

• 更智能的读取路径: PostgreSQL 18 在大扫描和混合流量下更稳定,不容易抖动。实测中,用相同配置从早期版本升级后,吞吐量更平稳。

• 更安全的默认值: 容易踩坑的地方变少了,意味着更少的"为什么卡住了?"的深夜排查。

• 规划器和并行优化: 在统计信息新鲜的情况下,宽查询的并行计划选择更自信。配合调优的 work_mem,大查询无需改代码就能加速。

(但仍然需要良好的统计信息和 vacuum)

一周见效的最小化方案

• 在前面加 PgBouncer(transaction 模式)处理嘈杂的应用

• 调优 Autovacuum:降低 scale factor,提高 worker 数量

• 平缓 Checkpoint:更大的 max_wal_size, LZ4 压缩

• 合理分配内存:shared_buffers 和 work_mem 消除溢出

• JIT 开关:微查询关,分析开

• 适度并行:在分析节点上尝试,监控 P95

• 补上缺失的索引:pg_stat_statements 里最耗时的查询在呼唤它

如何验证效果

找到真正的问题查询:

SELECT query, calls, total_exec_time, mean_exec_time, rowsFROM pg_stat_statementsORDER BY total_exec_time DESCLIMIT 10;

证明执行计划是好是坏:

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)SELECT ... ;

捕获溢出:

SHOW work_mem;-- 逐步提高-- 在 EXPLAIN ANALYZE 中查找 "Disk: " 行

观察 Autovacuum 工作:

SELECT relname, n_dead_tup, vacuum_count, autovacuum_countFROM pg_stat_user_tablesORDER BY n_dead_tup DESCLIMIT 15;

postgresql.conf 起步配置 

# 连接max_connections = 300
# 内存shared_buffers = 8GBwork_mem = 64MBmaintenance_work_mem = 2GBeffective_cache_size = 24GB
# WAL 和 Checkpointwal_compression = lz4max_wal_size = 8GBcheckpoint_timeout = 15minsynchronous_commit = on
# 并行max_parallel_workers_per_gather = 4max_parallel_workers = 8max_worker_processes = 16
# JIT(根据负载调整)jit = onjit_above_cost = 200000

 每次只改一个参数,重复测试三次。