7 个 PostgreSQL 性能调优实战技巧：索引策略、查询优化、主从复制全解析

从索引设计到查询优化，从配置调优到主从复制，手把手提升 PostgreSQL 性能。

---

为什么 PostgreSQL 性能会变差？

随着数据量增长，常见的性能瓶颈：

• 缺少索引或索引设计不合理 → 全表扫描
• 查询语句写得差 → 大量无用 IO
• 配置参数沿用默认值 → 内存/并发配置不匹配
• 没有主从分离 → 读写互相影响

---

技巧1：索引策略——不是建得越多越好

1.1 查看缺失索引

-- 找出执行最慢的查询
SELECT 
    query,
    calls,
    total_time,
    mean_time,
    rows
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 10;

-- 查看表上的索引使用情况
SELECT 
    schemaname,
    relname AS table_name,
    indexrelname AS index_name,
    idx_scan AS index_scans,
    seq_scan AS sequential_scans
FROM pg_stat_user_tables t
JOIN pg_stat_user_indexes i ON t.relid = i.relid
WHERE seq_scan > idx_scan * 10  -- 顺序扫描远多于索引扫描
ORDER BY seq_scan DESC;

1.2 创建合适的索引

-- ❌ 错误：对低区分度字段建索引（如性别）
CREATE INDEX idx_gender ON users(gender);  -- 只有2个值，索引效果不佳

-- ✅ 正确：对高区分度字段建索引
CREATE INDEX idx_email ON users(email);  -- email 唯一，区分度极高

-- ✅ 复合索引（注意字段顺序：区分度高的放前面）
CREATE INDEX idx_orders_user_created ON orders(user_id, created_at DESC);

-- ✅ 部分索引（只索引部分数据，节省空间）
CREATE INDEX idx_active_users ON users(email) WHERE status = 'active';

-- ✅ GIN 索引（用于 JSONB / 数组字段）
CREATE INDEX idx_metadata ON products USING GIN(metadata);

1.3 索引使用原则

场景	推荐索引类型
精确匹配（=）	B-tree（默认）
范围查询（>, <, BETWEEN）	B-tree
模糊查询（LIKE ‘%xxx%’）	不支持索引，考虑全文搜索
模糊查询（LIKE ‘xxx%’）	B-tree
包含查询（JSONB）	GIN
地理位置	GiST / SP-GiST

---

技巧2：查询优化——EXPLAIN 是你的好朋友

2.1 读懂执行计划

-- 开启 BUFFERS 可以看到 IO 情况
EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM orders WHERE user_id = 12345;

/*
Seq Scan on orders  (cost=0.00..153.00 rows=50 width=88) (actual time=0.020..1.200 rows=48 loops=1)
  Filter: (user_id = 12345)
  Rows Removed by Filter: 99952
  Buffers: shared hit=128
*/
-- ↑ 问题：Seq Scan（全表扫描），扫描了99952行才找到48行
-- 解决：给 user_id 建索引

2.2 常见慢查询优化

-- ❌ 慢查询：使用函数导致索引失效
SELECT * FROM users WHERE DATE(created_at) = '2024-01-01';
-- DATE() 函数让索引失效，全表扫描

-- ✅ 优化：用范围查询
SELECT * FROM users 
WHERE created_at >= '2024-01-01' 
  AND created_at < '2024-01-02';

-- ❌ 慢查询：SELECT * 返回所有字段
SELECT * FROM orders WHERE user_id = 123;

-- ✅ 优化：只查需要的字段
SELECT id, status, total_amount FROM orders WHERE user_id = 123;

-- ❌ 慢查询：NOT IN 子查询
SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM banned_users);

-- ✅ 优化：用 NOT EXISTS 或 LEFT JOIN
SELECT u.* FROM users u
LEFT JOIN banned_users b ON u.id = b.user_id
WHERE b.user_id IS NULL;

---

技巧3：配置调优——不要只用默认值

3.1 关键参数（postgresql.conf）

# 内存配置（64GB 服务器示例）
shared_buffers = 16GB          # 物理内存的 25%
effective_cache_size = 48GB     # 物理内存的 75%
work_mem = 64MB                 # 每个操作的内存，调大可加速排序/哈希
maintenance_work_mem = 2GB      # VACUUM / CREATE INDEX 时的内存

# WAL 配置（影响写入性能）
wal_buffers = 64MB              # WAL 缓冲区
checkpoint_timeout = 30min      # 检查点间隔，调大减少 IO
checkpoint_completion_target = 0.9  # 分散 IO 压力

# 并发配置
max_connections = 200           # 根据连接池调整，不要设太大

3.2 用 pgTune 自动计算参数

# 在线工具：https://pgtune.leopard.in.ua/
# 或本地运行：
docker run --rm pgtuned \
  -c "db_type=web" \
  -c "total_memory=64GB" \
  -c "cpus=16" \
  -c "connections=200"

---

技巧4：VACUUM 和 ANALYZE——维护表健康

-- 查看表膨胀情况
SELECT 
    schemaname,
    relname AS table_name,
    n_dead_tup,
    n_live_tup,
    round(n_dead_tup * 100.0 / (n_live_tup + n_dead_tup), 2) AS dead_percent
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY dead_percent DESC;

-- 手动 VACUUM（回收死元组空间）
VACUUM (VERBOSE, ANALYZE) your_table;

-- 设置自动 VACUUM（在 postgresql.conf）
autovacuum_max_workers = 6       # 同时运行的 autovacuum 进程数
autovacuum_vacuum_threshold = 100  # 触发 VACUUM 的最小更新行数

---

技巧5：分区表——应对亿级数据

-- 创建分区主表（按时间分区）
CREATE TABLE orders (
    id BIGSERIAL,
    user_id BIGINT NOT NULL,
    amount DECIMAL(10,2),
    created_at TIMESTAMP NOT NULL
) PARTITION BY RANGE (created_at);

-- 创建分区（每月一个分区）
CREATE TABLE orders_2024_01 PARTITION OF orders
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE orders_2024_02 PARTITION OF orders
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

-- 查询时只扫描相关分区（分区裁剪）
SELECT * FROM orders WHERE created_at BETWEEN '2024-01-01' AND '2024-01-31';
-- ↑ 只扫描 orders_2024_01 分区，其他分区不扫描

---

技巧6：主从复制——读写分离

6.1 配置主库（Primary）

# postgresql.conf（主库）
wal_level = replica              # 开启 WAL 复制
max_wal_senders = 10           # 最大复制连接数
wal_keep_size = 1GB            # 保留 WAL 文件大小

-- 创建复制用户
CREATE USER replicator REPLICATION LOGIN PASSWORD 'your_password';

-- pg_hba.conf 允许从库连接
# TYPE  DATABASE  USER         ADDRESS         METHOD
  host   replication  replicator  192.168.1.0/24   md5

6.2 配置从库（Replica）

# 从主库基础备份
pg_basebackup -h primary-host -D /var/lib/postgresql/data \
  -U replicator -P -v -R

# 启动从库（会自动以只读模式运行）
pg_ctl start -D /var/lib/postgresql/data

-- 验证复制状态（在主库执行）
SELECT client_addr, state, sent_lsn, write_lsn, flush_lsn 
FROM pg_stat_replication;

-- 在应用中实现读写分离
-- 写操作 → 主库
-- 读操作 → 从库（用连接池配置）

---

技巧7：连接池——减少连接开销

PostgreSQL 每个连接消耗约 10MB 内存，连接数过多会严重影响性能。

# 使用 PgBouncer 或 SQLAlchemy 连接池
from sqlalchemy import create_engine
from sqlalchemy.pool import QueuePool

engine = create_engine(
    "postgresql://user:pass@localhost/mydb",
    poolclass=QueuePool,
    pool_size=20,          # 连接池大小
    max_overflow=10,       # 超出 pool_size 后最多创建的连接数
    pool_recycle=3600,     # 连接回收时间（秒）
)

---

性能调优检查清单

□ 是否存在全表扫描的大表？（用 pg_stat_statements 查）
□ 索引是否合理？（高区分度字段有索引，低区分度字段无冗余索引）
□ 是否启用了 autovacuum？
□ shared_buffers / effective_cache_size 是否按服务器内存调整？
□ 是否有分区表应对大表？
□ 是否配置了主从复制实现读写分离？
□ 应用是否使用了连接池？

---

总结

优化层次	具体措施	预期效果
SQL 层	优化慢查询、避免 SELECT *	减少 50-90% 响应时间
索引层	合理建索引、删除冗余索引	查询提速 10-100 倍
配置层	调整 postgresql.conf	提升并发处理能力
架构层	分区表 + 主从复制	支撑亿级数据

记住：先测瓶颈，再优化。用 EXPLAIN ANALYZE 和 pg_stat_statements 找到真问题。

---

👤 作者简介

一枚在大中原腹地（河南）卖公有云的从业者，主营腾讯云/阿里云/火山云，曾踩坑无数，现专注AI大模型应用落地。关注公众号「公有云cloud」，围观AI前沿动态~

博客：y1cloud.cn