本文介紹分區表按時間軸自動冷存的最佳實務。
情境描述
帶有時間序列的資料採用分區表格儲存體，並按時間進行分區，隨著時間的推移，一段時間之前的資料訪問頻率大大降低（到期），為了降低儲存成本，需要自動將超過某個固定時間的分區表進行冷存處理。
對於這種情境，PolarDB PostgreSQL版（相容Oracle）支援通過建立pg_cron擴充外掛程式的方式來實現，詳細操作步驟如下。
建立擴充
CREATE EXTENSION IF NOT EXISTS pg_cron;說明
pg_cron擴充只能在postgres庫中建立，使用者可使用高許可權賬戶串連到postgres庫中執行建立。如果沒有高許可權賬戶，可以登入控制台介面建立高許可權賬戶。
操作步驟
以db01資料庫為例，描述分區表自動轉冷存的步驟。
準備資料。
--建立分區表 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'); --往分區表中寫入測試資料 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'); --建立分區表索引 db01=> CREATE INDEX traj_idx on traj(tr_id);建立osfs協助工具輔助。
db01=> CREATE EXTENSION IF NOT EXISTS polar_osfs_toolkit;制定定時執行任務。
使用高許可權賬戶串連到postgres資料庫中執行以下SQL，為db01資料庫建立一個名為task1的任務，該任務為每分鐘檢查子分區表資料，並自動將超過3天的子分區轉為冷存，並返回任務ID：
-- 每分鐘執行 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除了設定每分鐘自動轉存的任務外，還可以設定每天固定時間、每月固定時間等自訂規則的自動處理任務：
-- 每天的 10:00am (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 -- 每個月的 4號 執行 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。
查看執行結果。
--任務執行的結果就是分區錶轉存至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上，不再佔用雲端硬碟儲存空間，大大降低了儲存成本，同時增刪改查操作也完全透明。