postgresql

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

PostgreSQL

Data Type Defaults

数据类型默认值

Need	Use	Avoid
Primary key	`BIGINT GENERATED ALWAYS AS IDENTITY`	`SERIAL` , `BIGSERIAL`
Timestamps	`TIMESTAMPTZ`	`TIMESTAMP` (loses timezone)
Text	`TEXT`	`VARCHAR(n)` unless constraint needed
Money	`NUMERIC(precision, scale)`	`MONEY` , `FLOAT`
Boolean	`BOOLEAN` with `NOT NULL DEFAULT`	nullable booleans
JSON	`JSONB`	`JSON` (no indexing), text JSON
UUID	`gen_random_uuid()` (PG13+)	`uuid-ossp` extension
IP addresses	`INET` / `CIDR`	text
Ranges	`TSTZRANGE` , `INT4RANGE` , etc.	pair of columns

需求	推荐使用	避免使用
主键	`BIGINT GENERATED ALWAYS AS IDENTITY`	`SERIAL` 、 `BIGSERIAL`
时间戳	`TIMESTAMPTZ`	`TIMESTAMP` （会丢失时区信息）
文本	`TEXT`	`VARCHAR(n)` （除非有明确长度约束需求）
金额	`NUMERIC(precision, scale)`	`MONEY` 、 `FLOAT`
布尔值	带 `NOT NULL DEFAULT` 的 `BOOLEAN`	可空布尔值
JSON 数据	`JSONB`	`JSON` （不支持索引）、文本格式的 JSON
UUID	`gen_random_uuid()` （PG13+）	`uuid-ossp` 扩展
IP 地址	`INET` / `CIDR`	文本格式
范围类型	`TSTZRANGE` 、 `INT4RANGE` 等	用两个列表示范围

Schema Rules

模式设计规则

Every FK column gets an index (PG does NOT auto-create these)
```
NOT NULL
```
on every column unless NULL has business meaning
```
CHECK
```
constraints for domain rules at DB level

EXCLUDE

constraints for range overlaps:

EXCLUDE USING gist (room WITH =, during WITH &&)

Default

created_at TIMESTAMPTZ NOT NULL DEFAULT now()

Separate
```
updated_at
```
with trigger, never trust app layer alone. Gate it with
```
WHEN (OLD.* IS DISTINCT FROM NEW.*)
```
so a no-op write neither fires the function nor bumps the timestamp -- on
```
BEFORE UPDATE
```
the row image is built before the trigger runs, so the comparison sees the caller's row, not the one the trigger is about to stamp.
Use
```
BIGINT
```
PKs -- cheaper JOINs than UUID, better index locality
Safe migrations:
```
CREATE INDEX CONCURRENTLY
```
, add columns with a non-volatile
```
DEFAULT
```
(instant add). Never
```
ALTER TYPE
```
on large tables in-place.
A
```
DEFAULT
```
whose expression is
```
VOLATILE
```
rewrites the entire table under
```
ACCESS EXCLUSIVE
```
; only
```
IMMUTABLE
```
/
```
STABLE
```
defaults get the metadata-only fast path. Check before shipping the migration:
```
SELECT provolatile FROM pg_proc WHERE proname = 'gen_random_uuid';
```
--
```
v
```
is volatile,
```
s
```
/
```
i
```
are not. So
```
DEFAULT 7
```
and
```
DEFAULT now()
```
are instant,
```
DEFAULT gen_random_uuid()
```
is a full rewrite; add the column nullable, backfill in batches, then set the default.
```
NULLS NOT DISTINCT
```
on unique indexes (PG15+) -- treats NULLs as equal for uniqueness
Under
```
NULLS NOT DISTINCT
```
, a pre-flight duplicate check written with SQL
```
=
```
misses NULL/NULL collisions -- the index rejects the second row, but
```
NULL = NULL
```
evaluates to NULL (not true), so a self-join or
```
WHERE a.col = b.col
```
probe silently skips exactly the pairs the index will reject. Write the probe with
```
IS NOT DISTINCT FROM
```
so NULL/NULL compares as equal.
Revoke default public schema access:
```
REVOKE ALL ON SCHEMA public FROM public
```

每个外键（FK）列都需要创建索引（PostgreSQL 不会自动创建）
除非 NULL 具有明确业务含义，否则所有列都需设置
```
NOT NULL
```
在数据库层面通过
```
CHECK
```
约束实现领域规则

用

EXCLUDE

约束防止范围重叠：

EXCLUDE USING gist (room WITH =, during WITH &&)

默认添加

created_at TIMESTAMPTZ NOT NULL DEFAULT now()

单独设置
```
updated_at
```
字段并通过触发器维护，绝不单独依赖应用层。触发器需添加
```
WHEN (OLD.* IS DISTINCT FROM NEW.*)
```
条件，确保无变更的写入不会触发函数或更新时间戳——在
```
BEFORE UPDATE
```
阶段，行镜像在触发器执行前已构建，因此比较操作会读取调用者提交的行，而非触发器即将标记的行。
使用
```
BIGINT
```
作为主键——比 UUID 更高效的 JOIN 操作，更好的索引局部性
安全迁移：使用
```
CREATE INDEX CONCURRENTLY
```
，添加列时使用非易失性的
```
DEFAULT
```
（可即时添加）。绝不在大型表上直接执行
```
ALTER TYPE
```
。
表达式为
```
VOLATILE
```
的
```
DEFAULT
```
会在
```
ACCESS EXCLUSIVE
```
锁下重写整个表；只有
```
IMMUTABLE
```
/
```
STABLE
```
类型的默认值才能使用仅修改元数据的快速路径。迁移前需检查：
```
SELECT provolatile FROM pg_proc WHERE proname = 'gen_random_uuid';
```
——
```
v
```
表示易失性，
```
s
```
/
```
i
```
表示非易失性。因此
```
DEFAULT 7
```
和
```
DEFAULT now()
```
可即时完成，
```
DEFAULT gen_random_uuid()
```
会触发全表重写；应先添加可空列，批量回填数据，再设置默认值。
在唯一索引上使用
```
NULLS NOT DISTINCT
```
（PG15+）——将 NULL 视为相等值以保证唯一性
在
```
NULLS NOT DISTINCT
```
规则下，用 SQL
```
=
```
编写的前置重复检查会遗漏 NULL/NULL 的冲突——索引会拒绝第二行，但
```
NULL = NULL
```
结果为 NULL（而非 true），因此自连接或
```
WHERE a.col = b.col
```
的查询会跳过索引拒绝的配对。需使用
```
IS NOT DISTINCT FROM
```
编写查询，使 NULL/NULL 被视为相等。
撤销 public 模式的默认访问权限：
```
REVOKE ALL ON SCHEMA public FROM public
```

Migration Safety

迁移安全规范

Core rules:

Every schema change is a migration. No ad-hoc DDL in production.
Migrations are immutable once deployed -- never edit a migration that has run in any shared environment.
Schema migrations and data migrations are separate files. Schema changes are fast and transactional; data backfills are slow and may need batching.
Forward-only in production. Rollback = a new forward migration that reverses the change.

Expand-contract pattern for zero-downtime renames and removals:

Expand: add the new column/table, backfill data, update writes to populate both old and new
Migrate: switch reads to the new column/table, verify in production
Contract: remove the old column/table in a later deploy

Never rename or remove a column in a single migration -- callers reading the old name will break between deploy and code rollout.

Dangerous operations:

```
NOT NULL
```
without a
```
DEFAULT
```
on an existing table locks and rewrites every row. Add the column nullable first, backfill, then add the constraint.
```
CREATE INDEX
```
(without
```
CONCURRENTLY
```
) locks writes for the duration. Always use
```
CONCURRENTLY
```
, which cannot run inside a transaction block -- keep it in its own migration.
Large data backfills: batch with
```
FOR UPDATE SKIP LOCKED
```
to avoid locking the entire table:

sql

UPDATE target SET new_col = compute(old_col)
WHERE id IN (
  SELECT id FROM target
  WHERE new_col IS NULL
  LIMIT 1000
  FOR UPDATE SKIP LOCKED
);

Run in a loop until zero rows affected.

Full-replace clobber on read-modify-write loops. A migration that loops

SELECT col → mutate in app → UPDATE SET col = new_full_value WHERE id = ?

silently drops concurrent writes that landed between SELECT and UPDATE. Any column written by live traffic is exposed:

jsonb

documents, comma-separated tag fields, denormalized counters, JSON-encoded attribute blobs. Mitigations, in order of preference:

In-place atomic update when the edit is expressible as SQL:

UPDATE t SET col = jsonb_set(col, '{path}', :value) WHERE ...

, or

UPDATE t SET tags = array_append(tags, :tag) WHERE ...

— no read-modify-write window.

Row-level lock during the loop: wrap each iteration in a transaction,
```
SELECT ... WHERE id = ? FOR UPDATE
```
, then mutate and write. Cheaper to author, accepts more lock contention.
Compare-and-swap retry: include the original snapshot in
```
WHERE col = :original_value
```
, check the affected-row count; on 0, re-read and retry. Robust under contention, requires explicit retry-loop handling.

Default chunked decode-encode loops are only safe during a maintenance window with writes blocked. ORM "chunkById + load + mutate + save" patterns hit this same trap.

核心规则：

所有模式变更都需通过迁移完成，禁止在生产环境执行临时 DDL
迁移脚本一旦部署即不可修改——绝不要编辑已在任何共享环境中执行过的迁移脚本
模式迁移和数据迁移需分开存储为独立文件。模式变更快速且支持事务；数据回填速度慢，可能需要分批处理
生产环境仅支持正向迁移。回滚操作需通过新的正向迁移脚本实现逆向变更

零停机重命名与删除的扩展-收缩模式：

扩展：添加新列/表，回填数据，更新写入逻辑以同时填充新旧字段
迁移：将读取逻辑切换到新列/表，在生产环境验证
收缩：在后续部署中删除旧列/表

绝不要在单个迁移脚本中重命名或删除列——在部署完成到代码更新完成的间隙，调用者读取旧字段会导致报错。

危险操作：

在已有表上添加无
```
DEFAULT
```
的
```
NOT NULL
```
约束会锁定并重写每一行。应先添加可空列，回填数据，再添加约束。
```
CREATE INDEX
```
（不带
```
CONCURRENTLY
```
）会在执行期间锁定写入操作。始终使用
```
CONCURRENTLY
```
，且该命令不能在事务块内执行——需单独放在一个迁移脚本中。
大型数据回填：使用
```
FOR UPDATE SKIP LOCKED
```
分批处理，避免锁定整个表：

sql

UPDATE target SET new_col = compute(old_col)
WHERE id IN (
  SELECT id FROM target
  WHERE new_col IS NULL
  LIMIT 1000
  FOR UPDATE SKIP LOCKED
);

循环执行直到影响行数为 0。

读-改-写循环中的全量覆盖问题。若迁移脚本采用

SELECT col → 在应用中修改 → UPDATE SET col = new_full_value WHERE id = ?

的循环逻辑，会静默丢失在 SELECT 和 UPDATE 之间提交的并发写入。任何被实时流量写入的列都会受影响：

jsonb

文档、逗号分隔的标签字段、反规范化计数器、JSON 编码的属性 blob。缓解方案按优先级排序：

原地原子更新：若修改逻辑可通过 SQL 表达，直接使用 SQL 更新：
```
UPDATE t SET col = jsonb_set(col, '{path}', :value) WHERE ...
```
，或
```
UPDATE t SET tags = array_append(tags, :tag) WHERE ...
```
——无需读-改-写窗口。
循环期间的行级锁：将每次迭代包裹在事务中，先执行
```
SELECT ... WHERE id = ? FOR UPDATE
```
，再修改并写入。实现成本更低，但会增加锁竞争。
比较-交换重试：在
```
WHERE
```
子句中包含原始快照
```
WHERE col = :original_value
```
，检查影响行数；若为 0，则重新读取并重试。在高并发场景下更可靠，但需显式处理重试逻辑。

默认的分块解码-编码循环仅在维护窗口（禁止写入）期间安全。ORM 的“chunkById + 加载 + 修改 + 保存”模式也会遇到同样问题。

Index Strategy

索引策略

Type	Use When
B-tree (default)	Equality, range, sorting, `LIKE 'prefix%'`
GIN	JSONB ( `@>` , `?` , `?&` ), arrays, full-text ( `tsvector` )
GiST	Geometry, ranges, full-text (smaller but slower than GIN)
BRIN	Large tables with natural ordering (timestamps, serial IDs)

Index rules:

Composite: most selective column first, max 3-4 columns
Partial:
```
WHERE status = 'active'
```
-- smaller, faster
Covering:
```
INCLUDE (col)
```
-- avoids heap lookup
Expression:
```
ON (lower(email))
```
-- for function-based WHERE
A GIN index on an array column serves the containment operators, not
```
=
```
:
```
WHERE 'x' = ANY(col)
```
seq-scans even with
```
enable_seqscan = off
```
, because
```
ANY
```
over an array expands to equality and no GIN operator class implements it. Write the predicate as
```
WHERE col @> ARRAY['x']
```
to reach the index.
```
fillfactor = 70-90
```
on write-heavy tables -- reserves space for HOT updates, reducing index bloat
Drop unused indexes (only after one full business cycle since last restart -- check
```
pg_stat_database.stats_reset
```
first, otherwise you may drop a primary key on a freshly restarted DB or read replica):
```
SELECT * FROM pg_stat_user_indexes WHERE idx_scan = 0
```

Detect unindexed foreign keys:

sql

SELECT conrelid::regclass, a.attname
FROM pg_constraint c
JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = ANY(c.conkey)
WHERE c.contype = 'f'
  AND NOT EXISTS (
    SELECT 1 FROM pg_index i
    WHERE i.indrelid = c.conrelid AND a.attnum = ANY(i.indkey)
  );

类型	使用场景
B-tree（默认）	等值查询、范围查询、排序、 `LIKE 'prefix%'` 前缀匹配
GIN	JSONB（ `@>` 、 `?` 、 `?&` ）、数组、全文检索（ `tsvector` ）
GiST	几何数据、范围类型、全文检索（比 GIN 占用空间小但速度慢）
BRIN	具有自然排序的大型表（时间戳、自增 ID）

索引规则：

复合索引：选择性最高的列放在最前面，最多包含 3-4 列
部分索引：
```
WHERE status = 'active'
```
——占用空间更小，速度更快
覆盖索引：
```
INCLUDE (col)
```
——避免堆表查询
表达式索引：
```
ON (lower(email))
```
——用于基于函数的 WHERE 查询
数组列上的 GIN 索引支持包含操作符，但不支持
```
=
```
：
```
WHERE 'x' = ANY(col)
```
即使设置
```
enable_seqscan = off
```
仍会执行顺序扫描，因为数组上的
```
ANY
```
会展开为等值查询，而 GIN 操作符类未实现该逻辑。需将条件改写为
```
WHERE col @> ARRAY['x']
```
以命中索引。
写入频繁的表设置
```
fillfactor = 70-90
```
——为 HOT 更新预留空间，减少索引膨胀
删除未使用的索引（需在数据库重启后经过一个完整业务周期再操作——先检查
```
pg_stat_database.stats_reset
```
，否则可能误删刚重启的主库或只读副本上的主键）：
```
SELECT * FROM pg_stat_user_indexes WHERE idx_scan = 0
```

检测未索引的外键：

sql

SELECT conrelid::regclass, a.attname
FROM pg_constraint c
JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = ANY(c.conkey)
WHERE c.contype = 'f'
  AND NOT EXISTS (
    SELECT 1 FROM pg_index i
    WHERE i.indrelid = c.conrelid AND a.attnum = ANY(i.indkey)
  );

JSONB Patterns

JSONB 使用模式

sql

-- GIN index for containment queries
CREATE INDEX ON items USING gin (metadata);
SELECT * FROM items WHERE metadata @> '{"status": "active"}';

-- Expression index for specific key access
CREATE INDEX ON items ((metadata->>'category'));
SELECT * FROM items WHERE metadata->>'category' = 'electronics';

Prefer typed columns over JSONB for frequently queried, well-structured data. Use JSONB for truly dynamic/variable attributes.

Use

jsonb_path_ops

operator class for containment-only (

@>

) queries -- 2-3x smaller index. Use default

jsonb_ops

when key-existence (

?|

) is needed.

Delete operators:

Operator	Operand	Behavior	Example
`-`	text	remove top-level key from object	`'{"a":1,"b":2}'::jsonb - 'a'` → `{"b":2}`
`-`	text[]	remove multiple top-level keys	`'{"a":1,"b":2}'::jsonb - ARRAY['a','b']` → `{}`
`-`	integer	remove array element by index	`'[1,2,3]'::jsonb - 1` → `[1,3]`
`#-`	text[]	remove value at nested path	`'{"a":{"b":1}}'::jsonb #- '{a,b}'` → `{"a":{}}`

Common mistakes:

```
col - 'a,b'
```
treats
```
'a,b'
```
as a single key name (no-op against a normally-structured document — the comma isn't a path separator).
```
col - 'a' - 'b'
```
first removes the entire
```
a
```
subtree before attempting
```
- 'b'
```
on the result (data loss of
```
a.*
```
, then a no-op).
```
jsonb_set(col, '{a,b}', 'null'::jsonb)
```
sets the value to JSON
```
null
```
rather than removing the key — strict "key absent" checks downstream then fail. Worse:
```
jsonb_set(col, '{a,b}', NULL)
```
with a bare SQL
```
NULL
```
makes the STRICT function return SQL
```
NULL
```
, clobbering the entire column on update. To delete the key, use
```
#-
```
; to set it explicitly to JSON null, use
```
'null'::jsonb
```
(and know that's distinct from absence).

For nested deletes, use

#-

with a text-array path. Verify with one round-tripped row of the worst-case shape before committing the migration:

SELECT col #- '{a,b}' FROM t WHERE id = ? LIMIT 1

, then confirm the key is gone (not present-as-null, no sibling data loss).

sql

-- 用于包含查询的 GIN 索引
CREATE INDEX ON items USING gin (metadata);
SELECT * FROM items WHERE metadata @> '{"status": "active"}';

-- 用于特定键访问的表达式索引
CREATE INDEX ON items ((metadata->>'category'));
SELECT * FROM items WHERE metadata->>'category' = 'electronics';

对于频繁查询、结构固定的数据，优先使用类型化列而非 JSONB。仅当属性真正动态可变时使用 JSONB。

仅需包含查询（

@>

）时，使用

jsonb_path_ops

操作符类——索引大小可缩小 2-3 倍。当需要键存在性检查（

、

?|

）时，使用默认的

jsonb_ops

。

删除操作符：

操作符	操作数	行为	示例
`-`	文本	移除对象的顶层键	`'{"a":1,"b":2}'::jsonb - 'a'` → `{"b":2}`
`-`	文本数组	移除多个顶层键	`'{"a":1,"b":2}'::jsonb - ARRAY['a','b']` → `{}`
`-`	整数	按索引移除数组元素	`'[1,2,3]'::jsonb - 1` → `[1,3]`
`#-`	文本数组	移除嵌套路径上的值	`'{"a":{"b":1}}'::jsonb #- '{a,b}'` → `{"a":{}}`

常见错误：

```
col - 'a,b'
```
会将
```
'a,b'
```
视为单个键名（对正常结构的文档无作用——逗号不是路径分隔符）。
```
col - 'a' - 'b'
```
会先移除整个
```
a
```
子树，再尝试对结果执行
```
- 'b'
```
（丢失
```
a.*
```
数据，后续操作无作用）。
```
jsonb_set(col, '{a,b}', 'null'::jsonb)
```
会将值设置为 JSON
```
null
```
，而非删除键——下游的严格“键不存在”检查会失败。更糟的是：
```
jsonb_set(col, '{a,b}', NULL)
```
使用纯 SQL
```
NULL
```
会使 STRICT 函数返回 SQL
```
NULL
```
，更新时覆盖整个列。要删除键，使用
```
#-
```
；要显式设置为 JSON null，使用
```
'null'::jsonb
```
（需注意这与键不存在是不同的）。

对于嵌套删除，使用

#-

搭配文本数组路径。提交迁移前，用最坏情况的行数据验证：

SELECT col #- '{a,b}' FROM t WHERE id = ? LIMIT 1

，确认键已被删除（不是以 null 存在，且未丢失同级数据）。

Row-Level Security (RLS)

行级安全（RLS）

sql

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE orders FORCE ROW LEVEL SECURITY;  -- applies to table owner too

-- Set session context (generic, no extensions needed)
SET app.current_user_id = '123';

CREATE POLICY orders_user_policy ON orders
  FOR ALL
  USING (user_id = current_setting('app.current_user_id')::bigint);

Performance: Policy expressions evaluate per row. Wrap function calls in a scalar subquery so PG evaluates once and caches:

sql

-- BAD: called per row
USING (get_current_user() = user_id)
-- GOOD: evaluated once, cached
USING ((SELECT get_current_user()) = user_id)

Always index columns referenced in RLS policies. For complex multi-table checks, use

SECURITY DEFINER

helper functions.

sql

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE orders FORCE ROW LEVEL SECURITY;  -- 对表所有者也生效

-- 设置会话上下文（通用方式，无需扩展）
SET app.current_user_id = '123';

CREATE POLICY orders_user_policy ON orders
  FOR ALL
  USING (user_id = current_setting('app.current_user_id')::bigint);

性能优化： 策略表达式会逐行计算。将函数调用包裹在标量子查询中，使 PostgreSQL 仅计算一次并缓存：

sql

-- 不好：逐行调用
USING (get_current_user() = user_id)
-- 好：仅计算一次，缓存结果
USING ((SELECT get_current_user()) = user_id)

始终为 RLS 策略中引用的列创建索引。对于复杂的多表检查，使用

SECURITY DEFINER

辅助函数。

Query Optimization

查询优化

Always
```
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
```
before optimizing
Use
```
pg_stat_statements
```
for slow-query detection and
```
pg_stat_user_tables
```
for bloat (see Detection queries below for the full SQL)
Sequential scan on large table -> add index or check
```
WHERE
```
for function wrapping
High
```
rows removed by filter
```
-> index doesn't match predicate
CTEs are inlined by default; use
```
MATERIALIZED
```
/
```
NOT MATERIALIZED
```
hints to control optimization
Prefer
```
EXISTS
```
over
```
IN
```
for correlated subqueries
Use
```
LATERAL JOIN
```
when subquery needs outer row reference

Cursor pagination (

WHERE id > $last ORDER BY id LIMIT $n

) over

OFFSET

Approximate row counts:

SELECT reltuples FROM pg_class WHERE relname = 'table'

-- avoids full

count(*)

on large tables

Materialized views for expensive aggregations:
```
REFRESH MATERIALIZED VIEW CONCURRENTLY
```
(needs unique index). Schedule refresh, not per-query.

优化前始终执行
```
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
```
使用
```
pg_stat_statements
```
检测慢查询，使用
```
pg_stat_user_tables
```
检测表膨胀（完整 SQL 见下方检测查询）
大型表上的顺序扫描 → 添加索引或检查 WHERE 子句是否包含函数包装
高
```
rows removed by filter
```
值 → 索引与查询条件不匹配
CTE 默认会被内联；使用
```
MATERIALIZED
```
/
```
NOT MATERIALIZED
```
提示控制优化行为
关联子查询优先使用
```
EXISTS
```
而非
```
IN
```
当子查询需要引用外部行时，使用
```
LATERAL JOIN
```

使用游标分页（

WHERE id > $last ORDER BY id LIMIT $n

）替代

OFFSET

近似行数统计：

SELECT reltuples FROM pg_class WHERE relname = 'table'

——避免在大型表上执行全表

count(*)

对昂贵的聚合查询使用物化视图：
```
REFRESH MATERIALIZED VIEW CONCURRENTLY
```
（需要唯一索引）。定时刷新，而非每次查询时刷新。

Concurrency Patterns

并发模式

See concurrency-patterns.md for UPSERT, deadlock prevention, N+1 elimination, batch inserts, and queue processing with SKIP LOCKED.

有关 UPSERT、死锁预防、N+1 查询消除、批量插入和使用 SKIP LOCKED 的队列处理，请参考 concurrency-patterns.md。

Partitioning

分区

Use when table exceeds ~100M rows or needs TTL purge:

```
RANGE
```
-- time-series (by month/year), most common
```
LIST
```
-- categorical (by region, tenant)
```
HASH
```
-- even distribution when no natural key

Partition key must be in every unique/PK constraint. Create indexes on partitions, not parent.

当表行数超过约 1 亿或需要按 TTL 清理时使用分区：

```
RANGE
```
——时间序列数据（按月份/年份），最常用
```
LIST
```
——分类数据（按地区、租户）
```
HASH
```
——无自然键时的均匀分布

分区键必须包含在所有唯一/主键约束中。在分区上创建索引，而非父表。

Transactions & Locking

事务与锁

Keep transactions short -- long txns block vacuum and bloat tables
Advisory locks for application-level mutual exclusion:
```
pg_advisory_xact_lock(key)
```
Non-blocking alternative:
```
pg_try_advisory_lock(key)
```
-- returns false instead of waiting

Check blocked queries:

SELECT * FROM pg_stat_activity WHERE wait_event_type = 'Lock'

Monitor deadlocks:

SELECT deadlocks FROM pg_stat_database WHERE datname = current_database()

SELECT ... FOR UPDATE
only locks rows that already exist -- it does not prevent a phantom insert of a missing row. Two transactions can both query a key, both see no row, both proceed to insert; the second fails the unique constraint (or both succeed if none existed). For a get-or-create / insert-if-missing race,
```
FOR UPDATE
```
is the wrong tool -- use a partial unique index +
```
INSERT ... ON CONFLICT DO NOTHING/UPDATE
```
, or serialize the key with
```
pg_advisory_xact_lock(hashtext(:key))
```
before the existence check.
A unique-violation (SQLSTATE 23505) caught inside an open transaction can't continue in that same transaction -- once any statement raises, the transaction enters the aborted state and every later statement fails with
```
current transaction is aborted, commands ignored until end of transaction block
```
. Wrap the risky statement in a
```
SAVEPOINT
```
and
```
ROLLBACK TO SAVEPOINT
```
on error, or push the insert-or-update into a single
```
ON CONFLICT
```
statement that never raises. A bare try/catch around the failing statement is not enough on PostgreSQL.
A nested
BEGIN
(or framework
transaction()
wrapper) becomes a
SAVEPOINT
, not an independent transaction -- only the outermost
```
BEGIN
```
is a real transaction. A per-iteration "transaction" inside an outer one does not commit independently and does not release row locks between iterations (held until the outer
```
COMMIT
```
); an unhandled inner error aborts the whole outer transaction. For a long backfill that needs per-row commit and lock release, run each unit as its own top-level transaction -- don't nest it under an outer one.

保持事务简短——长事务会阻塞 vacuum 并导致表膨胀
使用 advisory 锁实现应用级互斥：
```
pg_advisory_xact_lock(key)
```
非阻塞替代方案：
```
pg_try_advisory_lock(key)
```
——返回 false 而非等待

检查被阻塞的查询：

SELECT * FROM pg_stat_activity WHERE wait_event_type = 'Lock'

监控死锁：

SELECT deadlocks FROM pg_stat_database WHERE datname = current_database()

SELECT ... FOR UPDATE
仅锁定已存在的行——无法阻止插入不存在的行。两个事务可以同时查询某个键，都未找到行，然后都执行插入；第二个事务会因唯一约束失败（若没有唯一约束则两个都成功）。对于“获取或创建/不存在则插入”的竞争场景，
```
FOR UPDATE
```
不是正确的工具——使用部分唯一索引 +
```
INSERT ... ON CONFLICT DO NOTHING/UPDATE
```
，或在检查存在性前使用
```
pg_advisory_xact_lock(hashtext(:key))
```
序列化键。
在未提交的事务中捕获唯一约束冲突（SQLSTATE 23505）后，事务无法继续——一旦有语句抛出异常，事务进入中止状态，后续所有语句都会报错：
```
current transaction is aborted, commands ignored until end of transaction block
```
。需将风险语句包裹在
```
SAVEPOINT
```
中，出错时执行
```
ROLLBACK TO SAVEPOINT
```
，或使用单个
```
ON CONFLICT
```
语句实现插入或更新，避免抛出异常。仅在异常周围添加 try/catch 在 PostgreSQL 中是不够的。
嵌套的
BEGIN
（或框架的
transaction()
包装器）会成为
SAVEPOINT
，而非独立事务——只有最外层的
```
BEGIN
```
是真正的事务。外层事务内的每一轮“事务”不会独立提交，也不会在迭代之间释放行锁（直到外层事务
```
COMMIT
```
）；未处理的内层错误会中止整个外层事务。对于需要逐行提交和释放锁的长时回填任务，需将每个单元作为独立的顶层事务执行——不要嵌套在外层事务中。

Full-Text Search

全文检索

See full-text-search.md for weighted tsvector setup, query syntax, highlighting, and when to use PG full-text vs external search.

有关加权 tsvector 配置、查询语法、高亮显示以及何时使用 PostgreSQL 全文检索 vs 外部检索系统，请参考 full-text-search.md。

Connection Pooling

连接池

Always pool in production. Direct connections cost ~10MB each.

PgBouncer in
```
transaction
```
mode for most workloads
```
statement
```
mode if no session-level features (prepared statements, temp tables, advisory locks)

Prepared statement caveat: Named prepared statements are bound to a specific connection. In transaction-mode pooling, the next request may hit a different connection. Use unnamed/extended-query-protocol statements (most ORMs default to this), or deallocate immediately after use.

生产环境始终使用连接池。直接连接每个约占用 10MB 内存。

大多数工作负载使用 PgBouncer 的
```
transaction
```
模式
若无需会话级特性（预准备语句、临时表、advisory 锁），使用
```
statement
```
模式

预准备语句注意事项： 命名预准备语句绑定到特定连接。在事务模式连接池中，下一个请求可能分配到不同连接。使用未命名/扩展查询协议的语句（大多数 ORM 默认使用），或使用后立即释放。

Operations

运维

See operations.md for performance tuning, maintenance/monitoring, WAL, replication, and backup/recovery.

有关性能调优、维护/监控、WAL、复制和备份/恢复，请参考 operations.md。

Vector Search (pgvector)

向量检索（pgvector）

sql

CREATE EXTENSION vector;
ALTER TABLE items ADD COLUMN embedding vector(1536);  -- match your model's output dimensions

-- HNSW: better recall, higher memory. Default choice.
CREATE INDEX ON items USING hnsw (embedding vector_cosine_ops);

-- IVFFlat: lower memory for large datasets. Set lists = sqrt(row_count).
CREATE INDEX ON items USING ivfflat (embedding vector_cosine_ops) WITH (lists = 1000);

Always filter BEFORE vector search (use partial indexes or CTEs with pre-filtered rows). Distance operators:

<=>

cosine,

<->

L2,

<#>

inner product.

sql

CREATE EXTENSION vector;
ALTER TABLE items ADD COLUMN embedding vector(1536);  -- 匹配模型输出维度

-- HNSW：召回率更高，内存占用更大。默认选择。
CREATE INDEX ON items USING hnsw (embedding vector_cosine_ops);

-- IVFFlat：大型数据集内存占用更低。设置 lists = sqrt(行数)。
CREATE INDEX ON items USING ivfflat (embedding vector_cosine_ops) WITH (lists = 1000);

始终先过滤再执行向量检索（使用部分索引或预过滤行的 CTE）。距离操作符：

<=>

余弦距离，

<->

L2 距离，

<#>

内积。

Anti-Patterns

反模式

Anti-Pattern	Fix
`SELECT *`	List needed columns
N+1 queries in application loop	Use `JOIN` , `IN` , or batch fetch
`OFFSET` for pagination on large tables	Cursor pagination: `WHERE id > $last ORDER BY id LIMIT $n`
`count(*)` on large tables	Approximate: `SELECT reltuples FROM pg_class WHERE relname = 'table'`
Nullable booleans	`NOT NULL DEFAULT false` -- three-valued logic causes subtle bugs
Missing FK indexes	See detection query in Index Strategy above
`ORDER BY RANDOM()`	Use `TABLESAMPLE` or application-side shuffle

Detection queries:

sql

-- Slow queries (requires pg_stat_statements)
SELECT query, mean_exec_time, calls
FROM pg_stat_statements
WHERE mean_exec_time > 100
ORDER BY mean_exec_time DESC LIMIT 20;

-- Table bloat (dead tuples awaiting vacuum)
SELECT relname, n_dead_tup, last_vacuum, last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

-- Unused indexes (candidates for removal)
SELECT schemaname, relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND indexrelname NOT LIKE '%_pkey'
ORDER BY pg_relation_size(indexrelid) DESC;

反模式	修复方案
`SELECT *`	列出所需列
应用循环中的 N+1 查询	使用 `JOIN` 、 `IN` 或批量获取
大型表上使用 `OFFSET` 分页	游标分页： `WHERE id > $last ORDER BY id LIMIT $n`
大型表上执行 `count(*)`	近似统计： `SELECT reltuples FROM pg_class WHERE relname = 'table'`
可空布尔值	`NOT NULL DEFAULT false` ——三值逻辑会导致隐蔽 bug
缺失外键索引	见索引策略中的检测查询
`ORDER BY RANDOM()`	使用 `TABLESAMPLE` 或应用层洗牌

检测查询：

sql

-- 慢查询（需要 pg_stat_statements）
SELECT query, mean_exec_time, calls
FROM pg_stat_statements
WHERE mean_exec_time > 100
ORDER BY mean_exec_time DESC LIMIT 20;

-- 表膨胀（等待 vacuum 的死元组）
SELECT relname, n_dead_tup, last_vacuum, last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

-- 未使用的索引（可考虑删除）
SELECT schemaname, relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND indexrelname NOT LIKE '%_pkey'
ORDER BY pg_relation_size(indexrelid) DESC;

Verify

验证

Run

EXPLAIN (ANALYZE, BUFFERS)

on changed queries. Confirm no sequential scans on large tables and no unindexed FK columns before declaring done.

对修改后的查询执行

EXPLAIN (ANALYZE, BUFFERS)

。确认大型表无顺序扫描、无未索引的外键列后，方可完成优化。