备案控制台
文档
产品文档
输入文档关键字查找
首页 云原生数据库 PolarDB 云原生数据库PolarDB PostgreSQL版 自研内核能力 冷数据分层存储 冷数据分层存储最佳实践 分区表自动冷存

分区表自动冷存

更新时间：
一键部署
产品详情
相关技术圈
我的收藏

本文介绍分区表自动冷存的最佳实践。

场景描述

带有时间序列的数据采用分区表存储，并按月进行分区，随着时间的推移，一段时间之前的数据访问频率大大降低（过期），为了降低存储成本，需要自动将超过某个固定时间的分区表进行冷存处理。

对于这种场景，PolarDB PostgreSQL版支持通过创建pg_corn扩展插件的方式来实现，详细操作步骤如下。

创建扩展

CREATE EXTENSION IF NOT EXISTS pg_cron;
说明

pg_cron扩展只能在postgres库中创建，用户可使用高权限账户连接到 postgres 库中执行创建。如果没有高权限账户，可以登录控制台界面创建高权限账户。

操作步骤

以db01数据库为例，描述分区表自动转冷存的步骤。

  1. 准备数据。

    --创建分区表
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);

  2. 创建osfs辅助工具。

    db01=> CREATE EXTENSION IF NOT EXISTS polar_osfs_toolkit;

  3. 制定定时执行任务。

    使用高权限账户连接到 postgres 数据库中执行以下 SQL，为 db01 数据库创建一个名为 task1 的任务，该任务为每分钟检查子分区表数据，并自动将超过3个子分区的历史子分区转为冷存，并返回任务 ID：

    -- 每分钟执行
postgres=> SELECT cron.schedule_in_database('task1', '* * * * *', 'select polar_alter_subpartition_to_oss(''traj'', 3);', 'db01');
 schedule_in_database 
----------------------
                    1
    说明

    polar_alter_subpartition_to_oss函数的使用说明可以参考如何实现一键转冷存。

    pg_cron除了设置每分钟自动转存的任务外，还可以设置每天固定时间、每月固定时间等自定义规则的自动处理任务：

    -- 每天的 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
    说明

    pg_cron更多使用方法可参考pg_cron。

  4. 查看执行结果。

    --任务执行的结果就是分区表转存至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"，说明数据已经转入冷存了。

  5. 查看定时任务历史执行记录。

    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('traj', 3); | 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('traj', 3); | 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('traj', 3); | 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('traj', 3); | succeeded | 1 row          | 2024-03-10 03:15:00.006494+00 | 2024-03-10 03:15:00.012056+00
(4 rows)

这样就实现了按规则将过期子分区表自动转为冷存了，冷存后的表数据全部存储在OSS上，不再占用云盘存储空间，大大降低了存储成本，同时增删改查操作也完全透明。

文档推荐
  • 本页导读 （0）
文档反馈