PostgreSQL 10 GIN索引 锁优化

postgresql , gin , 倒排索引 , 全文检索 , 性能优化

postgresql gin索引接口常被用于多值列的检索，例如全文检索类型、数组类型。

有兴趣了解更多索引接口的原理和使用场景，可以参考下文。

<a href="https://github.com/digoal/blog/blob/master/201706/20170627_01.md">《postgresql 9种索引的原理和应用场景》</a>

今天要说道一下postgresql gin索引的代码优化。

在说gin代码优化前，我们先来看一个场景，以及在老版本下的性能表现。

创建一张测试表，三个字段，其中一个全文检索字段，另一个pk，还有一个时间。

全文检索字段使用随机字符串生成，建立索引。

测试sql

更新crt_time时间字段，但是不更新全文检索字段。

注意，虽然我们没有更新全文检索字段，但是依旧会导致gin索引的变更，因为token-&gt;ctid，由于pg多版本的原因这里的ctid会变化，如果ctid变成了其他page的行，那么索引也需要变化。

即使是更新后的记录在同一个page（hot更新），vacuumm时将老的记录删掉也需要变更索引entry。

总之这个为了突出业务上可能忽视的问题。以为不更新索引字段，索引就不需要变化。

ps：pg 10或将来会支持二级索引，就不会存在以上问题。那么用户只需要考虑索引字段value被更新的情况。

1、4并发

2、16并发

3、64并发

我们发现，并发越高，性能抖动非常严重，但是数据库中并未发现waiting。

跟踪进程pstack，如下，出现了lock和sleep。

pg gin索引有一个fastupdate的选项，实际上是因为一条记录涉及多个token，为了防止索引频繁更新，pg设计的一种快速dml方法。就是先将数据写入pending list，然后由vacuum, analyze或当list满时触发将pengding list合并到gin tree的动作。

首先看一下pending list区域的大小由什么控制。

postgresql 9.4的pending list大小由work_mem参数控制。

<a href="https://www.postgresql.org/docs/9.4/static/gin-implementation.html#gin-fast-update">https://www.postgresql.org/docs/9.4/static/gin-implementation.html#gin-fast-update</a>

src/backend/access/gin/ginfast.c

postgresql 10的gin pending list大小由表级参数，或者全局参数gin_pending_list_limit控制。

<a href="https://www.postgresql.org/docs/10/static/gin-implementation.html">https://www.postgresql.org/docs/10/static/gin-implementation.html</a>

src/include/access/gin_private.h

性能抖动和pending list大小有没有关系呢？

默认work_mem, gin_pending_list_limit都是4mb。

1、work_mem = 64kb

4并发

64并发

2、work_mem = 128kb

3、work_mem = 32mb

观察到一个现象：

1、pending list(work_mem)越大，性能抖动越严重，tps=0越持久。

2、work_mem越小，性能抖动越少，但是峰值性能会有一定的下降。

3、并发越低，性能越稳定。

4、work_mem较小时，即使并发较高，tps=0的几率也非常小。

postgresql 9.4的优化建议：

1、work_mem设置为64kb，降低更新并发（例如使用连接池控制并发）。

2、将创建了gin索引的字段剥离到独立的表，通过pk将两者进行关联。

例如

postgresql 10提交了一个patch，解决了gin vacuum时需要对整个posting tree的所有页面长时间持锁的问题。

<a href="https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=218f51584d5a9fcdf702bcc7f54b5b65e255c187">https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=218f51584d5a9fcdf702bcc7f54b5b65e255c187</a>

1、pending_list_limit = 64kb

2、pending_list_limit = 128kb

3、pending_list_limit = 4mb

4、pending_list_limit = 128mb

postgresql 10 性能非常的平稳，即使是高并发，高pending list的情况下，没有出现tps=0的情况。

同时在pg 10下，pstack没有观测到idx_test_info索引被更新的情况，这也是一个大的改进，可以找一下git.postgresql.org对应哪个patch。

对于需要频繁更新的表，如果这个表的某些字段建立了gin索引，为了减少gin索引的更新开销，优化如下。

1、设置表的fillfactor（如=50），尽量使用让数据库使用hot更新。减少行迁移，从而减少索引entry的更新。

2、设置较小work_mem，例如设置为64kb。

3、使用连接池，控制并发。

4、将tsvector字段拆分出来，使用pk进行关联。完全杜绝没必要的更新。

其中1,2是最好实施的，不影响业务，效果立竿见影(但是在巨大压力、巨大并发下依旧偶尔会有一两秒的tps=0)。

pg 10在巨大压力、巨大并发(同时伴随checkpoint, vacuum的虐待)下，tps表现都非常平稳，抖动不超过5%。

1、使用二级索引

<a href="https://github.com/digoal/blog/blob/master/201703/20170312_21.md">《postgresql 10.0 preview 性能增强 - 间接索引(secondary index)》</a>