このトピックでは、スケジュールされたタスクを作成して、パーティション分割されたテーブルデータのコールドストレージへのアーカイブを自動化するベストプラクティスについて説明します。
背景情報
時間範囲で分割されたテーブルの場合、古いパーティションにアクセスする頻度は時間とともに減少します。 ストレージコストを削減するために、スケジュールされたタスクを構成して、パーティション分割されたテーブルデータのコールドストレージへのアーカイブを自動化できます。
PolarDB for PostgreSQL (Compatible with Oracle) クラスターを使用すると、pg_corn拡張機能を使用して、パーティション分割されたテーブルデータのコールドストレージへのアーカイブを自動化できます。
pg_cron拡張子の作成
CREATE EXTENSION IF NOT EXISTS pg_cron;pg_cron拡張機能は、PostgreSQLデータベースでのみ作成できます。 PostgreSQLデータベースにpg_cron拡張機能を作成するには、特権アカウントを使用してPostgreSQLデータベースに接続します。 特権アカウントを持っていない場合は、コンソールにログインして特権アカウントを作成します。
手順
この例では、db01データベースを使用して、パーティション分割されたテーブルデータをコールドストレージに自動的にアーカイブする方法を説明します。
データを準備します。
-- Create a partitioned table. db01=> CREATE TABLE traj( tr_id serial, tr_lon float, tr_lat float, tr_time timestamp(6) )PARTITION BY RANGE (tr_time); db01=> CREATE TABLE traj_202301 PARTITION OF traj FOR VALUES FROM ('2023-01-01') TO ('2023-02-01'); db01=> CREATE TABLE traj_202302 PARTITION OF traj FOR VALUES FROM ('2023-02-01') TO ('2023-03-01'); db01=> CREATE TABLE traj_202303 PARTITION OF traj FOR VALUES FROM ('2023-03-01') TO ('2023-04-01'); db01=> CREATE TABLE traj_202304 PARTITION OF traj FOR VALUES FROM ('2023-04-01') TO ('2023-05-01'); -- Write test data to the partitioned table. db01=> INSERT INTO traj(tr_lon,tr_lat,tr_time) values(112.35, 37.12, '2023-01-01'); db01=> INSERT INTO traj(tr_lon,tr_lat,tr_time) values(112.35, 37.12, '2023-02-01'); db01=> INSERT INTO traj(tr_lon,tr_lat,tr_time) values(112.35, 37.12, '2023-03-01'); db01=> INSERT INTO traj(tr_lon,tr_lat,tr_time) values(112.35, 37.12, '2023-04-01'); -- Create an index on the partitioned table. db01=> CREATE INDEX traj_idx on traj(tr_id);OSFSツールを作成します。
db01=> CREATE EXTENSION IF NOT EXISTS polar_osfs_toolkit;スケジュールタスクを作成します。
特権アカウントを使用してdb01データベースに接続し、次のSQL文を実行して、データベース用のtask1という名前のタスクを作成します。 タスクは毎分テーブルのパーティションをチェックし、3日より古いパーティションをコールドストレージに自動的にアーカイブし、タスクIDを返します。
-- Execute the task every minute. postgres=> SELECT cron.schedule_in_database('task1', '* * * * *', 'select polar_alter_subpartition_to_oss_interval(''traj'', ''3 days''::interval);', 'db01'); schedule_in_database ---------------------- 1説明polar_alter_subpartition_to_oss_interval関数の使用方法の詳細については、「 polar_alter_subpartition_to_oss_interval」をご参照ください。
毎分実行されるタスクを設定するだけでなく、pg_cronを使用して、毎日特定の時点で、または毎月特定の日に実行されるようにタスクを設定できます。
-- Run the task at 10:00 AM every day. The time is in GMT. postgres=> SELECT cron.schedule_in_database('task2', '0 10 * * *', 'select polar_alter_subpartition_to_oss_interval(''traj'', ''3 days''::interval);', 'db01'); schedule_in_database ---------------------- 2 -- Run the task on the fourth day of every month. postgres=> SELECT cron.schedule_in_database('task3', '* * 4 * *', 'select polar_alter_subpartition_to_oss_interval(''traj'', ''3 days''::interval);', 'db01'); schedule_in_database ---------------------- 3説明pg_cronの使用方法の詳細については、「 pg_cron」をご参照ください。
タスクの実行結果を表示します。
-- The subpartitioned table is dumped to OSS. db01=> \d+ traj_202301 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') 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: FULL Tablespace: "oss" Access method: heap説明Tablespace: "oss" は、データがコールドストレージにダンプされることを示します。
スケジュールされたタスクの実行履歴を表示します。
postgres=> select * from cron.job_run_details ; jobid | runid | job_pid | database | username | command | status | return_message | start_time | end_time -------+-------+---------+----------+----------+----------------------------------------------------+-----------+----------------+-------------------------------+------------------------------- 1 | 1 | 469075 | db01 | user1 | select polar_alter_subpartition_to_oss_interval('traj', '3 days'::interval); | succeeded | 1 row | 2024-03-10 03:12:00.016068+00 | 2024-03-10 03:12:00.135428+00 1 | 2 | 469910 | db01 | user1 | select polar_alter_subpartition_to_oss_interval('traj', '3 days'::interval); | succeeded | 1 row | 2024-03-10 03:13:00.008358+00 | 2024-03-10 03:13:00.014189+00 1 | 3 | 470746 | db01 | user1 | select polar_alter_subpartition_to_oss_interval('traj', '3 days'::interval); | succeeded | 1 row | 2024-03-10 03:14:00.013165+00 | 2024-03-10 03:14:00.019002+00 1 | 4 | 471593 | db01 | user1 | select polar_alter_subpartition_to_oss_interval('traj', '3 days'::interval); | succeeded | 1 row | 2024-03-10 03:15:00.006494+00 | 2024-03-10 03:15:00.012056+00 (4 rows)
このように、期限切れのテーブルパーティションは、設定されたルールに基づいて自動的にコールドストレージにアーカイブされます。 アーカイブされたテーブルデータはOSSに保存されます。OSSはディスク領域を占有せず、ストレージコストを大幅に削減します。 さらに、作成、読み取り、更新、および削除 (CRUD) 操作を指定するすべてのSQL文は透過的です。