PostgreSQL 性能优化实战：从配置到索引，把 PG 性能榨干

上次写了 PostgreSQL 安装部署（文章 #25），这次把性能优化这块补上。从配置调优到索引设计，从 SQL 优化到日常运维，一篇文章帮你把 PG 的性能潜力榨干。

性能优化全景图

PostgreSQL 性能优化可以分成几个层次，从下往上依次影响：

最重要的一条原则： 不要凭感觉调优，先用数据说话。一切优化的起点都是 EXPLAIN ANALYZE。

一、先诊断，再开药

1.1 找出慢查询

在开始调优之前，先找到哪些查询在拖后腿：

-- 查看当前正在运行的查询
SELECT pid, now() - pg_stat_activity.query_start AS duration,
       query, state
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;

-- 查看执行时间最长的历史查询
SELECT query, calls, total_exec_time, mean_exec_time,
       rows, shared_blks_hit, shared_blks_read
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

⚠️ 注意：pg_stat_statements 是个扩展，需要先安装：

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

并在 postgresql.conf 中加上 shared_preload_libraries = 'pg_stat_statements'，然后重启。

1.2 EXPLAIN ANALYZE 读心术

找到慢查询后，用 EXPLAIN ANALYZE 看它到底慢在哪：

EXPLAIN (ANALYZE, BUFFERS, TIMING) 
SELECT * FROM orders WHERE created_at > '2026-01-01' ORDER BY total_amount DESC;

输出中的几个关键信号：

1.3 一键诊断脚本

-- 缓存命中率（低于 99% 说明 shared_buffers 可能不够）
SELECT 'index hit rate' AS name,
       (sum(idx_blks_hit)) / nullif(sum(idx_blks_hit + idx_blks_read), 0) * 100 AS ratio
FROM pg_statio_user_indexes
UNION ALL
SELECT 'table hit rate',
       (sum(heap_blks_hit)) / nullif(sum(heap_blks_hit + heap_blks_read), 0) * 100
FROM pg_statio_user_tables;

-- 表膨胀率
SELECT schemaname, tablename, n_dead_tup, n_live_tup,
       round(n_dead_tup * 100 / nullif(n_live_tup, 0), 2) AS dead_pct
FROM pg_stat_user_tables
WHERE n_live_tup > 0
ORDER BY dead_pct DESC;

-- 未使用索引
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND indexrelid NOT IN (
    SELECT indexrelid FROM pg_constraint WHERE conindid = indexrelid
);

二、配置参数调优

2.1 核心参数速查表

以下是一台 16GB 内存、8核 CPU、SSD 存储 机器的参考配置：

# === 内存配置（最重要的参数） ===
shared_buffers = 4GB              # RAM 的 25%，不要超过 8GB
effective_cache_size = 12GB        # RAM 的 50%~75%
work_mem = 64MB                    # 默认 4MB 太小，但并发高时别太大
maintenance_work_mem = 1GB         # VACUUM/索引维护用
wal_buffers = 64MB                 # 写密集型再加大

# === 优化器 ===
random_page_cost = 1.1             # SSD 设 1.1，HDD 保持默认 4
effective_io_concurrency = 200     # SSD 设 200，HDD 设 2
default_statistics_target = 500    # 默认 100，统计信息更精确

# === 连接 ===
max_connections = 200              # 别设太大，推荐连接池方案
superuser_reserved_connections = 10

# === WAL / 检查点 ===
wal_level = replica
max_wal_size = 4GB
min_wal_size = 1GB
checkpoint_completion_target = 0.9
wal_compression = zstd             # PG 18 支持 zstd 压缩 WAL

# === 自动维护 ===
autovacuum = on
autovacuum_max_workers = 4
autovacuum_naptime = 30s
autovacuum_vacuum_scale_factor = 0.01   # 默认 0.2 太大，改小
autovacuum_vacuum_threshold = 50

2.2 使用 PgTune 生成配置

懒得手动算？用 PGTune 在线生成：

填上你的服务器规格（内存、CPU、连接数、存储类型），它自动给出推荐配置，比手动估算靠谱得多。

2.3 参数调试方法

不要一次改一堆参数！ 每次只改 1~2 个，重启后通过 EXPLAIN ANALYZE 对比前后差异。

# 查看当前配置
psql -c "SHOW ALL;" | grep -E 'shared_buffers|work_mem|effective_cache'

# 热加载配置（不需要重启的部分）
SELECT pg_reload_conf();

# 查看当前运行时的实际使用量
SELECT pg_size_pretty(sum(pg_database_size(datname))) AS total_size
FROM pg_database;

三、索引优化——性价比最高的优化手段

3.1 索引类型怎么选？

PostgreSQL 支持 6 种索引类型，选对了事半功倍：

3.2 实用索引技巧

覆盖索引（Index-Only Scan）：

-- 如果只查 amount 和 status，把这两个字段也放进索引
CREATE INDEX idx_orders_covering ON orders(created_at) INCLUDE (amount, status);

这样查询不需要回表，直接走索引就能拿到全部数据。

部分索引（Partial Index）：

-- 只索引"未处理"的订单，索引体积缩小 90%
CREATE INDEX idx_orders_pending ON orders(created_at)
WHERE status = 'pending';

适合低频值查询的场景——只索引你想要的那部分数据。

复合索引的字段顺序：

-- 等值条件放前面，范围条件放后面
CREATE INDEX idx_orders_lookup ON orders(status, created_at DESC);

经验法则：

• WHERE a = 1 AND b = 2 → 索引 (a, b)，顺序无所谓
• WHERE a = 1 AND b > 10 → 索引 (a, b)，等值在前，范围在后
• WHERE a = 1 ORDER BY b → 索引 (a, b)，把排序列放在最后

3.3 索引维护

-- 查看索引使用率
SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;

-- 重建索引（PG 18 支持 CONCURRENTLY 重建，不锁表）
REINDEX INDEX CONCURRENTLY idx_orders_created_at;

定期检查零扫描的索引——如果一个索引长期没用，果断删掉，别让它拖慢写入。

四、SQL 优化——实战经验

4.1 COUNT 优化

-- 慢！精确计数大表很慢
SELECT COUNT(*) FROM orders;

-- 快！用 pg_class 估算（误差可接受）
SELECT reltuples::bigint AS estimated_count
FROM pg_class WHERE relname = 'orders';

-- 更快！用事务 ID 范围近似
SELECT txid_current();

4.2 分页优化

-- ❌ 传统的 OFFSET 分页，越翻越慢
SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 100000;

-- ✅ 游标分页（Keyset Pagination），翻到天荒地老也一样快
SELECT * FROM orders
WHERE id > 100000
ORDER BY id
LIMIT 20;

4.3 JOIN 优化

-- 用 EXISTS 替代 IN（当子表很大时）
-- ❌ 慢
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);

-- ✅ 快（只要找到一条就停止）
SELECT * FROM users WHERE EXISTS (SELECT 1 FROM orders WHERE orders.user_id = users.id);

4.4 批量操作

-- 不要一条一条 INSERT
INSERT INTO orders (user_id, amount, created_at) VALUES
(1, 100, NOW()),
(2, 200, NOW()),
(3, 300, NOW());

-- 大数据量导入用 COPY
COPY orders FROM '/tmp/orders.csv' WITH (FORMAT CSV, HEADER true);

五、日常维护不能少

5.1 VACUUM——PG 的垃圾回收

PostgreSQL 的 MVCC 机制会导致"死元组"堆积，VACUUM 就是清理它们的：

-- 查看需要 VACUUM 的表
SELECT schemaname, tablename, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

-- 手动 VACUUM 指定表
VACUUM ANALYZE orders;

关键点： 现在 autovacuum 默认开启，一般不需要手动操作。但如果你发现某张大表频繁更新删除，可能需要调小 autovacuum_vacuum_scale_factor。

5.2 更新统计信息

-- 全库 ANALYZE
ANALYZE;

-- 指定表，更精确
ANALYZE orders;

5.3 查看表大小

-- 按大小排序
SELECT relname, pg_size_pretty(pg_total_relation_size(oid)) AS total_size
FROM pg_class
WHERE relkind = 'r' AND relnamespace = (
    SELECT oid FROM pg_namespace WHERE nspname = 'public'
)
ORDER BY pg_total_relation_size(oid) DESC;

六、架构层面的优化

6.1 连接池

PostgreSQL 的进程模型决定了：连接越多越慢。每个连接都是一个独立进程，200 个连接就是 200 个进程在抢 CPU。

推荐用 PgBouncer 做连接池：

# 安装
sudo apt install pgbouncer

# 最小配置
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
listen_port = 6432
pool_mode = transaction     # 事务级复用，最推荐
max_client_conn = 500
default_pool_size = 20      # 实际到 PG 的连接数

6.2 表分区

对于超过千万级的表，分区可以显著提升查询和 DML 性能：

-- PG 18 支持原生分区
CREATE TABLE orders_partitioned (
    id BIGSERIAL,
    created_at TIMESTAMPTZ NOT NULL,
    amount NUMERIC(10,2)
) PARTITION BY RANGE (created_at);

-- 按月分区
CREATE TABLE orders_2026_q3 PARTITION OF orders_partitioned
    FOR VALUES FROM ('2026-07-01') TO ('2026-10-01');

6.3 并行查询

PG 18 的并行查询已经相当成熟：

-- 查看并行配置
SHOW max_parallel_workers_per_gather;
SHOW max_parallel_workers;

-- 让大查询自动并行（通常不需要干预）
SET max_parallel_workers_per_gather = 4;

对于大表上的聚合查询和 JOIN，并行查询能自动利用多核 CPU。

七、实用工具推荐

八、常见问题速查

Q：改了 shared_buffers 后性能反而下降了？

可能是因为 OS 缓存被挤占了。共享缓冲区（shared_buffers）和 OS 页面缓存是两级缓存，shared_buffers 占太多挤占了 OS 缓存的空间，总缓存命中率反而下降。建议 shared_buffers 不超过 RAM 的 40%。

Q：work_mem 设很大是不是更好？

不是。work_mem 是每个查询操作可以使用的内存。如果设 1GB，同时有 50 个查询在做排序，就是 50GB——内存直接爆炸。64MB~256MB 是比较平衡的区间。

Q：VACUUM FULL 能用吗？

尽量不要在生产环境用。VACUUM FULL 会锁表并重建，导致该表完全不可用。PG 18 中有更温和的 pg_repack 扩展，可以在不锁表的情况下回收空间。

总结

PostgreSQL 性能优化不是玄学，有章可循：

1. 先诊断：用 EXPLAIN ANALYZE 和 pg_stat_statements 找到问题
2. 再调参数：从 shared_buffers、work_mem、random_page_cost 入手
3. 索引是免费的午餐：覆盖索引、部分索引、复合索引顺序，成本低见效快
4. SQL 写法有讲究：OFFSET 分页、IN 子查询、逐行 INSERT 都是坑
5. 维护别偷懒：autovacuum 配置优化，统计信息及时更新
6. 架构兜底：连接池 + 分区表 + 并行查询，解决规模问题

最后送一句话：PostgreSQL 的默认配置只保证能跑，不保证跑得快。 花半小时调一下参数，可能比升级硬件划算得多。

本文由老张整理编写，信息综合自 PostgreSQL 官方文档、PGTune 社区实践及个人实战经验。

📖 上一篇：PostgreSQL 安装部署实战：从零搭建生产级环境 — 如果还没装 PG，先看这篇。