内容：
- 波斯特格雷斯15
- 表中包含数亿甚至数十亿行，这些表包含一个id列和一个JSONB列
- 无其他约束（FK）
- 从表中选择通常/经常通过搜索JSON字段的内容来进行
- 在本例中，一个JSONB信元大约为5 kB
- 不使用Posterre是不可能的
问题：有时候，表处于很大的upsert压力之下（INSERT ON CONFLICT），在这种情况下，我们需要高吞吐量，因此我们希望使用并发upsert。问题是，为了便于在表中搜索，我们在JSONB列上使用了GIN索引。维护此索引似乎会限制我们可以扩展upsert性能的程度。
- 默认情况下，Posterre使用
fastupdate来允许快速upsert，并推迟GIN维护，直到一堆非索引元组累积。默认设置为4 MB。我们看到p50和p95 upsert的持续时间<15 msec。但是在高负载下，每20-40秒我们就会看到10秒的暂停，在此期间没有任何东西可以插入到表中，我们认为这是由于GIN索引维护，即处理挂起的非索引元组。吞吐量平均为每秒约80-100个upserts，与线程数无关。 - 如果我们将
fastupdate=off改为fastupdate=off，情况就会好一些; upsert会变得有点慢，但是在高负载下，并发性仍然受到限制，所以我们不能将upsert扩展到2-3以上，这将吞吐量限制在大约100个upsert。
如果从这个表中读取变得慢一点，对我们来说是可以的，所以我想我们可以使用GIST索引来代替，但我不知道如何正确地这样做。
> create index concurrently gist_idx on thetablename using gist(thejsoncolumn);
ERROR: data type jsonb has no default operator class for access method "gist"
HINT: You must specify an operator class for the index or define a default operator class for the data type.字符串
AFAIK GIST索引维护也更加并发友好，因为使用GIN插入表需要锁定索引中的多行。
问题：
- 我的GIST想法看起来合理吗？
- 我应该如何在JSONB列上创建GIST索引（键和值都要搜索，因此类似于
json_ops的内容是可以的） - 有人有
fastupdate=off在并发负载下的行为经验吗？ - 也许我们应该将
gin_pending_list_limit设置为非常高的值（千兆字节），然后让autovacuum在后台处理它，在此之前，我们会获得较低的SELECT性能？
谢谢.
1条答案
按热度按时间wwodge7n1#
GiST专门用于可以以类似连续的方式重叠的数据，如几何图形。而不是可以以分散的方式重叠的数据，如“这是否包含字母序列'dasdf'在其中的某个地方”。因此，JSONB上的GiST索引需要使用一些高度折叠的基于签名的方法，而那些索引方法往往会吸收大元素数的数据（比如5 kB的JSONB）。这可能就是为什么没有人费心去实现GiST的原因。GIN确实是实现这一目的的正确索引方法。
我们看到p50和p95 upsert持续时间<15 msec，但在高负载下，每20-40秒我们看到一个10秒的暂停，在此期间没有东西可以插入表中
这不应该发生。一个插入进程将被分配清理挂起列表，并且该进程将在这样做时停止。但是并发插入进程不应该在它发生时阻塞，它们应该可以自由地继续。我猜可能发生的是，如果并发进程在很大程度上工作在ON CONFLICT中使用的相同的唯一键值上，那么也许一个进程在清理列表时停止，而其他进程阻塞等待涉及第一个停止的进程的冲突解决。但这应该是罕见的，除非你有一些反常的数据插入模式。另一方面，也许其他进程并没有真正停止，它们只是由于IO拥塞而变慢。你能用pg_stat_activity.wait_event（也许还有pg_locks）来识别实际发生了什么吗？
如果我们关闭fastupdate=，那么情况会好一些; upsert会变慢，但在高负载下，并发性仍然有限制，所以我们不能将upsert扩展到2-3以上，这将吞吐量限制在大约100个upsert左右。
同样，查看pg_stat_activity看看发生了什么。它可能只是IO饱和，或者可能有其他事情发生，而不仅仅是关闭了fastupdate的gin索引。但是我们需要知道其他事情是什么。
也许我们应该把gin_pending_list_limit设置得很高（千兆字节），然后让autovacuum在后台处理它，在此之前，我们会得到较低的SELECT性能？
您可以使用gin_clean_pending_list函数来清理挂起列表，而无需执行vacuum所做的所有其他操作。您必须创建自己的后台进程，定期调用此函数，因为没有内置的调度程序。这可以给予两全其美的效果，您可以经常这样做以保持列表较小，并且它发生在后台，（希望）不会阻塞任何前台任务。