The startup's Postgres survival guide

The startup’s Postgres survival guide

初创公司的 Postgres 生存指南

A guide to preventing Postgres from toppling over. 一份防止 Postgres 数据库崩溃的指南。

Alexander Belanger, Co-Founder, Hatchet Alexander Belanger，Hatchet 联合创始人

Over the past half year or so, I’ve been writing an internal doc for our engineers trying to distill two years of Postgres battles into a somewhat cohesive document. While I love the Postgres manual, I find it’s hard to turn to when shit hits the fan because it’s just so darn comprehensive. I thought this might be useful for others and would appreciate feedback (or other tidbits that you’ve learned running Postgres in production). 在过去半年左右的时间里，我一直在为我们的工程师编写一份内部文档，试图将两年的 Postgres 实战经验提炼成一份相对连贯的指南。虽然我很喜欢 Postgres 的官方手册，但当紧急情况发生时，它实在太全面了，以至于很难快速查阅。我想这份文档可能对其他人也有用，同时也欢迎大家提供反馈（或者分享你在生产环境运行 Postgres 时学到的其他技巧）。

Before starting Hatchet, while I was familiar with SQL, the extent of my knowledge was basically: if a query is slow, you need an index. That’s the starting point for this doc; I’m going to assume you’re familiar with SQL basics, rows, tables, and know roughly what an index is. And if Claude is writing all of your queries, this might be a waste of time! I recommend supabase/agent-skills. 在创办 Hatchet 之前，虽然我熟悉 SQL，但我的知识水平基本仅限于：如果查询慢，就需要加索引。这就是本文档的起点；我假设你熟悉 SQL 基础知识、行、表，并且大致了解什么是索引。如果你的查询全是由 Claude 编写的，那这篇文章可能是在浪费你的时间！我建议去看看 supabase/agent-skills。

A quick note on ORMs

关于 ORM 的简要说明

This guide should still be useful, but you might need to translate some of these tips into your ORM of choice. Lots of optimizations as you scale just aren’t possible with ORMs unless you can break past the abstraction layer and write SQL. You can do this gracefully or non-gracefully; Prisma TypedSQL or equivalents look interesting for this. We use sqlc at Hatchet which gets us very similar behavior; highly recommend if you’re a Go stack. 本指南依然适用，但你可能需要将其中一些建议转化为你所使用的 ORM 的语法。随着规模扩大，许多优化在 ORM 中是无法实现的，除非你能突破抽象层直接编写 SQL。你可以优雅或不那么优雅地实现这一点；Prisma TypedSQL 或类似工具在这方面很有趣。我们在 Hatchet 使用 sqlc，它能提供非常相似的效果；如果你使用 Go 技术栈，强烈推荐。

The simple stuff: good reads, writes and schemas

基础篇：良好的读写操作与模式设计

Let’s start with the basics: queries and schemas at low volume. 让我们从基础开始：低负载下的查询与模式设计。

Writing a good schema

编写良好的模式 (Schema)

After you’re deployed, schemas are by far the hardest to change moving forward, so it’s worth spending some time on them. I’d recommend building your schema iteratively: start with a rough approximation for your tables and primary keys, then write some queries on those tables based on your application needs. You can approximate this with some questions: Is this a high-read and/or high-write table? What are the most common filters on reads? Which columns am I updating the most? 在部署之后，模式（Schema）是后续最难更改的部分，因此值得花时间打磨。我建议以迭代方式构建模式：先为表和主键建立一个粗略的框架，然后根据应用需求编写一些查询。你可以通过以下问题来评估：这是一个高读还是高写表？读取时最常用的过滤条件是什么？哪些列更新最频繁？

If you want to be more formal about it, you can look into database normalization into 1NF/2NF/3NF, but I’ve found normal forms to sometimes be at odds with query efficiency and ease of use, which is critical when you’re moving fast—sometimes it’s just easier to dump data into a jsonb column. 如果你想更规范一些，可以研究数据库的 1NF/2NF/3NF 范式，但我发现范式有时会与查询效率和易用性产生冲突，而这在快速迭代时至关重要——有时直接把数据塞进 jsonb 列反而更简单。

My rules of thumb for schemas are: 我关于模式设计的经验法则如下：

Use identity columns (auto-incrementing integers, slightly more performant than bigserial) or built-in UUIDs for primary keys. (使用 identity 列（自增整数，比 bigserial 性能稍好）或内置 UUID 作为主键。)
Always use timestamptz. (始终使用 timestamptz。)
Always use primary keys. (始终使用主键。)
Use foreign keys with cascading deletes for low-volume tables, particularly where database consistency and correctness are important. Careful at higher volume. (在低负载表中使用带有级联删除的外键，特别是在数据库一致性和正确性很重要的情况下。在高负载下需谨慎使用。)

Writing good read queries

编写良好的读取查询

Let’s start with SELECT queries. A useful—albeit slightly inaccurate—mental model for fast selects is: under the hood, Postgres is either going to find a single row in a table very quickly, or it’s going to read every single row in your table using something called a sequential scan 😞. 让我们从 SELECT 查询开始。对于快速查询，一个有用但略显不精确的思维模型是：在底层，Postgres 要么非常快地找到表中的某一行，要么就必须使用所谓的“顺序扫描”（sequential scan）来读取表中的每一行 😞。

It’s going to find a single row very quickly when you filter by: 当你通过以下方式过滤时，它能非常快地找到单行：

An explicit index (显式索引)
A unique constraint (just a special case of index) (唯一约束，这只是索引的一种特殊情况)
A primary key (these are automatically indexed in Postgres) (主键，Postgres 会自动为其建立索引)

Indexes by default use a btree implementation. It’s most helpful to think of indexes as just another table in Postgres, with data stored in a specific format which is optimized for lookups. These trees are great because finding a single row happens in approximately log(n) time, where n is the number of rows in the table—in other words, really fast. 索引默认使用 btree 实现。将索引视为 Postgres 中的另一张表会很有帮助，其数据以特定格式存储，专门针对查找进行了优化。这些树结构非常棒，因为查找单行的时间复杂度大约是 log(n)，其中 n 是表中的行数——换句话说，非常快。

When Postgres can’t use an index, it’ll use something called a sequential scan, or seq scan. Seq scans are much slower than index lookups, but modern databases are so fast at loading rows into memory that you probably won’t even notice at first: seq scans on tables with less than 20k rows are pretty much instant. 当 Postgres 无法使用索引时，它会使用所谓的顺序扫描（seq scan）。顺序扫描比索引查找慢得多，但现代数据库将行加载到内存中的速度非常快，以至于你起初可能根本察觉不到：对于少于 2 万行的表，顺序扫描几乎是瞬间完成的。

Writing performant joins

编写高性能的连接 (Join)

For inner joins, there’s rarely an argument for not using primary keys as the inner join; it usually speaks to a schema design or normalization problem. Treat ON clauses with the same respect as a WHERE clause—the same principles apply. Use an index. 对于内连接（inner join），几乎没有理由不使用主键进行连接；如果非要这样做，通常说明存在模式设计或范式化问题。对待 ON 子句要像对待 WHERE 子句一样重视——同样的原则适用。请务必使用索引。

Compound indexes and aligning ORDER BY to your indexes

复合索引与 ORDER BY 的对齐

Often the first slow query in your application will be a list query across a large table. In more complex cases, a good rule of thumb is: the ORDER BY columns should be the last columns in the index, and you should align columns to the ordering in the ORDER BY. Note that Postgres can scan btrees in both directions, so sometimes the DESC is irrelevant—but for compound indexes it’s good practice. 通常，应用中第一个慢查询往往是针对大表的列表查询。在更复杂的情况下，一个好的经验法则是：ORDER BY 的列应该是索引中的最后几列，并且你应该使列的顺序与 ORDER BY 中的顺序对齐。请注意，Postgres 可以双向扫描 btree，因此有时 DESC 并不重要，但对于复合索引来说，这是一种良好的实践。

Writing good write queries

编写良好的写入查询

The premise of successful writes is: Keep transactions short. Don’t go querying an external service in the middle of a transaction unless you have a really good reason to. Be careful of the rows you’re locking for writing; in other words, only lock what you need. Every time you update a row, you’re taking out a lock on that row for a short period of time until the transaction commits. As your system gets busier, you’re going to start noticing the impact of locks more. 成功写入的前提是：保持事务简短。除非有非常充分的理由，否则不要在事务中间去查询外部服务。要小心你为写入而锁定的行；换句话说，只锁定你需要的部分。每次更新一行时，你都会在该行上加锁，直到事务提交。随着系统负载增加，你将开始更明显地感受到锁的影响。