3年部署3000套PG实例的架构设计与踩坑经验( 五 )

本文插图
在性能上，多字段and组合查询时Gin的性能可达到Btree的3倍。

本文插图
但是Gin也不是万能的，它也有一些短板，有些场景下使用Gin索引会适得其反。概况起来主要有以下几点，需要注意：

两边界的范围查询效率很低；

按上下边界分别扫描，效率很低。但优化器的估算cost却严重偏低，容易误走gin索引

高基数字段的索引的效率不如btree；

空间占用
查询速度
Fast update list过大的会影响点查询的QPS(可通过gin_pending_list_limit控制)

对固定的多字段AND组合查询(比如 a=1 and b=2) ，效率不如btree；
不支持唯一索引；
不支持使用索引优化like前缀匹配，比如like ‘abc%‘（pg_trgm支持）；
不支持使用索引优化组合字段的oredr by ， group by(单字段支持)；
对更新的性能影响比btree更大；
比btree更容易膨胀。

问题二：后端内存占用
PostgreSQL是多进程架构，每一个连接对于一个进程。每个进程的私有内存空间中会缓存一些元数据，比如系统表数据，表定义，执行计划等等。如果使用不当，可能会由于后端进程私有内存占用过大导致系统内存不足，导致内存SWAP等问题。

本文插图
我们可以用下面的命令查看某个后端进程的私有内存分配情况：
gdb --batch-silent -ex ‘call MemoryContextStatsDetail(TopMemoryContext,100)’ -p ${后端进程号}
执行上面的命令后，相关输出反应在PostgreSQL的日志文件中。

本文插图
上面这个图里，私有内存的大部分被元数据缓存占用了，即CacheMemoryContext 。
通常，元数据缓存中占用内存最多的是下面两个系统表

pg_statistic
pg_attribute

当数据库里表，分区以及表字段很多时，它们的元数据会占用的内存也会比较多。下表的例子可以反映这一点。

本文插图
从上面的数据我们可以知道，表相关的元数据缓存占用内存的大小主要和下面几个因素有关：

查询实际访问的表或分区的数量
查询实际访问的字段的数量

如果在使用PostgreSQL的过程中出现内存不足的问题，我们可以采取以下回避措施：

调小应用端连接池大小(max pool size和min pool size)；
部署pgbouncer连接池；
减少分区数；
访问不同表或分区的连接尽量隔离，比如需要访问全部分区的连接使用单独的连接池；
通过ALTER TABLE SET STATISTICS对不需要通过柱状图评估选择性的字段，减少收集的统计信息；
拆分负载。

读写分离
sharding

问题三：垃圾回收
PostgreSQL的MVCC实现机制和其它传统的关系数据库不太一样。更新记录时不是在原地更新并且把修正的前镜像记录到UNDO日志，而是在数据文件中把原来的记录标记为“被删除”再插入一条新的记录，以后再通过VACUUM把这些”被删除“记录占用的空间回收掉。也就是说PostgreSQL中只有REDO日志，没有UNDO日志。在这种MVCC设计下， PostgreSQL回滚事务可以立即完成，和事务大小无关，回滚事务时只需要在CLOG事务状态文件中标记这个事务的状态为ABORTED即可。