初创公司的 PostgreSQL 生存指南：两年线上实战经验总结

原文来源：Hatchet Blog — Alexander Belanger — Hatchet 联合创始人分享两年 Postgres 线上实战中积累的核心经验

PostgreSQL 是初创公司最常用的数据库之一。它功能强大、生态成熟，但有相当多的「坑」只有线上实战过才知道。这篇文章来自 Hatchet（一个云原生任务队列服务）联合创始人的经验总结，涵盖了从日常读写到迁移策略的核心知识点。

基础篇：读写、Schema 与连接管理

Schema 设计的基本原则

建表时有几条简单的规则可以让你省去很多麻烦：

• 主键用自增整数或内置 UUID。identity column 是 Postgres 10+ 推荐的做法
• 时间戳统一用 timestamptz。不要用 timestamp，前者带时区信息，后者不带——线上环境跨时区的问题很难事后修复
• 始终设置主键。Postgres 会自动为主键建索引
• 低流量表可以用外键加级联删除。高流量表就要小心了，级联删除在大批量操作时可能拖垮性能
• 不要过度范式化。有时候 jsonb 字段反而更快，这是合理的技术取舍

高效读取的要点

Postgres 找一条记录的速度取决于它怎么找到这条记录：通过索引（B-tree，O(log n) 复杂度）或者顺序扫描。顺序扫描在小于 20,000 行的小表上不是问题，但在大表上就是性能杀手。

理解查询的关键模型很简单：每个查询要么走索引扫描，要么走顺序扫描，二选一。

编写高性能 Join

Join 的性能很大程度上取决于关联字段是否有索引。最佳实践非常直接：始终用主键做 join。如果你发现自己不得不用非主键字段做 join，那通常说明 Schema 设计有问题。

ON 子句和 WHERE 子句同样需要索引支持。把 ON 当作 WHERE 来处理，确保关联字段有合适的索引。

复合索引与排序优化

对于大表上的列表查询（分页、排序），复合索引是必须的。经验法则是：ORDER BY 的列放在索引最后，并且排序方向要匹配。

CREATE INDEX idx_your_table_filter1_filter2_order_col
ON your_table (filter1, filter2, order_col DESC);

Postgres 可以在两个方向上扫描 B-tree 索引，但显式匹配 DESC 在复合索引中能获得最佳性能。

高效写入的三大纪律

1. 事务要短——永远不要在事务内部调用外部服务（HTTP 请求、外部 API）。事务持有锁，外部调用一慢，整个数据库跟着慢
2. 只锁定必要的行——每次更新都会持有行锁直到事务提交
3. 大表建索引永远用 CREATE INDEX CONCURRENTLY——普通 CREATE INDEX 会阻塞写入

迁移的最佳实践

线上数据库迁移有一条铁律：迁移操作必须是增量的。不要直接删列，用 expand/contract 模式（Martin Fowler 的 ParallelChange 模式）。

事务内执行迁移以便回滚。但要警惕阻塞操作：ALTER TABLE、添加 check 约束都会阻塞写入。使用 NOT VALID 来避免阻塞：

ALTER TABLE your_table ADD CONSTRAINT check_valid CHECK (amount > 0) NOT VALID;
-- 之后单独验证
ALTER TABLE your_table VALIDATE CONSTRAINT check_valid;

连接管理

Postgres 的连接很贵（CPU + 内存开销）。使用长连接比频繁创建销毁连接好得多。但也要小心「连接风暴」——大量连接同时涌入会导致 Postgres 内部锁竞争。

始终使用连接池——可以是外部的 pgbouncer，也可以是应用层内存池（比如 Go 的 pgxpool）。

进阶篇：查询计划器、批量写入、Autovacuum

查询计划器：Postgres 里最「漏水的抽象」

查询计划器根据表统计信息来估算查询成本。统计信息由 ANALYZE 收集，在 autovacuum 过程中更新。所以 autovacuum 越频繁，统计信息越新，计划器的决策就越准确。

遇到慢查询怎么办？用 EXPLAIN ANALYZE：

EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON) SELECT * FROM ...

然后把 JSON 结果粘贴到 explain.dalibo.com 可视化分析。不要过度微调查询——坚持主键/索引查找，计划器自然表现更好。

顺序扫描不一定是坏事

有时候计划器在有可用索引的情况下也会选择顺序扫描，因为索引扫描有额外开销（heap lookup）。如果改写查询不能解决问题，考虑分区表。

批量写入：吞吐量提升 10 倍

用 SendBatch（比如 Go 的 pgx 驱动）将多行写入打包到隐式事务中。实测吞吐量提升约 10 倍。

默认 Autovacuum 设置可能搞垮你的数据库

每次 UPDATE 或 DELETE 操作后，Postgres 会留下「死元组」（dead tuples）。autovacuum 负责清理它们。

如果 autovacuum 跟不上写入速度，你会遇到表膨胀（table bloat），最严重的时候会遇到事务 ID 回卷（transaction ID wraparound）——这会导致数据库强制停服。

监控 autovacuum 的运行状态：

SELECT query, state, now() - pg_stat_activity.query_start AS duration
FROM pg_stat_activity
WHERE query LIKE '%autovacuum%';

如果某个 autovacuum 进程运行超过 1 小时，说明你的 autovacuum 配置需要调优。对于高写入的表，激进地调大 autovacuum 参数。推荐的参考：CyberTec 的 autovacuum 调优指南。

其他类型的膨胀

除了死元组导致的表膨胀，还有表页膨胀——Postgres 以 8KB 为单位的页面可能被部分填充。最好的预防手段就是 autovacuum 调优。如果真的需要回收空间，用 pg_repack（不要用 VACUUM FULL，后者会锁表）。

实用技巧：用 FOR UPDATE SKIP LOCKED 做任务队列

这是一个非常实用的 Postgres 特性。如果你想在数据库层实现一个简单的任务队列，FOR UPDATE SKIP LOCKED 让你可以跳过已被其他 worker 锁定的行，只取可用的行：

BEGIN;
SELECT * FROM job_queue
WHERE status = 'pending'
ORDER BY created_at
LIMIT 10
FOR UPDATE SKIP LOCKED;
-- 处理任务...
COMMIT;

这种方式足够简单可靠，适合初期不需要 Redis 等独立队列系统的场景。

总结

Postgres 是一款出色的数据库，但用好它需要理解一些关键原理。总结几条最重要的经验：

1. 索引不是万能的——过度索引会拖慢写入
2. 了解查询计划器——你知道它不是完美的，就不要写出让它难办的查询
3. 调优 autovacuum——默认配置适合低写入场景，高写入场景必须手工调整
4. 批量写入——10 倍的吞吐量提升值得你改几行代码
5. 迁移必须增量——CONCURRENTLY、NOT VALID 这些关键字要刻在脑子里
6. 连接池不能省——pgbouncer 是 Postgres 生态里最值得投资的基础设施之一

如果你正在用 Postgres 做新项目的数据库，花半天时间把 autovacuum 调好、把迁移策略定好，后面省下的时间是以周为单位计算的。