本文翻译自：
https://andyatkinson.com/blog/2023/07/27/partitioning-growing-practice
我们最近在处理一个大型表时面临了一个挑战，即查询性能下降。这个表是用于跟踪申请人在招聘过程中活动的高增长表。
继续阅读以了解我们如何提高性能和管理增长的方法。
在本文中，我们将探讨如何通过 PostgreSQL 表分区解决我们由于快速数据增长而面临的运营挑战。
大纲
为什么我们使用了表分区？
我们如何实施这个变更？
这个过程进行得如何？
表分区和成本节约
遇到的挑战和错误
未来的改进
为什么要进行分区？
在处理高增长率的表时，性能可能会下降。查询变慢，添加索引或约束等修改需要更长的时间。
一种解决方案是将表拆分为一组表，这是表分区的基本原理。
通过从一个单独的大表中分离出来，每个较小的表变得更容易处理。
声明式分区
从 PostgreSQL 的 10版本开始，可以使用声明式分区来拆分表。声明式分区的好处之一是相对于过去的表分区方式更容易推行。在 PostgreSQL 的早期版本中，可以构建分区表关系，但可能涉及使用表继承、触发器函数和检查约束，这样做会涉及大量的创建和维护工作。
声明式分区使得更多用户能够使用表分区。
什么时候应该进行分区？
较小的表更容易处理，但仍然有一个问题，即何时执行转换。为了回答这个问题，让我们简要讨论一下 PostgreSQL 的架构和内存，因为它是一个单写入数据库。PostgreSQL 服务器有一定的可用内存。PostgreSQL 建议在表的大小超过系统内存的容量时将表迁移到分区表。
由于我们的数据库服务器有384GB内存，而表的大小接近1.5 TB，我们认为现在是进行转换的好时机。随着表的不断持续增大，这个转换将帮助我们的可维护性和可扩展性。
下图可视化描述了分区表如何成为具有子表的“父”表。在我们的情况下，每个月的数据都有一个子表，因为我们使用了RANGE分区，时间间隔为1个月。
下图中加粗的分区是当前正在写入的分区。对于这个例子，加粗的当前活动分区将是当前月份。
PostgreSQL 根据分区边界将INSERT语句路由到当前月份。未来月份对应的分区提前创建，并显示为虚线。
现在我们已经了解了为什么以及何时进行分区，那么我们如何实施它呢？
如何进行分区？
我们的主数据库运行在 PostgreSQL 13 上。自从 10 版本以来，每个 PostgreSQL 版本都增加了对声明式分区的改进。
我们使用逻辑复制作为我们的数据管道过程的一部分，将行修改复制到数据仓库。
在13版本中，对分区表的逻辑复制支持1得到了增强，这正好是我们想要分区表也能被复制的时候！
让我们来看一下要迁移到分区表的表的更多细节。
在可用的声明式分区类型中，我们选择了
RANGE类型，因为行是基于时间的，并且主要是插入操作。我们以
created_at列作为分区键，该列是插入行的时间戳。我们创建了月份边界，这意味着我们需要24个边界来覆盖2年的数据，并对未来的月份进行一些覆盖。我们希望保留2年的活动数据。
我们使用了 pgslice 命令行程序来帮助我们执行转换。
转换过程是在在线进行的，这意味着没有计划停机时间。在此过程运行时，PostgreSQL会继续运行，为其他请求提供服务。
使用 pgslice 进行转换
pgslice 是一个用 Ruby 编写的命令行程序。其工作原理如下。
首先克隆原始表，该表将用作复制行的目标。
pgslice 提供了批量复制行的命令。使用这个命令，您可以将行从原始表复制到目标表。
复制完成后，更新表统计信息(
ANALYZE)，然后将替换表重命名为原始表的名称。现在，原始的未分区表可以进行归档了。
现在，应用程序正在写入和读取一个分区表。
pgslice 的一个重要限制是它设计用于只追加（Append Only）的表，这意味着这个表的查询模式仅涉及行的插入，而不涉及更新或删除。
幸运的是，这个表是一个“几乎仅追加”表，我们可以使用单独的过程将少量更新带入其中。
重复执行
我们管理着10个生产数据库，每个数据库都有自己的一份这个表的副本。现在，pgslice 可以成为我们下一个表转换的标准工具。它具有参数化和灵活的特性，但我们还添加了一个小的 Ruby 辅助类来包装 gem 命令调用，以处理数据库模式细节之间的不一致性。
这个辅助类还用作文档，说明我们调用的参数及其值。
测试，测试，测试
作为转换技术，行复制包含一些需要注意的权衡。需要对应用程序代码的兼容性进行充分的测试。一个经验教训是尽早让代码库与使用分区表的 CI 兼容。
我们使应用程序同时完全兼容两种类型的表，以便我们可以前后转换。
在转换过程中，会产生更多的磁盘 IO、空间消耗和 WAL 写入。您可能需要调整复制过程的速度，这意味着复制需要更长时间，但会允许后台进程（如索引维护和复制）赶上。
我们建议为数据库资源提供过度配置（使用大型服务器），并在低活动时段运行，以便分区表数据迁移不会对应用程序查询造成附带的影响。
此外，您还需要有足够的空间容量来运行这个过程，因为您的空间消耗将在短期内暂时增加很多，如果所有行都被复制，空间消耗可能增加一倍。
转换过程进行得如何？
过程进行得如何？
表分区转换取得了成功。现在，我们的所有环境中都运行着一个分区表，已经过了几个星期。
在遇到一些小问题后，我们及时解决了这些问题。在这个系列的第2部分2中，我们将详细介绍我们如何解决与主键相关的一个棘手问题。
在与产品利益相关者的协作下，我们决定限制从这个表中所需的数据量，以供应用程序使用。对于工程团队来说，这个决定很有帮助，因为它意味着旧数据可以被归档。归档数据对于提高这个表的性能和可靠性特性非常有帮助，因为它将有效地使数据“不再增长”或者在导出新数据以平衡新数据增长时增长缓慢。
这也意味着我们可以通过断开超过2年的数据来有效地缩小表的总大小。
节省了多少成本？
成本节省
对于 AWS PostgreSQL 数据库，将数据从 Aurora PostgreSQL 中转移到对象存储可以直接降低成本。Aurora 每月按10 GB 为单位计费3。随着消耗的空间越多，费用也会增加，但是4随着消耗的空间减少，费用也会降低。
通过这个项目，数据可以从数据库重新定位到 AWS S3。S3 存储的每 GB-月成本比 Aurora 低80%。
当对 TB 级别的数据进行这种重定位时，成本节省可能变得非常显著。除了一次性的成本节省外，自动化这个重定位过程意味着成本会随着时间的推移而更加稳定，而不是持续增加。
有什么可以做得更好吗？
遇到的挑战和错误
我们努力避免数据丢失并将错误降到最低。然而，由于时间和工程约束，有些场景在较低的环境中很难完全复制。
复制行使用了大量资源。使用批处理、限制速率或者在复制影响应用程序查询时停止复制。
复制会消耗大量空间。短期内成本会增加。确保导出和转储退役表，以便最终产生较低的成本，而不是更高的成本！
复制会导致大量事务日志更新。
删除目标表的索引有助于加快插入速度。使用以下查询从原始表获取创建索引的 DDL，然后在复制行后在分区表上重新创建索引。
以下查询列出了如何创建索引的方法。
SELECT indexdef FROM pg_indexes WHERE indexname = 'index_name';
总结
PostgreSQL 的声明式分区可以帮助你解决高增长表的运营挑战。虽然迁移到分区表并非易事，但可以提前进行充分测试，并且可以使用pgslice在线进行。
感谢 Bharath Dakanna 和 Bobby Ryterski 在这个项目上的帮助，以及对这篇文章早期版本的审阅。
分区表现在可以被复制了 https://amitlan.com/2020/05/14/partition-logical-replication.html ↩
PostgreSQL表分区主键 - 关键时刻 - 第2部分 https://andyatkinson.com/blog/2023/07/28/partitioning-primary-keys-reckoning ↩
CloudZero RDS价格指南 https://www.cloudzero.com/blog/rds-pricing ↩
Aurora动态调整大小 https://aws.amazon.com/about-aws/whats-new/2020/10/amazon-aurora-enables-dynamic-resizing-database-storage-space/ ↩
本文分享自微信公众号 - 开源软件联盟PostgreSQL分会（kaiyuanlianmeng）。
如有侵权，请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”，欢迎正在阅读的你也加入，一起分享。
