Enable and use tiered storage for cold data

Version requirements

Supported regions

Move 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

  When you run ALTER TABLE tblname SET TABLESPACE OSS on a table, only the table data is moved to OSS by default. To move the table's indexes, you must run the ALTER INDEX statement for each one.

• Create new 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 set the default tablespace to OSS, all new tables, indexes, and materialized views are created in OSS. To revert to the previous behavior, reset the default tablespace:

    RESET default_tablespace;

Move LOB fields to cold storage separately

LOB fields in PolarDB for PostgreSQL refer to fields with blob, text, json, jsonb, or array data types, as well as spatio-temporal data types. These fields often contain large individual objects that consume a large amount of storage space but are not frequently updated. To help reduce costs in such scenarios, the tiered storage for cold data feature of PolarDB for PostgreSQL allows you to move only the LOB fields to cold storage, while the storage of other fields remains unchanged. You can use a single SQL statement to move LOB fields to cold storage. The specific usage is as follows:

-- Create a table that contains a LOB field.
CREATE TABLE  test_large_object(id serial, val text);
-- Move the LOB field to cold storage separately.
ALTER TABLE test_large_object alter column val set (storage_type='oss');
-- Write data to the LOB field. 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)));

Move partitioned tables to cold storage

Partitioned tables are common candidates for cold storage. You can use specific methods to manage their storage.

• Move all partitions of a partitioned table to cold storage.

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

    -- prt1 is the partitioned table (parent table).
    -- prt1_p1 is a partition of prt1.
    -- prt1_p2 is another partition of prt1.
    -- Run the ALTER statement on all 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;
    -- Partitions inherit the tablespace property from the parent table by default and are created 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 and then 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 partitions to cold storage:

  If you need to move only expired (infrequently accessed) partitions to cold storage, you can directly change the tablespace for those specific partitions. Active (frequently accessed) partitions remain on the cloud disk. This approach preserves the query performance of the partitioned table while reducing storage costs.

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

Manage data tiers

Materialized cache

You can set the size of the materialized cache by modifying the polar_smgrcache_size parameter.

1. Log on to the PolarDB console. In the left-side navigation pane, click Clusters. Select the cluster's region, and then click its ID to open the details page.

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. See the following table for examples.

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

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

After you enable the materialized cache, you can query its usage as follows:

-- Create the extension.
CREATE extension polar_monitor;
-- Query the basic usage of the materialized cache.
SELECT * FROM polar_smgrcaches;
-- Description of fields:
  -- smgrcache: The cache ID.
  -- relfilenode: The table file to which the cache corresponds.
  -- relchunknumber: The position of the cache in the table file.
  -- nblocks: The size of the cache.
  -- dirty: Specifies whether the cache is dirty.
  -- 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

Insert, delete, update, and query cold data

Standard SQL statements for inserting, deleting, updating, and querying data work transparently on cold data (objects stored in the OSS tablespace).

Restore cold data

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

Delete cold data

Standard SQL statements for deleting cold tables, indexes, and materialized views work transparently.

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