避免 Postgres 崩溃实用指南

Alexander Belanger 联合创始人 Hatchet

过去半年多，我一直在为团队工程师撰写一份内部文档，试图把两年里和 Postgres 打交道的经验整理成一份条理清晰的指南。我很认可Postgres 官方手册，但遇到紧急问题时却很难直接从中找到答案——内容实在太庞杂了。我想这份文档或许对其他人也有用，欢迎大家提出反馈，或是分享你们在生产环境运维 Postgres 的实用技巧。

在创立 Hatchet 之前，我虽然懂 SQL，但认知基本停留在「查询慢就加索引」的层面。这份文档就从这个起点展开，默认你已经掌握 SQL 基础、了解行和表的概念，并且大致知道索引是什么。

如果你的查询全靠 Claude 生成，那这份指南可能对你没用！我推荐了解 supabase/agent-skills

即便你用 ORM（对象关系映射），这份指南依然有参考价值，但你可能需要把部分技巧适配到你选用的 ORM 中。随着业务规模增长，很多优化仅靠 ORM 无法实现，必须打破抽象层直接编写 SQL。你可以选择优雅的方式，比如Prisma TypedSQL这类工具就很有意思。我们 Hatchet 用的是 sqlc，能实现类似效果；如果你用 Go 技术栈，强烈推荐试试。

• 基础篇：高效读写与合理设计表结构
• 进阶篇：查询规划器、批量更新与自动清理
• 高级特性篇

我们先从基础讲起：低数据量场景下的查询与表结构设计。

系统上线后，表结构是最难修改的部分，因此前期值得多花些精力打磨。我建议采用迭代式设计：先大致规划表和主键，再根据业务需求编写一些查询语句。你可以通过几个问题来梳理思路：这张表属于高读量还是高写量？查询时最常用的过滤条件是什么？哪些字段更新最频繁？

如果想更规范些，可以参考数据库规范化，将表结构设计到 1NF/2NF/3NF 标准。但我发现规范化有时会与查询效率和易用性冲突，而这两点在快速迭代阶段至关重要——有时候直接把数据存入 jsonb 字段反而更省事。

我设计表结构的几条经验法则：

• 主键选用自增身份列（性能略优于 bigserial）或内置 UUID
• 时间字段一律使用 timestamptz
• 所有表必须设置主键
• 低数据量且对一致性要求高的表，使用带级联删除的外键；高数据量场景下需谨慎使用

接下来聊聊 SELECT 查询。理解高效查询的一个简单（虽有轻微偏差）思路是：Postgres 在底层要么能快速定位到单条数据，要么就得用「顺序扫描」遍历整张表的所有行😞。

当你通过以下条件过滤时，Postgres 能快速定位单条数据：

• 显式创建的索引
• 唯一约束（本质是一种特殊索引）
• 主键（Postgres 会自动为主键创建索引）

默认情况下，索引采用 btree（二叉树）实现。你可以把索引理解成 Postgres 里的一张特殊表，数据以优化查询的格式存储（后续会详细说明）。这种树形结构的优势在于，查找单条数据的时间复杂度约为 log(n)（n 为表的行数）——换句话说，速度非常快。

当 Postgres 无法使用索引时，就会执行顺序扫描（简称 seq scan）。顺序扫描比索引查询慢得多，但现代数据库加载数据到内存的速度极快，数据量较小时你可能根本察觉不到：行数少于 2 万的表，顺序扫描几乎是瞬间完成的。

对于内连接，除非表结构或规范化存在问题，否则几乎都应该用主键作为连接条件。对待 ON 子句要像对待 WHERE 子句一样重视——两者遵循相同的优化原则：使用索引。

应用中出现的第一个慢查询，往往是针对大数据量表的列表查询，比如：

这种情况下，你可以创建复合索引，合理的设计可能是：

更复杂的场景下，有个实用原则：ORDER BY 子句中的字段应放在索引的末尾，且顺序要与 ORDER BY 一致。需要注意的是，Postgres 可以双向扫描二叉树，因此有时 DESC 关键字并不重要，但对于复合索引来说，保持顺序仍是最佳实践。更多细节可参考这里。

高效写入的核心原则是：

• 事务要短。除非有充分理由，否则不要在事务中调用外部服务。
• 谨慎锁定行。只锁定你需要操作的行。每次更新行时，Postgres 会在该行加锁，直到事务提交才释放。

随着系统负载升高，锁的影响会越来越明显。比如你以后可能会用简单的 CREATE INDEX 命令创建索引，但这个操作会锁定整个表，阻止插入和更新！因此，在已有大数据量表上创建索引时，务必使用 CREATE INDEX CONCURRENTLY。

熟练掌握迁移技巧是重要的技术优势：它能帮你更快迭代，同时提升系统可用性。入门阶段，尽量让迁移操作保持「增量式」（即不删除字段），并尽可能在事务中执行，这样回滚或处理部分失败的迁移会容易很多。进阶后，可以学习「扩容-收缩」迁移模式。

判断迁移操作是否安全的简单标准是：它会不会阻塞所有写入？不带 CONCURRENTLY 的索引创建会阻塞写入，可能导致系统停机。一般来说，所有 ALTER TABLE 操作都需要格外注意；比如给大数据量表添加新的检查约束时，也会阻塞写入（除非使用 NOT VALID 关键字）。

每次向数据库发起事务或查询，都会占用一个连接。连接在 CPU、内存等多个维度的成本都很高，频繁创建销毁连接会造成大量不必要的资源浪费，因此连接应保持长生命周期。「连接风暴」（短时间内创建大量新连接）还可能引发与 Postgres 内部锁相关的疑难问题。

正因如此，pgbouncer 这类外部连接池工具非常实用！如果因某些原因无法使用外部连接池，内存连接池是不错的替代方案。比如 Hatchet 是开源项目，我们无法假设所有用户的数据库都使用连接池，因此采用了pgxpool（Go 语言的内存连接池）来管理连接。

当查询复杂度上升到一定程度，简单的索引可能就不够用了（而且也不能无限制地添加索引——它们会带来额外开销）。这类查询可能包含多个 JOIN 语句，或涉及不同类型的连接，导致数据查询路径不明确。

这时你需要关注查询规划器。它本质上是一个「泄漏抽象」：作为 Postgres 的内部实现，你几乎无法直接控制它，但必须了解它那看似随机、有时甚至不太合理的行为——就像和大语言模型打交道一样！

查询规划器会分析你提交的查询，然后决定如何将其转换为数据库内部的操作序列。比如它会判断是否需要使用索引。理想情况下，它应该能针对每个查询和参数组合选择最优执行计划，但由于只能基于有限信息做决策，它有时会选错方案。

这里的「有限信息」指的是表统计数据。你可以直接在 Postgres 中查询这些数据：

这些统计数据由 ANALYZE 命令收集，自动清理（autovacuum）运行时也会执行该命令（下文会详细说明）。因此自动清理越频繁，查询统计数据就越及时。查询行为异常的常见原因之一，就是统计数据更新不及时。

我认为将查询分为「顺序扫描」和「非顺序扫描」两类很有用，原因在于：你对查询的微优化越多，查询规划器出现异常的风险就越高。如果始终基于主键和索引查询，查询规划器的工作会轻松很多。

如果查询看起来没有明显问题，但依然很慢，该怎么调试？部分 Postgres 云服务商（比如 Google CloudSQL）会采样并保存慢查询日志，但很多服务商并不提供这个功能。这时 EXPLAIN ANALYZE 就是你的好帮手。它会输出查询的执行计划并实际执行查询（生产环境中需谨慎使用，你可以单独用 EXPLAIN 只生成执行计划），然后将基于表统计数据的估算结果与实际扫描行数进行对比。我通常会把 SQL 查询写入文件，在开头加上 EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON)，然后执行：

接着用explain.dalibo.com可视化执行计划。

有时明明应该使用索引，但查询规划器在统计数据最新、索引有效的情况下，依然选择顺序扫描。这种情况通常是因为 Postgres 估算后认为，顺序扫描的成本低于索引扫描。索引扫描确实存在额外开销：索引与表的实际数据（称为堆）分开存储，从堆中查找所有匹配行的成本可能很高！

除非能大幅重构查询，否则你可能不得不接受顺序扫描，或是考虑分区等方案（下文会介绍）。

当应用规模扩大，需要快速写入大量数据时，每个查询本身会带来一些额外开销（和之前提到的连接开销不同）：包括数据库往返时间、应用连接池获取连接的时间，以及 Postgres 处理查询的时间（其中Postgres 内部锁在高吞吐量场景下可能成为瓶颈）。

要减少这类开销，我们可以把多行数据打包到一个查询中。最简单的方式是在隐式事务中一次性发送所有查询（在 Go 中，可以用 pgx 的 SendBatch 方法）。批量写入的效果非常显著：我们测试发现，它能将吞吐量提升约 10 倍。我在这篇文章中详细介绍了批量写入及其他快速写入技巧。

自动清理（autovacuum）是 Postgres 中至关重要的操作，有时需要根据场景调优，尤其是高写入量场景。自动清理守护进程负责多项工作，包括清理死元组和管理事务 ID。

什么是死元组？元组是行在文件系统中的存储实例。每次更新或删除行时，Postgres 会保留该行的旧版本，直到所有在更新/删除操作前启动的事务全部提交或回滚。那些无法被任何事务读取的行版本，就是死元组。

如果写入速度过快，自动清理可能跟不上，系统会迅速陷入不健康状态。你可以通过查询数据库中的活跃进程发现这一点：

如果自动清理进程运行时间超过约 1 小时，你可能需要调整自动清理设置！更多信息可参考这篇文章。

这个指标值得重点监控：如果自动清理还未来得及回收事务 ID，系统就耗尽了所有可用 ID，就会进入可怕的事务 ID 回卷状态，导致长时间停机。

除了死元组，繁忙的 Postgres 系统还常出现另外两类膨胀问题：

• 表膨胀：由页面未完全填充导致。Postgres 以页为单位在磁盘上存储行，每页大小为 8KB。当新行无法放入现有页面时，Postgres 会创建新页面。但死元组被回收后，页面可能无法被填满，导致磁盘占用显著增加。避免表膨胀的最佳方式是在膨胀发生前调优自动清理。对于已经膨胀的表，可以借助 pg_repack 这类扩展工具处理，而 Postgres 内置的 VACUUM FULL 通常不是好选择。值得一提的是，Postgres 19 将引入 REPACK...CONCURRENTLY 命令，我尚未测试，但它似乎是一个支持并发的表重组解决方案。
• 索引膨胀：是表膨胀的特殊情况，同样可以通过合理的自动清理设置解决。Postgres 内置了处理索引膨胀的命令：REINDEX INDEX CONCURRENTLY。

最后，我想介绍几个对 Hatchet 帮助很大的 Postgres 高级特性。

理解这个特性的最佳方式是：它会为你在事务中选中的行加锁，防止其他查询干扰，但不会阻塞其他操作。我们主要用它实现任务队列；基于 Postgres 的单查询队列可以这样实现：

它还适用于以下场景：需要对多行数据进行独立更新，或是在多个应用实例间管理对象的租约（比如我们用它在 Hatchet 引擎间分配租户租约）。

Postgres 支持内置分区功能，可以根据行值（如时间戳或哈希值）将表拆分为多个子表。这对时序数据（比如我们的历史任务数据）非常有用，原因如下：

• 每个分区可以独立执行自动清理，让你能针对表的不同部分调整清理策略
• 删除旧数据几乎是瞬间完成的——只需删除对应的分区，无需逐行遍历

分区也有缺点：如果查询规划阶段 Postgres 没有正确裁剪分区，读查询会产生额外开销（不过 Postgres 在新版本中已经大幅优化了这一点）。

我在这篇文章中详细分享了我们使用分区的经验。

（注：这里指的不是数据库迁移，而是将大量数据从一张表迁移到另一张表，我们每年都会遇到几次这样的需求）

如果直接在单个事务中复制大数据量表，可能需要数小时才能完成。这很不可取——长时间运行的事务会阻碍自动清理正常工作，导致系统因死元组积累而膨胀。此外，如果迁移期间仍向旧表写入数据，新表无法同步这些新增数据。

因此我们需要一种安全的迁移方式：不依赖事务，且迁移启动后写入旧表的数据能同步到新表。我们摸索出的技巧之一是：使用 Postgres 触发器，并在事务外执行批量数据回填，同时利用主键的唯一约束防止重复写入。

以上就是全部内容！如果你有其他 Postgres 扩容经验，或是相关问题，欢迎随时交流。