两年生产踩坑换出的Postgres生存指南:索引之后,锁与连接才是常见瓶颈

开源工作流平台Hatchet最近公开了一份内部文档,题目直白:Postgres生存指南。素材来自过去两年生产环境里踩过的故障,不是官方手册的缩写版——官方文档写得足够全,但真出问题时没人有空一页页翻。

指南真正想纠正一个流行想法:数据库变慢,加个索引就能解决。Hatchet两年里攒下的经验是,表变大、并发变高之后,更常卡住系统的是锁、连接暴增和过期的统计信息,索引反而是最容易处理的那一部分。

Hatchet的指南:索引之后,锁、连接和统计信息更常成为瓶颈

指南从最基础的场景讲起——查询慢,加索引,btree结构把查找从全表扫描的O(n)降到log(n)。这部分没有争议,也不是指南的重点。

真正反直觉的地方在后面。给已经在跑的大表直接执行CREATE INDEX,会阻塞这张表上的写入,不阻塞读取,线上的插入和更新全部排队。Hatchet的建议是用CREATE INDEX CONCURRENTLY,代价是建索引的时间明显变长,而且一旦中途失败,会留下一个无效索引,需要手动检查再重建。

给大表加约束也有同样的坑。直接跑ALTER TABLE加CHECK约束会锁写入,Hatchet的做法是先加NOT VALID版本的约束,再单独执行VALIDATE CONSTRAINT,把校验历史数据的开销和加约束本身的锁分开。

连接是第二个容易被低估的成本。每条查询占一个连接,连接本身吃CPU和内存,短时间内连接数暴增还会触发内部锁的异常行为。Hatchet的做法是在服务前面挂PgBouncer这类外部连接池;因为项目开源,用户自建数据库不一定配了连接池,团队又在自己的Go服务里用pgxpool做一层内存级兜底。

PgBouncer不是接上就万事大吉。它的事务级连接池模式会让部分依赖会话状态的ORM功能失效,比如prepared statement缓存和某些advisory lock。要保留这些功能,通常得用会话级模式,或者像Hatchet一样在应用层再加一道pgxpool。

查询计划器是指南里说的“最漏的抽象”。它依赖pg_stats里的表统计信息做决策,统计信息越陈旧,计划器越容易选错执行路径。排查工具是EXPLAIN ANALYZE,把预估行数和实际扫描行数摆在一起对比,再丢进explain.dalibo.com看可视化结果。

这里有个容易被忽略的细节:EXPLAIN ANALYZE会真的执行这条语句。如果是写操作,最好包在一个会回滚的事务里测试,别在生产库上直接跑一条真实的DELETE。

ORM省下的开发时间,扩容期通常要还回去

Hatchet提醒用ORM的团队,指南里不少优化,普通ORM调用做不到,除非能跳出抽象层直接写SQL。Prisma TypedSQL和Hatchet自己在Go栈里用的sqlc,都是给ORM开一个能写原生SQL的口子。

这道取舍在小规模阶段基本不会显形。表不大、并发不高时,ORM生成的查询和手写SQL跑起来差别很小,顺序扫描在小表上也照样很快——这不是ORM的功劳,是数据量还没到需要精细调优的门槛。

门槛过了之后,ORM插不了手的地方开始变多:锁的粒度、执行计划的走向、autovacuum的触发频率,这些都得工程师自己盯。

对技术负责人来说,判断该不该绕开ORM,不用看代码规不规范,看这条查询是不是站点里最慢的那几条之一。全站前十的慢查询值得单独写SQL调,剩下九成查询留给ORM就够了。

经验阈值有边界:小表、托管服务和不同负载不能照搬

指南里给出的很多数字,比如“两万行以内的顺序扫描通常很快”,是Hatchet两年生产实践里的参考经验,不是PostgreSQL的性能红线。真实表现取决于写入量、表大小、硬件配置,以及是不是用了托管服务——像Google Cloud SQL这类平台自带慢查询采样,行为和自建实例不完全一样。

索引本身也不是免费的。每加一个索引,写入和更新都要多维护一份数据结构,表越大、写入越密集,这份成本越明显。默认的autovacuum设置会不会拖垮数据库,同样取决于具体的写入压力和参数配置,不是一个必然结果。

排查慢的时候,顺序更值得参考:先看pg_stat_activity里的连接数和等待状态,再看pg_locks有没有长时间持有的锁,然后翻pg_stat_user_tables里最近一次autovacuum的时间,最后才轮到EXPLAIN ANALYZE对比执行计划。索引缺失往往是这条排查链里最后才被确认的原因,不是第一个该怀疑的对象。

对正在把数据库运维交给几个后端工程师的初创团队,这份指南的价值不是给一套万能配置,而是提醒一件更基本的事——数据库过了某个规模拐点,ORM的便利和PostgreSQL的默认设置都只是起点,后面得靠工程师自己盯锁、执行计划和维护机制。