主页 / dba / 问题 / 25129
Accepted
davetapley
Asked: 2012-09-29 14:43:00 +0800 CST

我们应该如何处理在 PostgreSQL 中一旦过时就不会被查询的行？

  • 772

我们在 PostgreSQL 数据库中有一个表，该表每天以数百万行的数量级增长。

每行包括：

ID
Foreign user ID
Date and time
Other data

日期和时间与 并不严格单调ID，但它们很接近。

查询此表时，我们只对获取给定外国用户 ID 的行感兴趣，这些行的日期和时间在过去两周内。两周前的行将永远不会被查询，但是我们希望保留它们以供存档。

鉴于这个特殊用例：

  1. 我们应该在日期和时间列上建立索引吗？
  2. 是否有任何提示我们可以给出日期和时间（几乎）随 ID 单调增加的提示。
  3. 我们是否应该考虑尝试从表中删除超过两周的行，这可能会提高性能吗？
postgresql performance

1 个回答

  1. Best Answer
    1. 分区和/或使用部分索引，因此您只有您关心的部分的索引
    2. 不，据我所知，Pg 中还没有相关提示。遗憾。
    3. 很大程度上取决于查询的类型，但这样做是值得的，特别是如果您可以通过分区来做到这一点。

    分割

    这是表分区的经典用法。将表分成两个（或更多）部分，一个包含热数据，一个包含旧归档数据。在热表上索引任何你需要的东西，在冷表上使用更少的索引来节省空间和insert时间。

    new_table启用约束排除后，Pg 将仅在您的查询匹配分区约束时自动扫描。

    如果您的查询不都使用相同的约束，您可以添加一个where使用约束的测试，或者直接查询new_table分区。

    部分索引

    或者，您可以创建仅覆盖您感兴趣的范围的部分索引。同样，只有当您的查询使用 Pg 可以识别为与部分索引匹配的约束时，这些才会起作用。在您的情况下，您必须随着时间的推移创建和删除它们以保持您的时间范围相关，因为您无法索引动态表达式，如WHERE (some_field > current_timestamp - INTERVAL '2' week).

    • 6

相关问题