PostgreSQL 工程决策：索引、类型与并发的取舍

我很长一段时间把数据库当作「放数据的地方」，建表时随手 varchar(255)，写查询时从不管有没有索引，分页一律 LIMIT x OFFSET y。直到某天一张几百万行的表让接口 P99 飙到 8 秒，才逼着自己把 PostgreSQL 的工具箱翻了一遍。翻完才意识到：PG 给的工具已经足够，缺的只是「什么场景选什么、为什么」这条决策链。

索引：不是越多越好，是选对类型

最常见的误区是「加索引就是加 B-tree」。B-tree 确实是默认选项，等值查询和范围查询都能覆盖，但它对 JSONB 字段的包含查询、全文搜索几乎无效——这时候需要 GIN。

-- JSONB 包含查询，B-tree 无效，GIN 才能命中
CREATE INDEX idx ON events USING gin (payload);
-- WHERE payload @> '{"type": "login"}'
 
-- 时序数据范围扫描，BRIN 远比 B-tree 小，写入代价几乎为零
CREATE INDEX idx ON metrics USING brin (recorded_at);

更容易被忽视的是复合索引的列序：等值列在前，范围列在后。(status, created_at) 能同时支持 WHERE status = 'active' AND created_at > '2024-01-01'，反过来就废掉了范围过滤的索引优势。

覆盖索引（INCLUDE）是另一个值得养成的习惯。如果查询只需要少数几列，把它们放进 INCLUDE，执行计划就能避免回表——对高频读取的接口，这种优化往往立竿见影。

还有一个常被遗漏的反模式：外键列没有索引。PG 不会自动给外键建索引，级联删除或关联查询时会全表扫描。用系统视图定期扫一下不难：

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)
  );

类型：字段类型是业务语义的第一道保障

字段类型的选择不只影响存储，更是业务约束的第一道防线。几个容易踩的坑：

• 时间戳用 timestamptz 而不是 timestamp。不带时区的 timestamp 在跨地区部署或夏令时切换时会产生歧义，往往在最不想处理 bug 的时候出现。
• 金额用 numeric(10,2) 而不是 float。浮点数的精度误差在财务场景不可接受，这不是性能问题，是正确性问题。
• 主键用 bigint 而不是 int。int 的上限约 21 亿，表增长快时比你想象的更快到达天花板；随机 UUID 虽然分布式友好，但对 B-tree 索引的写入性能有持续的隐性开销。

text 替代 varchar(255) 也是 PG 的惯例——两者在 PG 内部存储相同，但 varchar(255) 会给维护者留下「255 是否有业务含义」的困惑。如果有长度约束，用 CHECK 约束表达更清晰。

并发：用数据库原语，不要绕开它

并发问题最常见的错误是「把数据库当哑巴存储，在应用层自己实现锁」。PG 原生就有专为并发设计的工具，用对了远比应用层锁可靠。

队列任务抢占是典型场景。FOR UPDATE SKIP LOCKED 让多个 worker 同时拉取任务时互不干扰，既无等待，又不会重复处理：

UPDATE jobs SET status = 'processing'
WHERE id = (
  SELECT id FROM jobs WHERE status = 'pending'
  ORDER BY created_at LIMIT 1
  FOR UPDATE SKIP LOCKED
) RETURNING *;

分页是另一个高频误区。OFFSET N 要先跳过前 N 行，N 越大越慢，时间复杂度是 O(N)。游标分页用主键做条件，复杂度降为 O(1)：

-- 游标分页：O(1)，无论翻到第几页
SELECT * FROM products WHERE id > $last_id ORDER BY id LIMIT 20;

UPSERT 也是应该从数据库层解决的问题。应用层先查再插的写法在高并发下必然产生竞态，INSERT ... ON CONFLICT DO UPDATE 是原子的：

INSERT INTO settings (user_id, key, value)
VALUES (123, 'theme', 'dark')
ON CONFLICT (user_id, key)
DO UPDATE SET value = EXCLUDED.value;

一句话心法

数据库的工具箱比你想象的更完整；在应用层绕开数据库原语来解决并发、分页、冲突问题，几乎都是在用更高的复杂度换取更低的可靠性。