作者基于两年Postgres实战经验，为工程师编写了内部文档，现分享给更多人。文档假设读者熟悉SQL基础，并指出ORM在扩展时存在局限，需突破抽象层编写SQL。

好的，这是根据您的要求，对原文主要内容进行的中文重述，保留了关键细节，并删减了与主题无关的引导性内容。

Postgres 生存指南：从基础到进阶

本文作者基于两年多的Postgres实战经验，为工程师团队编写了一份内部文档，现将其核心内容分享出来。作者假设读者已熟悉SQL基础、行、表及索引概念。

一、基础篇：读写、模式与连接

1. 设计良好的数据库模式 模式一旦部署就难以更改，因此值得花时间设计。建议迭代式构建：先为表和主键设计一个粗略方案，然后根据应用需求编写查询来验证。可以思考：这是高读还是高写表？读取时最常用的过滤条件是什么？哪些列更新最频繁？ 一些经验法则： * 使用自增整数（identity列，性能略优于bigserial）或内置UUID作为主键。 * 始终使用timestamptz类型存储时间。 * 始终使用主键。 * 在低流量表中使用带级联删除的外键，以保证数据一致性和正确性；高流量时需谨慎。

2. 编写高效的读取查询 Postgres执行SELECT查询时，要么通过索引快速定位单行，要么进行全表顺序扫描（seq scan）。索引（默认是B树）能实现约log(n)时间复杂度的查找，非常快。当无法使用索引时，就会进行顺序扫描，对于小于2万行的表，顺序扫描几乎是瞬间完成的。

3. 编写高效的连接查询 对于内连接，通常应使用主键作为连接条件。ON子句应像WHERE子句一样对待，同样需要索引。

4. 复合索引与ORDER BY对齐 当列表查询变慢时，可以使用复合索引。一个经验法则是：ORDER BY中的列应放在索引的最后，并且列的顺序应与ORDER BY一致。例如，对于按organization_id和created_at排序的查询，可以创建索引(organization_id, created_at DESC)。

5. 编写高效的写入查询 成功写入的关键： * 保持事务简短：不要在事务中间查询外部服务。 * 谨慎锁定行：只锁定你需要更新的行。每次更新行都会短暂锁定该行直到事务提交。 * 创建索引时使用CONCURRENTLY：简单的CREATE INDEX会锁定整个表，阻止插入和更新。在现有大表上创建索引时，务必使用CREATE INDEX CONCURRENTLY。

6. 迁移 优秀的迁移能力能加快迭代速度并提高系统可用性。基本原则是：保持迁移的“增量性”（不删除列），并尽可能在事务中运行。更高级的做法是采用“扩展与收缩”模式。判断迁移是否安全的核心是：它是否会阻塞所有写入操作？例如，不带CONCURRENTLY的索引创建会阻塞写入。ALTER TABLE操作（如添加检查约束）也需谨慎，可以使用NOT VALID关键字避免阻塞。

7. 连接管理 数据库连接在CPU和内存上都很昂贵，应保持长连接。连接风暴（同时建立大量新连接）可能导致难以调试的内部锁问题。因此，外部连接池（如pgbouncer）非常有用。如果无法使用，内存中的连接池（如Go语言的pgxpool）也是很好的选择。

二、进阶篇：查询计划器、批量写入与自动清理

1. 查询计划器 当查询变得复杂时，简单的索引可能不够。你需要了解查询计划器。它基于有限的表统计信息（可通过pg_stats查询）来决定执行计划，有时会做出次优选择。统计信息在每次ANALYZE或自动清理时更新。如果查询表现异常，一个常见原因是统计信息不够新。 调试慢查询时，可以使用EXPLAIN ANALYZE（生产环境慎用，可用EXPLAIN代替）获取执行计划，并通过可视化工具（如explain.dalibo.com）分析。

2. 何时接受顺序扫描 有时即使索引有效且统计信息最新，查询计划器仍会选择顺序扫描。这是因为Postgres估算顺序扫描的成本低于索引扫描。索引扫描需要先在索引中查找，再在数据堆中定位实际行，这本身有开销。如果无法大幅重构查询，可能需要接受顺序扫描，或考虑分区。

3. 批量写入大量数据 每个查询都有开销（网络往返、连接获取、内部锁等）。为了提升写入吞吐量，可以将多行数据打包到一个查询中，通过隐式事务一次性发送给Postgres服务器。这种方法可以将吞吐量提升约10倍。

4. 默认自动清理设置可能拖垮数据库 自动清理（autovacuum）负责清理“死元组”（被更新或删除的行版本）和管理事务ID。在高写入场景下，默认的自动清理设置可能跟不上，导致系统进入不健康状态。如果发现自动清理进程运行超过1小时，就需要调整设置。如果不及时清理，耗尽所有事务ID会导致“事务ID回绕”，造成长时间停机。

5. 其他类型的膨胀 除了死元组，还有两种常见的膨胀： * 表膨胀：由部分填充的数据页导致。通过调整自动清理来预防是最好的方法。可以使用pg_repack扩展来处理已膨胀的表（内置的VACUUM FULL通常不推荐）。 * 索引膨胀：是表膨胀的特例，同样可通过良好的自动清理设置解决。Postgres内置了REINDEX INDEX CONCURRENTLY命令来处理。

三、高级技巧

1. FOR UPDATE SKIP LOCKED 此功能可以锁定你选中的行供当前事务使用，同时不干扰其他查询。它非常适合实现任务队列、管理分布式租约等场景。

2. 分区 Postgres内置的分区功能允许根据时间戳或哈希值等将表细分。这对时序数据非常有用，因为： * 每个分区可以独立进行自动清理，从而提升清理效率。 * 删除旧数据几乎是瞬间完成的，只需删除整个分区即可。 分区的主要缺点是，如果查询计划器在规划阶段没有进行分区裁剪，可能会增加读取开销。

3. 大表数据迁移技巧 这里指的不是数据库模式迁移，而是将大量数据从一个表移动到另一个表。如果在一个事务中复制大表数据，会耗时很长，阻止自动清理，并导致系统膨胀。安全的做法是：不使用事务，利用Postgres触发器，并在事务外运行大规模的批量回填，同时利用主键的唯一约束防止重复写入。

根据评论内容，总结主要观点如下：

1. 监控与告警的重要性（评分：None） - 评论强调Postgres有几种关键故障模式需避免，应通过告警提前预警（如XID wraparound）。 - 关键引用：thundergolfer：“Postgres has a few key failure modes that you want to avoid ever happening, and you can use alerting to get early warning”；“AWS will send you an email if you're approaching XID wraparound... you want whatever AWS is watching to be connected to a pager.”

2. 备份策略与工具（评分：None） - 评论指出备份和恢复计划应是生产数据库的生存指南，但文章未提及。 - 关键引用：theallan：“Should one of the first things you do with a database not be to have a backup strategy?”；“What do you all use for your pg backups? Is Barman still the way many do it?”

3. 数据库设计与规范化（评分：None） - 评论强调规范化设计的重要性，并推荐《Database Design for Mere Mortals》一书。 - 关键引用：zer00eyz：“To this articles credit, it does start out with normalization and design! There needs to be more emphasis how important this is!”；“The best text I have ever found on this is 'Database Design for Mere Mortals'.”

4. 迁移策略与工具（评分：None） - 评论建议使用迁移工具（如Grate）管理模式变更，并强调向后兼容的变更习惯。 - 关键引用：tracker1：“using a migration stack in a repository for deployments... is IMO more reliable than magic comparison tools”；mjr00：“Get used to separating application and database deployments early... only doing backwards compatible schema changes.”

5. 性能优化与JSON列使用（评分：None） - 评论建议利用JSON列避免复杂连接，并注意UUIDv7等序列化方式对索引性能的影响。 - 关键引用：tracker1：“leverage JSON columns and avoid joins altogether for a lot of use cases”；“knowing how/when to leverage denormalization and JSON can be one of the most impactful things you can do in terms of performance.”

6. 成本与替代方案（评分：None） - 评论指出Postgres在初创阶段成本较高，可能选择DynamoDB、SQLite等替代方案。 - 关键引用：hmokiguess：“Postgres is prohibitively costly when bootstrapping something that is lean and frugal”；“I end up with a mixture of serverless storage like DynamoDB, S3, DuckDB on S3, and SQLite.”

7. 连接池与安全性（评分：None） - 评论质疑连接池可能泄露权限或信息。 - 关键引用：groundzeros2015：“Lately I been questioning whether it's actually a good idea to pool connections. Don't you risk leaking privileges or information from other requests?”

8. 内存连接与查询优化（评分：None） - 评论建议在特定场景下用内存连接替代复杂SQL查询，以提升可预测性。 - 关键引用：mrkaye97：“we've had a lot of success performing joins in memory in a few very specific situations where the alternative is a single, often overcomplicated query”；“it is actually beneficial in these cases because of more predictable query planning behavior.”

9. 外键与级联删除（评分：None） - 评论反对级联删除，认为其难以维护，建议显式删除语句。 - 关键引用：mjr00：“I hate cascades... it can be very hard to understand 'why did deleting a row from table A delete something from table B automatically'”；“IMO it's better for long-term maintainability to emit explicit delete clauses.”

10. 存储函数与SQL注入防护（评分：None） - 评论批评文章未讨论存储函数，认为其有助于防止SQL注入。 - 关键引用：traceroute66：“Not even the most cursory of discussion of stored functions?”；“stored functions can help against SQL injection attacks.”

11. 索引类型与查询计划（评分：None） - 评论建议使用uuidv7、hash索引、GIN/GIST索引，并利用EXPLAIN分析查询计划。 - 关键引用：ComputerGuru：“Use uuidv7 not uuid in general”；“Consider using a hash index instead if you just need to look up by column/id but not sort”；“learn about GIN (and GIST) indexes.”

12. 死锁避免（评分：None） - 评论询问如何在实际代码库中避免死锁，如通过表排序。 - 关键引用：ucarion：“Do folks have any thoughts on ways of avoiding deadlocking access patterns?”；“can you realistically... impose an 'ordering' on your tables to avoid dining philosophers?”