postgres-tuning

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

Skill: PostgreSQL Tuning (Standard 2026)

技能：PostgreSQL调优（2026标准）

Role: The PostgreSQL Tuning Specialist is responsible for ensuring the data layer is never the bottleneck. In 2026, this role masters the native AIO subsystem of PostgreSQL 18, high-density vector indexing (pgvector), and forensic query analysis using the enhanced EXPLAIN BUFFERS output.

角色： PostgreSQL调优专家负责确保数据层永远不会成为瓶颈。2026年，该角色需精通PostgreSQL 18的原生AIO子系统、高密度向量索引（pgvector），以及使用增强版EXPLAIN BUFFERS输出进行查询计划分析。

🎯 Primary Objectives

🎯 核心目标

I/O Performance: Optimizing the Asynchronous I/O subsystem for 2-3x throughput gains on NVMe storage.
Query Forensic: Mastering the "Execution Plan" to identify seq-scans, nested loops, and memory spills.
Indexing Excellence: Leveraging B-tree Skip Scans and native UUIDv7 for high-insertion workloads.
Bloat & Vacuum Control: Tuning autovacuum for high-churn tables to prevent performance degradation.

I/O性能优化： 优化异步I/O子系统，在NVMe存储上实现2-3倍的吞吐量提升。
查询计划分析： 精通「执行计划」，识别顺序扫描、嵌套循环和内存溢出问题。
索引优化： 利用B-tree跳扫和原生UUIDv7处理高插入量工作负载。
膨胀与清理控制： 针对高变动表调优自动清理（autovacuum），防止性能下降。

🏗️ The 2026 Database Stack

🏗️ 2026年数据库技术栈

1. Engine & Extensions

1. 引擎与扩展

PostgreSQL 18: Featuring native AIO and parallel
```
COPY FROM
```
.
pgvector 0.8+: For high-speed semantic search.
pg_stat_statements: Mandatory for identifying slow query patterns.

PostgreSQL 18： 原生支持AIO和并行
```
COPY FROM
```
。
pgvector 0.8+： 用于高速语义搜索。
pg_stat_statements： 必须用于识别慢查询模式。

2. Monitoring Tools

2. 监控工具

pg_aios: For monitoring the asynchronous I/O workers.
pg_stat_io: For granular I/O analysis across tables and indexes.
pganalyze / pgMustard: For visual execution plan analysis.

pg_aios： 用于监控异步I/O工作进程。
pg_stat_io： 用于对表和索引进行细粒度I/O分析。
pganalyze / pgMustard： 用于可视化执行计划分析。

🛠️ Implementation Patterns

🛠️ 实践方案

1. Asynchronous I/O Configuration (PG 18)

1. 异步I/O配置（PG 18）

Unlocking the full potential of modern SSDs.

ini

undefined

充分发挥现代SSD的潜力。

ini

undefined

postgresql.conf 2026 Standard

postgresql.conf 2026标准配置

io_method = worker # Or io_uring on Linux io_workers = 4 # Tune based on CPU/Storage concurrency max_async_ios = 1024 # Depth of the AIO queue

undefined

io_method = worker # Linux系统可使用io_uring io_workers = 4 # 根据CPU/存储并发量调整 max_async_ios = 1024 # AIO队列深度

undefined

2. UUIDv7 Migration

2. UUIDv7迁移

Replacing random UUIDv4 with sequential v7 to reduce index fragmentation.

sql

-- PostgreSQL 18 Native UUIDv7
CREATE TABLE transactions (
    id uuid DEFAULT uuid_generate_v7() PRIMARY KEY,
    created_at timestamptz DEFAULT now()
);
-- Benefit: 30% faster insertions and better cache locality

用顺序UUIDv7替代随机UUIDv4，减少索引碎片。

sql

-- PostgreSQL 18原生UUIDv7
CREATE TABLE transactions (
    id uuid DEFAULT uuid_generate_v7() PRIMARY KEY,
    created_at timestamptz DEFAULT now()
);
-- 优势：插入速度提升30%，缓存局部性更佳

3. Forensic Plan Analysis

3. 查询计划分析

Using the enhanced EXPLAIN output to find silent I/O costs.

sql

EXPLAIN (ANALYZE, BUFFERS, SETTINGS, WAL)
SELECT * FROM orders WHERE status = 'pending';
-- Goal: BUFFERS Shared Hit > 95%

使用增强版EXPLAIN输出发现隐性I/O开销。

sql

EXPLAIN (ANALYZE, BUFFERS, SETTINGS, WAL)
SELECT * FROM orders WHERE status = 'pending';
-- 目标：BUFFERS Shared Hit > 95%

🚫 The "Do Not List" (Anti-Patterns)

🚫 「禁忌清单」（反模式）

NEVER run
```
SELECT *
```
in high-frequency queries. Fetch only needed columns to save I/O.
NEVER ignore "Seq Scans" on tables over 10,000 rows. Add an index.
NEVER use random UUIDs as primary keys for high-write tables (Causes B-tree page splits).
NEVER set
```
shared_buffers
```
over 40% of total RAM without extensive testing.

绝对不要在高频查询中使用
```
SELECT *
```
。仅获取所需列以节省I/O。
绝对不要忽略超过10,000行表的「顺序扫描」。应添加索引。
绝对不要在高写入表中使用随机UUID作为主键（会导致B-tree页分裂）。
绝对不要在未进行充分测试的情况下将
```
shared_buffers
```
设置为超过总内存的40%。

🛠️ Troubleshooting & Latency Audit

🛠️ 故障排查与延迟审计

Issue	Likely Cause	2026 Corrective Action
High I/O Wait	Synchrounous I/O bottleneck	Enable `io_method = worker` (PG 18).
Index Bloat	High UPDATE/DELETE volume	Tune `autovacuum_vacuum_scale_factor` to 0.01.
Memory Spills	Low `work_mem` for large sorts	Increase `work_mem` for specific heavy sessions.
Slow Vector Search	Unoptimized HNSW index	Rebuild index with higher `m` and `ef_construction` values.

问题	可能原因	2026年修正措施
高I/O等待	同步I/O瓶颈	启用 `io_method = worker` （PG 18）。
索引膨胀	高更新/删除量	将 `autovacuum_vacuum_scale_factor` 调至0.01。
内存溢出	大排序任务的 `work_mem` 值过低	为特定高负载会话增加 `work_mem` 。
向量搜索缓慢	HNSW索引未优化	重建索引时提高 `m` 和 `ef_construction` 值。

📚 Reference Library

📚 参考库

AIO & I/O Tuning: Mastering the storage engine.
Query Plan Analysis: The forensic guide.
Indexing & Bloat: Maintaining data density.

AIO与I/O调优： 掌握存储引擎。
查询计划分析： 分析指南。
索引与膨胀： 维持数据密度。

📊 Performance Metrics

📊 性能指标

Cache Hit Ratio: > 99% for Shared Buffers.
Vacuum Latency: < 60s for standard tables.
Median Query Latency: < 10ms for OLTP workloads.

缓存命中率： 共享缓冲区（Shared Buffers）> 99%。
清理延迟： 标准表< 60秒。
中位数查询延迟： OLTP工作负载< 10ms。

🔄 Evolution of PG Performance

🔄 PostgreSQL性能演进

v15-16: Improved aggregation and parallelization.
v17: Incremental backups and memory-efficient vacuum.
v18: Native Asynchronous I/O (AIO), Parallel Copy, and B-tree Skip Scans.

End of PostgreSQL Tuning Standard (v1.1.0)

v15-16： 改进聚合与并行处理。
v17： 增量备份与内存高效清理。
v18： 原生异步I/O（AIO）、并行Copy和B-tree跳扫。

PostgreSQL调优标准结束（v1.1.0）