在Postgres中，有多少个数据表分区是太多的？

Question

在Postgres中，有多少个数据表分区是太多的？

performancepostgresqlpartitioning

33

我正在对包含时间数据的非常大的表进行分区，考虑应该将分区粒度设置为多少。Postgres的分区文档声称，“大量的分区可能会显著增加查询计划时间”，并建议使用“最多100个”分区进行分区。

假设我的表保存了十年的数据，如果按周进行分区，我将得到超过500个分区。在排除此选项之前，我想更好地了解分区数量对查询计划时间的影响。是否有人对此进行了基准测试，或者是否有人了解其内部工作原理？

- DNS

他们几乎肯定会这样做；我只是选择每周来获得更现实的更大数字。人们可以考虑在20年内使用月度分区。我主要关心约束条件，以及50个和100个分区之间的区别是什么。 - DNS

4个回答

6

"大量分区很可能会显著增加查询规划时间"，并建议将分区用于 "最多可能有一百个" 分区。因为每个额外的分区通常都与检查约束相关联，这会使规划器想知道哪些分区需要进行查询。在最好的情况下，规划器确定您只击中了一个分区，并完全摆脱了 "追加" 步骤。就行数而言，正如DNS和Seth所指出的那样，硬件的效果因人而异。但总体而言，查询1M行表和10M行表之间没有显着差异--特别是如果您的硬盘允许快速的随机访问并且使用您最频繁击中的索引进行聚集（请参见 "cluster" 语句）。"

- Denis de Bernardy

2

每个表分区都占用文件系统上的一个inode。 "非常大"是一个相对的术语，取决于您选择的文件系统的性能特征。如果您想要明确的性能基准，您可能可以查看来自您选择的操作系统和文件系统的各种邮件系统的性能基准。一般而言，直到您进入数万到数十万个表空间（在FreeBSD的UFS2上使用dirhash会更好），我不会担心它。还要注意，这个限制也适用于PostgreSQL中的数据库、表或任何其他基于文件系统的数据库对象。

- Sean

-3

如果您不想相信编写代码的PostgreSQL开发人员，那么我建议您自己尝试并使用不同的分区方案运行一些示例查询，并使用explain analyze和时间对它们进行测量。无论如何，您特定的硬件和软件配置可能会主导任何答案。

我假设查询优化器用于确定要使用哪些连接和限制的行优化缓存存储在每个分区中，因此它可能需要加载和读取每个分区的部分来规划查询。

- Seth Robertson

4

我相信开发人员，但他们的警告非常模糊，所以我想更好地理解它。我的问题像 Stack Overflow 上的大多数问题一样被提出，以便如果有人已经知道答案，我不必花费数小时来构建一个代表性的测试环境来重现他们的工作。 - DNS

1

@DNS 这是模糊的，因为它取决于您的硬件和软件配置、数据和查询。一个对某个人正确的答案对另一个人来说可能并不正确。SQL 就是这样微妙。 - Seth Robertson

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Greg Smith · Accepted Answer

查询规划器必须为查询所使用的每个表的分区进行约束信息的线性搜索，以确定实际涉及哪些表--可以拥有所需数据行的表。随着加入更多的表格，规划器考虑的查询计划数量呈指数增长。因此，这种线性搜索需要耗费足够长的时间才会引起麻烦，而确切的时间则取决于查询复杂度。连接的表格越多，就越容易受到影响。 “高达一百”的数字来自于注意到即使是在那个点周围的较简单的查询中，查询规划时间也会添加到一个不容忽视的时间量。特别是在Web应用程序中，响应时间的延迟很重要，这是一个问题;因此发出了警告。

您是否可以支持500？当然可以。但您将为优化器考虑的涉及该表的每个查询计划搜索500个检查约束。如果查询规划时间对您不重要，那么也许您不在乎。但是，大多数网站最终会不喜欢使用这么多分区时花费在查询规划上的时间比例，这也是为什么月度分区成为大多数数据集的标准之一的原因之一。您可以轻松地存储10年的数据，在开始注意到规划开销之前，每月分区一次。