Postgres 中多少个表分区算太多？
我正在对一个包含时态数据的非常大的表进行分区，并考虑应该以什么粒度进行分区。 Postgres 分区文档声称“可能存在大量分区显着增加查询计划时间”，并建议将分区与“最多一百个”分区一起使用。
假设我的表保存了 10 年的数据，如果我按周分区，最终会得到超过 500 个分区。在排除这种可能性之前，我想更好地了解分区数量对查询计划时间的影响。有没有人对此进行过基准测试，或者有人了解其内部工作原理吗？
I'm partitioning a very large table that contains temporal data, and considering to what granularity I should make the partitions. The Postgres partition documentation claims that "large numbers of partitions are likely to increase query planning time considerably" and recommends that partitioning be used with "up to perhaps a hundred" partitions.
Assuming my table holds ten years of data, if I partitioned by week I would end up with over 500 partitions. Before I rule this out, I'd like to better understand what impact partition quantity has on query planning time. Has anyone benchmarked this, or does anyone have an understanding of how this works internally?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
查询规划器必须对查询中使用的表的每个分区的约束信息进行线性搜索，以找出实际涉及的分区——那些可以包含所请求的数据所需的行的分区。当您连接更多表时，规划器考虑的查询计划数量呈指数增长。因此，线性搜索花费足够多的时间而造成麻烦的确切位置实际上取决于查询的复杂性。加入越多，你受到的打击就越严重。 “多达一百”的数字来自于注意到即使在该点附近的更简单的查询上，查询计划时间加起来也是一个不小的时间量。特别是在 Web 应用程序中，响应时间的延迟很重要，这是一个问题；因此发出警告。
你能支持500吗？当然。但是，您将为优化器考虑的涉及该表的每个查询计划搜索 500 个检查约束中的每一个。如果您不关心查询计划时间，那么您可能不在乎。但大多数站点最终都不喜欢在具有如此多分区的查询规划上花费的时间比例，这就是为什么每月分区成为大多数数据集标准的原因之一。您可以轻松存储 10 年的数据，每月进行分区，然后再开始跨越规划开销开始明显的地方。
The query planner has to do a linear search of the constraint information for every partition of tables used in the query, to figure out which are actually involved--the ones that can have rows needed for the data requested. The number of query plans the planner considers grows exponentially as you join more tables. So the exact spot where that linear search adds up to enough time to be troubling really depends on query complexity. The more joins, the worse you will get hit by this. The "up to a hundred" figure came from noting that query planning time was adding up to a non-trivial amount of time even on simpler queries around that point. On web applications in particular, where latency of response time is important, that's a problem; thus the warning.
Can you support 500? Sure. But you are going to be searching every one of 500 check constraints for every query plan involving that table considered by the optimizer. If query planning time isn't a concern for you, then maybe you don't care. But most sites end up disliking the proportion of time spent on query planning with that many partitions, which is one reason why monthly partitioning is the standard for most data sets. You can easily store 10 years of data, partitioned monthly, before you start crossing over into where planning overhead starts to be noticeable.
因为每个额外的分区通常都会与检查约束相关联，这将导致规划器想知道需要查询哪些分区。在最好的情况下，规划器会识别出您只访问单个分区，并完全摆脱
append步骤。就行数而言，正如 DNS 和 Seth 所指出的，您的里程将随硬件而变化。不过，一般来说，查询 1M 行表和 10M 行表之间没有显着差异 - 特别是如果您的硬盘驱动器允许快速随机访问并且使用以下命令进行集群（请参阅
cluster语句）：您最常点击的索引。Because every extra partition will usually be tied to check constraints, and this will lead the planner to wonder which of the partitions need to be queried against. In a best case scenario, the planner identifies that you're only hitting a single partition and gets rid of the
appendstep altogether.In terms of rows, and as DNS and Seth have pointed out, your milage will vary with the hardware. Generally speaking, though, there's no significant difference between querying a 1M row table and a 10M row table -- especially if your hard drives allow for fast random access and if it's clustered (see the
clusterstatement) using the index that you're most frequently hitting.每个表分区占用文件系统上的一个索引节点。 “非常大”是一个相对术语，取决于您选择的文件系统的性能特征。如果您想要明确的性能基准，您可能可以从您选择的操作系统和文件系统中查看邮件系统的各种性能基准。一般来说，除非您进入数万到数十万的表空间（使用 dirhash 将会获胜）。另请注意，同样的限制适用于 PostgreSQL 中的数据库、表或任何其他文件系统支持的数据库对象。
Each Table Partition takes up an inode on the file system. "Very large" is a relative term that depends on the performance characteristics of your file system of choice. If you want explicit performance benchmarks, you could probably look at various performance benchmarks of mails systems from your OS and FS of choice. Generally speaking, I wouldn't worry about it until you get in to the tens of thousands to hundreds of thousands of table spaces (using dirhash on FreeBSD's UFS2 would be win). Also note that this same limitation applies to DATABASES, TABLES or any other filesystem backed database object in PostgreSQL.
如果您不想信任编写代码的 PostgreSQL 开发人员，那么我建议您自己尝试一下，并使用不同的分区方案运行一些示例查询并解释分析和计时。无论如何，您的特定硬件和软件配置可能会主导任何答案。
我假设查询优化器用来确定要使用哪些连接和限制的行优化缓存存储在每个分区中，因此它可能需要加载和读取每个分区的部分内容来规划查询。
If you don't want to trust the PostgreSQL developers who wrote the code, then I recommend that you simply try it yourself and run a few example queries with explain analyze and time them using different partition schemes. Your specific hardware and software configuration is likely to dominate any answer in any case.
I'm assuming that the row optimization cache which the query optimizer uses to determine what joins and restrictions to use is stored with each partition, so it probably needs to load and read parts of each partition to plan the query.