性能调优与连接管理

性能问题多来自 慢 SQL、错误参数、连接风暴、膨胀。DBA 用监控 + 计划 + 参数三板斧。与 /roadmaps/postgresql-dba 性能节点对齐。

学习目标

• 启用并查询 pg_stat_statements 找 Top SQL
• 调整 shared_buffers、work_mem、max_connections 基本思路
• 理解 PgBouncer 与 Spring 连接池分工

核心概念

pg_stat_statements

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT calls, round(total_exec_time::numeric, 2) AS total_ms,
       round(mean_exec_time::numeric, 2) AS mean_ms,
       left(query, 100) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

关注高 calls × mean_time；结合 EXPLAIN (ANALYZE, BUFFERS) 优化。

关键参数（postgresql.conf）

shared_buffers = 256MB          # 约内存 25%，按实例调整
work_mem = 4MB                  # 排序/哈希 per operation，慎过大
max_connections = 100           # 与 PgBouncer 配合
effective_cache_size = 1GB      #  planner 假设 OS 缓存
random_page_cost = 1.1          # SSD 可下调

改参后 SELECT pg_reload_conf();；部分需重启。

连接池架构

Spring HikariCP (每实例 10–30) → PgBouncer (transaction pooling) → PostgreSQL

# Spring Boot
spring.datasource.hikari.maximum-pool-size: 20
spring.datasource.hikari.connection-timeout: 30000

避免「每微服务 × 每 Pod × 大连接池」打满 max_connections。

与 MySQL/Spring Boot 对照

项	PostgreSQL	MySQL 8
慢 SQL 聚合	pg_stat_statements	performance_schema / slow log
缓冲池	shared_buffers	innodb_buffer_pool_size
连接池中间件	PgBouncer	ProxySQL / 直连
Spring 池	HikariCP	HikariCP（同）

本站 MySQL 本机开发可直连；PG 生产建议 应用池 + PgBouncer 双层。

练习

• 安装 pg_stat_statements，跑 5 条不同 SQL 后查 Top 3
• 估算：10 个 Pod × pool 30，max_connections 设多少才安全？
• 对一条 Seq Scan 慢查询加索引并对比 mean_time

小结

先量化 Top SQL，再调索引与参数；连接数必须全局规划。 下一课备份与 PITR。