Hatchet 的 Postgres 生存指南 – 核心要点与 HN 讨论

概览

本指南为在生产环境中运行 Postgres 提供了一份简洁的检查清单，涵盖了模式设计、查询性能、autovacuum 以及诸如 FOR UPDATE SKIP LOCKED 和分区等高级特性。

模式设计

从迭代式模式开始：草拟表和主键，针对它们编写查询，然后进行优化。使用 identity columns（自增整数）或内置的 UUID 作为主键。始终使用 timestamptz 处理时间戳。务必定义主键。仅在一致性至关重要的低数据量表中应用带有级联删除的外部键；在高数据量表中请谨慎使用。

查询性能

为了避免缓慢的顺序扫描，请通过索引列、唯一约束或主键进行过滤。在 ORDER BY 列是最后一个索引键且匹配排序方向时，使用复合索引。保持事务简短，并且只锁定你需要的行。在现有大表上创建索引时，务必使用 CREATE INDEX CONCURRENTLY 以避免阻塞写入。

批量写入与 Autovacuum

通过分批处理行（例如使用 pgx SendBatch）来提高插入吞吐量，这可以将吞吐量提高约 10 倍。监控 autovacuum：如果一个 autovacuum 查询运行时间超过约 1 小时，请调整设置以防止死元组（dead tuple）堆积和事务 ID 回绕（transaction ID wraparound）。使用 pg_repack 等扩展（或 Postgres 19 中即将推出的 REPACK…CONCURRENTLY）来解决表膨胀问题，使用 REINDEX INDEX CONCURRENTLY 来解决索引膨胀问题。

高级特性

使用 FOR UPDATE SKIP LOCKED 在不阻塞其他会话的情况下实现作业队列或租约分配。对时间序列数据应用声明式分区，以便实现独立的 autovacuum 并通过删除分区实现即时清理旧数据。对于无法在单个事务中运行的大表迁移，可以将触发器与事务外的分批回填结合使用，依靠唯一约束来避免重复写入。

社区见解 (HN 评论)

评论者补充了实用的建议和反面观点：

• 备份策略：“你应该做的第一件事之一难道不应该是制定备份策略吗？” – @theallan

• UUID 版本与锁顺序：“通常使用 uuidv7 而不是 uuid (通常是 v4)” 以及 “确保你的锁在所有查询中都是确定性排序的（例如，始终按 id asc），否则你会发生死锁” – @ComputerGuru

• 查询规划器提示：“使用 explain (generic_plan) 可以实现：a) 原样复制带有参数占位符的查询，b) 在 Postgres 无法看到特定参数值的情况下，查看查询实际是如何优化的” – @ComputerGuru

• 索引类型：“每个人都默认使用 btree 索引，这很沉重并会增加索引膨胀。如果你只需要通过列/id 进行查找，而不需要排序或获取大于/小于某个参数的值，请考虑使用 hash 索引。” 以及 “学习一下 GIN (和 GIST) 索引。” – @ComputerGuru

• 外部键级联：“我讨厌级联，原因很简单：在大多数地方，大多数开发人员……生活在与数据库通信的 Python/Node/Go/或其他应用程序中……级联删除……基本上是魔法，理解为什么删除表 A 中的一行会自动删除表 B 中的内容可能会非常困难” – @mjr00

• 监控 XID 回绕：“如果你接近 XID 回绕，AWS 会给你发邮件。在一家初创公司，这封邮件很可能会被忽略……你希望 AWS 监控的任何内容都能通过呼叫器（pager）发送给你。” – @thundergolfer

• 范式 vs JSONB：“我发现范式有时会与查询效率和易用性相抵触……有时直接将数据dump进一个 jsonb 列反而更容易。” – @sgarland (同时也提到了 BRIN 对时间序列的实用性)

• 关于时间戳建议的分歧：“我倾向于使用 timestamp (不带时区)，这迫使我在所有地方都使用 UTC……” – @lennoff

• 避免死锁：“是否存在一种行之有效的“纪律”或实践？例如，在现实世界混乱的业务代码库中，你真的能对表施加“顺序”来避免哲学家就餐问题吗？” – @ucarion

• 内存中连接 (In-memory joins)：“我们在代码库中有几个地方会独立执行两个或更多更简单的查询，然后遍历它们的结果并使用 map 来匹配相关的行……” – @mrkaye97 (来自 Hatchet 的 Matt)

• 迁移工具和 JSON 列：“有一个名为 Grate 的.Net 工具，我倾向于用它进行模式迁移……还有一个没提到的点……在很多用例中利用 JSON 列并完全避免连接 (joins)。” – @tracker1

• 缺失的存储函数和 text 类型：“我在那篇文章里搜索了“function”，结果为零。令人失望。甚至没有对存储函数进行最粗略的讨论？” 以及 “完全没有提到 text，在 Postgres 中强烈建议使用 text 而不是愚蠢的旧 varchar(255)” – @traceroute66

• 连接池风险：“你难道不会面临从其他请求中泄露权限或信息的风险吗？” – @groundzeros2015

• 托管建议：“数据库管理的第一条规则是，除非你愿意雇人全职负责，否则不要托管或管理你的数据库” – @thisismyswamp

• 规范化与角色：“需要更多地强调这一点的重要性！……一个人也不应该害怕拥有多个 Postgres 实例……最后，Postgres 角色（其“用户”系统）拥有惊人的力量。” – @zer00eyz

• 检查清单式建议：包含九个要点的列表，包括避免使用 ORM、使用 serial PK、谨慎使用 jsonb、使用 append-only 作为事实来源、使用连接池、避免显式事务、避免使用 SELECT FOR UPDATE、不要重新发明类型系统，以及不要重新发明图数据库 (graph DBs)。 – @frollogaston

• 分析型工作负载：“如果你的领域是重分析型的，不要试图为分析优化你的 Postgres。遵循标准模式，将你的数据镜像到数据仓库，然后在那里大显身手。” – @hasyimibhar

这些社区观点通过原文章未涵盖的运维考量扩展了指南。