PolarDB:Enable and use the cold data tiered storage feature

Cold storage for data tables, indexes, or materialized views

• Dump existing data tables, indexes, or materialized views into OSS.

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

  Note

  By default, when you execute the ALTER TABLE tblname SET TABLESPACE OSS statement on a table, only the data in the table is dumped into OSS. To dump indexes in the table into OSS, execute the ALTER INDEX statement on the table.

• Create data tables, indexes, or materialized views in an OSS bucket. The following methods are supported:

  • Method 1: Add the TABLESPACE OSS clause 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 an OSS bucket.

    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, data tables, indexes, or materialized views are created in OSS. If you do not want to create cold data objects, you must reset the default tablespace.

    RESET default_tablespace;

Individual cold storage for large object (LOB) fields

LOB fields are of the blob, text, json, jsonb, array, or spatio-temporal data type in PolarDB for PostgreSQL. A single LOB field is large in size, occupies a large amount of storage space, and is not frequently updated. For information about spatio-temporal data, see Overview. To reduce the storage costs of LOB fields, the cold data tiered storage feature of PolarDB for PostgreSQL supports individual cold storage for LOB fields and retains other fields in database disks. You can execute the following statements to dump LOB fields into OSS:

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

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

-- Insert LOB data into the table. The data is 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

In most cases, cold storage is used for partitioned tables.

• Cold storage for all partitioned tables

  • Method 1: Dump existing child partitioned tables in a parent partitioned table into OSS in sequence.

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

  • Method 2: Create a parent partitioned table in an OSS bucket.

    CREATE TABLE prt1 (a int, b int) PARTITION BY RANGE(a) TABLESPACE OSS;
    
    -- By default, all child tables inherit the attributes of the parent table and are created in the OSS bucket.
    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 an OSS bucket before creating a 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);

• Cold storage for some partitioned tables

  If you want to dump only child partitioned tables that are expired and infrequently accessed into OSS, you can change the tablespace of the child tables. Child partitioned tables that are not expired and are frequently accessed remain stored in database disks. This does not affect the access performance of partitioned tables and reduces storage costs.

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

Tiered storage for cold and hot data

Materialized cache for cold data

You can modify the value of the polar_smgrcache_size parameter to specify the materialized cache size of cold data. Perform the following steps:

1. Log on to the PolarDB console.

2. Click the ID of the cluster that you want to manage to go to the Basic Information page.

3. In the left-side navigation pane, choose Settings and Management > Parameters. Then, find the polar_smgrcache_size parameter and click Modify Parameter.

4. Configure the polar_smgrcache_size parameter. The following table describes the valid values of the parameter.

   Value	Cache size
   0	0 GB (Materialized caching is disabled for cold data.)
   1	1 GB
   2	2 GB
   128	128 GB (maximum value)

5. Click Submit Changes in the upper-left corner. Then, click OK in the Save Changes message. After you modify the parameter value, the cluster is restarted.

After you enable materialized caching for cold data, you can execute the following statements to query cache usage:

-- Install the polar_monitor extension.
CREATE extension polar_monitor;

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

-- Field description:
  -- smgrcache: the ID of the cache.
  -- relfilenode: the table file corresponding to the cache.
  -- relchunknumber: the location of the cache in the table file.
  -- nblocks: the size of the cache.
  -- dirty: whether the cache contains dirty blocks.
  -- usagecount: the number of times that the cache is accessed or utilized.
  -- pinning_backends: the number of backend processes tied to or dependent on the cache for their operations.

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

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

Access to cold data

Add, delete, modify, and query cold data

You can execute SQL statements to add, delete, modify, and query cold data in the same manner that you manage data in regular storage. No additional modifications are required.

Restore cold data

Data that is dumped into OSS is compressed. If you want to restore data stored in OSS to cloud disks, make sure that the cloud disks have sufficient storage space. In most cases, the required storage space is approximately 1.4 to 4 times the size of the OSS storage space.

Clear cold data

You can execute SQL statements to delete cold tables, indexes, or materialized views in the same manner that you delete the same objects in regular storage.

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