创业公司的 Postgres 生存指南

原文：The startup’s Postgres survival guide · 作者：Alexander Belanger（Hatchet 联合创始人）· 发布于 2026-07-22

本文为全文翻译，严格保留原文的所有代码示例、论断与数据。译者补充一律以「译注：」标出，与原文内容明确区分。

过去大半年里，我一直在为我们的工程师写一份内部文档，试图把两年的 Postgres 实战经验浓缩成一份相对成体系的笔记。我很喜欢 Postgres 官方手册，但真到救火的时候它很难上手——因为它实在太「全面」了。我想这份东西对别人可能也有用，也欢迎反馈（或分享你在生产环境跑 Postgres 学到的其他经验）。

在创办 Hatchet 之前，虽然我熟悉 SQL，但我的知识储备基本就是：查询慢了，就加个索引。这份文档的起点就在这里——我会假设你熟悉 SQL 基础、行、表，也大致知道索引是什么。

（当然，如果你的查询全是 Claude 写的，这篇可能对你没啥用！我推荐 supabase/agent-skills。）

关于 ORM 的一点说明

本指南对你应该依然有用，但有些技巧你需要翻译成你所用 ORM 的写法。随着规模扩大，很多优化在 ORM 里根本做不到，除非你能突破那层抽象直接写 SQL——突破的方式可以优雅也可以不优雅；Prisma TypedSQL 之类的东西看起来挺有意思。我们在 Hatchet 用 sqlc，能拿到非常类似的行为；如果你是 Go 技术栈，强烈推荐。

第一部分：基础——读、写与 schema

让我们从最基础的开始：低负载下的查询和 schema。

1. 写一个好的 schema

部署上线之后，schema 是后续最难改动的东西，所以值得在上面花点时间。我建议迭代式地构建 schema：先给表和主键搭个粗略的雏形，然后基于应用需求在这些表上写一些查询。你可以用几个问题来近似这个过程：这是一张高读和/或高写的表吗？读的时候最常见的过滤条件是什么？我最多更新哪些列？

如果想更正式一点，可以研究一下数据库范式化（1NF/2NF/3NF），但我发现范式有时会与查询效率和易用性相冲突——而当你快速迭代时，易用性至关重要——有时候直接把数据塞进一个 jsonb 列反而更省事。

我对 schema 的经验法则是：

• 主键用 identity 列（自增整数，比 bigserial 性能略好）或内置 UUID
• 一律用 timestamptz
• 一律用主键
• 对低负载的表使用带级联删除的外键，尤其是在数据库一致性和正确性重要的地方；高负载时要谨慎

译注：identity 列即 GENERATED ... AS IDENTITY（SQL 标准），bigserial 是 Postgres 老的自增语法。timestamptz 存 UTC、按会话时区呈现，而 timestamp 不存时区，多时区环境下容易埋 bug。

2. 写好的读查询

先从 SELECT 查询说起。一个有用（尽管略不准确）的「快查询」心智模型是：在底层，Postgres 要么非常快地在表里找到单行，要么用一种叫顺序扫描（sequential scan，seq scan）😞 的方式把表里每一行都读一遍。

当你按以下方式过滤时，它会非常快地找到单行：

• 一个显式索引
• 一个唯一约束（索引的特例）
• 一个主键（Postgres 会自动为主键建索引）

索引默认用 btree 实现。最有助于理解的方式，是把索引当成 Postgres 里的「另一张表」——数据按某种为查找优化的格式存放。这些树之所以棒，是因为找单行大约只需要 log(n) 时间（n 是表的行数）——换句话说，非常快。

当 Postgres 用不上索引时，它就会用顺序扫描（seq scan）。Seq scan 比索引查找慢得多，但现代数据库把行加载进内存的速度快到你一开始可能根本注意不到：在不到 2 万行的表上做 seq scan 基本是瞬时的。

3. 写高性能的 JOIN

对于内连接（inner join），几乎没有什么理由不用主键做内连接；如果不用主键，通常说明 schema 设计或范式化有问题。把 ON 子句当作 WHERE 子句一样重视——同样的原则适用：用索引。

4. 复合索引：让 ORDER BY 与索引对齐

应用里第一个变慢的查询，通常是对一张大表的列表查询。类似这样：

1
2
3
4
5
6

SELECT *
FROM documents
WHERE organization_id = <uuid>
  AND created_at >= NOW() - INTERVAL '1 day'
ORDER BY created_at DESC
LIMIT 50;

这种情况下，你可以用一个复合索引——一个合理的写法是：

1
2

CREATE INDEX CONCURRENTLY idx_documents_org_created
    ON documents (organization_id, created_at DESC);

更复杂的场景里，一条好的经验法则是：ORDER BY 的列应该是索引里最后的列，并且要让列与 ORDER BY 中的顺序对齐。注意 Postgres 可以双向扫描 btree，所以有时 DESC 并不影响——但对复合索引来说，这样做是好习惯。更多信息见这里。

译注：CREATE INDEX CONCURRENTLY 的 CONCURRENTLY 关键字表示建索引时不阻塞写操作（普通 CREATE INDEX 会锁表禁止 insert/update）。生产环境对已有的大表建索引必须加它，代价是构建稍慢、且不能在事务内执行。

5. 写好的写查询

成功写操作的前提是：

1. 保持事务简短。 除非有非常充分的理由，否则不要在事务中间去查询外部服务。
2. 小心你为写而锁住的行——换句话说，只锁你需要的。每次更新一行，你都会在事务提交前的短时间内对该行加锁。

随着系统变忙，你会越来越明显地感受到锁的影响。尤其是，你将来某个时候可能想用一条简单的 CREATE INDEX 建索引：结果它会锁住你的表、阻止插入和更新！在对已有大表建索引时，永远用 CREATE INDEX CONCURRENTLY。

6. 迁移

把迁移写好是一项重要的技术优势：它能让你迭代更快、提高可用时间。作为起点，尽量保持迁移是累加式的（换句话说，不要删除或移除列），并尽可能在事务里执行；这会让回滚和部分迁移好处理得多。等你更熟练了，可以研究 expand and contract 迁移模式。

好迁移最简单的心智模型是：这个操作会不会阻塞我所有的写？ 不带 CONCURRENTLY 建索引会阻塞所有写，所以可能出现停机。一般来说，凡是调用 ALTER TABLE 的操作都值得再看一眼；例如，给一张非常大的表加一个新的 check 约束也会阻塞写（除非你用 NOT VALID 关键字添加）。

7. 连接管理

每次对数据库执行事务或查询，你都在占用一个连接。连接在多个维度上都很昂贵（CPU 和内存），高频率地开关连接会导致大量不必要的资源浪费，所以连接应该是长生命周期的。连接风暴（你同时开始用大量新连接）还会导致一些与 Postgres 内部锁相关的、极难调试的边界情况。

正因为这些连接「坑」，像 pgbouncer 这样的外部连接池器非常棒！如果因为某些原因加不了，内存级连接池是很好的次选。例如，因为 Hatchet 是开源的，我们不假设所有用户数据库都用了连接池器，所以我们用 pgxpool（Go 的内存连接池）来做这件事。

第二部分：进阶——查询计划器、批量写入与 autovacuum

8. 引入「最漏的抽象」——查询计划器

到了某个阶段，你的查询可能复杂到简单索引已经不够用（而且你不应该没完没了地给表加索引——索引是有开销的）。查询可能涉及很多 JOIN，或者不同类型的连接，使得查询数据的正确路径并不清晰。

这时候，你就需要关心**查询计划器（query planner）**了。往好了说，查询计划器是一个漏的抽象（leaky abstraction）。它是个内部实现，你几乎无法控制它，但你必须了解它那些自发的、有时近乎无理的行为。这有点像在跟一个 LLM 打交道！

查询计划器看你传入的查询，判断该如何把它翻译成数据库内部的一组操作。例如，它可能看了你的查询，意识到需要用索引。理想情况下，查询计划器对每一条查询和每一组参数都能选出完美计划。但查询计划器是在有限信息下运作的，有时它选的并不是最优解。

这有限的信息就是表的统计信息。 在 Postgres 里你可以直接查到：

1
2
3

SELECT *
FROM pg_stats
WHERE tablename = 'mytable';

这些统计信息在每次 ANALYZE 时收集。autovacuum 运行时也会触发（见下文），所以更频繁的 autovacuum 也意味着你的查询统计信息会更新。查询表现异常的一个常见原因，就是 analyze 得不够频繁。

我认为把查询看作二元——「要么 seq scan，要么不 seq scan」——之所以有用，原因是：你对一个查询越是微优化，查询计划器「发疯」的风险就越大。如果你坚持按主键和索引来查询，查询计划器的日子会好过得多。

假设你的查询里没有明显错误，但它还是慢——你怎么调试？一些 Postgres 数据库提供商（如 Google CloudSQL）会采样你的查询并保存慢查询——但很多提供商不会。这时 EXPLAIN ANALYZE 就是你的朋友。它会输出查询计划并执行查询（在生产环境要小心——你可以用不带 ANALYZE 的 EXPLAIN 只拿计划），然后把它基于表统计的估算与实际扫描的行数做对比。我通常把 SQL 查询放进一个文件，前面加上 EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON)，然后运行：

1

psql -XqAt -f explain.sql -d $DATABASE_URL > analyze.json

再用 explain.dalibo.com 来可视化执行计划。

9. 有时候 Seq Scan 就是合理的

有些情况下，你以为该用索引，但查询计划器依然在 seq scan——尽管表统计是最新的、索引也是有效的。这些情况下，Postgres 通常是在估算 seq scan 的成本会低于索引扫描的成本。索引扫描确实有开销：索引与表里实际数据（称为 heap）分开存放——在 heap 里找到所有行可能很贵！

除非你能大幅重构查询，否则你可能只能接受它会 seq scan，或者考虑像分区这样的方案（见下文）。

10. 写大量数据

假设你的应用在扩张，你需要快速写入大量数据。每条查询都有一些相关开销（与我们之前谈到的连接开销是两回事）：这包括到数据库的往返时间、应用内部连接池获取连接的时间，以及 Postgres 处理查询的时间（包括一组内部 Postgres 锁，它们在高吞吐场景下可能成为瓶颈）。

为了降低这个开销，我们可以把一批行打包进每条查询。最简单的做法是把所有查询一次性发给 Postgres 服务器，放进一个隐式事务里（在 Go 里，我们可以用 pgx 执行一个 SendBatch）。批量写入非常强大：我们发现它能让吞吐量提升约 10 倍（~10×）。 我在这里写了更多关于这个以及其他快速写入数据的技巧。

11. 默认的 autovacuum 设置可能搞垮你的数据库

autovacuum 是 Postgres 数据库里一项关键操作，有时需要调优，尤其是在高写入场景下。autovacuum 守护进程负责很多事情，包括清理死元组和管理工作 ID。

什么是死元组（dead tuple）？元组是文件系统上一行数据的实例。每次你更新或删除一行，该行的一个版本会留在 Postgres 里，直到所有在该行被更新或删除之前开始的事务都已提交或回滚。这些已经无法被任何事务读到的行，就是死元组。

如果你写入数据快到一定程度，有时 autovacuum 会跟不上，这会让你非常快地陷入一个极不健康的状态。你在查询数据库的活动进程时会看到这一点：

1
2
3
4
5
6
7
8
9
10
11
12
13
14

SELECT
    pid,
    usename,
    application_name,
    client_addr,
    query_start,
    now() AS now,
    state,
    now() - query_start AS duration,
    LEFT(query, 200) AS query
FROM pg_stat_activity
WHERE state != 'idle'
  AND query NOT ILIKE '%pg_stat_activity%'
ORDER BY duration DESC;

如果你看到一个 autovacuum 查询运行超过约 1 小时（~1 hour），你可能就该考虑调整 autovacuum 设置了！ 见这篇文章了解更多。

这件事值得监控：如果系统在 autovacuum 回收事务 ID 之前就把它们用光了，你会陷入一种可怕的状态，叫做事务 ID 回卷（transaction id wraparound）。这意味着一大段停机。

译注：原文在这一节给出的可执行 SQL 就是上面这条 pg_stat_activity 监控查询，以及「超过约 1 小时需调」这条判断红线，并未给出具体的 autovacuum 参数调优 SQL。具体如何调（如调整 autovacuum_vacuum_scale_factor 等阈值）可参考原文链接的 Cybertec 文章。

12. 其他类型的膨胀

除了死元组，在一个繁忙的 Postgres 系统里，你还会经常遇到另外两种膨胀：

1. 由未填满的数据页导致的表膨胀。 Postgres 把行存在磁盘上的页（page）里，每页 8KB。当 Postgres 无法把新行塞进现有页时，它会新建一页。但当死元组被回收后，这可能导致页没有被完全填满，从而增加 Postgres 的磁盘占用，有时增幅显著。避免表膨胀最好的办法，是在膨胀之前就调好 autovacuum。但也有一些扩展能帮助处理已经膨胀的表，比如 pg_repack——因为 Postgres 内置的 VACUUM FULL 几乎从来不是个好主意。注意 Postgres 19 将引入 REPACK...CONCURRENTLY，我还没测试过，但它看起来可能是并发重打包表的一个不错方案。
2. 索引膨胀是表膨胀的一个特例，同样可以通过良好的 autovacuum 设置来缓解。但 Postgres 有一个内置命令专门处理它，就是 REINDEX INDEX CONCURRENTLY。

译注：VACUUM FULL 虽能回收表空间，但会锁死整张表并重写，生产环境对大表几乎不可用；这正是原文推荐 pg_repack 的原因。

第三部分：一些高级用法

我想用一组对我们在 Hatchet 特别有用的高级 Postgres 特性来收尾。

13. FOR UPDATE SKIP LOCKED

理解这个 Postgres 特性最好的方式是：它把你选中的行预留给你的事务使用，同时又不会干扰其他查询。 我们主要用它来实现我们的任务队列；在 Postgres 里可以用单条查询实现一个队列，像这样：

1
2
3
4
5
6
7
8
9
10
11
12
13
14

-- name: PopTasks :many
WITH eligible_tasks AS (
    SELECT *
    FROM tasks
    WHERE status = 'QUEUED'
    ORDER BY id ASC
    FOR UPDATE SKIP LOCKED
    LIMIT 100
)
UPDATE tasks
SET status = 'RUNNING'
FROM eligible_tasks
WHERE tasks.id = eligible_tasks.id
RETURNING tasks.*;

译注：这是原文的原始写法——用 CTE（WITH eligible_tasks）配合 FOR UPDATE SKIP LOCKED 选出至多 100 条排队任务，再在同一条语句里 UPDATE ... FROM ... RETURNING 把它们原子地标记为运行中并返回。这正是原文强调的「**单条查询（single-query）**队列」：多个 worker 并发执行时，SKIP LOCKED 保证它们各自拿到不同的行、互不阻塞也不重复。

它在你需要对行做大量相互独立的更新时也非常有用，或者在你跨应用的多个实例管理对象租约时（例如，我们用它把租户租约分发到各个 Hatchet 引擎上）。

14. 分区

Postgres 有内置的分区功能，允许你按行值（如时间戳或哈希）把表细分。这对时序数据（在我们的场景里是历史任务数据）极其有用，因为：

1. 每个分区可以独立 autovacuum，这让你能在表上横向扩展 autovacuum 的能力
2. 删除老数据近乎瞬时——你直接 drop 掉那个表分区，而不是逐行删除

分区确实也有代价：如果 Postgres 在规划阶段没有裁剪分区（partition pruning），读查询会有额外开销（这一点 Postgres 在近几个版本已经好很多了）。

我在这里写了更多关于我们分区经验的内容。

译注：原文这一节只口头描述了分区的收益与代价，并未给出建分区的 SQL 示例；它最想强调的其实是第一条——「每个分区可独立 autovacuum」，这是分区对写入繁忙的大表一个常被忽略的好处。

15. 大表数据迁移的技巧

（注意，这里说的不是数据库 schema 迁移，而是把大量数据从一张表搬到另一张表——我们发现自己一年总要干几次这种事。）

如果你试图迁移非常大的表，在单个事务里复制数据可能要花好几个小时。这可不好——长时间运行的事务会妨碍 autovacuum 正常工作，从而让整个系统被死元组塞满膨胀。而且如果你想继续往老表写入，新表是看不到这些新数据的。

所以我们需要搞清楚：如何在没有事务的情况下安全迁移数据，同时让（迁移开始后的）新写入也被复制到新表。我们学到的一个技巧是：使用 Postgres 触发器，并在事务之外运行大批量的回填（backfill），用主键上的唯一约束来防止重复写入。

就这些了！如果你有其他关于 Postgres 扩展的经验——或者对 Postgres 扩展有任何问题——欢迎随时联系。

原文：The startup’s Postgres survival guide — Hatchet Blog · 作者 Alexander Belanger

本文为全文翻译，保留原文全部代码示例（documents 复合索引、pg_stats / pg_stat_activity 查询、PopTasks 单语句队列等）、论断与数据（批量写入约 10× 吞吐、autovacuum 超过约 1 小时需调等）。译者补充一律以「译注：」标出。点击文末「阅读原文」可查看英文原版。

如果这篇翻译对你有帮助，欢迎点赞、在看、转发三连。

你们在生产环境的 Postgres 踩过哪些坑？最让你印象深刻的是哪一类问题？欢迎在评论区留言交流 👇