Postgres 生产运维实战：连接、膨胀、锁与监控

说明：本文是原创的 Postgres 生产运维经验整理，非任何外文文章的翻译。内容来自通用的 Postgres 运维实践与常见踩坑经验。

过去这一年半里，在生产环境用 Postgres，我们几乎把常见的坑都踩了一遍——从连接池打满、慢查询把 CPU 拖垮，到膨胀（bloat）悄悄吃掉磁盘、锁等待把整个库卡死。每救一次火，就把诊断手段和修复方式记下来。

这篇文章就是这份运维自查清单：不讲架构选型的大道理，只讲那些真正会让数据库在生产环境倒下的具体问题，以及每个问题对应的、可以直接复制运行的 SQL。

如果你想看一份按 schema 设计、读写查询、复合索引、查询计划器、autovacuum 调优、分区等递进展开的内容，可以看本博客的《创业公司的 Postgres 生存指南》（那是一篇 Hatchet 博客的全文翻译）。本文与它角度不同，本文聚焦的是线上已部署数据库的运维诊断。

一、先看清数据库正在经历什么

动手优化前，先打开「仪表盘」。

1.1 看当前正在跑什么

SELECT
  pid,
  now() - pg_stat_activity.query_start AS duration,
  query,
  state
FROM pg_stat_activity
WHERE state != 'idle';

列出所有非空闲的查询，并算出每条已经跑了多久。看到 duration 是分钟级别的，基本就是它了。

1.2 看连接都被谁占着

SELECT
  state,
  count(*)
FROM pg_stat_activity
GROUP BY state;

最危险的是 idle in transaction——连接拿着一个未提交的事务空转，既占着连接数，又阻塞 vacuum、撑大膨胀。看到这个数字不为零，就要去查是谁开了事务没关。

二、连接池：99% 的崩溃从「连接打满」开始

Postgres 的连接是进程模型——每个连接 fork 一个后端进程，成本远高于 MySQL 的线程模型。默认 max_connections 是 100，看起来不少，但稍有不慎就会被打爆。

2.1 为什么不能直接调大 max_connections

很多人第一反应是把 max_connections 从 100 调到 500、1000。这是错的。每个后端进程大约占用 5–10MB 内存，1000 个连接光进程本身就要吃掉几个 G，而且进程间上下文切换会让 CPU 在高并发下急剧退化。

经验值：Postgres 的有效并发上限通常在 100–200 个连接左右。超过这个数，吞吐量不升反降。

2.2 正确做法：上连接池

用 PgBouncer（或 Supavisor、pgcat 等）在应用和数据库之间加一层池化。PgBouncer 的 transaction 模式是绝大多数场景的最佳选择：

应用释放连接后，PgBouncer 把后端连接复用给下一个请求；
几千个客户端连接，可以复用几十个真正的 Postgres 连接。

; pgbouncer.ini 核心配置
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
listen_port = 6432
listen_addr = 0.0.0.0
pool_mode = transaction
default_pool_size = 20
max_client_conn = 1000
reserve_pool_size = 5

💡 关键点：pool_mode = transaction 意味着连接在事务结束时才归还到池里。这要求应用不能跨事务使用会话级特性，比如 LISTEN/NOTIFY、临时表、SET 会话变量、advisory lock。如果用到这些，需要单独留一个非池化的连接（直连 5432 端口）给它们。

2.3 怎么判断连接池该开多大

一个被反复验证的经验公式：

1	`连接数 ≈ (CPU 核心数 × 2) + 有效磁盘数`

比如一台 4 核机器，开 8–10 个后端连接往往就能跑满数据库的处理能力。剩下的并发靠队列在应用层吸收，而不是堆给 Postgres。

三、长事务：温水煮青蛙的杀手

长事务是 Postgres 里最隐蔽的问题之一。它不报错、不告警，但会带来三个连锁后果：

阻塞 vacuum：只要事务还开着，它启动之后产生的死 tuple 就不能被回收，表和索引开始膨胀。
撑大膨胀：膨胀一旦形成，查询要走更多数据页，IO 和内存压力上升。
持有锁：长事务里的 SELECT 也会拿 AccessShareLock，阻塞后续的 DDL。

3.1 找出运行时间过长的事务

SELECT
  pid,
  now() - xact_start AS duration,
  query,
  state
FROM pg_stat_activity
WHERE state IN ('idle in transaction', 'active')
ORDER BY duration DESC;

按 duration 倒序，最上面的几条就是元凶。

3.2 兜底：设置 idle_in_transaction_timeout

与其等事故发生，不如在数据库层加一道硬约束：

1 2	`-- 对特定角色设置空闲事务超时（单位毫秒，这里设为 60 秒） ALTER ROLE my_app SET idle_in_transaction_session_timeout = 60000;`

超过 60 秒还 idle in transaction 的连接会被 Postgres 主动杀掉。这是一个强烈推荐的防御性配置，尤其是对定时任务、后台 worker 这类容易「开着事务忘了关」的组件。

四、N+1 查询：最经典的性能陷阱

表现是：列表接口慢得不正常，但单条记录查询又很快。

4.1 怎么确认是 N+1

查 pg_stat_statements，按调用次数排序：

SELECT
  substring(query, 1, 80) AS short_query,
  calls,
  total_exec_time,
  mean_exec_time,
  rows
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 20;

如果看到同一条简单查询被调用了几千上万次，而每次都很快（mean_exec_time 很小），但 total_exec_time 很高——典型的 N+1。

💡 要用 pg_stat_statements，需要在 postgresql.conf 里把它加进 shared_preload_libraries 并重启。这是排查性能问题的第一神器，强烈建议生产环境默认开启。

4.2 修复方式

ORM 里用 eager loading（Rails 的 includes、Django 的 select_related/prefetch_related、SQLAlchemy 的 joinedload/selectinload）；
手写 SQL 的话，用 JOIN 或 WHERE id = ANY($1::int[]) 一次批量取回。

核心思想只有一个：把 N+1 次往返，压缩成 1 次或 2 次。

五、索引：用对、用够、别让它膨胀

5.1 找出缺失的索引

SELECT
  relname AS table_name,
  attname AS column_name,
  n_distinct,
  most_common_vals
FROM pg_stats
WHERE n_distinct > 100
ORDER BY n_distinct DESC;

更直接的信号来自 pg_stat_user_tables——大量扫描都走了全表扫描（seq_scan 高、seq_tup_read 很大）的表，说明缺索引：

SELECT
  relname,
  seq_scan,
  seq_tup_read,
  idx_scan
FROM pg_stat_user_tables
ORDER BY seq_tup_read DESC
LIMIT 10;

5.2 找出没人用的索引

索引不是免费的——每次写操作都要维护所有索引。没用的索引是纯负担：

SELECT
  schemaname,
  relname AS table_name,
  indexrelname AS index_name,
  idx_scan AS index_scans
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

idx_scan = 0 的索引，尤其是体积大的，是可以安全删掉的候选：

1 2	`-- 删除无用索引（CONCURRENTLY 不锁表，推荐生产环境使用） DROP INDEX CONCURRENTLY idx_my_unused_index;`

5.3 用 EXPLAIN 验证

1 2	`EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE user_id = 123 AND created_at > '2026-01-01';`

重点看：有没有 Seq Scan（大表上是红灯）；有没有 Index Only Scan（最理想）；Rows Removed by Filter 大不大（索引选错了）；Buffers: shared read 高不高（大量来自磁盘）。

六、膨胀（Bloat）：磁盘和性能的隐形黑洞

Postgres 的 MVCC 机制决定了：更新和删除不会立即释放空间，而是产生「死 tuple」，要等 vacuum 回收。

6.1 估算膨胀程度

-- 启用扩展（需要超级用户）
CREATE EXTENSION IF NOT EXISTS pgstattuple;

-- 查看某张表的实际膨胀情况
SELECT
  table_len,
  tuple_count,
  tuple_len,
  dead_tuple_count,
  dead_tuple_len,
  free_space
FROM pgstattuple('my_table');

dead_tuple_count 占比越高、free_space 越大，膨胀越严重。一般认为死 tuple 占比超过 10–20% 就该处理了。

6.2 让 autovacuum 别掉队

默认配置对写入密集的表往往太保守。针对热点表单独调：

ALTER TABLE my_hot_table SET (
  autovacuum_vacuum_scale_factor = 0.05,
  autovacuum_vacuum_threshold = 1000,
  autovacuum_analyze_scale_factor = 0.02
);

6.3 已经膨胀了怎么办

-- 重建索引，不阻塞读写（推荐）
REINDEX INDEX CONCURRENTLY idx_my_bloated_index;

-- 重建整张表（会锁表，谨慎使用，或用 pg_repack 在线重写）
VACUUM FULL my_table;

⚠️ VACUUM FULL 会锁死整张表并重写，生产环境对大表几乎不能用。在线重建推荐用 pg_repack 或 pg_squeeze。

七、锁等待：当查询互相挡道

7.1 看谁在等谁

SELECT
  blocked.pid     AS blocked_pid,
  blocked.query   AS blocked_query,
  blocking.pid    AS blocking_pid,
  blocking.query  AS blocking_query,
  now() - blocked.query_start AS waiting_duration
FROM pg_stat_activity AS blocked
JOIN pg_stat_activity AS blocking
  ON blocking.pid = ANY (pg_blocking_pids(blocked.pid))
WHERE blocked.state = 'active';

这条 SQL 能直接画出「阻塞关系图」：谁挡着谁、挡了多久。

7.2 关键参数：lock_timeout

DDL（比如 ALTER TABLE）默认会一直等锁。生产环境很危险——建议加超时：

1 2	`SET lock_timeout = '3s'; ALTER TABLE my_table ADD COLUMN new_col integer;`

如果 3 秒内拿不到锁，这条 DDL 会主动放弃，而不是死等。配合重试脚本，就能在业务低峰期「见缝插针」地完成迁移。

八、慢查询日志与监控告警

8.1 开启慢查询日志

在 postgresql.conf 里：

log_min_duration_statement = 200   # 记录执行超过 200ms 的语句
log_line_prefix = '%m [%p] %u@%d ' # 带时间、进程、用户、库
log_lock_waits = on                # 记录锁等待
log_temp_files = 0                 # 记录所有临时文件使用

8.2 监控告警红线

指标	含义	告警阈值建议
连接数使用率	`活跃连接 / max_connections`	> 80% 告警
`idle in transaction` 数量	空闲但持有事务的连接	> 5 持续 1 分钟告警
死 tuple 占比	表的膨胀程度	单表 > 20% 告警
复制延迟	主从延迟（如果有从库）	> 30s 告警
缓存命中率	`heap_blks_hit / (hit + read)`	< 90% 需要关注
数据库可用磁盘	剩余空间	< 15% 紧急告警

其中磁盘空间最容易被忽视、后果又最严重——Postgres 在磁盘写满时会进入只读模式（panic 状态），恢复极其麻烦。

写在最后

Postgres 是一台极其可靠的机器，但它不会自己保护自己。大多数线上事故，都不是因为 Postgres 撑不住业务，而是因为几个被忽视的小问题——一个忘关的事务、一个缺失的索引、一个没调的 vacuum——慢慢累积成了雪崩。

把这份清单里的检查项落地成日常监控和巡检，能让数据库在业务起飞时依然稳得住。

问题	一句话对策	关键命令/参数
连接打满	上连接池，别调大 max_connections	PgBouncer `pool_mode=transaction`
长事务	设 idle 事务超时	`idle_in_transaction_session_timeout`
N+1 查询	用 `pg_stat_statements` 定位	按 `calls` 排序
索引问题	补缺失、删冗余	`pg_stat_user_indexes`
膨胀	调激进 autovacuum	`autovacuum_vacuum_scale_factor`
锁等待	DDL 加 `lock_timeout`	`SET lock_timeout='3s'`
慢查询	开慢查询日志	`log_min_duration_statement=200`
监控	盯连接、膨胀、磁盘	磁盘 < 15% 紧急告警

如果这篇运维经验对你有帮助，欢迎点赞、在看、转发三连。

你们在生产环境的 Postgres 踩过哪些坑？欢迎在评论区留言交流 👇

数据库

#PostgreSQL #数据库 #运维 #性能优化 #连接池 #索引

Postgres 生产运维实战：连接、膨胀、锁与监控

https://www.boer.xyz/posts/postgres-ops-checklist/

作者

boer

发布于

2026年7月23日

许可协议

创业公司的 Postgres 生存指南下一篇