sql - 是否可以在 postgresql 中为每个主键创建索引？

Question

假设我有一个表，其中经常插入两个字段:

user_id uuid,
date timestamp

我还有一个关于 (user_id, date) 的普通 b 树索引。

这种方法的问题是 postgres 不能并行插入不同的 user_ids，因为索引必须按顺序更新，因为它是一棵树，每次插入后都可以重新平衡，所以它必须等到每个特定的插入完成。

我想要的是每个 user_id 的独立索引，这样插入就可以并行完成。有办法吗？

-------- 已编辑:Laurenz Albe 的完美答案如下

Answer 1

摘要:

你低估了 B 树索引的力量。

B 树索引上的多个插入可以并行运行，并且树永远不会重新平衡。相反，您偶尔会有一个索引页面拆分，它只会在短时间内阻止对该页面的操作。

引用资料:

分页算法在Lehman and Yao的著名论文中有描述(任何对索引内部原理感兴趣的人都必须阅读)和 nbtree README来自 PostgreSQL 源的描述了更多细节，例如如何处理删除。

插入算法的简短描述:

只要索引页未满，就会插入新条目。这只会导致对索引页的短暂锁定。

如果页面已满，将使用 Lehman & Yao 算法拆分，该算法一次最多锁定三个页面。这种拆分需要父页面中新创建页面的新条目，因此该页面可能也必须拆分，可能会重复出现到根页面。

仍然需要不超过三个锁，因为这些操作一个接一个地发生。

请注意，这样的根页面拆分在索引的生命周期内只发生 3-4 次，因为很少有索引的深度超过 5 层。

这样，B-tree 索引的所有分支都具有相同的深度，因此索引始终是平衡的，不需要重新平衡。重新平衡只能在条目删除期间才有意义，但 PostgreSQL 不会这样做(除了当索引页完全变空时它会回收索引页)。

关于您问题的其他说明:

像您建议的那样使用多个索引不会使这一切变得更快——如果您必须为每个 user_id 创建一个索引，它会使事情变得更加复杂和缓慢，而且这样的索引不能无论如何都用于搜索。

尽管如此，索引还是大大减慢了插入速度。如果同时插入和查询数据，这是无法避免的问题。如果在您执行批量插入时没有人查询数据，您可以删除索引并在之后重新创建它。