PostgreSQL分区？

Question

我的软件每30分钟运行一个cronjob，从Google Analytics / 社交网络中获取数据并将结果插入到Postgres DB中。

数据看起来像这样:

url text NOT NULL,    
rangeStart timestamp NOT NULL,
rangeEnd timestamp NOT NULL,
createdAt timestamp DEFAULT now() NOT NULL,
...
(various integer columns)

由于一次查询会返回10,000多个条目，将这些数据存储在单个表中显然不是一个好主意。以这种速度，cronjob每天将生成约480,000条记录，每月大约1450万条记录。

我认为解决方案是使用几个表，例如我可以使用一个特定的表来存储在给定月份生成的数据：stats_2015_09、stats_2015_10、stats_2015_11等等。

我知道Postgres支持表分区。然而，我对这个概念还很陌生，所以我不确定在这种情况下最好的方法是什么。我需要在这种情况下进行分区吗，还是应该手动创建这些表？或者也许有更好的解决方案？

数据稍后将以各种方式查询，并且这些查询预计会快速运行。

编辑：

如果我最终拥有12-14个表，每个表存储10-20百万行，那么Postgres仍应能够快速运行select语句，对吗？插入操作不必非常快。

- user2297996

---

这个主题的官方文档很简洁，应该足够了。 - klin

请参阅pg_partman，它是一个易于使用的扩展程序，提供了许多内置功能缺失的便利性。 - IMSoP

Answer 1

在各种情况下，分区是个好主意。以下是两种情况：

• 如果您的查询有一个可以轻松映射到一个或一些分区的WHERE子句。
• 如果您想要快速删除历史数据（删除分区比删除记录更快）。

如果不知道要运行的查询类型，则很难说分区是否是一个好主意。

我认为将数据拆分成不同的表是一个糟糕的想法，因为它是一个维护噩梦：

• 您不能在该表中设置外键引用。
• 涉及多个表的查询很麻烦，所以简单问题很难回答。
• 维护表格变得非常困难（添加/删除列）。
• 如果您有不同角色的用户，则必须仔细维护权限。

在任何情况下，开始学习Postgres分区的文档是正确的做法，该文档在这里：here。我应该指出，Postgres的实现比其他数据库略微笨拙，因此您可能需要查看MySQL或SQL Server的文档，以了解它在做什么。

- Gordon Linoff

---

感谢您的回复。我无法确定我们需要哪种查询，因为历史数据将由不同的应用程序查询。但我认为最重要的关键是日期范围（从-到），因此也许根据此创建分区是个好主意。 - user2297996

Answer 2

首先，我想挑战你问题的前提：

由于一个查询返回了10,000多个项目，将这些数据存储在单个表中显然并不是一个好主意。

据我所知，数据库没有根本性原因无法处理数百万行的单个表。在极端情况下，如果你创建了一个没有索引的表，并且只是向其中添加行，Postgres只需继续将这些行写入磁盘，直到你的存储空间用尽为止。（可能存在其他内部限制，但如果有的话，它们是很大的。）

问题只会在你尝试对该数据进行某些操作时出现，并且确切的问题-以及因此确切的解决方案-取决于你做了什么。

如果您想定期删除所有插入时间超过一定时限的行，则可以按照createdAt列对数据进行分区。这样，DELETE将变成一个非常高效的DROP TABLE，而所有INSERT都将通过触发器路由到“当前”分区（或者甚至可以绕过它，如果您的导入脚本知道分区命名方案）。然而，在SELECT中，可能无法在其WHERE子句中指定createAt值的范围，因此需要查询所有分区并组合结果。您保留的分区越多，效率就越低。

或者，您可以检查表上的工作负载，并看到所有查询都已经明确规定了rangeStart值，或者很容易规定。在这种情况下，您可以按rangeStart进行分区，查询计划器将能够在计划每个SELECT查询时消除除一个或几个分区之外的所有分区。INSERT需要通过触发器路由到适当的表格，维护操作（例如删除不再需要的旧数据）将不那么高效。

也许您知道，一旦rangeEnd变得“太旧”，您将不再需要数据，并且可以获得两种好处：按rangeEnd进行分区，确保所有的SELECT查询明确提到rangeEnd，并删除包含您不再感兴趣的数据的分区。

借用Linus Torvald在git中的术语，“分区”的“管道”已经以表继承的形式内置于Postgres中，如此记录，但除了手册中的示例之外，几乎没有其他的“瓷器”。然而，有一个非常好的名为pg_partman的扩展程序，它提供了基于ID或日期范围管理分区集的函数；值得阅读文档以了解不同的操作模式。在我的情况下，没有一个完全匹配，但是分叉该扩展比从头开始编写所有内容要容易得多。

记住，分区并非免费的。如果没有基于上述考虑的明显候选列可用于分区，则可能最好将数据留在一个表中，并考虑其他优化策略。例如，部分索引（CREATE INDEX ... WHERE）可以处理最常查询的子集；也许与“覆盖索引”结合使用，其中Postgres可以直接从索引返回查询结果，而无需参考主表结构（“仅索引扫描”）。

- IMSoP

---

谢谢您详细的解释，非常有用！我已经查看了文档，并将现在检查pg_partman。 到目前为止，createdAt似乎是一个很好的分区列。我查看了查询代码，最重要的查询可以明确地提到它（作为范围）。 - user2297996

关于将数据保存在一个表中：这是一个选择。但是，即使该表被正确地建立了索引，如果有5亿行数据，SELECT查询仍然会相对较慢，除非索引能够保证每次查询只需要检查数据集的一小部分。 因此，使用分区或其他技术来拆分数据似乎是更好的方法。我对此有误吗？ - user2297996

@user2297996 "除非索引能够保证每次查询只需要检查数据集的一小部分" - 这正是索引的作用，高效地跳转到数据集的正确部分。我不知道btree索引的工作细节，但我的经验法则是，如果我不知道它是如何工作的，那么数据库管理系统在优化低级结构方面做出的决策可能比我手动优化更好。 - IMSoP