PolarDB:Enable and use tiered storage for cold data

Version requirements

Supported regions

Standard tables, indexes, and materialized views

• Move existing tables, indexes, or materialized views to cold storage.

  ALTER  TABLE  tblname  SET  TABLESPACE OSS;
  ALTER  INDEX  idxname  SET TABLESPACE OSS;
  ALTER Materialized View mvname SET TABLESPACE OSS;

  Note

  By default, ALTER TABLE tblname SET TABLESPACE OSS moves only the table data to OSS. To move the indexes, you must run a separate ALTER INDEX command for each one.

• Create new tables, indexes, or materialized views directly in OSS.

  • Method 1: Specify TABLESPACE OSS in the CREATE statement.

    CREATE TABLE tblname (...) TABLESPACE OSS;
    CREATE TABLE tblname(...) TABLESPACE OSS as SELECT ...;
    CREATE INDEX idxname ON tblname(columnname) TABLESPACE OSS;
    CREATE INDEX idxname ON tblename USING GiST(columnname) TABLESPACE OSS;
    CREATE MATERIALIZED VIEW table_name TABLESPACE OSS AS query [ WITH [ NO ] DATA ];

  • Method 2: Set the default tablespace to the OSS tablespace.

    SET default_tablespace = 'oss';
    CREATE  TABLE  tblname  (...) ;
    CREATE  INDEX  idxname ON  tblname(columnname);
    CREATE  INDEX  idxname  ON tblename USING GiST(columnname);
    CREATE MATERIALIZED VIEW  table_name  AS query [ WITH [ NO ] DATA ];

    Note

    After you set the default tablespace to oss, new tables, indexes, and materialized views are created directly in OSS. To stop creating objects in cold storage, reset the default tablespace:

    RESET default_tablespace;

Independent LOB field storage

In , LOB fields refer to the blob, text, json, jsonb, and array data types, as well as . Objects of these data types are typically large and consume significant storage space, but they are infrequently updated. To reduce costs in such scenarios, the tiered storage for cold data feature allows you to store LOB fields separately in cold storage, while the storage of other fields remains unchanged. You can move LOB fields to cold storage by using a single SQL statement. The usage is as follows:

-- Create a table with a large-object field.
CREATE TABLE  test_large_object(id serial, val text);

-- Move the large-object field to cold storage independently.
ALTER TABLE test_large_object alter column val set (storage_type='oss');

-- Insert large-object data. The content of the text field is stored in OSS.
INSERT INTO test_large_object(val) VALUES((SELECT string_agg(random()::text, ':') FROM generate_series(1, 10000)));

Partitioned table cold storage

Partitioned tables are a common use case for cold storage and have specific archiving methods.

• Move all partitions to cold storage.

  • Method 1: Move existing child partitions to cold storage one by one.

    -- prt1 is the partitioned table (parent table).
    -- prt1_p1 is a child partition of prt1.
    -- prt1_p2 is another child partition of prt1.
    -- Run the ALTER operation on all child partitions.
    ALTER  TABLE  prt1_p1  SET  TABLESPACE OSS;
    ALTER  TABLE  prt1_p2  SET  TABLESPACE OSS;

  • Method 2: Create the parent partitioned table directly in the OSS tablespace.

    CREATE TABLE prt1 (a int, b int) PARTITION BY RANGE(a) TABLESPACE OSS;
    
    -- Child partitions inherit the parent's tablespace attribute by default and are created directly in the OSS tablespace.
    CREATE TABLE prt1_p1 PARTITION OF prt1 FOR VALUES FROM (0) TO (250);
    CREATE TABLE prt1_p2 PARTITION OF prt1 FOR VALUES FROM (250) TO (500);

  • Method 3: Set the default tablespace to the OSS tablespace before you create the partitioned table.

    SET default_tablespace = 'oss';
    CREATE  TABLE  prt1 (a int, b int) PARTITION BY RANGE(a);
    CREATE TABLE prt1_p1 PARTITION OF prt1 FOR VALUES FROM (0) TO (250);
    CREATE TABLE prt1_p2 PARTITION OF prt1 FOR VALUES FROM (250) TO (500);

• Move specific child partitions to cold storage:

  When you only need to move expired (infrequently accessed) child partitions to cold storage, you can directly change the tablespace for those partitions. Active (frequently accessed) partitions remain on the database's cloud disks. This approach reduces storage costs without affecting query performance on the partitioned table.

  -- prt1 is the partitioned table (parent table).
  -- prt1_p1 is an active child partition.
  -- prt1_p2 is an expired child partition.
  -- Run the ALTER operation on the expired child partition.
  ALTER  TABLE  prt1_p2  SET  TABLESPACE OSS;

Hot and cold data tiering

Materialized cache for cold data

You can configure the size of the materialized cache for cold data by modifying the polar_smgrcache_size parameter.

1. Log on to the PolarDB console. In the left-side navigation pane, click Clusters. Select the Region where your cluster is located, find the cluster, and click its ID.

2. In the left-side navigation pane, choose Configuration and Management > Parameter Configuration. Find the polar_smgrcache_size parameter and click Modify Parameter.

3. Set the polar_smgrcache_size parameter as shown in the following examples.

   Value	Cache size
   0	0 (Cache is disabled)
   1	1 GB
   2	2 GB
   128	128 GB (current maximum)

4. In the upper-left corner, click Submit modification. In the Save Changes dialog box, click OK. Modifying this parameter restarts the cluster.

After you enable the materialized cache for cold data, you can query its usage with the following statements:

-- Create the extension.
CREATE extension polar_monitor;

-- Query basic usage of the materialized cache.
SELECT * FROM polar_smgrcaches;

-- Field descriptions:
  -- smgrcache: Cache ID.
  -- relfilenode: The table file that corresponds to the cache.
  -- relchunknumber: The position of the cache in the table file.
  -- nblocks: The size of the cache in blocks.
  -- dirty: Whether the cache is dirty.
  -- usagecount: The usage count.
  -- pinning_backends: The reference count.

-- Forcefully flush the materialized cache to OSS.
SELECT polar_flush_smgrcache(); 

-- Forcefully evict the materialized cache.
SELECT polar_evict_smgrcache(); 

Access and manage cold data

Query and modify cold data

DML and query operations on cold data (objects stored in the OSS tablespace) are transparent and require no changes.

Restore cold data

Data is compressed when it is moved to OSS. If you need to restore data from OSS to a cloud disk, ensure the cloud disk has sufficient storage space. The required space is typically 1.4 to 4 times the data's size in OSS.

Delete cold data

SQL statements for deleting cold tables, indexes, or materialized views require no changes.

DELETE  FROM tblname WHERE ...;
TRUNCATE  TABLE  tblname;
DROP  TABLE  tblname;
...