在PostgreSQL中有数十亿行数据：应该进行分区还是不进行分区？

Question

我的设备：

• 一台拥有1颗Xeon处理器，8个逻辑核心和16GB内存的简单服务器，使用2个7200转/分钟的硬盘组成MDADM RAID1。
• PostgreSQL
• 有大量需要处理的数据，每天最多导入3000万行数据。
• 时间——复杂查询可能需要执行一个小时。

表格的简化模式将非常大：

id| integer | not null default nextval('table_id_seq'::regclass)
url_id      | integer | not null
domain_id   | integer | not null
position    | integer | not null

上述架构的问题在于我不知道如何精确地对其进行分区。 所有时间段的数据都将被使用（没有查询会有日期过滤器）。

我考虑在“domain_id”字段上进行分区，但问题是很难预测每个分区将有多少行。

我的主要问题是：

如果我不使用分区修剪并且不打算删除旧数据，那么分区数据是否有意义？

这样做的利弊是什么？

如果我不进行分区，导入速度会降低多少？

与规范化相关的另一个问题： url是否应该导出到另一个表中？

规范化的好处

- 表将具有平均大小为20-30字节的行。 - 对“url_id”进行的连接比对“url”字段进行的连接快得多

非规范化的好处

- 数据可以更快地导入，因为在每次插入之前我不必查找“url”表。

谁能给我一些建议？ 谢谢！

- Oleg Golovanov

---

正面朝上，你进行规范化；反面朝上，你不进行规范化。✔ - user1200540

根据您对这些数据的处理需求，您的硬件可能有点不足，特别是磁盘阵列。您需要仔细调整和设计工作流程才有机会成功。不要误解，我们拥有在PostgreSQL数据库中存储5TB数据、每天处理数千万请求且性能非常出色的机器，但我们并不是在一对7200 RPM驱动器上运行。 - kgrittn

Answer 1

如果你的查询大部分时间都有选择条件，让规划器大部分时间可以跳过大多数分区的访问，或者你想定期清除所有分配给分区的行，或者两者兼备时，分区是最有用的。(删除表是删除大量行的非常快速的方法!) 我听说过有人达到了分区帮助保持索引较浅，因此提高性能的阈值；但实际上这又回到了第一点，因为你有效地将索引树的第一级移动到另一个位置——它仍然必须发生。

乍一看，似乎分区不会有所帮助。

另一方面，规范化可能比你想象的更能提高性能。通过使所有这些行变得更窄，你可以将更多的行放入每个页面中，减少总体磁盘访问。我会进行适当的第三范式规范化，并仅基于证据偏离规范化。如果在你仍然有足够磁盘空间来保存数据的第二份副本时遇到性能问题，请尝试创建一个非规范化的表格，并比较其与规范化版本的性能。

- kgrittn

Answer 2

根据您的使用情况，我认为这是有意义的。我不知道您的30B行历史记录有多久，但如果您的事务性数据库不需要您决定的分区中的更多分区，则进行分区是有意义的。

例如，如果您每次只查询两个月的数据，则按月份进行分区是非常合理的。一年中的其他十个月可以移动到报告仓库中，使事务存储更小。

对于可以在分区中使用的字段存在限制，您必须小心处理。

获取性能基线，进行分区，然后重新测量以检查性能影响。

- duffymo

---

我在我的帖子中写道：“将使用所有时期的数据。”这里我的意思是，没有查询会有日期过滤器。这就是为什么我在这里问，是否有必要进行分区。 - Oleg Golovanov

Answer 3

考虑到给定的数据量，您将主要等待IO。如果可能的话，请使用不同的硬件配置进行一些测试，以获取最佳的IO数据。在我看来，除非幕后有其他东西，否则2个磁盘不足够。

您的表将按已知比例每天增长。很可能每天都会查询它。由于您没有提到数据被清除（如果将被清除，则分区），这意味着每天查询速度会变慢。在某个时间点，您将开始寻找如何优化查询。其中一种可能性是在应用程序级别上并行查询。但是，这里必须满足一些条件：

- 为了并行查询，您的表应该被分区； - 硬件应该能够以N个并行流传递所请求的IO量。

所有答案都应该由不同设置的性能测试给出。

正如其他人提到的那样，分区表对DBA有更多好处，因此，我个人会为预计每个间隔接收超过5M行的任何表进行分区。

- vyegorov

---

这里的主要问题是 - 如果我不使用分区修剪，并且我不打算删除旧数据 - 那么我是否会从分区中获得任何好处，例如按日期（以预测行的扩散）？ 一百个小表/索引是否比一个大表/索引更好？在什么情况下？ - Oleg Golovanov

对于DBA来说，维护分区表更容易，因为所有操作都可以按分区进行，并且对系统和其他查询的影响较小。对于ORACLE，即使我们不希望获得性能提升，我们也会对所有大型表进行分区。尽管到目前为止，我们已经通过分析查询在所有情况下找到了一个好的分区键。我建议您进行一些测试，以便更好地了解情况。 - vyegorov

这个问题特别涉及到PostgreSQL，在PostgreSQL中，分区在大多数情况下并没有简化DBA的工作。目前还没有声明式分区的方法；它是通过继承机制以一种相当手动的方式实现的。在PostgreSQL中，分区不允许查询的执行并行化——至少不是由规划器；我想你可以建立多个连接，并使用单独的查询查询每个分区的数据，然后在它们全部完成后以某种方式将所有结果汇总在一起，但在两个驱动器上似乎不太可能获胜。 - kgrittn

@kgrittn，我必须同意你的观点，在这种情况下分区可能不是最好的选择。 - vyegorov