postgres-best-practices

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

Postgres

Rules for schema and queries on Postgres. They are language-agnostic; the Go half lives in the

go-idiomatic

skill (

references/database.md

references/patterns.md

Postgres 上 schema 和查询的规则。它们与语言无关；Go 部分位于

go-idiomatic

skill (

references/database.md

references/patterns.md

)。

Where to Look

查阅位置

If you're working on…	Load this
Go code that runs these queries ( `database/sql` , pgx)	`go-idiomatic` `references/database.md`
A closed value set mirrored as a Go type	`go-idiomatic` `references/patterns.md` § Typed Value Sets
Writing or changing a migration, any tool	`references/migrations.md`
Converting an existing Postgres `ENUM` to `text` + `CHECK`	`references/migrations.md` § ENUM Conversion
Migrations in a repo that uses goose	`references/goose.md` (only when the repo uses goose)
Only if the repo has `sqlc.yaml` : queries, config, CI	`references/sqlc.md`

如果你正在处理……	加载此内容
使用 `database/sql` 、pgx 运行这些查询的 Go 代码	`go-idiomatic` `references/database.md`
映射为 Go 类型的封闭值集合	`go-idiomatic` `references/patterns.md` § Typed Value Sets
编写或修改 migration（使用任何工具）	`references/migrations.md`
将现有 Postgres `ENUM` 转换为 `text` + `CHECK`	`references/migrations.md` § ENUM Conversion
使用 goose 的 repo 中的 migration	`references/goose.md` （仅当 repo 使用 goose 时）
仅当 repo 包含 `sqlc.yaml` 时：查询、配置、CI	`references/sqlc.md`

Schema

Closed value sets are
text
+ a named
CHECK
, never a Postgres
ENUM
. Adding a value to an
```
ENUM
```
is a type migration and removing one is not supported; a
```
CHECK
```
is one
```
ALTER
```
. Never mix the two styles in one schema, and never leave a status column unchecked. An existing
```
ENUM
```
is a conversion to schedule, not a style to extend.
Every foreign key column has an index. Postgres does not create one. Without it, deleting a parent scans the child table, and
```
ON DELETE CASCADE
```
turns that into one scan per row.
timestamptz
for instants,
date
only for calendar dates. Never store times as
```
text
```
.
jsonb
for JSON, never
text
.
Name every constraint and index, with one scheme (
```
<table>_<cols>_key
```
,
```
_fkey
```
,
```
_check
```
,
```
<table>_<cols>_idx
```
). Names are what errors and migrations refer to.
One meaning for absence per column. NULL or
```
''
```
, not both. Pick NULL unless the column is
```
NOT NULL DEFAULT ''
```
by design.
An append-only table gets a retention policy in the migration that creates it. Outboxes, run logs and audit trails only grow; decide how long rows live, index the column the cleanup filters on, and delete expired rows in batches (
```
DELETE ... WHERE id IN (SELECT id ... LIMIT n)
```
) from a scheduled job. Added later, the first cleanup is one huge delete on a huge table.

封闭值集合使用
text
+ 具名
CHECK
，绝不使用 Postgres
ENUM
。向
```
ENUM
```
添加值属于类型 migration，而删除值则不受支持；
```
CHECK
```
只需一次
```
ALTER
```
。同一 schema 中绝不能混用两种风格，也绝不能让状态列缺少
```
CHECK
```
约束。已有的
```
ENUM
```
应安排转换，而不是把它当作可继续扩展的风格。
每个外键列都要有索引。 Postgres 不会自动创建索引。缺少索引时，删除父记录会扫描子表，而
```
ON DELETE CASCADE
```
会将操作变成逐行扫描。
timestamptz
用于时间点，
date
仅用于日历日期。绝不要将时间存为
```
text
```
。
JSON 使用
jsonb
，绝不使用
text
。
为每个约束和索引命名，并采用统一命名规则 (
```
<table>_<cols>_key
```
,
```
_fkey
```
,
```
_check
```
,
```
<table>_<cols>_idx
```
)。错误信息和 migration 都通过名称引用它们。
每列的缺失状态只表达一种含义。 NULL 或
```
''
```
，二者不可并用。除非该列在设计上明确采用
```
NOT NULL DEFAULT ''
```
，否则选择 NULL。
append-only 表必须在创建它的 migration 中同时制定保留策略。 Outbox、run log 和 audit trail 只会增长；要决定记录保留多久，为清理条件所过滤的列建立索引，并由定时任务分批删除过期记录 (
```
DELETE ... WHERE id IN (SELECT id ... LIMIT n)
```
)。如果之后再补上该策略，第一次清理就会在大型表上执行一次巨型删除。

Queries

One clock per comparison.
```
DEFAULT now()
```
is fine for audit columns. Any time the application compares against, or a test asserts on, is passed in as a parameter. Mixing SQL
```
NOW()
```
with the app's
```
time.Now()
```
makes leases and expiries untestable and skewed.
Explicit column lists. No
```
SELECT *
```
and no
```
RETURNING *
```
; adding a column must not change what callers receive.
Every list query has a hard
LIMIT
. A list without one returns the whole table once the table grows. On growing tables page by keyset (
```
WHERE (created_at, id) < ($1, $2) ORDER BY created_at DESC, id DESC LIMIT $3
```
), not
```
OFFSET
```
, which reads and discards every skipped row.
No N+1 loops. Fetch with
```
= ANY($1::uuid[])
```
, insert with multi-row
```
VALUES
```
or
```
COPY
```
.
Claiming work uses
FOR UPDATE SKIP LOCKED
so concurrent workers take different rows instead of queueing on one. Update the claimed row in the same statement or transaction.
Read-then-write uses
SELECT ... FOR UPDATE
in a transaction. A plain read races.
Match unique violations (SQLSTATE
23505
) by constraint name, not by message text or by assuming which constraint fired. This is why constraints are named.
ON CONFLICT
names its target (
```
ON CONFLICT (col)
```
or
```
ON CONSTRAINT name
```
). A bare
```
ON CONFLICT DO NOTHING
```
silently swallows violations of constraints you did not mean.

每次比较只使用一个时钟。 审计列可以使用
```
DEFAULT now()
```
。应用进行时间比较或测试进行时间断言时，相关时间都应作为参数传入。混用 SQL
```
NOW()
```
与应用的
```
time.Now()
```
会使 lease 和过期逻辑无法测试且产生偏差。
显式列出列。 不使用
```
SELECT *
```
，也不使用
```
RETURNING *
```
；新增列不得改变调用方收到的内容。
每个列表查询都必须设置硬性
LIMIT
。没有
```
LIMIT
```
的列表查询会随着表增长而返回整张表中的所有数据。对于不断增长的表，使用 keyset 分页 (
```
WHERE (created_at, id) < ($1, $2) ORDER BY created_at DESC, id DESC LIMIT $3
```
)，而不是
```
OFFSET
```
；后者会读取并丢弃每一个被跳过的行。
禁止 N+1 循环。 使用
```
= ANY($1::uuid[])
```
获取数据，使用多行
```
VALUES
```
或
```
COPY
```
插入数据。
领取任务时使用
FOR UPDATE SKIP LOCKED
，让并发 worker 领取不同的行，而不是都排队等待同一行。在同一条语句或同一个事务中更新已领取的行。
先读后写操作应在事务中使用
SELECT ... FOR UPDATE
。普通读取会产生竞态。
按约束名称匹配唯一性冲突（SQLSTATE
23505
），不要按错误消息文本匹配，也不要假定触发的是哪个约束。这正是约束需要命名的原因。
ON CONFLICT
必须指定目标 (
```
ON CONFLICT (col)
```
或
```
ON CONSTRAINT name
```
)。不带目标的
```
ON CONFLICT DO NOTHING
```
会静默吞掉非目标约束的冲突。

Altering tables

修改表

Add columns nullable or with a constant default, then backfill, then add
```
NOT NULL
```
. Never rewrite a large table inside the migration that adds the column.
Backfill in batches, keyed on the primary key, committing between batches. One giant
```
UPDATE
```
holds locks and bloats the table.
Create indexes on live tables with
CREATE INDEX CONCURRENTLY
, outside a transaction.
Add constraints in two steps on big tables:
```
ADD CONSTRAINT ... NOT VALID
```
, then
```
VALIDATE CONSTRAINT
```
, which does not block writes.
Migrations are forward-only once merged. Fix mistakes with a new migration.

添加列时，先以可为空或带常量默认值的方式添加列，然后执行 backfill，最后添加
```
NOT NULL
```
。绝不要在添加该列的 migration 中重写大型表。
分批执行 backfill，以主键为依据，并在批次之间提交。单个巨型
```
UPDATE
```
会持有锁并导致表膨胀。
在生产环境中的表上使用
CREATE INDEX CONCURRENTLY
创建索引，并在事务之外执行。
在大型表上分两步添加约束：
```
ADD CONSTRAINT ... NOT VALID
```
，然后
```
VALIDATE CONSTRAINT
```
，后者不会阻塞写入。
migration 合并后只能向前执行。 使用新的 migration 修复错误。