PostgreSQL 性能调优完全指南：2025 最佳实践

PostgreSQL 性能调优完全指南：2025 最佳实践

PostgreSQL 是高性能开源关系型数据库。本文深入讲解 PostgreSQL 性能调优的核心参数和实战技巧。

⚙️ 关键配置参数

1. shared_buffers：共享缓冲区

-- 推荐设置为物理内存的 25%-40%
shared_buffers = 4GB  -- 16GB 内存的服务器

2. work_mem：排序和哈希操作内存

work_mem = 64MB  -- 每个操作的内存，不是每个连接

3. maintenance_work_mem：维护操作内存

maintenance_work_mem = 1GB  -- VACUUM、CREATE INDEX 等操作

4. effective_cache_size：优化器假设的系统缓存

effective_cache_size = 12GB  -- 通常为物理内存的 75%

📊 查询优化技巧

1. 使用 EXPLAIN ANALYZE 分析查询

EXPLAIN ANALYZE
SELECT * FROM users WHERE email = 'test@example.com';

2. 创建合适的索引

-- B-tree 索引（默认）
CREATE INDEX idx_users_email ON users(email);

-- 复合索引
CREATE INDEX idx_users_name_age ON users(name, age);

-- 部分索引
CREATE INDEX idx_active_users ON users(email) WHERE active = true;

-- GIN 索引（JSONB、数组）
CREATE INDEX idx_users_data ON users USING gin(data);

3. 避免 N+1 查询问题

-- ❌ 错误：N+1 查询
SELECT * FROM orders;
-- 然后对每个 order 执行：SELECT * FROM items WHERE order_id = ?

-- ✅ 正确：JOIN 查询
SELECT orders.*, items.* FROM orders
LEFT JOIN items ON orders.id = items.order_id;

🗄️ 分区表（Partitioning）

-- 创建分区表
CREATE TABLE measurements (
  id SERIAL,
  measured_at TIMESTAMPTZ NOT NULL,
  value FLOAT
) PARTITION BY RANGE (measured_at);

-- 创建分区
CREATE TABLE measurements_2025_01 PARTITION OF measurements
  FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

🔧 维护操作

-- 更新统计信息
ANALYZE users;

-- 清理死元组
VACUUM (VERBOSE, ANALYZE) users;

-- 重建索引
REINDEX TABLE users;

📈 监控工具

• pg_stat_statements：查询性能统计
• pgBadger：日志分析工具
• Prometheus + Grafana：实时监控
• pgAdmin：图形化管理工具

总结：PostgreSQL 性能调优需要从配置参数、索引设计、查询优化、分区策略等多个维度综合考虑。定期维护和监控同样重要。

---

本文整理自 PostgreSQL 官方文档及性能优化社区实践