PostgreSQL 索引与 连接池 的正确姿势

最近接手了一个跑了好几年的老服务，数据库早就迁到 PostgreSQL 上了，一直相安无事。结果上周开始接口突然变慢，偶尔一个请求要好几秒才返回，过一会儿还蹦出来一个 FATAL: sorry, too many clients already。我的第一反应特别朴素：机器不行了，升配！差点就下单了，冷静下来翻了翻日志和监控才发现，锅一半在索引，一半在连接管理。下面是踩完坑之后整理的处理过程，都是实测可用的，照着做能少走点弯路。

索引：先 EXPLAIN，再动手

一开始我犯了个经典错误：觉得查询慢，凭感觉就建了个索引，建完该慢还是慢。后来老老实实把那条慢 SQL 拿出来跑了一遍：

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE user_id = 42 AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

输出里赫然写着 Seq Scan on orders，三百多万行挨个扫，能快才怪。

更搞笑的是我第一次建的是 status 的单列索引，建完执行计划纹丝不动。想想也是，这字段总共就三个值，九成的订单都是 paid，优化器一看这区分度，走索引还不如全表扫。这种低区分度的单列索引建了纯属给写入添堵。

真正有用的是联合索引，注意列的顺序：等值条件的列放前面，用来排序的列放最后。

CREATE INDEX CONCURRENTLY idx_orders_user_status_time
ON orders (user_id, status, created_at DESC);

加 CONCURRENTLY 是因为这是直接在生产表上建索引，不加的话会把表锁住，写操作全部排队，那场面想想就刺激。建完再跑一遍 EXPLAIN ANALYZE，变成了 Index Scan using idx_orders_user_status_time，耗时从 2.4s 掉到 3ms，效果立竿见影。

顺手说一句，索引不是越多越好，每个索引都在拖慢写入。可以隔段时间看看哪些索引是摆设：

SELECT indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE schemaname = 'public' AND idx_scan = 0;

查出来一堆 idx_scan = 0 的，多半是当年某个需求下线后没人敢动的。确认没用了就 DROP INDEX CONCURRENTLY 掉，写入立马轻快不少。

连接池：调大 max_connections 是饮鸩止渴

再看连接数报错。我的第一反应还是很朴素：too many clients？那把 max_connections 从 100 调到 500 不就完了。调完确实不报错了，然后高峰期整个库反而更卡了。

后来才搞明白，PostgreSQL 里每个连接对应一个独立进程，五百个连接哪怕大半在空闲，内存和进程切换的开销也不小，真正能干活的有效并发就那么点。连接堆得越多，CPU 花在切换上的时间越多，典型的干活的不多、围观的一大堆。

正确做法是在应用和数据库中间加一层 PgBouncer。我是直接 apt 装的，配置文件改起来不复杂：

[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
default_pool_size = 25
max_client_conn = 500

重点说 pool_mode。默认的 session 模式下，应用连上来之后这个后端连接就被它独占了，等于池子白建。改成 transaction，一个后端连接可以轮流服务多个客户端，事务一结束就还回池子，25 个连接扛 500 个客户端轻轻松松。

不过 transaction 模式有个坑要提前说：它不支持会话级的 prepared statement。要是你的驱动或 ORM 默认开着这功能（Java 的 JDBC 就经常默认开），切过去会直接报错。PgBouncer 1.21 之后的版本可以在配置里加一行 max_prepared_statements = 100 解决，老版本就老老实实把应用侧的 prepared statement 关掉，或者退回 session 模式。我这边版本比较新，加一行配置就完事了。

userlist.txt 里就是用户名和密码，一行一个：

"myuser" "mypassword"

改完重启 pgbouncer，应用侧连接串的端口从 5432 改成 6432，其他什么都不用动。想看池子的状态，可以连到 6432 端口的 pgbouncer 库里执行 SHOW POOLS;，cl_waiting 那一列要是经常不是 0，说明池子小了，再慢慢往上加。

另外 default_pool_size 别拍脑袋设个 200，连接池存在的意义就是用少量连接干活，一般按 CPU 核数的两倍左右起步，观察着调就行。

顺带记几个小坑

一是 LIKE '%关键词%' 这种两边都带百分号的查询，B-tree 索引完全帮不上忙，别在那儿纳闷索引为什么不生效。真有这种需求就装个 pg_trgm 扩展建 GIN 索引，CREATE EXTENSION pg_trgm; 一行就装好了，具体用法到时候再搜，反正方向是这个方向。

二是大批量导入或者删过大量数据之后，记得手动跑一下 ANALYZE。统计信息太旧的话，优化器会拿着过期数据选执行计划，好端端的索引它就是不走，你还以为索引坏了。

三是有些连接忘了 commit，一直挂在 idle in transaction 状态，既占着连接池的坑，还会阻塞 vacuum。给数据库设个超时，省心很多：

ALTER SYSTEM SET idle_in_transaction_session_timeout = '60s';

折腾完这些，接口的 P99 从 3 秒多回到了 100ms 以内，too many clients 的报警也没再响过。总结下来其实就两句话：查询慢，先 EXPLAIN ANALYZE 看清楚再动手，别凭感觉建索引；连接多，上 PgBouncer，别跟 max_connections 较劲。省下来的升配钱，拿去买几杯奶茶不香吗。