PostgreSQL中BRIN索引和表分区的区别

Question

什么是PostgreSQL中的BRIN索引和表分区之间的区别？我何时应该使用其中一个而不是另一个？它们似乎提供非常相似的好处并且也有类似的用例。

举个例子，假设我们有以下表结构。

CREATE TABLE orders (
id SERIAL PRIMARY KEY,
store_id INT,
client_id INT,
created_at timestamp,
information jsonb
)

具有以下特征的表：

• 只能插入订单，不允许删除，并且更新很少并且不涉及created_at列
• created_at列包含行在数据库中插入的时间戳，因此该列中的值严格递增
• 几乎每个查询都在条件中使用created_at列，其中一些可能使用store_id和client_id列
• 最常访问的行是根据created_at列的最新行
• 某些查询可能返回少量记录（例如：分析单个记录或创建在短时间间隔内的记录），而其他查询可能会扫描大量记录（例如：仪表板功能的聚合函数）

我选择这个例子是因为它非常普遍，而且两种方法都可以使用（在我看来）。在这种情况下，在整个表上使用BRIN索引还是使用带有B树索引的分区表（或者只是简单的B树索引而不进行分区）之间应该选择哪一个？表的大小是否影响选择？

- Sarfo

---

你有什么（性能）问题？你能给我们展示一下在出现这个问题的SELECT语句中EXPLAIN(ANALYZE, VERBOSE, BUFFERS)的结果吗？对于每月接收数亿条记录的表，我使用表分区，但我从未使用过brin。但是，如果没有问题，就没有什么需要解决的了。 - Frank Heikens

@FrankHeikens 我没有实际的问题。我想知道这两种方法之间的区别，因为它们可以在类似的情况下使用并提供相同的好处。我还为了澄清而做了一个例子，但那只是一个理论上的例子。 - Sarfo

没有真实的例子，你所能做的不多。据我所知，这两者之间有很大的区别，但我从未使用过brin。如果不知道记录数量和实际查询，我不会花时间进行假设性优化。 - Frank Heikens

Answer 1

我曾经使用过这两种功能（尽管我要注意一下，我的分区体验是在引入CREATE TABLE ... PARTITION BY之前需要使用继承+约束），你说的对，它们表面上看起来相似，但它们的机制完全不同。

表分区基本上是这样工作的：将所有对的引用替换为（select * from table_partition1 union all select * from table_partition2 /* 重复所有分区 */）CHECK（client_id = 1）client_id = 2

。分区将具有对分区列的约束条件，因此如果这些列出现在WHERE中，则可以预先应用约束条件来剪枝哪些分区实际上被扫描。也就是说，如果table_partition1具有，并且您的WHERE具有，则table_partition1将被跳过，因为表约束自动排除了该分区中的所有行，使其无法通过该WHERE句子。相比之下，BRIN索引选择了表的块大小，然后对于每个块，记录了索引列的最小/最大边界。这样，当我们能够看到特定行块中的最大created_at低于WHERE子句中的created_at>={some_value}时，WHERE条件就可以跳过整个块。我不能告诉您一个确切的答案，哪个对您的情况更好。嗯，事实并非如此：确切的答案是，“为您自己的数据进行基准测试” ;)这有点模糊，但我的一般感觉是BRIN很轻量级，而表分区不是。BRIN可以很容易地添加到现有表中，索引本身非常小，在不加入过多的索引的情况下对写入的影响不大。另一方面，表分区是在磁盘上表示数据的不同方式；您实际上正在确定特定行将写入哪些数据文件。当将其引入到现有数据集中时，这需要一个更复杂的迁移过程。然而，可用于表分区的查询优化集合要大得多。不仅有我上面描述的约束排除，而且您还可以在每个单独的分区上拥有索引（甚至是BRIN！）。当然，您也可以在单个大表上使用BRIN和其他索引，但我不确定在实际情况下是否特别有用。还有几点想法：BRIN适用于单调数据（时间戳、递增ID等）；磁盘排序与索引值之间的相关性越高，BRIN索引剪枝块的效果就越好。然而，像客户ID这样的字段不太可能与BRIN配合得很好；任何给定的行块都可能具有至少一个相对较低和相对较高的ID。然而，像这样的字段非常适合分区：每个客户的分区或按客户ID的模数进行分区（更常见地称为分片）是一种几乎可以无限水平扩展的好方法。

- AdamKG

Answer 2

即使更新不改变索引列，任何更新都会使BRIN索引变得无用（除非是HOT更新）。即使没有这个问题，也存在差异，例如：

• 分区允许您有效地摆脱大量数据，但BRIN索引不行

• 分区表允许每个分区有一个自动清理工作进程，提高了自动清理性能

但如果您唯一关心的是有效地选择索引或分区键的所有行，两者可能提供大致相同的好处。

- Laurenz Albe