PolarDB:GanosBase のフルスペースデータ多態 (ホット/コールド) 階層化ストレージの分析とベストプラクティス

ストレージコストの比較

以下の表は、100 GB のデータに対する PolarDB ディスク (PSL4) と OSS の比較です：

OSS のストレージコストは、ディスクストレージコストの 10% 未満です。

サポートされるストレージの組み合わせ

多態階層化ストレージは、いくつかのストレージの組み合わせをサポートしています：

• すべてのテーブルデータを OSS に保存し、インデックスをディスク上に保持する — インデックスのアクセスパフォーマンスを維持しながらコストを削減します。

• ラージオブジェクト (LOB) フィールド (例： BLOB、TEXT、JSON、JSONB、ANYARRAY、または時空間データ型) のみを OSS に保存し、残りのフィールドをディスク上に保持します。

• 期限切れのパーティションを OSS に保存し、ホットパーティションをディスク上に保持します。さらに、コールドパーティションのインデックスを OSS に移動したり、ホットパーティションのインデックスをディスク上に保持したりするなど、さらなるカスタマイズが可能です。

制限事項

階層化ストレージを設定する前に、以下の制約にご注意ください：

• 新規書き込みにのみ適用： ALTER COLUMN 文を実行した後に挿入されたデータのみが OSS に移動します。既存のデータを移行するには、VACUUM FULL を ALTER COLUMN の後に実行します。これにより、すべてのテーブルデータが再書き込みされますが、処理中はテーブルが読み書き操作に利用できなくなり、大規模なテーブルでは時間がかかる場合があります。

• OSS のみ： この機能は OSS、MinIO、および Hadoop 分散ファイルシステム (HDFS) をサポートするように設計されていますが、現在サポートされているのは OSS のみです。

前提条件

開始する前に、以下を確認してください：

• PolarDB for PostgreSQL (Compatible with Oracle) クラスター

• 特権アカウントでの postgres データベースへのアクセス

拡張機能と定期アーカイブの設定

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 traj
       FOR VALUES FROM ('2023-01-01') TO ('2023-02-01');
   CREATE TABLE traj_202302 PARTITION OF traj
       FOR VALUES FROM ('2023-02-01') TO ('2023-03-01');
   CREATE TABLE traj_202303 PARTITION OF traj
       FOR VALUES FROM ('2023-03-01') TO ('2023-04-01');
   CREATE TABLE traj_202304 PARTITION OF traj
       FOR 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);

2. テストデータベースに polar_osfs_toolkit 拡張機能を作成します。これにより、テーブルとインデックスを OSS にアーカイブするためのユーティリティ関数が有効になります：

   CREATE EXTENSION polar_osfs_toolkit;

3. 特権アカウントで postgres データベースに接続し、pg_cron 拡張機能を作成します：

   -- postgres データベースでは特権アカウントのみがこの文を実行できます
   CREATE EXTENSION pg_cron;

   詳細については、「pg_cron」をご参照ください。

4. task1 という名前の定期タスクを作成し、1 分ごとに実行します。このタスクは polar_alter_subpartition_to_oss を呼び出し、3 か月より古いパーティションを OSS に移動します：

   SELECT cron.schedule_in_database('task1', '* * * * *', 'select polar_alter_subpartition_to_oss(''traj'', 3);', 'db01');

   タスク ID が返されます：

   schedule_in_database
   ----------------------
                       1

結果の検証

OSS に移動されたはずのパーティションのストレージ場所を確認します：

\d+ traj_202301

Tablespace フィールドに oss と表示されていれば、パーティションはアーカイブされています：

Table "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')
...
Tablespace: "oss" -- すでに OSS に保存済み
Access method: heap

定期タスクの実行履歴を確認するには、postgres データベースに接続して以下を実行します：

SELECT * FROM cron.job_run_details;

アーカイブされたパーティションは、もはやディスク領域を占有しません。それらに対する CRUD 操作は、完全な透過性を保ちながら機能し続けます。

LOB ストレージの OSS へのリダイレクト

1. LOB 列を持つテーブルを作成します。この例では TEXT を使用しますが、他の LOB 型にも同じ手順が適用されます：

   CREATE TABLE blob_table(id serial, val text);

2. val 列のストレージ場所を OSS に設定します：

   ALTER TABLE blob_table ALTER COLUMN val SET (storage_type='oss');

3. データを挿入し、ストレージ場所を確認します：

   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)

ALTER COLUMN を実行した後に挿入されたデータのみが OSS に保存されます。すでに使用中のテーブルから既存の LOB データを移動するには、まず ALTER COLUMN を実行してストレージの場所を設定し、次に VACUUM FULL を実行して既存のすべてのデータを OSS に再書き込みします。VACUUM FULL は実行中の CRUD 操作には影響しませんが、実行中はテーブルをロックするため、大規模なテーブルでは時間がかかることがあります。

リモートセンシング画像のインポートと分析

1. 4 つの Landsat リモートセンシング画像を準備します。

   

2. 画像をデータベースにインポートします：

   1. rastdb という名前のテストデータベースを作成します。

   2. ganos_raster 拡張機能を作成します。詳細については、「GanosBase Raster」をご参照ください：

      CREATE EXTENSION ganos_raster CASCADE;

   3. 画像をインポートします：

      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. 画像ブロックデータが使用するディスク領域を計算します。GanosBase では、ラスターメタデータとブロックデータは別々に保存されます。ブロックテーブルのサイズを合計するストアドプロシージャを作成します：

   CREATE OR REPLACE FUNCTION raster_data_internal_total_size( rast_table_name text, rast_column_name text)
     RETURNS int8 AS $$
   DECLARE
       sql                 text;
       sql2                text;
       rec                 record;
       size                int8;
       totalsize           int8;
       tbloid              Oid;
   BEGIN
       size := 0;
       totalsize := 0;
   
       -- Query the block data table of the raster object
       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 in
           execute sql
       loop
           sql2 = 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) then
               size := 0;
           else
               -- Calculate the size of each data table
               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;

   計算を実行します：

   SELECT pg_size_pretty(raster_data_internal_total_size('raster_table','rast'));

   画像は約 1.2 GB のデータベースストレージを占有します：

   pg_size_pretty
   ----------------
    1319 MB
   (1 row)

4. ディスク上のデータで NDVI を計算します。NDVI 分析の前に、モザイク操作で画像を結合します。ST_MosaicFrom の詳細については、「ST_MosaicFrom」をご参照ください：

   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 1
   Time: 39874.189 ms (00:39.874)

5. ブロックデータを OSS にアーカイブします。メタデータをディスク上に保持したまま、ブロックデータテーブルを OSS に移動するストアドプロシージャを作成します：

   CREATE OR REPLACE FUNCTION raster_data_alter_to_oss( rast_table_name text, rast_column_name text)
     RETURNS VOID AS $$
   DECLARE
       sql                 text;
       sql2                text;
       rec                 record;
   BEGIN
       -- Query the data table of the raster object
       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 in
           execute sql
       loop
           sql2 = 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');
   
   -- ディスク使用率が 0 になったことを確認します
   SELECT pg_size_pretty(raster_data_internal_total_size('raster_table','rast'));

   pg_size_pretty
   ----------------
    0 bytes
   (1 row)

   ブロックデータは OSS に格納されるようになりました。

6. OSS のブロックデータで NDVI を再計算します：

   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 1
   Time: 69414.201 ms (01:09.414)

   

ストレージコストとパフォーマンスの比較

OSS のストレージコストは、ディスクストレージコストの 10 分の 1 未満です。パフォーマンスのオーバーヘッドは 100% 未満 (2 倍未満) であり、これは非リアルタイムの統計分析には許容範囲です。