数据库筑基课 - Cluster 表
digoal约 10 分钟文中参考文档可点击阅读原文打开, 推荐《最好的PostgreSQL学习镜像》
数据库筑基课 - Cluster 表
往期
本节: Cluster 表
简介
cluster是针对heap表重新排序存储的行为.
从《数据库筑基课 - 行存之 heap 表组织结构》 我们知道了heap表是无序存储的, 数据插入heap表时根据fsm找到空闲空间满足行大小的位置插入. 更新时tuple新版本也可能存储到和old tuple不同的block内(非HOT的更新情况).
无序存储的好处是插入快, 因为不用像有序的索引那样选择固定的目标page、分裂Page、修改page的link信息等, 当快速并发插入排序字段value相近的数据时也不会因为热点page影响性能.
存储在heap表里的记录可以根据“数据块ID+块内line point”进行寻址.
cluster 就是将数据根据某个索引值的顺序重新排列并存入heap blocks中.
优势 & 适合场景
当按cluster 索引检索一个区间的数据时, 回表访问的block由于挨着所以IO是连续的, 可以减少IO.
特别是需要访问很多条记录, 并且这些记录散落在很多个数据块里面时, cluster 后的IO量会迅速降低.
例如, 共享单车的轨迹查询场景、时序数据按某个传感器进行时间范围聚合查询.
假设某个用户早上上班使用了共享单车, 这条骑行轨迹一共花费30分钟. 如果每5秒采1个位置点, 这条轨迹一共有360个点. 又由于上班高峰期有很多用户都在使用共享单车, 所以同一时刻可能很多用户都在上报位置, 假设一个heap block可以存放300条记录, 只要同一时刻使用共享单车等用户超过300人, 那么这个用户的360个点将会分布存放到360 个不同的heap block内. 因此即使有userid,traceid索引, 在查询这条轨迹的记录时, 回表访问其他字段的值或回表判断记录可见性, 就需要访问大量heap block(本例360个), 导致IO瓶颈. 按 userid,traceid 索引 cluster 后, 这些数据将重排, 仅需访问少量数据块, 减少IO.
下面这些例子给出了使用cluster或include index的优化建议:
《PostgreSQL index include - 类聚簇表与应用(append only, IoT时空轨迹, 离散多行扫描与返回)》 《PostgreSQL IoT，车联网 - 实时轨迹、行程实践 2 - (含index only scan类聚簇表效果)》 《重新发现PostgreSQL之美 - 8 轨迹业务IO杀手克星index include(覆盖索引)》
cluster 比较适合相对静态的数据, 例如历史轨迹的整理. 对这类数据在查询较多条有序字段范围内的数据时可以减少IO.
劣势 & 不适合场景
cluster 是一次性操作, 如果数据发生更新, 新的tuple可能会插入到其他heap block, 打乱原来的顺序. 新insert的记录也不会插入到你想要的顺序中.
所以cluster 不适合更新/插入频繁的表. 更新可以通过设置fillfactor让new tuple插入当前block, 但是insert无法解决.
DEMO
测试环境:
《2023-PostgreSQL Docker镜像学习环境 ARM64版, 已集成热门插件和工具》 《2023-PostgreSQL Docker镜像学习环境 AMD64版, 已集成热门插件和工具》
1、创建测试表
create table tbl (uid int, tid int, pos point, ts timestamp);
2、创建测试脚本
vi t.sql \set uid random(1,10000)
insert into tbl values (:uid, :uid+100, point(random(),random()), now());
3、插入80万条记录, 1万个uid, 平均每个uid 80个点.
pgbench -M prepared -n -r -P 1 -c 8 -j 8 -t 100000 -f ./t.sql
4、创建索引
postgres=# create index on tbl (uid,tid);
CREATE INDEX
5、按uid的某个轨迹tid进行查询, 并按时间排序输出
ctid括号里 第一个数是blockNum, 第二个数是linepoint(itemID).postgres=# select ctid,* from tbl where uid=1 order by ts;
ctid | uid | tid | pos | ts
------------+-----+-----+--------------------------------------------+----------------------------
(121,88) | 1 | 101 | (0.5619530151361722,0.5270297107822159) | 2024-10-24 09:35:29.357972
(130,77) | 1 | 101 | (0.05599683987518844,0.108510842157024) | 2024-10-24 09:35:29.363107
(168,106) | 1 | 101 | (0.557264542555238,0.881285135929506) | 2024-10-24 09:35:29.392454
(230,92) | 1 | 101 | (0.48928254301235796,0.32202576042525166) | 2024-10-24 09:35:29.440769
(227,125) | 1 | 101 | (0.016633306806106418,0.937955483978957) | 2024-10-24 09:35:29.441447
(290,59) | 1 | 101 | (0.3771216050092825,0.3338838803017268) | 2024-10-24 09:35:29.488756
(292,106) | 1 | 101 | (0.35063377662243767,0.5896065113781965) | 2024-10-24 09:35:29.491932
(315,1) | 1 | 101 | (0.14403442273194855,0.9266434002367525) | 2024-10-24 09:35:29.503566
(362,22) | 1 | 101 | (0.3144093096321683,0.9854259908258989) | 2024-10-24 09:35:29.542974
(512,128) | 1 | 101 | (0.21966701464293692,0.9558864929129349) | 2024-10-24 09:35:29.658858
(895,87) | 1 | 101 | (0.34818196646939725,0.15796744760222836) | 2024-10-24 09:35:29.945836
...
(4938,134) | 1 | 101 | (0.2463181885734862,0.8899417371252838) | 2024-10-24 09:35:33.01858
(5148,43) | 1 | 101 | (0.13513542203937234,0.5060178389204424) | 2024-10-24 09:35:33.175524
(5214,86) | 1 | 101 | (0.7567999896231541,0.7344274220742371) | 2024-10-24 09:35:33.227458
(5272,24) | 1 | 101 | (0.20975230683594503,0.23745633666365862) | 2024-10-24 09:35:33.270185
(5287,108) | 1 | 101 | (0.18436865982296524,0.7421268020501053) | 2024-10-24 09:35:33.281498
(5394,124) | 1 | 101 | (0.18477511442127081,0.3850246383474847) | 2024-10-24 09:35:33.365606
(5407,95) | 1 | 101 | (0.7979966382557677,0.7081895483505996) | 2024-10-24 09:35:33.372838
(5551,2) | 1 | 101 | (0.3789424455544861,0.7293704166008403) | 2024-10-24 09:35:33.482185
(5591,88) | 1 | 101 | (0.5427973323183011,0.8098804271726259) | 2024-10-24 09:35:33.516582
(5756,28) | 1 | 101 | (0.02173903814971112,0.19296336964166372) | 2024-10-24 09:35:33.634113
(5871,65) | 1 | 101 | (0.5074534105671802,0.8054008473074497) | 2024-10-24 09:35:33.743415
(79 rows)
加上索引本身的page, 一共需要访问90个block
postgres=# explain (analyze,verbose,timing,costs,buffers) select ctid,* from tbl where uid=1 order by ts;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=92.29..92.49 rows=79 width=38) (actual time=0.260..0.266 rows=87 loops=1)
Output: ctid, uid, tid, pos, ts
Sort Key: tbl.ts
Sort Method: quicksort Memory: 31kB
Buffers: shared hit=90
-> Index Scan using tbl_uid_tid_idx on public.tbl (cost=0.42..89.80 rows=79 width=38) (actual time=0.038..0.232 rows=87 loops=1)
Output: ctid, uid, tid, pos, ts
Index Cond: (tbl.uid = 1)
Buffers: shared hit=90 -- 需要访问90个block
Planning Time: 0.155 ms
Execution Time: 0.310 ms
(11 rows)
6、使用索引cluster这个表
postgres=# cluster tbl using tbl_uid_tid_idx ;
CLUSTER
7、现在加上索引本身的page, 一共仅需访问4个block
postgres=# select ctid,* from tbl where uid=1 order by ts;
ctid | uid | tid | pos | ts
--------+-----+-----+--------------------------------------------+----------------------------
(0,1) | 1 | 101 | (0.5619530151361722,0.5270297107822159) | 2024-10-24 09:35:29.357972
(0,2) | 1 | 101 | (0.05599683987518844,0.108510842157024) | 2024-10-24 09:35:29.363107
(0,3) | 1 | 101 | (0.557264542555238,0.881285135929506) | 2024-10-24 09:35:29.392454
(0,5) | 1 | 101 | (0.48928254301235796,0.32202576042525166) | 2024-10-24 09:35:29.440769
(0,4) | 1 | 101 | (0.016633306806106418,0.937955483978957) | 2024-10-24 09:35:29.441447
(0,6) | 1 | 101 | (0.3771216050092825,0.3338838803017268) | 2024-10-24 09:35:29.488756
(0,7) | 1 | 101 | (0.35063377662243767,0.5896065113781965) | 2024-10-24 09:35:29.491932
...
(0,71) | 1 | 101 | (0.7567999896231541,0.7344274220742371) | 2024-10-24 09:35:33.227458
(0,72) | 1 | 101 | (0.20975230683594503,0.23745633666365862) | 2024-10-24 09:35:33.270185
(0,73) | 1 | 101 | (0.18436865982296524,0.7421268020501053) | 2024-10-24 09:35:33.281498
(0,74) | 1 | 101 | (0.18477511442127081,0.3850246383474847) | 2024-10-24 09:35:33.365606
(0,75) | 1 | 101 | (0.7979966382557677,0.7081895483505996) | 2024-10-24 09:35:33.372838
(0,76) | 1 | 101 | (0.3789424455544861,0.7293704166008403) | 2024-10-24 09:35:33.482185
(0,77) | 1 | 101 | (0.5427973323183011,0.8098804271726259) | 2024-10-24 09:35:33.516582
(0,78) | 1 | 101 | (0.02173903814971112,0.19296336964166372) | 2024-10-24 09:35:33.634113
(0,79) | 1 | 101 | (0.5074534105671802,0.8054008473074497) | 2024-10-24 09:35:33.743415
(79 rows) postgres=# explain (analyze,verbose,timing,costs,buffers) select ctid,* from tbl where uid=1 order by ts;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=92.29..92.49 rows=79 width=38) (actual time=0.135..0.147 rows=87 loops=1)
Output: ctid, uid, tid, pos, ts
Sort Key: tbl.ts
Sort Method: quicksort Memory: 31kB
Buffers: shared hit=4
-> Index Scan using tbl_uid_tid_idx on public.tbl (cost=0.42..89.80 rows=79 width=38) (actual time=0.043..0.085 rows=87 loops=1)
Output: ctid, uid, tid, pos, ts
Index Cond: (tbl.uid = 1)
Buffers: shared hit=4 -- 需要访问4个block
Planning Time: 0.179 ms
Execution Time: 0.203 ms
(11 rows)
扩展问题
1、如何维持频繁变更/插入的表记录存储的永久顺序?
2、如果能维持永久顺序, 如何保证地下的block物理连续? 因为index page实际上是link起来的逻辑有序, 物理page实际并不连续, 只能起到聚集存储作用.
3、业务上的改进: 同一个轨迹的记录采用批量插入, 确保写入密集的数据块内.
4、将一个轨迹的记录存储到大字段内, 例如 array, jsonb.
扩展阅读
hbase 的数据存储结构
索引组织表
今日荐书
彩蛋:国产数据库周边生态
当然一款数据库要流行起来, 除了自己要强大, 还离不开生态. 用好周边生态工具, 管理水平战胜90%老司机!!! 下面简单介绍一下国产数据库周边生态.
1、管控软件
鸣嵩(前阿里云数据库总经理 / 研究员)等大佬们创业创办的云猿生, 核心产品是KubeBlocks. 他们的理念是让管理数据库和搭积木一样简单, 如果你要管理很多套并且种类(OLTP\OLAP\NoSQL\KV\TS\MQ等)很多的数据库产品, 推荐首选.
https://github.com/apecloud/kubeblocks
PG中文社区核心委员唐成老师的公司乘数开源的Clup, 专用管理PostgreSQL和PolarDB的集群管理软件, 如果你要管理很多套数据库, 推荐选择. 并且Clup还提供了企业版、自研的连接池、分布式存储、一体机、备份平台等, 是企业用户推荐之选.
https://www.csudata.com/
若航开源的pigsty, 集成了300多个PG插件的PG集群和PolarDB集群管理软件, 如果你要管理很多套PG或PolarDB数据库, 且对插件有特别多的需求, 推荐选择.
https://pigsty.cc/zh/
2、审计监控诊断优化
翟总(曾经是我背后的男人)到海信聚好看后研发的 DBdoctor, 采用ebpf技术, 在对数据库几乎没有影响的情况下实时监控数据库和服务器的各项指标, 发现和诊断问题根因非常方便.
https://www.dbdoctor.cn/
天舟老哥的核心产品Bytebase 是位于您和数据库之间的中间件。它是数据库 DevOps 的 GitLab/GitHub，专为开发人员、DBA 和平台工程师打造。
https://bytebase.cc/docs/introduction/what-is-bytebase/
PawSQL, SQL优化和诊断产品.
D-Smart, Oracle老前辈白老大出品, 专注企业级市场, 将业界顶级DBA经验的产品化作品, 产品功能包括数据库监控、诊断、优化等.
https://www.modb.pro/db/567140
3、国产数据库IDE
IDE是开发者的必备工具，例如社区有pgAdmin, 国产IDE则可以看看老程序猿达刚老师的DeskUI:
https://www.deskui.com
4、数据同步&迁移&备份恢复
NineData, 老领导出去创业做的产品, 产品涵盖了数据同步、迁移、备份、比对、devops、chatDBA等.
https://www.ninedata.cloud/home
DSG, 非常老牌的数据库同步迁移企业级产品, 支持各种数据库的异构和同构迁移, 用他们的话说, 没有dsg搞不定的迁移, 比goldengate还牛.
https://www.dsgdata.com/
公开课
如果你对PolarDB学习感兴趣可以阅读这个公开课系列:
除了PolarDB还非常值得关注的几款PG栈国产数据库:
HaloDB(基于PG兼容PostgreSQL、Oracle、MySQL. http://www.halodbtech.com/ )、 IvorySQL(基于开源PG兼容PG、Oracle. https://www.ivorysql.org/zh-cn/ )、 ProtonBase(云原生分布式数仓. https://protonbase.com/ )、 成都文武数据库（https://ww-it.cn）
参考文档点击阅读原文获得
感谢关注我的github (https://github.com/digoal/blog) 及视频号: