在过去的半年里，我一直在为我们工程师撰写一份内部文档，试图将两年的 Postgres 战斗经验凝炼成一个相对连贯的文档。虽然我喜欢 Postgres 手册，但我发现当事情出现问题时，这本手册实在太过全面，难以快速查找。我想这可能对其他人有用，并希望能得到反馈（或者其他你在生产环境下运行 Postgres 时学到的经验）。在创办 Hatchet 之前，尽管我熟悉 SQL，但我掌握的知识实际上仅限于：如果查询很慢，你需要一个索引。这是本文档的起点；我假设你熟悉 SQL 基础知识、行、表，并大致了解索引是什么。如果 Claude 在为你编写所有查询，那这可能就是浪费时间！我推荐使用 supabase/agent-skills。关于 ORM 的简要说明：这份指南仍然适用，但你可能需要将一些建议转化为你选择的 ORM。随着规模的扩大，很多优化在 ORM 下是不可能的，除非你能够突破抽象层并编写 SQL。你可以优雅或不优雅地做到这一点；Prisma TypedSQL 或类似的工具在这方面看起来很有趣。我们在 Hatchet 使用 sqlc，这为我们提供了非常类似的行为；如果你使用 Go 栈，我强烈推荐。目录：基础知识：良好的读取、写入和架构，编写良好的架构，编写良好的读取查询，编写高性能连接，复合索引和对齐 ORDER BY 到你的索引，编写良好的写入查询，迁移，连接管理；中级：查询规划器、大批量更新和自动真空；介绍最泄漏的抽象——查询规划器；有时序列扫描更合理；写入大量数据；默认的自动真空设置可能会毁掉你的数据库；其他类型的膨胀；一些高级内容：FOR UPDATE SKIP LOCKED、分区、大表迁移的技巧。基础知识：良好的读取、写入和架构。让我们从基础开始：低需求下的查询和架构。编写良好的架构：在你部署之后，架构是向前变化中最难的，因此值得花点时间在上面。我建议逐步构建你的架构：先为你的表和主键做一个粗略的近似，然后根据你的应用需求在这些表上编写一些查询。你可以通过一些问题来近似：这是一个高读取和/或高写入的表吗？读取时最常见的过滤条件是什么？我更新最多的列是哪些？如果你想要更正式一点，可以了解数据库的规范化到 1NF/2NF/3NF，但我发现规范形式有时与查询效率和易用性存在冲突，而这在快速发展的过程中至关重要——有时将数据直接放入 jsonb 列中更简单。我的架构经验法则是：使用身份列（自增整数，性能稍好于 bigserial）或内置 UUID 作为主键；始终使用 timestamptz；始终使用主键；对低流量表使用带有级联删除的外键，特别是在数据库一致性和正确性很重要的情况下。在高流量时要小心。编写良好的读取查询：让我们首先谈谈 SELECT 查询。一个有用——尽管稍微不准确的——快速选择的心理模型是：在幕后，Postgres 要么会非常快速地找到表中的一行，要么会使用一种叫做顺序扫描的方法读取你表中的每一行 😞。当你按以下条件过滤时，它能够快速找到一行：显式索引、唯一约束（索引的特殊情况）、主键（这些在 Postgres 中会自动建立索引）。索引默认使用 btree 实现。最便于理解索引的是将其视作 Postgres 中的另一张表，其数据以特定格式存储，优化了查找（稍后会详细讲解）。这些树很棒，因为找到一行大约需要 log(n) 的时间，其中 n 是表中的行数——换句话说，速度非常快。当 Postgres 无法使用索引时，它会使用顺序扫描，或者称为 seq scan。Seq 扫描比索引查找慢得多，但现代数据库在将行加载到内存中的速度如此之快，以至于你可能一开始根本不会注意到：在行数少于 2 万的表上，seq 扫描几乎是瞬时的。编写高性能连接：对于内连接，很少有理由不使用主键作为内连接；这通常表明架构设计或规范化问题。在处理 ON 子句时，给予它与 WHERE 子句同样的尊重——同样的原则适用。使用索引。复合索引和将 ORDER BY 对齐到你的索引：通常应用程序中的第一个缓慢查询是在大表中的列表查询。类似于：加载语法高亮...在这种情况下，你可以使用复合索引——一个合理的索引可能是：加载语法高亮...在更复杂的情况下，一个好的经验法则是：ORDER BY 列