Ganos全空间数据多态分层存储能力解析与最佳实践
P
PostgreSQL码农集散地约 15 分钟
PostgreSQL码农集散地约 15 分钟关于Ganos
Ganos是阿里云数据库产品事业部联合飞天数据库与存储实验室共同研发的新一代云原生位置智能引擎，它将时空数据处理能力融入了阿里云瑶池数据库产品体系中（包括云原生关系型数据库PolarDB-PG、云原生多模数据库Lindorm、云原生数据仓库AnalyticDB-PG和云数据库RDS-PG等）。Ganos目前拥有几何、栅格、轨迹、表面网格、体网格、3D实景、点云、路径、地理网格、快显十大核心引擎，为数据库构建了面向新型物理世界多模多态数据的存储、查询、分析、服务等一体化能力。
本文介绍的全空间数据多态分层存储能力，依托阿里云云原生关系型数据库PolarDB for PostgreSQL产品建设输出。
关于多态分层存储
业务背景
如何在OLTP数据库中让用户拥有更为廉价的数据库存储介质（非外表方式），降低用户成本；
如何保障这类廉价存储的查询计算效率不会有大规模衰减；
如何让客户用更为透明的方式管理与使用多种存储介质；
功能简介
基于多态分层存储功能，用户可以通过简便的SQL操作将过期数据、大对象数据、全空间数据等转存在OSS上，享受OSS带来的弹性、低成本和数据高可靠优势；转存后用户可以完全透明的进行增删改查及表表联合等复杂分析操作，无需做任何SQL改动；另外当数据更新访问频率增加时，可以通过动态调整物化缓存以达到跟数据库云盘同水平的访问性能。
技术优势
成本：支持压缩，平均压缩率50%，部分可达20%，成本降为1/10甚至更低； 易用：数据由冷到热分层存储，冷存后支持增删改查SQL完全透明； 性能：物化缓存层加速冷存数据访问，性能衰减可控制在20%-80%； 可靠：借助OSS的高可靠性，冷数据在不增加存储的前提下支持快照，数据具备恢复还原的能力； 灵活：灵活的冷热分层存储模式，支持按表、按大字段、按子分区分别存储在OSS中。
成本维度
云盘 | 多态分层存储（onOSS） | |
存储计费标准 | PLS4 1.5元/GB/月 | 本地冗余：0.12元/GB/月 同城冗余：0.15元/GB/月 |
压缩率 | 无 | 50% |
最终费用 | 1.5*100 = 150元 |
共计：9元 |
性能维度
易用性维度
可靠性维度
灵活性维度
将整表数据存储在OSS中，索引存储在云盘中，降本后还能有良好的访问性能； 将表中的大字段、辅助性字段独立存储在OSS中，其余字段存储在云盘中； 只将分区表中过期子分区存储在OSS中，热分区存储在云盘中，这是最经典的冷热分离模式。这里面还能衍生出好几种组合，比如冷分区数据与索引都存入OSS中，温分区数据存入OSS但索引保留在云盘中，而热分区全部在云盘，使得查询性能基本无衰减。
基于全空间数据多态分层存储的最佳实践
实践一：分区表过期子分区自动冷存
场景描述：轨迹数据采用分区表存储，并按月进行分区，随着时间的推移，三个月之前的轨迹数据访问频率大大降低（过期），为了降低存储成本，需要数据库自动将超过三个月的分区表进行冷存处理。
操作步骤：
（1）数据准备
--创建分区表CREATE TABLE traj(tr_id serial,tr_lon float,tr_lat float,tr_time timestamp(6))PARTITION BY RANGE (tr_time);CREATE TABLE traj_202301 PARTITION OF trajFOR VALUES FROM ('2023-01-01') TO ('2023-02-01');CREATE TABLE traj_202302 PARTITION OF trajFOR VALUES FROM ('2023-02-01') TO ('2023-03-01');CREATE TABLE traj_202303 PARTITION OF trajFOR VALUES FROM ('2023-03-01') TO ('2023-04-01');CREATE TABLE traj_202304 PARTITION OF trajFOR VALUES FROM ('2023-04-01') TO ('2023-05-01');--往分区表中写入测试数据INSERT INTO traj(tr_lon,tr_lat,tr_time) values(112.35, 37.12, '2023-01-01');INSERT INTO traj(tr_lon,tr_lat,tr_time) values(112.35, 37.12, '2023-02-01');INSERT INTO traj(tr_lon,tr_lat,tr_time) values(112.35, 37.12, '2023-03-01');INSERT INTO traj(tr_lon,tr_lat,tr_time) values(112.35, 37.12, '2023-04-01');--创建分区表索引CREATE INDEX traj_idx on traj(tr_id);
create extension polar_osfs_toolkit;（3）创建pg_cron插件
如果当前database无法创建pg_cron，则需要使用高权限账户连接到 postgres 数据库中创建pg_cron插件，高权限账户可以在PolarDB for PostgreSQL 14控制台界面创建。
注意:只有高权限账户可以创建该插件，并且需要连接到postgres这个数据库中执行创建create extension pg_cron;（4）制定定时执行任务
-- 每分钟执行postgres=> SELECT cron.schedule_in_database('task1', '* * * * *', 'select polar_alter_subpartition_to_oss(''traj'', 3);', 'db01');schedule_in_database----------------------1-- 每天的 10:00am (GMT) 执行postgres=> SELECT cron.schedule_in_database('task2', '0 10 * * *', 'select polar_alter_subpartition_to_oss(''traj'', 3);', 'db01');schedule_in_database----------------------2-- 每个月的 4号 执行postgres=> SELECT cron.schedule_in_database('task3', '* * 4 * *', 'select polar_alter_subpartition_to_oss(''traj'', 3);', 'db01');schedule_in_database----------------------3
（5）查看执行结果及历史执行记录
-- 任务执行的结果就是分区表转存至OSS中,查看分区表的存储位置db01=> \d+ traj_202301Table "public.traj_202301"Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description---------+--------------------------------+-----------+----------+-------------------------------------+---------+-------------+--------------+-------------tr_id | integer | | not null | nextval('traj_tr_id_seq'::regclass) | plain | | |tr_lon | double precision | | | | plain | | |tr_lat | double precision | | | | plain | | |tr_time | timestamp(6) without time zone | | | | plain | | |Partition of: traj FOR VALUES FROM ('2023-01-01 00:00:00') TO ('2023-02-01 00:00:00')Partition constraint: ((tr_time IS NOT NULL) AND (tr_time >= '2023-01-01 00:00:00'::timestamp(6) without time zone) AND (tr_time < '2023-02-01 00:00:00'::timestamp(6) without time zone))Replica Identity: FULLTablespace: "oss" --已经存储在oss了Access method: heap--查看定时任务历史执行记录select * from cron.job_run_details ;
实践二：单表大字段分层存储
CREATE TABLE blob_table(id serial, val text);
（2）设置大字段存储位置
alter table blob_table alter column val set (storage_type='oss');
（3）写入数据并查看存储
--插入数据，此时val字段完全存储在OSS中INSERT INTO blob_table(val) VALUES((SELECT string_agg(random()::text, ':') FROM generate_series(1, 100000)));--查看val字段存储位置WITH tmp as (select 'pg_toast_'||b.oid||'_'||c.attnum as tblname from pg_class b, pg_attribute c where b.relname='blob_table' and c.attrelid=b.oid and c.attname='val') select t.spcname as storage_engine from pg_tablespace t, pg_class r, tmp m where r.relname = m.tblname and t.oid=r.reltablespace;storage_engine----------------oss(1 row)
实践三：时空分析场景如何实现降本增效（高级进阶案例）
场景描述：遥感影像（栅格）数据在空间业务中应用越来越广泛，遥感数据数据体量规模又比较大，且往往存在影像浏览与分析统计等多种业务，针对分析统计类的非实时业务，存储成本的降低且易用性提升往往更具有吸引力，本实践介绍如何用低廉的OSS存储支撑遥感影像入库管理，并提供同样高效的统计分析功能。
LC08_L1TP_114029_20200905_20200917_01_T1.tiff LC08_L1TP_114028_20191005_20191018_01_T1.tiff LC08_L1TP_113029_20191030_20191114_01_T1.tiff LC08_L1TP_113028_20190912_20190917_01_T1.tiff
（2）数据入库
--连接进入PolarDB for PostgresSQL,创建rastdb数据库CREATE DATABASE rastdb;\c rastdb--创建Ganos Raster扩展CREATE EXTENSION ganos_raster CASCADE;--导入影像数据CREATE TABLE raster_table (id integer, rast raster);INSERT INTO raster_table VALUES (1, ST_ImportFrom('rbt','/home/postgres/LC08_L1TP_113028_20190912_20190917_01_T1.TIF'));INSERT INTO raster_table VALUES (2, ST_ImportFrom('rbt','/home/postgres/LC08_L1TP_113029_20191030_20191114_01_T1.TIF'));INSERT INTO raster_table VALUES (3, ST_ImportFrom('rbt','/home/postgres/LC08_L1TP_114028_20191005_20191018_01_T1.TIF'));INSERT INTO raster_table VALUES (4, ST_ImportFrom('rbt','/home/postgres/LC08_L1TP_114029_20200905_20200917_01_T1.TIF'));
（3）统计数据占用的存储空间
CREATE OR REPLACE FUNCTION raster_data_internal_total_size( rast_table_name text, rast_column_name text)RETURNS int8 AS $$DECLAREsql text;sql2 text;rec record;size int8;totalsize int8;tbloid Oid;BEGINsize := 0;totalsize := 0;--查询raster对象的块数据表sql = format('select distinct(st_datalocation(%s)) as tblname from %s where st_rastermode(%s) = ''INTERNAL'';', rast_column_name, rast_table_name, rast_column_name);for rec inexecute sqlloopsql2 = format('select a.oid from pg_class a, pg_tablespace b where a.reltablespace = b.oid and b.spcname=''oss'' and a.relname=''%s'';', rec.tblname);execute sql2 into tbloid;if (tbloid > 0) thensize := 0;else--统计每张数据表的大小sql2 = format('select pg_total_relation_size(''%s'');',rec.tblname);execute sql2 into size;end if;totalsize := (totalsize + size);end loop;return totalsize;END;$$ LANGUAGE plpgsql;
创建完成后，执行统计，结果表示目前影像数据占用的数据库存储空间在1.2GB左右。
select pg_size_pretty(raster_data_internal_total_size('raster_table','rast'));pg_size_pretty----------------1319 MB(1 row)
create table rast_mapalgebra_result(id integer, rast raster);INSERT INTO rast_mapalgebra_result select 1, ST_MapAlgebra(ARRAY(select st_mosaicfrom(ARRAY(SELECT rast FROM raster_table ORDER BY id), 'rbt_mosaic','','{"srid":4326,"cell_size":[0.005,0.005]')), '[{"expr":"([0,3] - [0,2])/([0,3] + [0,2])","nodata": true, "nodataValue":0}]', '{"chunktable":"rbt_algebra","celltype":"32bf"}');INSERT 0 1Time: 39874.189 ms (00:39.874)
CREATE OR REPLACE FUNCTION raster_data_alter_to_oss( rast_table_name text, rast_column_name text)RETURNS VOID AS $$DECLAREsql text;sql2 text;rec record;BEGIN--查询raster对象的数据表sql = format('select distinct(st_datalocation(%s)) as tblname from %s where st_rastermode(%s) = ''INTERNAL'';', rast_column_name, rast_table_name, rast_column_name);for rec inexecute sqlloopsql2 = format('alter table %s set tablespace oss;',rec.tblname);execute sql2;end loop;END;$$ LANGUAGE plpgsql;
创建完存储过程后，执行冷存处理，冷存后再次统计块数据表占用的云盘存储空间：
select raster_data_alter_to_oss('raster_table', 'rast');--统计冷存后块数据在共享盘占用的空间rastdb=# select pg_size_pretty(raster_data_internal_total_size('raster_table','rast'));pg_size_pretty----------------0 bytes(1 row)
INSERT INTO rast_mapalgebra_result select 2, ST_MapAlgebra(ARRAY(select st_mosaicfrom(ARRAY(SELECT rast FROM raster_table ORDER BY id), 'rbt_mosaic','','{"srid":4326,"cell_size":[0.005,0.005]')), '[{"expr":"([0,3] - [0,2])/([0,3] + [0,2])","nodata": true, "nodataValue":0}]', '{"chunktable":"rbt_algebra","celltype":"32bf"}');INSERT 0 1Time: 69414.201 ms (01:09.414)
（7）存储成本及性能表现对比
对比项 | 云盘存储 | OSS冷存 | 对比值 |
存储成本 | 1319MB 按1GB/月，费用为1.2元 | 1011.834MB 按0.13GB/月，费用为0.13元 | 10 : 1 |
NDVI统计耗时 | 39s | 69s | 1 : 1.76 |
对比结果显示，利用OSS冷存，费用只需云盘的1/10，计算性能降低控制在1倍以内，这个性价比对于不追求RT的统计分析场景来说还是非常高的。
总结
目前，Ganos已经发展到了v6.0版本，支撑了数十个行业领域的数千个应用场景，稳定、成本、性能与易用性一直Ganos长期坚持的目标，全空间多态存储能力是Ganos在PolarDB-PG数据库上打造的内核级核心竞争力，它为全空间数据管理提供了真正兼顾成本、性能与易用性的方案，欢迎各位用户开通体验。