初创公司必备！Postgres 生存指南大揭秘

初创公司的 Postgres 生存指南一份防止 Postgres 崩溃的指南。Alexander BelangerHatchet 联合创始人在过去半年左右的时间里我一直在为我们的工程师撰写一份内部文档试图将两年的 Postgres 使用经验浓缩成一份条理清晰的文档。虽然我很喜欢 Postgres 手册但我发现当遇到问题时很难参考它因为它实在是太全面了。我觉得这份文档可能对其他人也有用也欢迎大家提供反馈或者分享你在生产环境中运行 Postgres 的其他经验。在创立 Hatchet 之前我虽然熟悉 SQL但了解程度基本仅限于如果查询速度慢就需要添加索引。这也是本文档的起点我假设你熟悉 SQL 基础、行、表并且大致了解索引的概念。如果你的所有查询都是由 Claude 编写的那这篇文章可能就没什么用了我推荐 supabase/agent - skills关于 ORM 的一点说明本指南仍然有用但你可能需要将其中一些技巧转换为你所使用的 ORM 语言。在扩展规模时很多优化操作如果不突破抽象层并编写 SQL使用 ORM 是无法实现的。你可以优雅或不优雅地实现这一点Prisma TypedSQL 或类似工具在这方面看起来很有吸引力。我们在 Hatchet 使用 sqlc它能实现类似的功能。如果你使用 Go 栈强烈推荐使用。目录基础内容良好的读写操作和表结构设计设计良好的表结构编写高效的读查询编写高性能的连接查询复合索引以及让 ORDER BY 与索引对齐编写高效的写查询数据库迁移连接管理中级内容查询规划器、批量更新和自动清理介绍最容易出问题的抽象层查询规划器有时顺序扫描是合理的大量数据写入默认的自动清理设置可能会拖垮数据库其他类型的数据膨胀问题高级内容FOR UPDATE SKIP LOCKED表分区大型表迁移技巧基础内容良好的读写操作和表结构设计让我们从基础开始低数据量下的查询和表结构设计。设计良好的表结构部署后表结构是最难更改的部分所以值得花些时间来设计。我建议迭代式地构建表结构先大致确定表和主键然后根据应用需求编写一些查询。你可以通过以下问题来辅助思考这是一个高读和/或高写的表吗读操作中最常用的过滤条件是什么哪些列更新最频繁如果你想更规范一些可以了解一下数据库规范化将表结构设计为 1NF/2NF/3NF。但我发现规范化形式有时会与查询效率和易用性产生冲突而在快速开发时易用性至关重要。有时直接将数据存入 jsonb 列会更简单。我设计表结构的经验法则如下使用自增整数类型的标识列比 bigserial 性能略高或内置的 UUID 作为主键。始终使用 timestamptz 类型。始终设置主键。对于低数据量的表使用带有级联删除的外键特别是在需要保证数据库一致性和正确性的场景。但在高数据量场景下要谨慎使用。编写高效的读查询我们从 SELECT 查询开始。一个有用但不太准确的快速查询思维模型是在底层Postgres 要么能非常快速地在表中找到某一行要么会使用所谓的 顺序扫描 来读取表中的每一行这可就不太妙了。当你通过以下条件进行过滤时Postgres 能快速找到某一行显式索引唯一约束这只是索引的一种特殊情况主键在 Postgres 中主键会自动创建索引索引默认使用 btree 实现。可以把索引看作 Postgres 中的另一个表数据以特定格式存储这种格式针对查找操作进行了优化后面会详细介绍。这些树非常实用因为查找某一行的时间复杂度约为 log(n)其中 n 是表中的行数换句话说速度非常快。当 Postgres 无法使用索引时会使用顺序扫描seq scan。顺序扫描比索引查找慢得多但现代数据库将行加载到内存的速度非常快所以一开始你可能根本注意不到对少于 20000 行的表进行顺序扫描几乎是瞬间完成的。编写高性能的连接查询对于内连接几乎没有理由不使用主键作为连接条件否则通常意味着表结构设计或规范化存在问题。要像对待 WHERE 子句一样对待 ON 子句遵循相同的原则使用索引。复合索引以及让 ORDER BY 与索引对齐通常应用程序中第一个变慢的查询是对大表进行的列表查询。在这种情况下你可以使用复合索引一个合理的复合索引可能是……在更复杂的情况下一个好的经验法则是ORDER BY 子句中的列应该是索引中的最后几列并且要让列的顺序与 ORDER BY 中的顺序一致。需要注意的是Postgres 可以双向扫描 B 树所以有时 DESC 关键字并不重要但对于复合索引这是一个好习惯。编写高效的写查询成功写入数据的前提如下保持事务简短除非有充分的理由否则不要在事务中间查询外部服务。谨慎锁定要写入的行也就是说只锁定你需要的行。每次更新一行时你会在该行上短暂加锁直到事务提交。随着系统变得繁忙你会越来越明显地感受到锁的影响。特别是你可能会在未来某个时候尝试使用简单的 CREATE INDEX 命令创建索引结果发现这会锁定你的表阻止插入和更新操作在现有大表上创建索引时一定要使用 CREATE INDEX CONCURRENTLY。数据库迁移熟练编写数据库迁移脚本是一项重要的技术优势它能帮助你更快地迭代开发并提高系统的可用性。作为起点尽量让迁移操作是增量式的即不要删除列并尽可能在事务中运行迁移脚本这样回滚和部分迁移会更容易处理。当你更有经验时可以开始研究扩展与收缩迁移策略。判断一个迁移操作是否合理的最简单方法是它是否会阻塞所有写入操作如果不使用 CONCURRENTLY 创建索引会阻塞所有写入操作可能导致系统停机。一般来说调用 ALTER TABLE 的操作都值得仔细考虑例如给非常大的表添加新的检查约束也可能会阻塞写入操作除非使用 NOT VALID 关键字添加。连接管理每次对数据库执行事务或查询时都会使用一个连接。连接在多个方面CPU 和内存都很消耗资源频繁的连接创建和销毁会导致大量不必要的资源浪费所以连接应该保持长时间有效。连接风暴即同时大量创建新连接还可能导致与 Postgres 内部锁相关的难以调试的边缘问题。由于连接存在这些潜在问题像 pgbouncer 这样的外部连接池工具非常有用如果由于某种原因无法使用外部连接池内存连接池也是不错的选择。例如由于 Hatchet 是开源的我们不能假设所有用户数据库都使用连接池所以我们使用 pgxpoolGo 的内存连接池来实现这一目的。中级内容查询规划器、批量更新和自动清理介绍最容易出问题的抽象层查询规划器当查询变得足够复杂时简单的索引可能就不够用了而且你也不应该无休止地在表中添加索引因为索引会带来额外的开销。查询可能涉及多个 JOIN 语句或不同类型的连接此时查询数据的最佳路径并不明确。在这种情况下你需要关注 查询规划器。查询规划器充其量只是一个有漏洞的抽象层。它是一个内部实现你几乎无法控制它但你必须了解它的随机行为有时甚至是不合理行为就像使用大语言模型一样查询规划器会分析你传入的查询然后确定如何将其转换为数据库中的一组内部操作。例如它可能会分析你的查询然后意识到需要使用索引。在理想情况下查询规划器应该为每个查询和参数集找到最佳的执行计划。但查询规划器的信息有限有时它不会选择最佳方案。这些有限的信息就是表统计信息。你可以在 Postgres 中直接查询这些统计信息……每次执行 ANALYZE 时都会收集这些统计信息。自动清理autovacuum运行时也会进行收集见下文所以更频繁的自动清理意味着你的查询统计信息会更及时。查询行为异常的一个常见原因就是分析操作不够频繁。我认为将查询看作二元的即要么进行顺序扫描要么不进行顺序扫描是有帮助的因为你对查询进行的微观优化越多查询规划器出现异常的风险就越大。如果你坚持通过主键和索引进行查询查询规划器的工作会轻松很多。假设你的查询看起来没有明显问题但仍然很慢该如何调试呢一些 Postgres 数据库提供商如 Google CloudSQL会采样查询并保存慢查询但很多提供商不会这么做。这时EXPLAIN ANALYZE 就派上用场了。它会输出查询的执行计划并执行查询在生产环境中运行时要小心你可以使用不带 ANALYZE 的 EXPLAIN 来获取查询计划然后将基于表统计信息的估计值与实际扫描的行数进行比较。我通常会将 SQL 查询放在一个文件中在前面加上 EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON) 然后运行……然后使用 explain.dalibo.com 来可视化执行计划。有时顺序扫描是合理的有时候你认为应该使用索引但查询规划器仍然进行顺序扫描即使表统计信息是最新的索引也是有效的。在这种情况下Postgres 通常会估计顺序扫描的成本比索引扫描的成本低。索引扫描确实会有一些开销因为索引是与表中的实际数据称为堆分开存储的在堆中查找所有行可能会很耗时除非你能大幅重构查询否则可能不得不接受顺序扫描或者考虑使用表分区后面会详细介绍。大量数据写入假设你的应用程序正在扩展需要快速写入大量数据。每个查询都有一些相关的开销与前面提到的连接开销不同包括与数据库的往返时间、内部应用程序连接池获取连接的时间以及 Postgres 处理查询的时间包括一组 Postgres 内部锁在高吞吐量场景中这些锁可能会成为瓶颈。为了减少这种开销我们可以将一批行打包到每个查询中。最简单的方法是在一个隐式事务中一次性将所有查询发送到 Postgres 服务器在 Go 中我们可以使用 pgx 执行 SendBatch。批量操作非常强大我们发现它可以将吞吐量提高约 10 倍。默认的自动清理设置可能会拖垮数据库自动清理autovacuum是 Postgres 数据库中的一项关键操作有时需要进行调整特别是在高写入场景中。自动清理守护进程负责多项任务包括清理死元组和管理事务 ID。什么是 死元组 呢元组是文件系统中一行数据的实例。每次更新或删除一行时该行的一个版本会留在 Postgres 中直到在该行更新或删除之前开始的所有事务都提交或回滚。这些任何事务都无法读取的行就是 死元组。如果你写入数据的速度足够快有时自动清理可能无法跟上这会让数据库迅速陷入不健康状态。当你查询数据库中的活动进程时就会发现这个问题……如果你发现自动清理查询运行超过约 1 小时可能需要考虑更改自动清理设置监控这一点很有必要因为如果在自动清理回收之前系统中的所有事务 ID 都用完了就会进入可怕的 事务 ID 回绕 状态这将导致数据库长时间停机。其他类型的数据膨胀问题除了死元组在繁忙的 Postgres 系统中你还经常会遇到另外两种数据膨胀问题部分填充页面导致的表膨胀Postgres 将行存储在磁盘页面上每个页面大小为 8KB。当 Postgres 无法将新行放入现有页面时会创建一个新页面。但当回收死元组时可能会导致页面没有完全填充从而增加 Postgres 的磁盘使用量有时会显著增加。避免表膨胀的最佳方法是在出现膨胀之前调整自动清理设置。不过也有一些扩展可以帮助处理膨胀的表例如 pg_repack因为内置的 Postgres VACUUM FULL 通常不是一个好选择。需要注意的是Postgres 19 将支持 REPACK...CONCURRENTLY我还没有测试过但这似乎是并发表重新打包的一个潜在解决方案。索引膨胀这是表膨胀的一种特殊情况同样可以通过良好的自动清理设置来解决。不过Postgres 有一个内置命令 REINDEX INDEX CONCURRENTLY 来处理这个问题。高级内容最后我想介绍一些在 Hatchet 非常有用的 Postgres 高级特性。FOR UPDATE SKIP LOCKED理解这个 Postgres 特性的最佳方式是它会为你在事务中选择的行预留使用权限同时不影响其他查询。我们主要用它来实现任务队列在 Postgres 中可以这样实现单查询队列……在对多行进行独立更新或者在应用程序的多个实例之间管理系统中对象的租约时这个特性也非常有用例如我们用它在 Hatchet 引擎之间分配租户租约。表分区Postgres 内置了表分区功能允许你根据行值如时间戳或哈希值对表进行细分。这对于时间序列数据在我们的案例中是历史任务数据非常有用原因如下每个分区可以独立进行自动清理这样你就可以对表的自动清理操作进行扩展。删除旧数据几乎是瞬间完成的你只需删除表分区而不需要逐行迭代。表分区也有一些缺点如果 Postgres 在规划阶段没有对分区进行修剪读查询可能会有额外的开销不过在最近的版本中Postgres 在这方面已经有了很大的改进。大型表迁移技巧注意这里指的不是数据库迁移而是每年我们会进行几次的将大量数据从一个表迁移到另一个表的操作。如果你尝试迁移非常大的表在单个事务中复制数据可能需要数小时。这并不好因为长时间运行的事务会影响自动清理的正常工作导致整个系统充满死元组。此外如果你想继续向旧表写入数据新表将无法获取这些新数据。因此我们需要找到一种在不使用事务的情况下安全迁移数据的方法同时将迁移开始后的新写入数据复制到新表中。我们学到的一个技巧是使用 Postgres 触发器并在事务外运行大型批量回填操作利用主键上的唯一约束来防止重复写入。就这些啦如果你有其他关于 Postgres 扩展的经验或者对 Postgres 扩展有任何疑问欢迎随时联系我们。订阅获取更多技术深度剖析文章。及时了解我们在分布式系统、工作流引擎和开发者工具方面的最新进展。上一篇Supertoast 表社交平台链接X.com、GitHub、Discord、LinkedIn、Email相关链接文档、定价、客户案例、状态页面、招聘信息