作者：Carlota Soto 和 James Blackwood-Sewell
我们一直在探索 Postgres 分区主题 以及它如何帮助您扩展大型 PostgreSQL 数据库，无论是在性能还是操作管理方面。
今天，我们想解决在设计数据库分区策略时始终会出现的主要问题：“每个分区应该有多大？”
真正的答案是“视情况而定”。我知道，这很令人沮丧。就这个主题给出建议极其困难，因为我们说的几乎任何话都可能与个人用例的个人经验相矛盾——这就是它与环境的相关性。最好的选择始终是测试、测试、再测试。
但我们理解您的感受：您甚至应该从哪里开始？为了为您提供一些指导，我们针对该主题整理了一些非常笼统的建议，（我们希望）这些建议将为您指明寻找什么的正确方向。
也就是说，我们确切地知道，您中的一些人会有与我们的建议直接相矛盾的个人轶事——请在 Twitter/X 上与我们分享！我们很乐意听取您的故事，并帮助我们作为一个社区扩展知识。如果将来能根据您的建议更新这篇文章，那就太棒了。🔥
在那之前，让我们深入探讨 Postgres 分区的微妙世界，帮助您在使用范围（或基于时间）的分区时确定最佳分区大小！
此建议适用于 PostgreSQL 原生分区、pg_partman 和 Timescale （其中分区称为块）。
在开始数据分区之旅之前，必须先了解分区背后的“原因”。这值得回顾。本质上，PostgreSQL 中的分区是将表分成更小、更易于管理的块。通常，由一个称为分区键的列决定如何在分区之间分配数据。
此分区键对于确保数据正确路由到相应分区至关重要，并且必须是跨越所有分区的任何唯一索引或主键的一部分。虽然主表充当概念伞，但实际数据驻留在各个分区中，每个分区都可以有自己的索引。
理论上，通过将一个大表分解成更小的分区，您可以从改进的查询性能、优化的索引大小和更高效的数据维护操作中受益，从而使您的大型数据库整体上更加灵活和响应迅速。
但分区并不总是最佳选择：其有效性将在很大程度上取决于您的特定用例的特征。在继续实施分区之前，请务必查看我们关于何时考虑 PostgreSQL 分区的文章。
如果您的摄取率太低、数据访问模式是统一的、您经常进行全表扫描并且您不定期删除数据，那么分区很有可能不是您的最佳解决方案。
获得正确的分区大小（例如，一天或八小时）非常重要。在调整分区大小时，我们最好的建议是在太小和太大之间取得良好的平衡。如果您最终有太多分区，您的查询将受到长时间规划的困扰。如果您拥有的分区太少，这可能会完全抵消分区的优势。
如果将分区大小设置得太大，您将没有足够的分区来看到任何好处.
分区的主要目标，特别是如果您试图提高查询性能，是减少每个查询必须读取的表或索引数据量。如果您的分区太大，您仍然会扫描大量不需要的数据，并且任何潜在的性能改进都将微不足道。
如果将分区大小设置得太小，您很快就会拥有太多分区。
“那我们设置更小的分区吧，”您可能会想。但不要操之过急。较小的分区可能看起来很有效，尤其是在考虑数据检索时，但关键是要记住一点：在 PostgreSQL 中，分区本质上被视为单独的表。当您查询跨越多个分区的数据时，PostgreSQL 必须计划如何访问每个相关分区。
如果您的分区大小很小，并且您经常运行跨越多个分区的查询，则 Postgres 将花费更长的时间来规划该查询（有时比运行查询所需的时间还要长）。理想情况下，PostgreSQL 应该修剪（忽略）不相关的分区，并且只定位相关的分区，但是如果您的分区太小，则修剪效果就会消失。
从理论上讲，这种低效率达到了一个临界点：一旦查询触及超过一定数量的分区（默认为 12 个），PostgreSQL 就会切换到使用遗传算法来优化规划时间。但是随着分区数量的不断增加，即使是这种自适应算法也面临着挑战。较小的分区并不是解决所有问题的答案！
如果您的分区大小与典型的查询范围不一致，则 Postgres 可能需要为每个查询扫描许多表。
例如，假设您已对数据进行分区，每个分区包含一小时窗口的数据，但您通常按月提取数据。在这种情况下，PostgreSQL 将被迫扫描大约 730 个单独的分区才能满足一个月的查询。
这种额外的计划和扫描时间在计算上非常昂贵，会导致响应时间变慢和效率低下。想象一下，当您每天进行多次此类查询时的开销。这可不好。
如果来自分区（及其索引）的工作数据集不适合内存，您将更频繁地从磁盘读取数据。.
大多数生产工作负载都有一个模式，即最近的数据被访问的频率更高——想想显示过去一天或一周的最新指标或分析的图表。如果保存这些最近数据的 Partitions（及其索引）在内存中不可用，则查询速度会变慢。摄取速度也可能会降低，因为如果相关的索引不在内存中，系统必须从磁盘中获取它们，更新它们，然后可能将它们写回磁盘。
如果您的分区大小与保留设置不一致，您将无法通过截断表来删除旧数据。
假设您按周设置分区，但您的数据保留策略规定，一旦数据超过一年，就应该每天删除。如果您的分区按周划分，则没有直接的方法来仅修剪一天的旧数据。如果您尝试逐日删除数据，则实际上会重复进入每个为期一周的分区。
如您所见，分区大小设置没有神奇之处：您必须查看自己的用例并找到合适的平衡点。这是我们最普遍的建议，它应该适用于大多数情况，但请谨慎对待。
如果您希望通过实施保留策略来删除旧分区，那么您希望一次删除的数据量应该是分区大小的倍数。
使您的分区大小与您的数据保留策略保持一致将使事情变得更容易，无论从管理还是性能角度来看都是如此。例如，如果您的策略每月删除超过一年的数据，请考虑使用月度分区。这将允许您通过截断和删除整个分区来有效地删除数据，而不是使用昂贵且性能密集的 DELETE 操作。
如果您的应用程序经常读取最新数据，您可能希望调整分区大小以适应最常访问的数据范围。
例如，如果您的批量查询提取了上周内的数据，则每周分区可能是最佳选择。这在实时应用程序中可能是一种常见的查询模式，其中最重要的是获得最新数据的快速性能。
但是，对于其他应用程序，通常不可能将您的整个查询模式缩短到一个时间范围。请注意您最频繁的查询涉及多少个分区，并确保它们不太多。
尝试将您最近的数据放入内存中。
如果您的常用数据适合内存，您的性能将是最佳的，从而无需额外的磁盘 I/O。 PostgreSQL 使用其 shared_buffers 池中的内存进行缓存 （通常建议为系统内存的 25 %）。
一个好的经验法则是确保来自每个分区表的单个分区及其索引能够舒适地放入 shared_buffers 中，特别是当您处理的应用程序中最新数据是最常访问/性能最重要的数据时（例如，实时物联网）。
如果您按时间顺序添加数据，则您的分区大小至少应该能够容纳您通常在单个事务中摄取的数据量。
通过这样做，您将确保来自一个操作的数据主要落在一个或两个分区中，从而简化写入并减少碎片。
希望现在您已经清楚地了解了分区大小如何与您的数据库交互。但正如我们已经提到的，实际上只有一种方法可以确定哪种分区大小更适合您的用例：测试。我们再怎么强调也不为过。
您的数据库不是理论上的游乐场；它是一个具有现实世界需求的活生生的实体。因此，请设置测试环境。使用各种分区大小模拟您的工作负载，并测量查询性能、维护任务持续时间以及磁盘和内存使用情况。有了这些数据，您就可以对理想的分区大小做出明智的决定。
以下是运行测试的快速指南
建立测试环境。理想情况下，您需要一个尽可能模拟生产的暂存环境，因此请使用生产数据库的副本。像 Timescale 这样的托管数据库可以轻松地复制生产数据库以进行测试。
识别关键操作。列出您的数据库执行的最频繁的查询、插入操作和维护任务。这将帮助您模拟现实世界的场景。
定义您要测试的不同分区策略。例如，如果您按时间分区，您可能想尝试使用每日或每周分区。您还可以尝试使用范围、列表和哈希分区，以查看哪种方式最适合您的数据模式。
基准测试。记录每种分区策略的查询执行时间、插入时间和其他性能指标。执行 VACUUM、ANALYZE （我们最近构建了一个 BPFtrace 程序来监控前者）和备份操作等任务，以查看不同的分区策略如何影响它们的性能。
分析结果。寻找提供最佳性能一致性的分区策略，尤其是在峰值负载期间。确定哪种分区策略提供最顺畅的维护操作。
迭代。根据结果，调整分区大小或类型并重新运行测试以改进您的策略。
如果您是 Timescale 客户，我们很乐意在整个过程中为您提供个性化建议。 请联系我们。
最后一件重要的事情是：现在最优的可能不会一直如此。随着应用程序的发展，数据增长模式和查询行为可能会（或将会）发生变化。养成定期监控分区表的习惯非常有用——一旦出现问题，您就可以快速纠正，而不是让问题恶化并将原因归咎于其他原因。
您如何设置它？幸运的是，PostgreSQL 提供了工具和命令来帮助您调整分区策略，而无需大量的停机时间，因此请根据需要利用它们。
使用 pg_stat_statements 或 pgBadger 等工具。它们提供了对慢速查询、死行和其他可能表明效率低下的指标的见解。
监控指标，如分区数量、分区大小、分区创建和删除、频繁访问的分区、不从分区修剪中受益的查询的频率、索引和表膨胀等。
特别注意磁盘 I/O 操作，以确保分区策略不会导致过多或低效的 I/O 模式。
密切关注 CPU 峰值或内存使用量增加，这可能表明配置错误的分区导致数据访问模式效率低下。
跟踪您分区上的 VACUUM、ANALYZE 和 REINDEX 等操作，以深入了解分区运行状况和性能。
分区是 PostgreSQL 工具包中最强大的工具之一。它的效率取决于正确的实现，而分区大小起着至关重要的作用。不幸的是，没有一个答案适用于所有人的最佳分区大小：了解您的数据、进行严格的测试并保持适应性是取得成功的最佳途径。
对于所有第一次尝试 PostgreSQL 分区的开发人员：祝您分区愉快！我们希望这些建议对您有所帮助。
如果您按时间分区，请务必查看 Timescale——它将大大简化您的分区之旅。借助 超级表，您可以按时间进行完全自动化的分区。
Timescale 还提供旨在简化分区表维护操作的功能，例如 数据保留策略，它还可以通过增强的查询规划器和 连续聚合 等功能更轻松地实现最佳查询性能。
