PostgreSQL 性能优化

分为以下部分：

系统层优化（OS 层）
数据库配置优化（postgresql.conf）
表结构设计优化
索引优化
SQL / 查询优化
Vacuum / 膨胀（表膨胀）优化
WAL / checkpoint 优化
连接池优化
缓存与内存优化
监控指标与定位性能瓶颈

1. 🖥️ 系统层优化（OS 层）

1.1 文件系统推荐

文件系统	推荐理由
XFS（第一推荐）	大文件写入快、延迟稳定、并发好
EXT4	次优选择，较稳定

1.2 IO 调度

NVMe：

echo none > /sys/block/nvme0n1/queue/scheduler

SAS/SATA：

echo deadline > /sys/block/sda/queue/scheduler

1.3 Linux 内核参数

/etc/sysctl.conf：

vm.swappiness = 1
vm.dirty_ratio = 20
vm.dirty_background_ratio = 5
kernel.sched_migration_cost_ns = 5000000
vm.overcommit_memory = 2

避免内存被 swap，保证稳定延迟。

2. ⚙️ PostgreSQL 配置优化（最关键）

以下配置均为生产可用的最佳实践。

2.1 内存相关

shared_buffers = 25% RAM
work_mem = 32MB
maintenance_work_mem = 1GB
effective_cache_size = 70% RAM

注意：work_mem 是 per sort per query（极容易 OOM），不要超过 64MB，尤其是高并发系统。

2.2 WAL 相关（写入性能核心）

wal_level = replica
wal_compression = on
max_wal_size = 8GB
checkpoint_timeout = 15min
checkpoint_completion_target = 0.9

减少 checkpoint 卡顿。

2.3 并行查询

max_parallel_workers = 8
max_parallel_workers_per_gather = 4
parallel_leader_participation = on

PostgreSQL 并行计划非常智能，这部分收益巨大（特别是 OLAP）。

2.4 连接数

max_connections = 200

永远不要太高，否则上下文切换爆炸。

使用 PgBouncer 连接池（强烈推荐）。

3. 🏗️ 表结构设计优化

3.1 避免随机 I/O 的 UUID 主键（巨大性能坑）

如果你用：

uuid_generate_v4()

会导致插入完全随机 -> B-tree 大量 page split -> 页膨胀 -> 性能下降 10~50 倍。

优化方式：

使用 ULID（单调）
或使用 UUID v7（PostgreSQL 17+ 内置）
或大多数场景直接上 bigserial

3.2 字段类型优化

场景	类型
文本搜索	text（不要 varchar(n)）
状态字段	smallint
金额	numeric(18,2)（银行） or bigint（分单位）
时间	timestamp with time zone（强烈推荐）

varchar(n) 的长度限制无意义，PostgreSQL 不节省空间，还增加校验开销。

4. 🔍 索引优化（性能最关键）

4.1 索引创建基本原则

索引不要太多（写放大）

每增加 1 个索引：

INSERT 多写 1 次
UPDATE 多写 1 次
DELETE 多写 1 次

常见错误

创建：

index (col1, col2)
index (col1)

第二个永远用不到。

4.2 索引选型

类型	场景
B-tree	大部分查询
GIN	JSONB、全文检索
GiST	地理、范围
BRIN	时间序列、日志、Append-only 表（性能极高）

4.3 常用索引 SQL

单列索引

CREATE INDEX idx_user_email ON users(email);

复合索引

顺序很重要，过滤性最高的放前面。

JSONB 索引

CREATE INDEX idx_jsonb_gin ON table USING gin (payload jsonb_path_ops);

模糊匹配 index（必须用 pg_trgm）

CREATE EXTENSION pg_trgm;
CREATE INDEX idx_title_trgm ON article USING gin (title gin_trgm_ops);

5. 📌 SQL / 查询优化（核心）

5.1 查看 SQL 实际执行计划（必须学）

EXPLAIN (ANALYZE, BUFFERS) SELECT ...

看到：

seq scan？
index scan？
hash join？
sort in memory or disk？
并行计划？

5.2 常见 SQL 优化技巧

5.2.1 避免 SELECT *

增加 I/O
失去覆盖索引可能性

5.2.2 避免 WHERE 上的函数

坏例子：

WHERE date(created_at) = CURRENT_DATE

导致索引失效。

正确写法：

WHERE created_at >= CURRENT_DATE
  AND created_at < CURRENT_DATE + INTERVAL '1 day'

5.2.3 用 EXISTS 替代 IN（大量数据时）

SELECT * FROM a WHERE EXISTS (SELECT 1 FROM b WHERE b.id = a.id);

5.2.4 批量 INSERT 用 COPY 速度提升 ×50

COPY table FROM '/path/file.csv' CSV;

6. 🧹 Vacuum / 表膨胀优化（PostgreSQL 特有）

6.1 为什么膨胀？

MVCC 的旧版本无法立即删除
更新/删除频繁表会变巨慢
SELECT 读写都受影响

6.2 检查膨胀

SELECT * FROM pgstattuple('table');

6.3 强制 Vacuum

VACUUM FULL table;  -- 会锁表

更安全：

VACUUM (VERBOSE, ANALYZE);

6.4 Autovacuum 调优（非常关键）

autovacuum_vacuum_scale_factor = 0.1
autovacuum_analyze_scale_factor = 0.05
autovacuum_max_workers = 4
autovacuum_naptime = 10s

适用于高更新量系统。

7. 🧱 WAL / checkpoint 优化（写性能关键）

推荐配置

checkpoint_timeout = 15min
max_wal_size = 8GB
checkpoint_completion_target = 0.9
wal_compression = on

减少 “checkpoint spike” 卡顿。

8. 🧵 连接池优化（极重要）

PostgreSQL 的连接是重资源结构，不能无限开新连接。

建议：

场景	推荐方案
Web 服务	PgBouncer（transaction pooling）
大并发读写	PgBouncer + Patroni
后端批处理	使用 Python asyncpg 或 pgbouncer pooling

9. ☘️ 缓存与内存优化

9.1 操作系统缓存比 PostgreSQL 缓存更大

PostgreSQL 将磁盘读写交给 OS 的文件缓存，因此 effective_cache_size 很重要。

effective_cache_size = 70% RAM

告诉优化器：

OS 有多少 cache 可以利用

10. 📈 性能瓶颈定位流程（监控 + 调试）

10.1 监控核心指标

类别	指标	意义
连接	active、idle、idle in transaction	是否连接泄漏
锁	lock wait、deadlock	是否阻塞
I/O	read/write latency	慢查询原因
WAL	write rate	写瓶颈
Autovacuum	freeze age	膨胀风险
缓存	hit ratio	< 98% 需优化

10.2 查阻塞锁

SELECT * FROM pg_locks;
SELECT * FROM pg_stat_activity WHERE wait_event IS NOT NULL;

10.3 查慢 SQL

启用：

log_min_duration_statement = 500ms
log_lock_waits = on

慢 SQL 自动进入日志。

🎯 总结：PostgreSQL 性能优化黄金法则

分层	优化点
OS 层	swap=1、XFS、NVMe、IO 调度
PostgreSQL 层	shared_buffers、work_mem、WAL 参数
表结构	PK 连续、正确字段类型
索引	合理选择、避免过度索引
SQL	避免函数、避免 SELECT *、EXPLAIN 优化
Vacuum	预防膨胀
连接池	PgBouncer 必须用
高可用	Patroni / 流复制
监控	锁、IO、WAL、缓存命中率