初创公司的PostgreSQL生存指南

# Hatchet 来源：https://hatchet.run/blog/postgres-survival-guide 在过去半年多的时间里，我一直在为我们的工程师编写一份内部文档，试图将两年来的 Postgres 实战经验归纳成一篇相对连贯的文章。虽然我很喜欢 Postgres 手册（https://www.postgresql.org/docs/18/index.html），但当问题真正爆发时，读手册往往没那么直接——因为它实在是太详尽了。我觉得这份内容可能对其他人也有用，欢迎大家反馈（或者分享你们在生产环境中运行 Postgres 时学到的其他小技巧）。 在开始 Hatchet 之前，虽然我对 SQL 还算熟悉，但我的知识基本上只停留在“如果查询慢，那就建索引”。这也是本文的起点：我假设你已经熟悉 SQL 基础、行、表，并且大致了解索引是什么。 *如果所有查询都是 Claude 写的，那这份指南可能就没啥用了。推荐使用 supabase/agent-skills（https://github.com/supabase/agent-skills）* --- #### 关于 ORM 的快速说明（https://hatchet.run/blog/postgres-survival-guide#a-quick-note-on-orms） 这份指南依然有用，但你可能需要将一些技巧翻译成你喜欢的 ORM。随着规模扩大，很多优化在 ORM 下是无法实现的，除非你能突破抽象层直接写 SQL。你可以优雅地做到这一点，也可以不那么优雅；Prisma 的 TypedSQL（https://www.prisma.io/docs/orm/prisma-client/using-raw-sql/typedsql）或类似的东西看起来很有意思。我们在 Hatchet 使用 `sqlc`，它能提供非常类似的行为；如果你用的是 Go 技术栈，强烈推荐。 #### 目录（https://hatchet.run/blog/postgres-survival-guide#table-of-contents） - 简单基础：良好的读、写和模式（https://hatchet.run/blog/postgres-survival-guide#the-simple-stuff-good-reads-writes-and-schemas） - 编写良好的模式（https://hatchet.run/blog/postgres-survival-guide#writing-a-good-schema） - 编写良好的读查询（https://hatchet.run/blog/postgres-survival-guide#writing-good-read-queries） - 编写高性能的 JOIN（https://hatchet.run/blog/postgres-survival-guide#writing-performant-joins） - 复合索引及将 ORDER BY 与索引对齐（https://hatchet.run/blog/postgres-survival-guide#compound-indexes-and-aligning-order-by-to-your-indexes） - 编写良好的写查询（https://hatchet.run/blog/postgres-survival-guide#writing-good-write-queries） - 数据迁移（https://hatchet.run/blog/postgres-survival-guide#migrations） - 连接管理（https://hatchet.run/blog/postgres-survival-guide#connection-management） - 进阶：查询计划器、批量更新和自动清理（https://hatchet.run/blog/postgres-survival-guide#intermediate-the-query-planner-bulk-updates-and-autovacuum） - 认识最泄露的抽象：查询计划器（https://hatchet.run/blog/postgres-survival-guide#introducing-the-leakiest-of-abstractions-the-query-planner） - 有时候顺序扫描是合理的（https://hatchet.run/blog/postgres-survival-guide#sometimes-it-just-makes-sense-to-seq-scan） - 写入大量数据（https://hatchet.run/blog/postgres-survival-guide#writing-lots-of-data） - 默认的自动清理设置可能会搞垮你的数据库（https://hatchet.run/blog/postgres-survival-guide#default-autovacuum-settings-can-kill-your-database） - 其他类型的膨胀（https://hatchet.run/blog/postgres-survival-guide#other-types-of-bloat） - 一些高级内容（https://hatchet.run/blog/postgres-survival-guide#some-advanced-stuff） - FOR UPDATE SKIP LOCKED（https://hatchet.run/blog/postgres-survival-guide#for-update-skip-locked） - 分区（https://hatchet.run/blog/postgres-survival-guide#partitioning） - 大表迁移的技巧（https://hatchet.run/blog/postgres-survival-guide#tricks-for-large-table-migrations） ### 简单基础：良好的读、写和模式（https://hatchet.run/blog/postgres-survival-guide#the-simple-stuff-good-reads-writes-and-schemas） 让我们从基础开始：低流量下的查询和模式。 #### 编写良好的模式（https://hatchet.run/blog/postgres-survival-guide#writing-a-good-schema） 当应用部署后，模式是最难更改的部分，因此花些时间设计模式是值得的。我建议迭代式地构建模式：先为表和主键做一个粗略的设计，然后根据应用需求在这些表上编写一些查询。你可以通过以下问题来大致评估：*这是高读还是高写的表？读操作中最常见的过滤条件是什么？哪些列更新最频繁？* 如果想要更正规一些，可以参考数据库范式化（https://www.digitalocean.com/community/tutorials/database-normalization）到 1NF/2NF/3NF，但我发现范式有时与查询效率和易用性相矛盾，而这在快速迭代中至关重要——有时直接往 `jsonb` 列里塞数据反而更简单。 我对模式的几个经验法则： - 使用标识列（自增整数，性能略优于 `bigserial`）或内置 UUID 作为主键 - 始终使用 `timestamptz` - 始终使用主键 - 在低流量表上使用带级联删除的外键，尤其是当数据库一致性和正确性很重要时。高流量时需谨慎。 #### 编写良好的读查询（https://hatchet.run/blog/postgres-survival-guide#writing-good-read-queries） 从 `SELECT` 查询开始。一个有用但略不精确的心智模型是：在底层，Postgres 要么非常快速地找到表中的单行，要么通过*顺序扫描*读取表中的每一行 😞。 当使用以下条件过滤时，Postgres 会非常快速地找到单行： - 显式索引 - 唯一约束（只是索引的一种特殊形式） - 主键（在 Postgres 中，主键会自动索引） 默认情况下，索引使用 `btree` 实现。最好将索引视为 Postgres 中的另一个表，它以特定格式存储数据，从而优化查找（后面会详细介绍）。这些 btree 非常棒，因为查找单行的时间大约为 `log(n)`，其中 `n` 是表中的行数——也就是说，真的很快。 当 Postgres 无法使用索引时，它会使用顺序扫描，也叫 `seq scan`。顺序扫描比索引查找慢得多，但现代数据库加载行到内存的速度非常快，以至于一开始你可能根本注意不到：少于 20k 行的表上进行顺序扫描几乎是瞬间完成的。 #### 编写高性能的 JOIN（https://hatchet.run/blog/postgres-survival-guide#writing-performant-joins） 对于内连接，很少有不使用主键作为内连接的理由；否则通常意味着模式设计或范式化有问题。对待 `ON` 子句应该像对待 `WHERE` 子句一样尊重——同样的原则适用。使用索引。 #### 复合索引及将 ORDER BY 与索引对齐（https://hatchet.run/blog/postgres-survival-guide#compound-indexes-and-aligning-order-by-to-your-indexes） 通常，应用中第一个慢查询会是大表上的列表查询。类似于： 加载语法高亮... 这种情况下，你可以使用复合索引——一个合理的例子是： 加载语法高亮... 在更复杂的情况下，一个好的经验法则是：`ORDER BY` 列应该是索引中的最后一列，并且应将列与 `ORDER BY` 中的顺序对齐。注意，Postgres 可以双向扫描 btree，所以有时 `DESC` 无关紧要——但对于复合索引，这是好习惯。更多信息见这里（https://www.cybertec-postgresql.com/en/benefits-of-a-descending-index）。 #### 编写良好的写查询（https://hatchet.run/blog/postgres-survival-guide#writing-good-write-queries） 成功写入的前提是： 1. **保持事务短小。** 不要在事务中间去查询外部服务，除非你有充分理由。 2. **小心要锁定的行**；换句话说，只锁定你需要的行。每次更新一行时，你都会对该行加上一个短时间的锁，直到事务提交。 随着系统越来越忙，你会开始更多地感受到锁的影响。例如，将来你可能尝试用简单的 `CREATE INDEX` 命令创建索引——结果发现这会给表加锁，阻止插入和更新！在现有大表上创建索引时，始终使用 `CREATE INDEX CONCURRENTLY`。 #### 数据迁移（https://hatchet.run/blog/postgres-survival-guide#migrations） 精通编写迁移是一项重要的技术优势：它能帮助你更快迭代，并提高停机时间。作为起点，尽量保持迁移是累加式的（即不要删除列或修改列），并且尽可能在事务中运行迁移；这样回滚和部分迁移会更容易处理。随着你变得更熟练，可以开始研究扩展和收缩（expand and contract）迁移模式（https://martinfowler.com/bliki/ParallelChange.html）。 判断迁移好坏的最简单心智模型是：它是否会阻塞所有写入？不加 `CONCURRENTLY` 创建索引会阻塞所有写入，因此你可能会遇到停机。通常，需要调用 `ALTER TABLE` 的操作值得再三检查；例如，在非常大的表上添加新的检查约束也可能会阻塞写入（除非你使用 `NOT VALID` 关键字添加）。 #### 连接管理（https://hatchet.run/blog/postgres-survival-guide#connection-management） 每次对数据库执行事务或查询时，你都在使用一个连接。连接在多个维度（CPU 和内存）上都很昂贵，连接频繁切换可能导致大量不必要的资源浪费，因此连接应该是长生命周期的。连接风暴（同时启动大量新连接）也可能导致与内部 Postgres 锁相关的、非常难以调试的边缘情况。 由于所有这些连接隐患，外部连接池如 pgbouncer 非常棒！如果因某种原因无法添加，内存连接池是很好的第二选择。例如，因为 Hatchet 是开源的，我们并不假设所有用户数据库都使用连接池，所以我们使用 pgxpool（https://pkg.go.dev/github.com/jackc/pgx/v5/pgxpool）（Go 的内存连接池）来实现这一功能。 ### 进阶：查询计划器、批量更新和自动清理（https://hatchet.run/blog/postgres-survival-guide#intermediate-the-query-planner-bulk-updates-and-autovacuum） #### 认识最泄露的抽象：查询计划器（https://hatchet.run/blog/postgres-survival-guide#introducing-the-leakiest-of-abstractions-the-query-planner） 到了某个阶段，你的查询可能变得复杂到简单的索引不够用（而且你不应该无休止地向表添加索引——它们会带来开销）。这些查询可能涉及多个 `JOIN` 语句或不同类型的连接，查询数据的正确路径并不明确。 此时，你需要关注*查询计划器*。最好的情况下，查询计划器也是一个泄露的抽象（https://www.joelonsoftware.com/2002/11/11/the-law-of-leaky-abstractions/）。它是一个内部实现，你几乎无法控制它，但你必须了解它自发且有时非理性的行为。就像和 LLM 合作一样！ 查询计划器会查看你传入的查询，并找出如何将查询转换为数据库内部的一组操作。例如，它可能会查看你的查询，意识到需要使用索引。在理想世界中，查询计划器应该对于每个查询和参数集都知道最优的计划。但查询计划器基于有限的信息运行，有时它选不到最佳选项。 这个有限的信息就是表统计信息。你可以直接在 Postgres 中查询它： 加载语法高亮... 每次 `ANALYZE` 都会收集这些统计信息。自动清理运行时也会发生（见下文），因此更频繁的自动清理也意味着你的查询统计信息会更新。查询行为不正常的一个常见原因是 ANALYZE 不够频繁。 我认为将查询视为二进制（要么进行顺序扫描，要么不进行顺序扫描）有助于思考的原因是：你对查询进行越微观的优化，查询计划器出幺蛾子的风险就越大。如果你坚持通过主键和索引进行查询，查询计划器会轻松得多。 假设你的查询没有明显错误，但仍然很慢——如何调试呢？一些 Postgres 数据库提供商（如 Google CloudSQL）会对查询进行采样并保存慢查询——但很多不提供。这时，`EXPLAIN ANALYZE` 是你的好朋友。它会输出查询的查询计划并执行查询（在生产环境中执行要小心——你可以使用不带 `ANALYZE` 的 `EXPLAIN` 来仅获取计划），然后将其基于表统计信息的估算值与实际扫描的行数进行比较。我通常将 SQL 查询放在文件中，前面加上 `EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON)` 然后运行： 加载语法高亮... 接着使用 explain.dalibo.com（https://explain.dalibo.com/）来可视化执行计划。 #### 有时候顺序扫描是合理的（https://hatchet.run/blog/postgres-survival-guide#sometimes-it-just-makes-sense-to-seq-scan） 有些情况下，你认为应该使用索引，但查询计划器仍然进行顺序扫描，即使表统计信息是最新的且索引有效。在这些情况下，Postgres 通常估算顺序扫描的成本小于索引扫描的成本。索引扫描确实有一些开销；索引与表中的实际数据（称为堆）分开存储——在堆中找到所有行可能很昂贵！ 除非你能大幅重建查询，否则你可能不得不接受它会进行顺序扫描，或者考虑分区之类的方法（下面会详述）。 #### 写入大量数据（https://hatchet.run/blog/postgres-survival-guide#writing-lots-of-data） 假设你的应用正在扩展，需要快速写入大量数据。每个查询都有一些相关开销（除了我们之前讨论的连接开销外）：包括到数据库的往返时间、内部应用连接池获取连接的时间，以及 Postgres 处理查询的时间（包括一组内部 Postgres 锁（https://github.com/postgres/postgres/blob/master/src/backend/storage/lmgr/README），这些锁在高吞吐场景下可能成为瓶颈）。 为了减少这种开销，我们可以将一批行打包到每个查询中。最简单的方法是在一个隐式事务中一次性将所有查询发送到 Postgres 服务器（在 Go 中，我们可以使用 `pgx` 的 `SendBatch` 来执行）。批处理非常强大：我们发现它可以将吞吐量提高约 10 倍。我在这里（https://hatchet.run/blog/fastest-postgres-inserts）写了更多关于这一点以及其他快速写入数据的技巧。 #### 默认的自动清理设置可能会搞垮你的数据库（https://hatchet.run/blog/postgres-survival-guide#default-autovacuum-settings-can-kill-your-database） 自动清理是 Postgres 数据库中的关键操作，有时需要调整，特别是在高写入场景下。自动清理