PolarDB:Enable and use tiered storage for cold data

Version requirements

Supported regions

Cold storage for standard tables, indexes, and materialized views

• Move existing data 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

  When you execute ALTER TABLE tblname SET TABLESPACE OSS on a table, only the table data is transferred to OSS by default. To transfer the indexes of the table to OSS, you must also execute ALTER INDEX for each index.

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

  • 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;
    CREAE 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);
    CREAE  INDEX  idxname  ON tblename USING GiST(columnname);
    CREATE MATERIALIZED VIEW  table_name  AS query [ WITH [ NO ] DATA ];

    Note

    After you use this method to set the default tablespace to OSS, all subsequent CREATE TABLE, CREATE INDEX, and CREATE MATERIALIZED VIEW operations create objects directly in OSS storage. If you no longer want to create objects in cold storage, you can reset the default tablespace:

    RESET default_tablespace;

Individual cold storage for LOB fields

LOB fields include fields of the blob, text, json, jsonb, and array data types, along with spatio-temporal data types, in PolarDB for PostgreSQL and . These objects are often large, occupy significant storage space, and are infrequently updated. To reduce costs in these scenarios, the tiered storage feature for cold data in PolarDB for PostgreSQL and lets you move only LOB fields to cold storage while other fields remain on their original storage medium. You can move LOB fields to cold storage using a single SQL statement:

--Create a table that contains LOB fields.
CREATE TABLE  test_large_object(id serial, val text);

--Move only the LOB fields to cold storage.
ALTER TABLE test_large_object alter column val set (storage_type='oss');

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

Cold storage for partitioned tables

A common use case for cold storage is with partitioned tables. Special methods are available for using the tiered storage feature with partitioned tables.

• Move all partitions to cold storage.

  • Method 1: Sequentially move existing child partitions to cold storage.

    --prt1 is a partitioned table (parent table).
    --prt1_p1 is a child table of prt1.
    --prt2_p2 is another child table of prt1.
    --Execute the ALTER statement on all child tables.
    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;
    
    --By default, child tables inherit the tablespace property of the parent table 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 some child partitions to cold storage:

  To move only expired (infrequently accessed) child partitions to cold storage, you can change the tablespace for only those partitions. Unexpired (frequently accessed) child partitions are not moved and remain on the database disks. This approach reduces storage costs without affecting the access performance of the partitioned table.

  --prt1 is a partitioned table (parent table).
  --prt1_p1 is an unexpired child table of prt1.
  --prt2_p2 is an expired child table of prt1.
  --Execute the ALTER statement on the expired child table.
  ALTER  TABLE  prt1_p2  SET  TABLESPACE OSS;

Tiered storage of hot and cold data

Materialized cache for cold data

You can modify the polar_smgrcache_size parameter to set the size of the materialized cache for cold data.

1. Log on to the PolarDB console. In the navigation pane on the left, click Clusters. Select the region where the cluster is located. Find the cluster and click its ID to open the cluster details page.

2. In the navigation pane on the left, choose Settings And Management > Parameters. Find the polar_smgrcache_size parameter and click Modify Parameter.

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

   Value	Cache size
   0	0 (Cache is disabled)
   1	1 GB
   2	2 GB
   128	128 GB (The maximum supported value)

4. In the upper-left corner of the page, click Submit Changes. In the Save Changes dialog box, click OK. The cluster restarts after you modify this parameter.

After you enable the materialized cache for cold data, you can query the cache usage as follows:

-- Create the extension.
CREATE extension polar_monitor;

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

-- Field description:
  -- smgrcache: the 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.
  -- dirty: specifies whether the cache contains dirty blocks.
  -- usagecount: the usage count.
  -- pinning_backends: the reference count.

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

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

Access cold data

Add, delete, modify, and query cold data

SQL statements for adding, deleting, modifying, and querying cold data (objects already stored in the OSS tablespace) are transparent and require no changes.

Restore cold data

Data is compressed when transferred to OSS. If you want to restore data from OSS to a disk, ensure that the disk has sufficient storage space. The required space is typically 1.4 to 4 times the storage space that the data occupies on OSS.

Clear cold data

SQL statements for deleting cold tables, indexes, or materialized views are also transparent and require no changes.

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