PostgreSQL 实践笔记：索引、事务与查询计划

PostgreSQL 在企业里常扮演「真实数据源」角色：事务、约束与丰富索引类型，让它既能扛 OLTP，也能承担部分分析负载。本文从建模、索引、查询计划与运维参数几条线，整理日常最常用、也最容易踩坑的点。

建模与约束

优先用外键与检查约束把业务规则写进数据库，而不是只依赖应用层。这样多个服务或脚本写入时仍有一致底线。多租户场景下，company_id 与行级策略要一起设计，避免漏条件导致串数据。

命名与迁移

表与列命名保持可读、一致；迁移脚本要可回滚或可分阶段发布（加列默认值、回填、切读）。大表加索引使用 CONCURRENTLY，减少对写入的长时间锁。

索引：B-Tree 与更多

多数等值与范围查询，B-Tree 仍是首选。若查询总带固定谓词（如 status = 'active'），部分索引能减小体积、提高命中率。JSONB 上的 GIN、表达式索引适合明确访问模式，但要评估写入放大。

何时不该加索引

低选择性列单独建索引收益有限；过多索引会拖慢写入与 vacuum。用 EXPLAIN (ANALYZE, BUFFERS) 验证计划，而不是凭感觉堆索引。

读懂查询计划

关注：估计行数 vs 实际行数（偏差大往往要 ANALYZE）、连接顺序与 Nested Loop 放大、是否走了索引-only scan。BUFFERS 帮助判断是 CPU 还是 I/O 瓶颈。预发打开 auto_explain 捕获慢查询，比线上救火更省成本。

统计信息与直方图

统计信息过期会导致错误计划；大表分区后，注意每区统计维护。对倾斜严重的列，可考虑扩展统计。

事务与隔离级别

默认 READ COMMITTED 适合多数业务；需要可重复读快照时升级到 REPEATABLE READ 或序列化，并设计死锁重试。长事务会拖慢 vacuum 与版本回收，应拆分或异步化。

锁与并发

显式锁（SELECT FOR UPDATE）要明确范围与顺序，减少死锁概率。批量任务注意批次大小与提交频率。

连接池与内存

应用侧用 PgBouncer 等池化，避免连接数爆炸。work_mem 随排序哈希需求调优，但过大并发会吃光内存。共享缓冲区与操作系统缓存的关系也要结合机器规格评估。

备份与可恢复性

定期演练恢复：不仅是全量，还要验证时间点恢复与关键表逻辑一致。逻辑备份与物理备份按 RPO/RTO 选择。

小结

PostgreSQL 的「稳」来自约束与统计信息一起工作。把规则放进 schema，把计划读进日常习惯，比只会写 SQL 更重要。