PostgreSQL 物联网黑科技 - 瘦身500倍的索引(范围索引)

在数据库中用得最多的当属btree索引，除了BTREE，一般的数据库可能还支持hash, bitmap索引。
但是这些索引到了物联网，会显得太重，对性能的损耗太大。
为什么呢？
物联网有大量的数据产生和入库，入库基本都是流式的。在使用这些数据时，基本是FIFO，或者范围查询的批量数据使用风格。
btree索引太重，因为索引需要存储每条记录的索引字段的值和寻址，使得索引非常庞大。
另一方面，物联网的大量范围查询和批量处理用法决定了它不需要这么重的索引。
例子：
如下所示，btree索引的空间占比是非常大的。

postgres=# \dt+ tab
                    List of relations
 Schema | Name | Type  |  Owner   |  Size   | Description
--------+------+-------+----------+---------+-------------
 public | tab  | table | postgres | 3438 MB |
(1 row)

postgres=# \di+ idx_tab_id
                           List of relations
 Schema |    Name    | Type  |  Owner   | Table |  Size   | Description
--------+------------+-------+----------+-------+---------+-------------
 public | idx_tab_id | index | postgres | tab   | 2125 MB |
(1 row)

除了大以外，btree索引同时也会影响数据的更新，删除，或插入的性能。
例子：
有btree索引, 每秒入库28.45万行

postgres=# create unlogged table tab(id serial8, info text, crt_time timestamp);
CREATE TABLE
postgres=# create index idx_tab_id on tab(id);
CREATE INDEX
vi test.sql
insert into tab (info) select '' from generate_series(1,10000);

pgbench -M prepared -n -r -P 1 -f ./test.sql -c 48 -j 48 -T 100
tps = 28.453983 (excluding connections establishing)

无索引, 每秒入库66.88万行

postgres=# drop index idx_tab_id ;
DROP INDEX

pgbench -M prepared -n -r -P 1 -f ./test.sql -c 48 -j 48 -T 100
tps = 66.880260 (excluding connections establishing)

从上面的介绍和测试数据，可以明显的看出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万行

postgres=# drop index idx_tab_id ;
DROP INDEX
postgres=# create index idx_tab_id on tab using brin (id) with (pages_per_range=1);
CREATE INDEX

pgbench -M prepared -n -r -P 1 -f ./test.sql -c 48 -j 48 -T 100
tps = 62.838701 (excluding connections establishing)

最后还需要对比一下 btree, brin 索引的大小，还有查询的性能。
索引大小比拼：
表 4163MB
btree索引 2491 MB
brin索引 4608 kB

postgres=# \di+ idx_tab_btree_id
                              List of relations
 Schema |       Name       | Type  |  Owner   | Table |  Size   | Description
--------+------------------+-------+----------+-------+---------+-------------
 public | idx_tab_btree_id | index | postgres | tab   | 2491 MB |
(1 row)

postgres=# \di+ idx_tab_id
                           List of relations
 Schema |    Name    | Type  |  Owner   | Table |  Size   | Description
--------+------------+-------+----------+-------+---------+-------------
 public | idx_tab_id | index | postgres | tab   | 4608 kB |
(1 row)

postgres=# \dt+ tab
                    List of relations
 Schema | Name | Type  |  Owner   |  Size   | Description
--------+------+-------+----------+---------+-------------
 public | tab  | table | postgres | 4163 MB |
(1 row)

查询性能比拼 :
范围查询
全表扫描 11 秒
范围索引 64 毫秒
btree索引 24 毫秒

postgres=# /+ seqscan(tab) / explain (analyze,buffers,timing,costs,verbose) select count(*) from tab where id between 1 and 100000;
                                                           QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=1891578.12..1891578.13 rows=1 width=0) (actual time=11353.057..11353.058 rows=1 loops=1)
   Output: count(*)
   Buffers: shared hit=133202
   ->  Seq Scan on public.tab  (cost=0.00..1891352.00 rows=90447 width=0) (actual time=1660.445..11345.123 rows=100000 loops=1)
         Output: id, info, crt_time
         Filter: ((tab.id >= 1) AND (tab.id <= 100000))
         Rows Removed by Filter: 117110000
         Buffers: shared hit=133202
 Planning time: 0.048 ms
 Execution time: 11353.080 ms
(10 rows)

postgres=# /+ bitmapscan(tab idx_tab_id) / explain (analyze,buffers,timing,costs,verbose) select count(*) from tab where id between 1 and 100000;
                                                             QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=70172.91..70172.92 rows=1 width=0) (actual time=63.735..63.735 rows=1 loops=1)
   Output: count(*)
   Buffers: shared hit=298
   ->  Bitmap Heap Scan on public.tab  (cost=1067.08..69946.79 rows=90447 width=0) (actual time=40.700..55.868 rows=100000 loops=1)
         Output: id, info, crt_time
         Recheck Cond: ((tab.id >= 1) AND (tab.id <= 100000))
         Rows Removed by Index Recheck: 893
         Heap Blocks: lossy=111
         Buffers: shared hit=298
         ->  Bitmap Index Scan on idx_tab_id  (cost=0.00..1044.47 rows=90447 width=0) (actual time=40.675..40.675 rows=1110 loops=1)
               Index Cond: ((tab.id >= 1) AND (tab.id <= 100000))
               Buffers: shared hit=187
 Planning time: 0.049 ms
 Execution time: 63.755 ms
(14 rows)

postgres=# /+ bitmapscan(tab idx_tab_btree_id) / explain (analyze,buffers,timing,costs,verbose) select count(*) from tab where id between 1 and 100000;
                                                                 QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=76817.88..76817.89 rows=1 width=0) (actual time=23.780..23.780 rows=1 loops=1)
   Output: count(*)
   Buffers: shared hit=181
   ->  Bitmap Heap Scan on public.tab  (cost=1118.87..76562.16 rows=102286 width=0) (actual time=6.569..15.950 rows=100000 loops=1)
         Output: id, info, crt_time
         Recheck Cond: ((tab.id >= 1) AND (tab.id <= 100000))
         Heap Blocks: exact=111
         Buffers: shared hit=181
         ->  Bitmap Index Scan on idx_tab_btree_id  (cost=0.00..1093.30 rows=102286 width=0) (actual time=6.530..6.530 rows=100000 loops=1)
               Index Cond: ((tab.id >= 1) AND (tab.id <= 100000))
               Buffers: shared hit=70
 Planning time: 0.099 ms
 Execution time: 23.798 ms
(13 rows)

精确查询
全表扫描 8 秒
范围索引 39 毫秒
btree索引 0.03 毫秒

postgres=# /+ seqscan(tab) / explain (analyze,buffers,timing,costs,verbose) select count(*) from tab where id=100000;
                                                      QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=1598327.00..1598327.01 rows=1 width=0) (actual time=8297.589..8297.589 rows=1 loops=1)
   Output: count(*)
   Buffers: shared hit=133202
   ->  Seq Scan on public.tab  (cost=0.00..1598327.00 rows=2 width=0) (actual time=1221.359..8297.582 rows=1 loops=1)
         Output: id, info, crt_time
         Filter: (tab.id = 100000)
         Rows Removed by Filter: 117209999
         Buffers: shared hit=133202
 Planning time: 0.113 ms
 Execution time: 8297.619 ms
(10 rows)

postgres=# /+ bitmapscan(tab idx_tab_id) / explain (analyze,buffers,timing,costs,verbose) select count(*) from tab where id=100000;
                                                          QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=142.04..142.05 rows=1 width=0) (actual time=38.498..38.498 rows=1 loops=1)
   Output: count(*)
   Buffers: shared hit=189
   ->  Bitmap Heap Scan on public.tab  (cost=140.01..142.04 rows=2 width=0) (actual time=38.432..38.495 rows=1 loops=1)
         Output: id, info, crt_time
         Recheck Cond: (tab.id = 100000)
         Rows Removed by Index Recheck: 1811
         Heap Blocks: lossy=2
         Buffers: shared hit=189
         ->  Bitmap Index Scan on idx_tab_id  (cost=0.00..140.01 rows=2 width=0) (actual time=38.321..38.321 rows=20 loops=1)
               Index Cond: (tab.id = 100000)
               Buffers: shared hit=187
 Planning time: 0.102 ms
 Execution time: 38.531 ms
(14 rows)

postgres=# /+ indexscan(tab idx_tab_btree_id) / explain (analyze,buffers,timing,costs,verbose) select count(*) from tab where id=100000;
                                                            QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=2.76..2.77 rows=1 width=0) (actual time=0.018..0.018 rows=1 loops=1)
   Output: count(*)
   Buffers: shared hit=4
   ->  Index Scan using idx_tab_btree_id on public.tab  (cost=0.44..2.76 rows=2 width=0) (actual time=0.015..0.016 rows=1 loops=1)
         Output: id, info, crt_time
         Index Cond: (tab.id = 100000)
         Buffers: shared hit=4
 Planning time: 0.049 ms
 Execution time: 0.036 ms
(9 rows)

对比图 :



小结：
.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的教程，敬请期待。

时间： 2024-10-26 20:27:44

PostgreSQL 物联网黑科技 - 瘦身500倍的索引(范围索引)的相关文章

PostgreSQL 物联网黑科技 - 阅后即焚

在物联网应用场景中,有大量的传感器,会产生非常大量的消息以极高的并发进入数据库.这些数据如果直接进入面向OLAP场景设计的数据仓库,数据实时入库会成为瓶颈,并且OLAP系统很难接受非常高并发的请求.面对这样的应用场景,这些既要又要还要怎么满足呢?.1. 既要实时入库,.2. 又要实时分析,.3. 还要历史留档,应对随时变化的分析需求. 实时入库比较容易满足,我前些天写过一篇 "PostgreSQL 如何潇洒的处理每天上百TB的数据增量"https://yq.aliyun.com/art

物联网黑科技：不耗电的新wifi技术

国外媒体报道,美国华盛顿大学电子工程学院的学生们日前研发出了一种全新的WiFi技术,其最大特点是能耗不到当前WiFi的万分之一. Gartner去年公布的新兴技术周期图显示,2016年IoT成为最被期待的新兴技术,与之相关的IOT平台同样受到强烈的关注,未来5到10年IoT技术将趋于成熟.到2020年联网设备的总数将达到甚至超过500亿,物联网将把家庭中的很多设备囊括进来,其中小到智能恒温器,可穿戴设备名大到智能电冰箱-- 物联网黑科技:不耗电的新wifi技术 而电池有时候是移动设备与传感器的祸

分析加速引擎黑科技 - LLVM、列存、多核并行、算子复用 大联姻 - 一起来开启PostgreSQL的百宝箱

标签 PostgreSQL , LLVM , OLAP , 列存储 , IMCS , cstore , column storage , Code Gen , 数据分析 背景 随着移动互联网的发展,数据爆炸性增长,企业对数据产生价值的渴望越来越多. 比如很多APP中会埋点(已经不是什么秘密),以此收集用户的行为数据,用户的位置变化,上了什么网站,浏览了哪些网页,和什么人聊天. 又比如汽车类的应用,汽车的轨迹,油耗,停留的时间,最喜欢去的商圈等. 金融方面,用户的付费行为,和哪个商铺发生的交易,交

带宽翻倍 信号出众，飞鱼星F1携黑科技震撼上市

"您好,我们很抱歉的通知您,由于您乘坐的这趟航班超售,先到的旅客已经把座位分配完毕,无法给您提供座位--" 正当你准备怒发冲冠,和航空公司工作人员一番理论,争取自己正当权益的时候,你听到了恐怕是人世间最美好的"不过"二字. "不过,我们的头等舱没有坐满,我们免费将您升舱,您看可以吗?" 幸福来得太突然,懵了的你连连点头,就差没当众跳起来,脑子里在努力回忆今天出门的时候有没有踩到了狗屎-- 毋庸置疑,免费升舱这种事是每个经济舱乘客都希望自己能碰上的

PostgreSQL黑科技大集会

清晨放毒,让大家看看那些基于PostgreSQL让人咋舌的奇特功能.   1.异地多主节点,异步数据表复制     多数据中心多写多活不是梦,当然这是异步的,需要我们自己处理数据冲突时的处理流程.   2.基于MPP架构的OLAP数据仓库解决方案Greenplum     你想要50台还是100台服务器来做SQL分析? 北京的朋友,Greenplum今天在北京开技术沙龙,PostgreSQL大拿们全都在北京,你们知道吗?   3.pg_shard或基于FDW的数据分片技术     想想,你的一个

赵薇黑超遮面被路人指点小腹凸起瘦身未成功

路人对赵薇指指点点很好奇 近日,赵薇低调现身上海,刚当上妈妈一个月的她身材的恢复速度显然不及同样刚刚产子的张柏芝.身着一身黑色休闲服加牛仔裤的赵薇小腹仍然有些凸起,身材与未怀孕前还是分别明显.一路上,赵薇始终黑超掩面,与身边的助理零交流,生怕有人把她认出.不过,还是有路人认出了她,并且对其指点,与朋友一同评价这位明星妈妈.[page] 赵薇黑超遮面[page] 赵薇黑超遮面[page] 赵薇黑超遮面[page] 产后赵薇知性成熟[page] 产后赵薇知性成熟[page] 产后赵薇知性成熟[pag

通信黑科技：窄带物联网NB- IoT激活物联网

最近两个月来,通信板块的新技术正频频"引爆"市场.不仅窄带物联网NB- IoT激活了物联网板块,量子通信板块也呈现逆势拉升态势. 据记者了解,资金下一步或将盯上智能网联汽车LTE-V.在业内人士看来,顺应社会发展趋势.受到政策强力推进,并且有众多业内巨头重金参与的智能驾驶领域,有望在LTE-V成为中国技术路线标准后被激活,也吸引机构强力布局车联网领域. 通信黑科技频繁"引爆"市场 6月份以来,通信领域的"黑科技"开始受到市场追捧,新技术标准或者创

Gartner预测2017年十大黑科技 人工智能、物联网列其中

Gartner公司最近公布了2017年具备战略意义的重大科技发展趋势.这十大技术趋势可以总结概括为:以智能为中心,通过数字化实现万物互联.Gartner预计,这些趋势在未来五年内迅速增长.高度波动.预计达到临界点. Gartner副总裁兼研究员David Cearley说,"这十大趋势中,前三个趋势体现了'智能无处不在',以将智能物理和基于软件的系统应用于机器学习和自适应.接下来的三个趋势聚焦数字世界,物理和数字世界日益纠缠.而最后四个趋势则着眼于智能数字网络所需的平台和服务网络."

PostgreSQL 9.6 黑科技 bloom 算法索引，一个索引支撑任意列组合查询

PostgreSQL 确实是学术界和工业界的璀璨明珠,它总是喜欢将学术界的一些玩意工业化,这次的bloom又是一个代表.在PG很多的地方都能看到学术的影子,比如pgbench支持产生泊松分布,高斯分布的随机值.bloom filter是一个有损过滤器,使用有限的比特位存储一些唯一值集合所产生的bits.通过这些bits可以满足这样的场景需求,给定一个值,判断这个值是否属于这个集合.例如 create table test(c1 int); insert into test select trun