PostgreSQL生产调优完全指南：香港服务器配置优化 + 索引策略 + 慢查询分析实战

PostgreSQL 默认配置面向通用场景，适合开发环境而非生产负载。未经调优的 PostgreSQL 在高并发下会出现连接数耗尽、查询缓慢、WAL 写入成为瓶颈等问题。本文系统覆盖香港服务器上 PostgreSQL 生产调优的核心参数、连接池配置和慢查询分析流程。

一、参数调优（postgresql.conf）

配置文件位于 /etc/postgresql/16/main/postgresql.conf，按服务器内存调整：

内存参数（最关键）

<code"># 以 8G 内存服务器为例

# shared_buffers：PostgreSQL 自己管理的共享内存缓冲区
# 规则：总内存的 25%（Linux 上通常可以设更高，最高 40%）
shared_buffers = 2GB

# effective_cache_size：估算操作系统文件系统缓存大小（影响查询计划，不实际分配）
# 规则：总内存的 75%
effective_cache_size = 6GB

# work_mem：每个排序/哈希操作可用内存（并发高时需降低此值）
# 警告：并发连接数 × work_mem 才是实际占用（可能远超想象）
# 公式：(总内存 - shared_buffers) / max_connections / 2
# 8G 服务器，100并发：(8G - 2G) / 100 / 2 ≈ 30MB
work_mem = 32MB

# maintenance_work_mem：VACUUM、CREATE INDEX 等维护操作可用内存
# 规则：总内存的 5%～10%
maintenance_work_mem = 512MB

# huge_pages：大页内存（可显著降低 TLB Miss 开销）
# 需要先在操作系统启用
huge_pages = try

WAL（Write-Ahead Log）参数

<code"># WAL 日志级别（replica 支持流复制，minimal 性能最高但不支持复制）
wal_level = replica

# 检查点设置（频繁检查点增加 I/O 压力，但崩溃恢复更快）
checkpoint_completion_target = 0.9    # 检查点在此比例时间内完成（减少 I/O 峰值）
checkpoint_timeout = 15min            # 最大检查点间隔
max_wal_size = 4GB                    # WAL 总大小上限（超过触发检查点）
min_wal_size = 1GB

# WAL 写入方式（性能 vs 可靠性权衡）
# fsync = on（默认，最安全，每次提交都 fsync）
# synchronous_commit = off（异步提交，性能提升 3～5 倍，丢失最多几百毫秒数据）
synchronous_commit = off    # 对丢失容忍度高的场景（如日志写入）可开启

连接与并发参数

<code"># 最大连接数（每个连接约占 5-10MB 内存）
max_connections = 200      # 不要设太高，配合 PgBouncer 使用

# 并发写入的 IO 线程数（NVMe SSD 可设更高）
effective_io_concurrency = 200

# 并行查询（利用多核）
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
max_parallel_maintenance_workers = 4

# 随机 I/O 速度提示（影响查询计划）
random_page_cost = 1.1     # SSD 设为 1.1，HDD 保持默认 4.0

日志参数（生产必须开启）

<code"># 慢查询日志
log_min_duration_statement = 1000    # 超过 1000ms 的查询记录日志（ms）
log_checkpoints = on                 # 记录检查点信息
log_connections = off                # 连接日志（高并发时关闭，减少日志量）
log_lock_waits = on                  # 记录锁等待（排查死锁）
log_temp_files = 0                   # 记录所有临时文件（值为 0 = 所有，-1 = 关闭）

# 日志格式
log_line_prefix = '%t [%p]: [%l-1] db=%d,user=%u,app=%a,client=%h '

二、操作系统层面调优

<code"># /etc/sysctl.conf 追加
# 共享内存上限（必须大于 shared_buffers）
kernel.shmmax = 4294967296         # 4GB
kernel.shmall = 1048576

# 内存过度提交策略（PostgreSQL 推荐）
vm.overcommit_memory = 2
vm.overcommit_ratio = 90

# 脏页写入控制（减少 I/O 突刺）
vm.dirty_ratio = 10
vm.dirty_background_ratio = 5

# 禁用 THP（大页透明内存，PostgreSQL 反而不适合）
echo 'never' > /sys/kernel/mm/transparent_hugepage/enabled

sysctl -p

三、PgBouncer 连接池

<code"># PostgreSQL 连接建立成本高（每连接约 5-10ms），高并发时连接池必不可少

apt install -y pgbouncer

# /etc/pgbouncer/pgbouncer.ini
[databases]
# 将所有对 mydb 的连接请求路由到 PostgreSQL
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 5432                 # 应用连接 PgBouncer 的端口（与 PG 原端口区分）
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

# 连接池模式：
# session：连接独占（最兼容，类似直连）
# transaction：事务级复用（最推荐，事务结束即释放连接）
# statement：语句级复用（最高效，但不支持事务）
pool_mode = transaction

# 连接池大小
max_client_conn = 1000             # 应用端最大连接数
default_pool_size = 25             # 实际到 PostgreSQL 的连接数

# 超时设置
server_idle_timeout = 600
client_idle_timeout = 0
server_connect_timeout = 15

# 日志
logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid

<code"># 创建认证文件
echo '"appuser" "md5$(echo -n 'password123appuser' | md5sum | cut -d' ' -f1)"' \
  > /etc/pgbouncer/userlist.txt

# 或者直接使用明文密码（仅开发环境）
echo '"appuser" "password123"' > /etc/pgbouncer/userlist.txt

systemctl enable --now pgbouncer

# 应用连接字符串改为连接 PgBouncer（5432 → 6432）
# DATABASE_URL=postgresql://appuser:password@localhost:6432/mydb

# 监控 PgBouncer 连接状态
psql -h 127.0.0.1 -p 6432 -U appuser pgbouncer -c "SHOW POOLS;"
psql -h 127.0.0.1 -p 6432 -U appuser pgbouncer -c "SHOW STATS;"

四、索引策略

<code">-- 查看当前索引使用情况（找出从未使用的索引）
SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan AS index_scans,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND NOT indisprimary
ORDER BY pg_relation_size(indexrelid) DESC;

-- 查找缺少索引的高频顺序扫描（需要添加索引的表）
SELECT
    relname AS table_name,
    seq_scan AS sequential_scans,
    seq_tup_read AS rows_read,
    idx_scan AS index_scans
FROM pg_stat_user_tables
WHERE seq_scan > 100
ORDER BY seq_scan DESC
LIMIT 20;

<code">-- 索引类型选择指南

-- B-tree（默认，适合 = / < / > / BETWEEN / LIKE 'prefix%'）
CREATE INDEX idx_orders_user ON orders (user_id);
CREATE INDEX idx_orders_date ON orders (created_at DESC);

-- 复合索引（查询条件中同时用到多列时）
CREATE INDEX idx_orders_user_status ON orders (user_id, status);

-- 部分索引（只对满足条件的行建索引，更小更快）
CREATE INDEX idx_pending_orders ON orders (created_at)
WHERE status = 'pending';

-- GIN 索引（JSONB / 全文搜索 / 数组）
CREATE INDEX idx_product_tags ON products USING GIN (tags);
CREATE INDEX idx_product_search ON products USING GIN (to_tsvector('chinese', title));

-- BRIN 索引（时序数据，极小的索引体积）
CREATE INDEX idx_logs_time ON access_logs USING BRIN (log_time);
-- 适合：时间戳列且数据按时间顺序插入

-- 并发建索引（不锁表，生产环境必用）
CREATE INDEX CONCURRENTLY idx_orders_email ON orders (customer_email);

五、pg_stat_statements 慢查询分析

<code"># 启用 pg_stat_statements 扩展
# postgresql.conf 添加：
# shared_preload_libraries = 'pg_stat_statements'
# pg_stat_statements.max = 10000
# pg_stat_statements.track = all

# 重启 PostgreSQL
systemctl restart postgresql

# 在目标数据库中创建扩展
psql -U postgres mydb -c "CREATE EXTENSION pg_stat_statements;"

<code">-- 查找最慢的 Top 10 查询
SELECT
    round(total_exec_time::numeric, 2) AS total_ms,
    calls,
    round(mean_exec_time::numeric, 2) AS avg_ms,
    round(stddev_exec_time::numeric, 2) AS stddev_ms,
    round((100 * total_exec_time / sum(total_exec_time) OVER())::numeric, 2) AS pct,
    left(query, 100) AS query_snippet
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- 查找缓存命中率低的查询
SELECT
    left(query, 80) AS query_snippet,
    calls,
    round(mean_exec_time::numeric, 2) AS avg_ms,
    round(100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0), 2) AS cache_hit_pct
FROM pg_stat_statements
WHERE shared_blks_hit + shared_blks_read > 0
ORDER BY cache_hit_pct ASC
LIMIT 10;

六、VACUUM 与维护

<code"># PostgreSQL 自动 VACUUM 配置（postgresql.conf）
autovacuum = on
autovacuum_max_workers = 3
autovacuum_vacuum_scale_factor = 0.05    # 5% 死行时触发（默认20%太高）
autovacuum_analyze_scale_factor = 0.02  # 2% 变更时更新统计信息
autovacuum_vacuum_cost_delay = 2ms      # 降低 VACUUM 对正常查询的影响

# 手动 VACUUM（紧急情况）
VACUUM ANALYZE orders;          -- 回收死行 + 更新统计
VACUUM FULL orders;             -- 完全重写表（锁表！仅在维护窗口执行）

# 查看膨胀最严重的表（需要 VACUUM 的候选）
SELECT
    relname AS table_name,
    n_dead_tup AS dead_tuples,
    n_live_tup AS live_tuples,
    round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 2) AS dead_pct,
    last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

七、总结

PostgreSQL 生产调优的优先级：shared_buffers（内存配置）→ PgBouncer（连接池）→ 索引优化（消灭顺序扫描）→ 慢查询分析（pg_stat_statements）→ autovacuum 调优。按此顺序完成调优的香港服务器上，同等配置下 PostgreSQL 的吞吐量通常可以提升 3～5 倍，P99 响应时间降低 70% 以上。