直接通过块范围索引（BRIN）标识符查询Postgres表

Question

我有 N 台客户机。我想将每台机器加载不同的BRIN索引分区。

这需要以下操作：

• 创建具有预定义分区数量（等于客户机数量）的BRIN索引
• 从客户端发送查询，使用BRIN分区标识符而不是对索引列进行过滤的WHERE

主要目标是在将单个表从Postgres加载到分布式客户端机器时提高性能，保持客户端之间的行数相等或接近相等（如果行数不能被机器数量整除）。

我目前可以通过维护新的列来实现它，该列将我的表划分为等于客户机数量的桶的数量（或在运行时使用 row_number() over (order by datetime) % N）。但这种方法在时间和内存方面效率较低，而BRIN索引看起来是一种可以加速此类用例的好功能。

3台客户机的最小可重现示例：

CREATE TABLE bigtable (datetime TIMESTAMPTZ, value TEXT);
INSERT INTO bigtable VALUES ('2015-12-01 00:00:00+00'::TIMESTAMPTZ, 'txt1');
INSERT INTO bigtable VALUES ('2015-12-01 05:00:00+00'::TIMESTAMPTZ, 'txt2');
INSERT INTO bigtable VALUES ('2015-12-02 02:00:00+00'::TIMESTAMPTZ, 'txt3');
INSERT INTO bigtable VALUES ('2015-12-02 03:00:00+00'::TIMESTAMPTZ, 'txt4');
INSERT INTO bigtable VALUES ('2015-12-02 05:00:00+00'::TIMESTAMPTZ, 'txt5');
INSERT INTO bigtable VALUES ('2015-12-02 16:00:00+00'::TIMESTAMPTZ, 'txt6');
INSERT INTO bigtable VALUES ('2015-12-02 23:00:00+00'::TIMESTAMPTZ, 'txt7');

期望输出：

• 客户端1

2015-12-01 00:00:00+00, 'txt1'
2015-12-01 05:00:00+00, 'txt2'
2015-12-02 02:00:00+00, 'txt3'

• 客户端 2

2015-12-02 03:00:00+00, 'txt4'
2015-12-02 05:00:00+00, 'txt5'

• 客户端 3

2015-12-02 16:00:00+00, 'txt6'
2015-12-02 23:00:00+00, 'txt7'

问题：
我如何创建预定义分区数量的BRIN，并运行过滤分区标识符而不是索引列的查询？
此外，是否有其他方式可以使用BRIN（或其他pg好处）加速从单个表中并行加载多个客户端的任务？

- jangorecki

---

这个问题可能在https://dba.stackexchange.com上更容易得到解决。 - Kristján

@pozs，硬性假设是在查询表时没有写入操作，因为两个进程都按顺序运行在工作流中。BRIN 看起来非常适合这种用例：加载数据，创建 BRIN，分析表，然后运行查询。 - jangorecki

问：所有（客户端）机器在启动时是否都知道应该有多少个分区？换句话说，假设您在第n天使用x台机器启动进程，您希望将表均匀分割成x个分区，对吗？但是在第x+1天，您可能有y台机器，因此希望将其分割为y个分区。我的问题是：当客户端机器连接时，它是否“知道”它想要表的1/x或1/y？ - deroby

row_number() over (order by datetime) % N 会将每个第 N 行放入一个桶中（与您的示例中的前 N 行、第二行等不同）。例如，第一组将是 1、4、7 而不是 1、2、3。 - FuzzyTree

@FuzzyTree 这并不重要，因为row_number() over ()是一个较差的解决方法，如果足够暴露BRIN，则可以提供更好的解决方案。 - jangorecki

Answer 1

听起来你想在许多机器上分片一个表，并且每个本地表（全局表的一个分片）都有一个BRIN索引，恰好有一个桶。但这没有任何意义。如果单个BRIN索引范围覆盖整个（本地）表，那么它永远不会很有帮助。

听起来你正在寻找具有可用于分区排除的CHECK约束的分区。 PostgreSQL使用表继承长时间支持了这一点（尽管不是每个分区都在单独的机器上）。使用此方法，必须为每个分区显式设置CHECK约束中涵盖的范围。能够明确指定边界似乎正是你要寻找的，只是使用了不同的技术。

但是，分区排除约束代码不能很好地与模数配合使用。该代码足够聪明，知道WHERE id=5只需要检查CHECK（id BETWEEN 1 and 10）分区，因为它知道id = 5意味着id在1到10之间。更准确地说，它知道这个命题的逆否命题。

但是代码从未被编写成知道WHERE id=5意味着id%10 = 5%10，即使人类知道。因此，如果您基于模数运算构建分区，例如CHECK (id%10=5)而不是基于范围，如果您希望利用约束条件，则必须在所有查询中添加WHERE id = $1 and id % 10= $1 %10。

- jjanes

---

物理分区是解决这个问题的最佳方式，是否有一种动态的方法可以创建（分区）表作为选择？这样我就可以使用 row_number() over () 在数据库端准备分区了？ - jangorecki

Answer 2

根据您的描述和评论，我认为您正在朝错误的方向寻找。您想要事先拆分表格，以便访问快速简单，但是又不想事先拆分，因为这需要您提前知道节点数量，而这有点可变，如果我理解正确的话。而且，拆分也需要相当多的处理。

说实话，我会用不同的方式来解决你的问题。我建议为每个记录分配一个给定范围内的伪随机值，而不是将每个记录分配到一个桶中。我不知道Postgres怎么样，但在MSSQL中，我会使用BINARY_CHECKSUM(NewID())代替Rand()，主要原因是随机函数在SET-based的情况下更难使用。你也可以使用一些哈希代码来返回合理的工作空间。无论如何，在我的MSSQL环境中，结果值将是一个有符号整数，位于-2^31至+2^31的范围内（取决于精度，查看文档以获取确切的边界！）。因此，当主机器决定分配n个客户端机器时，每台机器都可以被分配一个确切的范围，这个范围将根据随机化/哈希算法的属性包含一个相对接近于工作负载除以n的合理近似值。假设你在选择字段上有索引，无论你决定将表拆分成一千个还是一百万个块，都应该相当快速。

附注：请注意，只有处理行数（大大）超过将执行处理的机器数量时，此方法才能正常运行。如果数量很小，则可能会看到一些机器没有得到任何东西，而其他机器则要做所有的工作。

- deroby

---

你理解得很正确。这可能需要与创建brin一样多的处理。你提出的解决方案看起来并不高效，至少不如创建brin。按索引进行选择会很快，但无法像直接访问分区数据那样扩展得好 - 这正是我想通过brin实现的。 - jangorecki

请随意发表不同意见，但在我看来，通过索引进行任意选择将很容易胜过您花费的额外时间来拆分表格（从我在BRIN索引帮助文件中所读到的内容来看，这是您需要事先做的）。我没有使用BRIN索引的经验，但从帮助文件中看来，它们看起来很像某种（有损）分区。我完全支持分区，但不要指望将具有20个分区的表格拆分为25个分区或反之亦然，这需要瞬间完成，否则您会感到非常失望。 - deroby

据我所知，您可以在没有任何预先准备的情况下直接在表格上创建分区索引。 - jangorecki

就像我说的，我不熟悉BRIN索引，但是你想让索引处理“你是第5块的一部分”的情况，对吧？那么你需要以某个字段值为基础预定义哪些记录是第5块的一部分（通过创建索引）。如果今天有X台客户端机器，但明天会有Y台，这意味着你需要重新定义桶并在“过夜”期间重新创建索引。如果BRIN确实将所有相关记录都保存在磁盘上，这可能导致需要移动数据。后续获取可能很快，但准备工作不会。 - deroby

Answer 3

基本上，你需要知道的就是在加载后关系的大小，然后将pages_per_range存储参数设置为能够得到所需分区数的除数。

不需要引入人工分区ID，因为有足够的类型和运算符支持。物理表布局在这里很重要，所以如果你坚持将分区ID作为键，并且最终引入了自然加载顺序和人工分区ID之间的无序映射，请确保在创建BRIN之前按该列的排序顺序对表进行聚集。

然而，同时记住，更离散的值比较少的值更容易命中索引，因此高基数更好——人工分区标识符将具有自然键的1/n基数，其中n是每个分区的不同值的数量。

更多这里和这里。

- Grega Bremec

---

不需要引入人工分区 ID - 那么如何查询数据并将其平均分成块，而无需猜测索引列的值或它们的分布，以便我可以猜测块的范围？我熟悉 pg 文档，但它没有回答我的问题，你的答案也没有。 - jangorecki

那么答案就是BRIN不是你想要的。就数值而言，它是非歧视性的，只是将整个范围分成相等大小的块。它将帮助您在顺序扫描中命中较少的页面（与任何索引一样），但仅此而已。它无法告诉您要搜索什么。 - Grega Bremec

但从逻辑角度来看，它是可以的，基本上只需要导出一个分区ID的API。也许内部信息已经存在，但尚未记录在案。 - jangorecki

你可以尝试广泛使用ctid伪列（它返回表中任何记录的（页面，记录）位置），以便从中理解一些意义。但你可能会发现，除了关键条件——具有自然聚集的数据（你有时间戳），你的解决方案与BRIN无关。我猜在表中有x个页面的情况下，第m个分区可以执行查询“where ctid between ((m-1)(x/n)) and (m(x/n))”。由于这直接涉及表结构，因此您不需要其他对象。 - Grega Bremec

请注意在where子句中需要从ctid中提取页码。 - Grega Bremec