优化 PostgreSQL 的 25 条实战经验：从能跑到跑的稳-北京尧图网络

这些年跑过几个千万级、亿级数据量的生产系统后我意识到一件事大部分 PostgreSQL 性能问题加 CPU 和内存是解决不了的。真正管用的是理解 PostgreSQL 的“思考方式”——它怎么做查询规划、怎么利用索引、怎么处理并发。下面这些经验是在多次凌晨三点的故障排查中反复验证过的。如果一开始有人能告诉我这些很多坑根本不用踩。1.SELECT *是默认要戒掉的坏习惯看着无害其实代价很大-- 坏习惯SELECT*FROMordersWHEREuser_id123;-- 更好SELECTid,amount,created_atFROMordersWHEREuser_id123;每多取一列PostgreSQL 就要多读一页数据、多传一些字节到网络。更重要的是SELECT *常常让Index Only Scan无法生效——这是查询规划器里最快的访问路径之一。字段越少越有可能只扫索引不碰表。2. 别猜看EXPLAIN我见过太多人凭直觉调优查询。正确做法是EXPLAIN(ANALYZE,BUFFERS)SELECT...然后认真看输出有没有 Seq ScanNested Loop 的驱动表是不是太小Rows Removed by Filter是不是很大执行计划会直接告诉你时间花在哪了——问题是很多人不看或者看了也当没看见。3. 索引不是越多越好一个常见误解是“索引越多查询越快”。实际上每个索引在写入时都需要维护。一个从未被查询使用的索引纯粹是写操作的额外负担只增加开销而不产生收益。加索引之前先问一个问题这个索引到底有没有查询会用可以用pg_stat_user_indexes查看现有索引的命中情况。4. 联合索引的列顺序不是随便排的如果查询条件是WHEREorg_id?ANDstatus?索引应该建在(org_id, status)而不是(status, org_id)——除非你的查询主要只按status过滤。最左列决定了索引是否可用这个细节踩坑率极高。5. 部分索引被严重低估不用索引所有行只索引真正需要查询的那些CREATEINDEXidx_pending_ordersONorders(created_at)WHEREstatuspending;如果只有 5% 的订单是pending索引大小就只有原来的 5%——维护成本更低扫描速度更快。6. 覆盖索引直接从索引拿数据不碰表CREATEINDEXidx_user_emailONusers(email)INCLUDE(name);当查询只需要email和name时PostgreSQL 可以直接从索引返回结果完全不需要访问表数据。7. 用 Keyset 分页别用OFFSETOFFSET 100000 LIMIT 20会让 PostgreSQL 扫描并丢弃前 10 万行。更好的做法是WHEREid:last_seen_idORDERBYidLIMIT20第一页和第五万页的查询成本基本一样。8. 随机 UUID 会拖垮写入性能UUIDv4 的写入位置在索引中完全随机会导致索引页频繁分裂、缓存命中率下降。如果用 UUID优先考虑UUIDv7、ULID 或 KSUID——它们保持时间顺序写入集中在索引“热端”对写入性能更友好。9. JSONB 是工具不是 schema 的替代品JSONB 适合存元数据、用户偏好、功能开关这类真正的无模式数据。但如果你的查询主要围绕 JSONB 字段做过滤建议加上合适的 GIN 索引并做好基准测试——因为 JSONB 的查询性能经常会出乎意料。10. 重视 VACUUMMVCC 意味着每次更新和删除都会留下“死元组”。不清理索引会膨胀、表会变大、查询会越来越慢。autovacuum 会自动处理大部分场景但需要关注它是否跟得上写入节奏——尤其是在大规模批量操作之后可能需要手动触发VACUUM。11. 连接池是性价比最高的基础设施PostgreSQL 每个连接都是一个进程建立和维护开销不小。用 PgBouncer 或应用层的连接池如pgxpool将连接数控制在合理范围内能省下大量资源。12. 批量插入别逐条插入1 万条单独的INSERT意味着 1 万次网络往返。合并成多行插入INSERTINTOevents(a,b)VALUES(...),(...),(...);差别往往是一个数量级。13.COPY是批量导入之王当数据量达到百万级时COPY的优势非常明显——几分钟能完成普通INSERT几个小时才能处理完的工作。14. 分区表表大到一定程度后按月或按租户分区会带来明显收益维护更方便删除旧数据只需DROP TABLE查询只扫描相关分区。不要过早使用但也不必畏惧。15. 顺序扫描有时就是最优解看到 Seq Scan 不用恐慌。如果 PostgreSQL 预期要读取表的大部分数据顺序扫描比逐行走索引更快。规划器通常知道自己在做什么——除非基准测试证明它错了否则先信任它。16. 别让 N1 问题悄悄积累10 次独立执行的快查询往往输给 1 个写得好的联合查询——网络往返的开销本身就可能成为瓶颈。JOIN、CTE 和窗口函数就是为了避免在应用代码里循环查询而设计的。17. 用窗口函数替代应用层逻辑排名、去重、累计求和、比较相邻行——这些在应用层做既慢又费代码ROW_NUMBER()OVER(PARTITIONBYuser_idORDERBYcreated_atDESC)PostgreSQL 在数据存储的位置完成这些计算通常比应用层循环快得多。18. CTE 物化行为会影响性能在旧版本中每个 CTE 都会被物化——完整计算、存储、再读取。现代版本12通常会将它们内联到主查询中并且可以用MATERIALIZED / NOT MATERIALIZED控制行为。这个细节可能让同一个查询的 performance 天差地别。19. 过期的统计信息会生成糟糕的执行计划当 PostgreSQL 选择了奇怪的执行计划时问题往往不在于查询本身而在于统计信息已经过时。数据量发生大的变化后执行一下ANALYZE往往就能恢复正常。20. 开启pg_stat_statements看不见的瓶颈没法优化。这个扩展会记录每条查询的总耗时、调用次数和平均延迟。真正消耗数据库资源的查询往往不是你以为的那几条。21.FOR UPDATE SKIP LOCKED是任务队列的利器用 PostgreSQL 做任务队列时SELECT*FROMjobsWHEREstatuspendingORDERBYcreated_atFORUPDATESKIP LOCKEDLIMIT10;多个 worker 可以并行拉取任务不会互相阻塞也不会抢到同一行。22. 选择正确的数据类型timestamptz优先于timestamp—— 否则时区问题迟早会找上门text优先于varchar(n)—— 长度限制在 PostgreSQL 里弊大于利计数类字段用bigint——serial溢出那天会很不好过金额用numeric不要用 float23. 提前设置超时别等出问题再补这三个配置帮我避免了很多次生产事故SETstatement_timeout30s;SETlock_timeout5s;SETidle_in_transaction_session_timeout60s;24.LATERALJOIN 解决“每组 Top N”问题每个客户的最新订单、每篇文章的前三条评论——这类问题用LATERAL处理非常方便SELECTc.name,o.*FROMcustomers cCROSSJOINLATERAL(SELECT*FROMordersWHEREcustomer_idc.idORDERBYcreated_atDESCLIMIT3)o;25. 先测量再改改完再测直觉会骗人基准测试不会。每次优化都应该遵循相同的流程EXPLAIN ANALYZE记录当前状态改动一处重新测量。没有测量过的优化只能算“碰运气”。我最想告诉新人的一条原则“PostgreSQL 调优最重要的是什么”不是索引不是分区也不是硬件。是会读执行计划并且理解 PostgreSQL 为什么这么选。当执行计划不再像天书的时候数据库调优就不再有“魔法”的感觉而是一步步可验证的工程实践。

优化 PostgreSQL 的 25 条实战经验：从能跑到跑的稳

相关推荐

基于 RA8P1 的触摸屏演示工程

【Kubernetes从入门到精通】第34篇：Pod健康检查——Liveness、Readiness和Startup探针

手机投屏到电脑总翻车？scrcpy投屏工具完整上手指南，一次解决五大痛点

更多资讯

Python多态机制与动态类型系统解析

Unity游戏音频工程化：跨平台音乐播放与发布实战指南

GaussDB与MyBatis-Plus分页兼容性问题解决方案

STM32F407+lwIP+FreeRTOS网口调试与socket实战

新能源微电网与电动车协同调度建模与优化

从完美主义到无可挑剔：一套可复制的高质量行动法

今日更新

基于YOLOv11的石油泄漏智能检测系统开发实践

AI行业变局：合规、Mod化与上下文管理的实战启示

计算机视觉与AI原生应用融合的技术实践

本周精选

防幻觉智能体架构：电源硬件设计中的大模型编排与工具化实践

OpenGL面试核心考点：渲染管线、矩阵变换与性能排查全解析

全国计算机二级C语言真题PDF高效刷题指南

本月热门

我发现了一个新思路：用 Remotion + Claude Code 像写代码一样自动化生成短视频

Windows下 Codex 中 Chrome 和 Computer Use 插件不可用问题排查及解决参考方式：TaoToken 统一 Key 配置与验证

2026 大模型集体涨价：用 Python 做企业 Token 成本测算与选型避坑（附配置）

读完文章，想聊聊您的网站？