digoal
2022-12-21
PostgreSQL , PolarDB , brin , 时序 , 块级别索引 , 相关性 , 泾渭分明 , 堆存储 , append only
PolarDB 的云原生存算分离架构, 具备低廉的数据存储、高效扩展弹性、高速多机并行计算能力、高速数据搜索和处理; PolarDB与计算算法结合, 将实现双剑合璧, 推动业务数据的
价值产出, 将数据变成生产力.
本文将介绍PolarDB 开源版通过 brin 实现千分之一的存储空间, 高效率检索时序数据
测试环境为macos+docker, polardb部署请参考:
PolarDB 的普通表采用堆存储, 最小分配单位为block, 不够了就在文件末尾追加block.
所以根据时序数据的append only 、 时间字段递增特征. 一个block内的时间字段的值基本上是相邻的, 相邻的block时间值也相邻.
时序数据通常是按片搜索, 例如分钟、小时、天等粒度的片搜索和统计.
怎样高效、低成本的检索时序数据? PolarDB BRIN 块级别范围索引, 千分之一的存储, 实现btree同级别的片区搜索性能.
brin为什么省存储呢? 因为一片blocks, 只存储其索引字段的min,max,nullif的统计值. 所以非常节省空间.
1、建立时序表
create table tbl (id int, v1 int, v2 int, crt_time timestamp(0));
2、写入500万条时序数据
insert into tbl select id, random()*10, random()*100, now()+(id||'second')::interval from generate_series(1,5000000) id;
3、查询时序字段的边界值
postgres=# select min(crt_time), max(crt_time) from tbl;
min | max
---------------------+---------------------
2022-12-21 08:47:19 | 2023-02-17 05:40:38
(1 row)
4、普通btree索引的测试, 占用空间等.
create index on tbl using btree (crt_time);
postgres=# \dt+
List of relations
Schema | Name | Type | Owner | Size | Description
--------+------+-------+----------+--------+-------------
public | tbl | table | postgres | 249 MB |
(1 row)
postgres=# \di+
List of relations
Schema | Name | Type | Owner | Table | Size | Description
--------+------------------+-------+----------+-------+--------+-------------
public | tbl_crt_time_idx | index | postgres | tbl | 107 MB |
(1 row)
postgres=# explain (analyze,verbose,timing,costs,buffers) select count(*) from tbl where crt_time between '2022-12-30' and '2022-12-31';
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=32345.17..32345.18 rows=1 width=8) (actual time=19.311..19.317 rows=1 loops=1)
Output: count(*)
Buffers: shared hit=790
-> Bitmap Heap Scan on public.tbl (cost=532.68..32282.67 rows=25000 width=0) (actual time=5.786..13.871 rows=86401 loops=1)
Recheck Cond: ((tbl.crt_time >= '2022-12-30 00:00:00'::timestamp without time zone) AND (tbl.crt_time <= '2022-12-31 00:00:00'::timestamp without time zone))
Heap Blocks: exact=551
Buffers: shared hit=790
-> Bitmap Index Scan on tbl_crt_time_idx (cost=0.00..526.43 rows=25000 width=0) (actual time=5.723..5.724 rows=86401 loops=1)
Index Cond: ((tbl.crt_time >= '2022-12-30 00:00:00'::timestamp without time zone) AND (tbl.crt_time <= '2022-12-31 00:00:00'::timestamp without time zone))
Buffers: shared hit=239
Planning Time: 0.081 ms
Execution Time: 19.550 ms
(12 rows)
5、判断时间字段是否适合brin索引: 相关性为1, 表明这个字段有自增属性、而且边界清晰. 非常适合brin索引.
相关性的范围是-1到1, 越接近1或者-1都适合brin.
postgres=# select correlation from pg_stats where tablename='tbl' and attname='crt_time';
correlation
-------------
1
(1 row)
6、测试brin索引, 观察其占用空间, 查询性能.
drop index tbl_crt_time_idx;
create index on tbl using brin (crt_time);
postgres=# explain (analyze,verbose,timing,costs,buffers) select count(*) from tbl where crt_time between '2022-12-30' and '2022-12-31';
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=33599.60..33599.61 rows=1 width=8) (actual time=26.022..26.025 rows=1 loops=1)
Output: count(*)
Buffers: shared hit=642
-> Bitmap Heap Scan on public.tbl (cost=33.38..33387.41 rows=84878 width=0) (actual time=0.937..18.871 rows=86401 loops=1)
Recheck Cond: ((tbl.crt_time >= '2022-12-30 00:00:00'::timestamp without time zone) AND (tbl.crt_time <= '2022-12-31 00:00:00'::timestamp without time zone))
Rows Removed by Index Recheck: 14079
Heap Blocks: lossy=640
Buffers: shared hit=642
-> Bitmap Index Scan on tbl_crt_time_idx (cost=0.00..12.16 rows=100402 width=0) (actual time=0.420..0.421 rows=6400 loops=1)
Index Cond: ((tbl.crt_time >= '2022-12-30 00:00:00'::timestamp without time zone) AND (tbl.crt_time <= '2022-12-31 00:00:00'::timestamp without time zone))
Buffers: shared hit=2
Planning Time: 0.168 ms
Execution Time: 26.162 ms
(13 rows)
postgres=# \di+
List of relations
Schema | Name | Type | Owner | Table | Size | Description
--------+------------------+-------+----------+-------+-------+-------------
public | tbl_crt_time_idx | index | postgres | tbl | 48 kB |
(1 row)
结论符合预期:
- brin占用空间只有btree的2000分之一大小, 但是在进行范围条件搜索时, brin索引性能相当于btree, 扫描更少的数据块得到同级别的性能.
- 《重新发现PostgreSQL之美 - 13 brin 时序索引》
- 《PostGIS空间索引(GiST、BRIN、R-Tree)选择、优化 - 2》
- 《PostgreSQL 14 preview - BRIN (典型IoT 时序场景) 块级索引支持 bloom filter - 随机,大量distinct value, 等值查询》
- 《PostgreSQL 14 preview - BRIN (典型IoT 时序场景) 块级索引支持 multi-range min-max [s] - 分段范围索引》
- 《PostgreSQL 14 preview - brin 索引内存优化》
- 《PostgreSQL 11 preview - BRIN索引接口功能扩展(BLOOM FILTER、min max分段)》
- 《HTAP数据库 PostgreSQL 场景与性能测试之 24 - (OLTP) 物联网 - 时序数据并发写入(含时序索引BRIN)》
- 《PostgreSQL BRIN索引的pages_per_range选项优化与内核代码优化思考》
- 《万亿级电商广告 - brin黑科技带你(最低成本)玩转毫秒级圈人(视觉挖掘姊妹篇) - 阿里云RDS PostgreSQL, HybridDB for PostgreSQL最佳实践》
- 《PostGIS空间索引(GiST、BRIN、R-Tree)选择、优化 - 阿里云RDS PostgreSQL最佳实践》
- 《自动选择正确索引访问接口(btree,hash,gin,gist,sp-gist,brin,bitmap...)的方法》
- 《PostgreSQL 并行写入堆表，如何保证时序线性存储 - BRIN索引优化》
- 《PostgreSQL 10.0 preview 功能增强 - BRIN 索引更新smooth化》
- 《PostgreSQL 聚集存储 与 BRIN索引 - 高并发行为、轨迹类大吞吐数据查询场景解说》
- 《PostgreSQL 物联网黑科技 - 瘦身几百倍的索引(BRIN index)》
- 《PostgreSQL 9.5 new feature - lets BRIN be used with R-Tree-like indexing strategies For "inclusion" opclasses》
- 《PostgreSQL 9.5 new feature - BRIN (block range index) index》
