postgres-tuning

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

PostgreSQL Tuning

PostgreSQL 性能调优

Overview

概述

Optimizes PostgreSQL 17/18+ performance across I/O, query execution, indexing, and maintenance. Covers the native AIO subsystem introduced in PostgreSQL 18 for throughput gains on modern storage, forensic query plan analysis with EXPLAIN BUFFERS (auto-included in PG18), B-tree skip scans for composite indexes, native UUIDv7 generation, and autovacuum tuning for high-churn tables.

When to use: Diagnosing slow queries, configuring async I/O, tuning shared_buffers and work_mem, optimizing indexes for write-heavy workloads, managing table bloat, pgvector HNSW tuning.

When NOT to use: Schema design (use a data modeling tool), application-level caching strategy, database selection decisions, ORM query generation.

Key monitoring views:

```
pg_stat_statements
```
— identifies slow query patterns by cumulative execution time
```
pg_stat_io
```
— granular I/O analysis by backend type, object, and context (PG16+)
```
pg_stat_checkpointer
```
— checkpoint frequency and timing (PG17+; previously in
```
pg_stat_bgwriter
```
)
```
pg_stat_user_tables
```
— dead tuple counts for bloat detection and autovacuum monitoring
```
pg_statio_user_tables
```
— buffer cache hit ratios per table
```
pg_aios
```
— in-progress AIO operations (PG18+)

针对I/O、查询执行、索引和维护环节优化PostgreSQL 17/18+的性能。涵盖PostgreSQL 18中引入的原生AIO子系统（可提升现代存储的吞吐量）、使用EXPLAIN BUFFERS进行查询计划深度分析（PG18中自动包含该功能）、复合索引的B-tree跳扫、原生UUIDv7生成，以及针对高变动率表的autovacuum调优。

适用场景： 诊断慢查询、配置异步I/O、调优shared_buffers和work_mem、优化写密集型工作负载的索引、管理表膨胀、pgvector HNSW调优。

不适用场景： schema设计（请使用数据建模工具）、应用级缓存策略、数据库选型决策、ORM查询生成。

核心监控视图：

```
pg_stat_statements
```
— 按累计执行时间识别慢查询模式
```
pg_stat_io
```
— 按后端类型、对象和上下文进行细粒度I/O分析（PG16+）
```
pg_stat_checkpointer
```
— 检查点频率与计时（PG17+；原属于
```
pg_stat_bgwriter
```
）
```
pg_stat_user_tables
```
— 用于膨胀检测和autovacuum监控的死元组计数
```
pg_statio_user_tables
```
— 各表的缓冲区缓存命中率
```
pg_aios
```
— 进行中的AIO操作（PG18+）

Quick Reference

快速参考

Pattern	Configuration / Query	Key Points
Async I/O	`io_method = worker` or `io_uring`	PG18 default is `worker` ; `io_uring` Linux-only (kernel 5.1+, requires liburing build flag)
I/O concurrency	`io_max_concurrency` and `io_workers`	`io_workers` defaults to 3; `io_max_concurrency` defaults to -1 (auto-calculated)
Forensic EXPLAIN	`EXPLAIN (ANALYZE, BUFFERS, SETTINGS)`	PG18 auto-includes BUFFERS with ANALYZE; target Shared Hit > 95%
UUIDv7 primary keys	`DEFAULT uuidv7()`	PG18 built-in; time-ordered, monotonic within a session; RFC 9562 compliant
B-tree skip scan	Composite index on `(a, b)`	PG18 skips leading column; works best with low-cardinality prefix and equality on trailing columns
Aggressive autovacuum	`autovacuum_vacuum_scale_factor = 0.01`	Triggers at 1% row change instead of default 20%
Shared buffers	Start at 25% of RAM	Do not exceed 40% without benchmarking
work_mem tuning	`SET work_mem = '64MB'` per session	Prevents sort spills to disk; allocated per operator, not per query
BRIN index	`CREATE INDEX USING brin(...)`	100x smaller than B-tree for physically ordered time-series data
HNSW vector index	`USING hnsw (col vector_cosine_ops)`	Tune `m` (default 16) and `ef_construction` (default 64) for recall vs speed
GIN index	`CREATE INDEX USING gin(...)`	JSONB containment, full-text search, array operators; slower writes
Checkpoint tuning	`checkpoint_timeout = 30min`	Spread writes over 90% of timeout window to avoid I/O storms
WAL compression	`wal_compression = zstd`	Available since PG15; reduces WAL I/O 50-70% for write-heavy workloads
Bloat detection	`pg_stat_user_tables.n_dead_tup`	Reindex concurrently if bloat > 30%
I/O monitoring	`SELECT * FROM pg_stat_io`	Watch `evictions` (cache too small) and `extends` (fast growth)
Checkpoint monitoring	`pg_stat_checkpointer`	PG17+ moved checkpoint stats out of `pg_stat_bgwriter`

模式	配置/查询语句	关键要点
异步I/O	`io_method = worker` 或 `io_uring`	PG18默认值为 `worker` ； `io_uring` 仅支持Linux（内核5.1+，需要liburing编译标志）
I/O并发度	`io_max_concurrency` 和 `io_workers`	`io_workers` 默认值为3； `io_max_concurrency` 默认值为-1（自动计算）
深度查询计划分析	`EXPLAIN (ANALYZE, BUFFERS, SETTINGS)`	PG18中使用ANALYZE时自动包含BUFFERS信息；目标Shared Hit需>95%
UUIDv7主键	`DEFAULT uuidv7()`	PG18内置功能；按时间排序，会话内保持单调；符合RFC 9562标准
B-tree跳扫	基于 `(a, b)` 的复合索引	PG18支持跳过前导列；在前缀列基数低且后续列使用等值条件时效果最佳
激进式自动清理	`autovacuum_vacuum_scale_factor = 0.01`	当行变更率达1%时触发，替代默认的20%
共享缓冲区	初始设置为内存的25%	未经基准测试不要超过40%
work_mem调优	按会话设置 `SET work_mem = '64MB'`	避免排序操作溢出到磁盘；该内存按运算符分配，而非按查询分配
BRIN索引	`CREATE INDEX USING brin(...)`	对于物理有序的时序数据，比B-tree索引小100倍
HNSW向量索引	`USING hnsw (col vector_cosine_ops)`	调优 `m` （默认16）和 `ef_construction` （默认64）以平衡召回率与速度
GIN索引	`CREATE INDEX USING gin(...)`	支持JSONB包含查询、全文搜索、数组操作；会降低写入速度
检查点调优	`checkpoint_timeout = 30min`	将写入操作分散到超时窗口的90%时间内，避免I/O风暴
WAL压缩	`wal_compression = zstd`	从PG15开始可用；对于写密集型工作负载可减少50-70%的WAL I/O
膨胀检测	`pg_stat_user_tables.n_dead_tup`	当膨胀率>30%时，执行并发重索引操作
I/O监控	`SELECT * FROM pg_stat_io`	关注 `evictions` （缓存过小）和 `extends` （数据快速增长）
检查点监控	`pg_stat_checkpointer`	PG17+中检查点统计信息从 `pg_stat_bgwriter` 迁移至此处

Key Version Changes

版本核心变更

PostgreSQL 18:

Native async I/O via
```
io_method
```
parameter (reads only; writes remain synchronous)
Built-in
```
uuidv7()
```
function with monotonic ordering within a session (RFC 9562)

uuidv4()

alias for

gen_random_uuid()

and

uuid_extract_timestamp()

for UUIDv7

B-tree skip scan for composite indexes (equality on trailing columns, low-cardinality prefix)
EXPLAIN ANALYZE auto-includes buffer statistics without specifying BUFFERS

pg_stat_io

gains byte-level columns (

read_bytes

write_bytes

extend_bytes

);

op_bytes

removed

```
effective_io_concurrency
```
default changed from 1 to 16
AIO monitoring via
```
pg_aios
```
system view for in-progress I/O operations

PostgreSQL 17:

Checkpoint statistics moved from
```
pg_stat_bgwriter
```
to
```
pg_stat_checkpointer
```

Column renames:

checkpoints_timed

num_timed

checkpoints_req

num_requested

buffers_backend

and

buffers_backend_fsync

removed from

pg_stat_bgwriter

(now in

pg_stat_io

)

PostgreSQL 15:

```
wal_compression
```
expanded from boolean to support
```
pglz
```
,
```
lz4
```
, and
```
zstd
```
algorithms

PostgreSQL 18：

通过
```
io_method
```
参数支持原生异步I/O（仅覆盖读操作；写操作仍为同步）
内置
```
uuidv7()
```
函数，会话内保持单调排序（符合RFC 9562）

为

gen_random_uuid()

添加

uuidv4()

别名，新增

uuid_extract_timestamp()

用于UUIDv7

复合索引支持B-tree跳扫（后续列使用等值条件，前缀列基数低）
EXPLAIN ANALYZE自动包含缓冲区统计信息，无需手动指定BUFFERS

pg_stat_io

新增字节级列（

read_bytes

、

write_bytes

、

extend_bytes

）；移除

op_bytes

```
effective_io_concurrency
```
默认值从1改为16
新增
```
pg_aios
```
系统视图用于监控进行中的AIO操作

PostgreSQL 17：

检查点统计信息从
```
pg_stat_bgwriter
```
迁移至
```
pg_stat_checkpointer
```

列名变更：

checkpoints_timed

改为

num_timed

，

checkpoints_req

改为

num_requested

pg_stat_bgwriter

中移除

buffers_backend

和

buffers_backend_fsync

（现位于

pg_stat_io

）

PostgreSQL 15：

```
wal_compression
```
从布尔类型扩展为支持
```
pglz
```
、
```
lz4
```
和
```
zstd
```
算法

Common Mistakes

常见错误

Mistake	Correct Pattern
Using `uuid_generate_v7()` or `gen_random_uuid()` for ordered keys	PG18 provides built-in `uuidv7()` for time-ordered UUIDs; pre-PG18 use `pg_uuidv7` extension
Using `max_async_ios` as a configuration parameter	The correct PG18 parameter is `io_max_concurrency` (max concurrent I/O ops per process)
Querying `pg_stat_bgwriter` for checkpoint statistics on PG17+	Checkpoint stats moved to `pg_stat_checkpointer` in PG17; columns renamed ( `num_timed` , `num_requested` )
Using SELECT * in high-frequency queries	Select only needed columns to reduce I/O and improve cache hit ratios
Ignoring sequential scans on tables over 10k rows	Add targeted indexes on columns used in WHERE, ORDER BY, and JOIN clauses
Setting shared_buffers above 40% of RAM without testing	Start at 25% and benchmark; excessive allocation causes OS page cache contention
Leaving autovacuum at default settings for high-churn tables	Tune `autovacuum_vacuum_scale_factor` to 0.01 for tables with frequent UPDATE/DELETE
Over-indexing columns rarely used in queries	Every extra index slows UPDATE/INSERT and prevents HOT (Heap Only Tuple) updates
Expecting B-tree skip scan to work with range predicates	PG18 skip scan only works with equality operators on trailing columns
Ignoring "External Merge Disk" in query plans	Increase work_mem for specific sessions; it indicates sort spills to disk
Setting `io_method = io_uring` without verifying build flags	PostgreSQL must be built with `--with-liburing` and requires Linux kernel 5.1+
Assuming PG18 AIO accelerates writes	AIO in PG18 only covers reads (seq scans, bitmap heap scans, VACUUM); writes remain synchronous

错误操作	正确做法
使用 `uuid_generate_v7()` 或 `gen_random_uuid()` 作为有序键	PG18提供内置 `uuidv7()` 生成按时间排序的UUID；PG18之前版本使用 `pg_uuidv7` 扩展
将 `max_async_ios` 作为配置参数	PG18中正确的参数是 `io_max_concurrency` （每个进程的最大并发I/O操作数）
在PG17+版本中查询 `pg_stat_bgwriter` 获取检查点统计信息	PG17中检查点统计信息已迁移至 `pg_stat_checkpointer` ；列名已变更（ `num_timed` 、 `num_requested` ）
在高频查询中使用SELECT *	仅选择所需列，以减少I/O并提升缓存命中率
忽略超过1万行的表上的顺序扫描	为WHERE、ORDER BY和JOIN子句中使用的列添加针对性索引
未经测试将shared_buffers设置为超过内存的40%	初始设置为25%并进行基准测试；分配过多会导致操作系统页缓存竞争
高变动率表保留autovacuum默认设置	对于频繁执行UPDATE/DELETE的表，将 `autovacuum_vacuum_scale_factor` 调优为0.01
为很少在查询中使用的列过度索引	额外的索引会减慢UPDATE/INSERT操作，且会阻止HOT（仅堆元组）更新
期望B-tree跳扫支持范围谓词	PG18的跳扫仅支持后续列使用等值运算符
忽略查询计划中的"External Merge Disk"	为特定会话提高work_mem；该标识表示排序操作已溢出到磁盘
未验证编译标志就设置 `io_method = io_uring`	PostgreSQL必须使用 `--with-liburing` 编译，且需要Linux内核5.1+版本
认为PG18的AIO可加速写操作	PG18中的AIO仅覆盖读操作（顺序扫描、位图堆扫描、VACUUM）；写操作仍为同步

Tuning Workflow

调优流程

Identify slow queries from
```
pg_stat_statements
```
(sort by
```
total_exec_time
```
)
Analyze execution plans with
```
EXPLAIN (ANALYZE, BUFFERS, SETTINGS)
```
Check buffer hit ratios via
```
pg_statio_user_tables
```
(target > 99%)
Monitor I/O patterns via
```
pg_stat_io
```
(watch evictions and disk reads)
Optimize with targeted indexes, work_mem adjustments, or query rewrites
Verify improvements by re-running EXPLAIN and comparing costs
Maintain with aggressive autovacuum settings for high-churn tables

识别：从
```
pg_stat_statements
```
中找出慢查询（按
```
total_exec_time
```
排序）
分析：使用
```
EXPLAIN (ANALYZE, BUFFERS, SETTINGS)
```
分析执行计划
检查：通过
```
pg_statio_user_tables
```
查看缓冲区命中率（目标>99%）
监控：通过
```
pg_stat_io
```
监控I/O模式（关注缓存驱逐和磁盘读操作）
优化：采用针对性索引、调整work_mem或重写查询
验证：重新运行EXPLAIN并对比成本，确认优化效果
维护：为高变动率表配置激进的autovacuum设置

Delegation

任务分工

Discover slow queries and I/O bottlenecks: Use
```
Explore
```
agent to analyze pg_stat_statements, pg_stat_io, and slow query logs
Execute query plan analysis and index optimization: Use
```
Task
```
agent to run EXPLAIN ANALYZE, create indexes, and verify performance improvements
Design database scaling and partitioning strategy: Use
```
Plan
```
agent to architect sharding, partitioning, and replication topology

发现慢查询与I/O瓶颈：使用Explore工具分析pg_stat_statements、pg_stat_io和慢查询日志
执行查询计划分析与索引优化：使用Task工具运行EXPLAIN ANALYZE、创建索引并验证性能提升
设计数据库扩容与分区策略：使用Plan工具设计分片、分区和复制拓扑

References

参考资料

Async I/O configuration and storage tuning
Query plan analysis and operator forensics
Indexing strategies and bloat management

异步I/O配置与存储调优
查询计划分析与算子深度解析
索引策略与膨胀管理