当PostgreSQL遇上物联网，玩出什么黑科技？

在数据库中用得最多的当属btree索引,除了BTREE,一般的数据库可能还支持hash, bitmap索引.

但是这些索引到了物联网,会显得太重,对性能的损耗太年夜.

为什么呢?

物联网有大量的数据发生和入库,入库基本都是流式的.在使用这些数据时,基本是FIFO,或者范围查询的批量数据使用风格.

btree索引太重,因为索引必要存储每条记录的索引字段的值和寻址,使得索引非常庞大.

另一方面,物联网的大量范围查询和批量处理用法决定了它不必要这么重的索引.

例子：

如下所示,btree索引的空间占比是非常年夜的.

除了年夜以外,btree索引同时也会影响数据的更新,删除,或插入的性能.

例子：

有btree索引, 每秒入库28.45万行

无索引, 每秒入库66.88万行

从上面的介绍和测试数据,可以明显的看出btree索引存在的问题：

体积年夜,影响性能.

接下来该让PostgreSQL黑科技登场了：

范围索引,术语brin, block range index.

范围索引的原理,存储持续相邻的BLOCK的统计信息(min(val), max(val), has null? all null? left block id, right block id ).

例如一个表占用10000个BLOCK,创建brin index 时,指定统计每128个BLOCK的统计信息,那么这个索引只必要存储79份统计信息.

空间占用非常的小.

办理了空间的问题,还需要办理性能的问题,我们测试一下,在创建了brin索引后,插入的性能有多少?

范围索引, 每秒入库62.84万行

最后还必要对比一下 btree, brin 索引的大小,还有查询的性能.

索引年夜小比拼：

表 4163MB

btree索引 2491 MB

brin索引 4608 kB

查询性能比拼 :

范围查询

全表扫描 11 秒

范围索引 64 毫秒

btree索引 24 毫秒

(因今日头条字数限制,代码无法全部展现,请移步德歌博客查看原文：https://yq.aliyun.com/articles/27860)

精确查询

全表扫描 8 秒

范围索引 39 毫秒

btree索引 0.03 毫秒

(因今日头条字数限制,代码无法全部展现,请移步德歌博客查看原文：https://yq.aliyun.com/articles/27860)

对比图 :

小结：

.1. 范围索引重点的使用场景是物联网类型的,流式入库,范围查询的场景. 不仅仅对插入的影响微乎其微,并且索引大小非常的小,范围查询的性能和BTREE差别微乎其微.

.2. 结合JSON和GIS功能,相信PostgreSQL会在物联网年夜放异彩.

ps: oracle 也有与之类似的索引,名为storage index. 但是只有Exadata产物里有,贵得离谱,屌丝绕道.哈哈.

https://docs.oracle.com/cd/E50790_01/doc/doc.121/e50471/concepts.htm#SAGUG20984

DBA应该具备抓住各种数据库的特性,而且将这种特性应用到适合的场景中去的能力.数据库与DBA的角色用千里马和伯乐来形容好像也不为过.

小伙伴们一起来玩PG吧,社区正在推Oracle DBA 7天速成PG的教程,敬请等待.