PolarDB:Automatically archive partitioned tables based on a timeline

Prerequisites

Before you begin, ensure that you have:

• A PolarDB for PostgreSQL cluster

• A privileged account with access to the PostgreSQL database. If you don't have one, create it in the console before proceeding.

Step 1: Install pg_cron

Connect to the PostgreSQL database using a privileged account, then run:

CREATE EXTENSION IF NOT EXISTS pg_cron;

pg_cron can only be installed in the PostgreSQL database. Run this command while connected to that database, not a user database.

Step 2: Install the OSFS toolkit

Connect to your target database (db01 in this example) and run:

CREATE EXTENSION IF NOT EXISTS polar_osfs_toolkit;

Step 3: Prepare a partitioned table

This step sets up a sample partitioned table. Skip it if you already have a table to archive.

-- Create a table partitioned by time range
CREATE TABLE traj(
  tr_id serial,
  tr_lon float,
  tr_lat float,
  tr_time timestamp(6)
) PARTITION BY RANGE (tr_time);

-- Create monthly partitions
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 test data
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 an index
CREATE INDEX traj_idx ON traj(tr_id);

Step 4: Create a scheduled archiving task

Connect to the PostgreSQL database using a privileged account, then call cron.schedule_in_database to create the task.

The following example creates a task named task1 that runs every minute. It archives any partition of traj older than three days to OSS cold storage.

-- Run every minute
postgres=> SELECT cron.schedule_in_database(
    'task1',          -- Job name
    '* * * * *',      -- Cron schedule (every minute)
    'select polar_alter_subpartition_to_oss_interval(''traj'', ''3 days''::interval);',
    'db01'            -- Target database
);
 schedule_in_database
----------------------
                    1

The cron.schedule_in_database function accepts four parameters:

┌───────────── minute (0–59)
│ ┌───────────── hour (0–23)
│ │ ┌───────────── day of month (1–31)
│ │ │ ┌───────────── month (1–12)
│ │ │ │ ┌───────────── day of week (0–6, Sunday=0)
│ │ │ │ │
* * * * *

-- Run 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 on the 4th 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

Step 5: Verify archiving results

After the task runs, check whether a partition has been moved to OSS. Use \d+ to inspect the partition metadata:

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" confirms the partition data is stored in OSS cold storage.

Step 6: Monitor job execution

Query cron.job_run_details to see past runs:

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)

This confirms that the expired table partitions are automatically archived to cold storage based on the configured rules. The archived data is stored in OSS, which no longer occupies disk space and greatly reduces storage costs. All CRUD operations remain transparent.

What's next

• polar_alter_subpartition_to_oss_interval — function reference for one-click cold storage conversion

• pg_cron — full pg_cron extension documentation