PolarDB:Enable and use tiered storage for cold data

Move existing tables, indexes, or materialized views to OSS

Run the corresponding ALTER statement to move an existing object to OSS:

ALTER TABLE tblname SET TABLESPACE OSS;
ALTER INDEX idxname SET TABLESPACE OSS;
ALTER MATERIALIZED VIEW mvname SET TABLESPACE OSS;

Create tables, indexes, or materialized views directly in OSS

Two methods are available.

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 tblname USING GiST(columnname) TABLESPACE OSS;
CREATE MATERIALIZED VIEW table_name TABLESPACE OSS AS query [ WITH [ NO ] DATA ];

Method 2: Set the default tablespace to OSS

Set default_tablespace once, then run CREATE statements as normal — all new objects go to OSS automatically.

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

RESET default_tablespace;

Move individual large object (LOB) fields to OSS

LOB fields of type blob, text, json, jsonb, array, or spatio-temporal data can be stored independently in OSS while the rest of the table remains on cloud disk. This is useful when a small number of large columns are driving most of your storage costs.

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

-- Configure the LOB field to store in OSS.
ALTER TABLE test_large_object ALTER COLUMN val SET (storage_type='oss');

-- Subsequent inserts store the LOB data in OSS automatically.
INSERT INTO test_large_object(val)
  VALUES((SELECT string_agg(random()::text, ':') FROM generate_series(1, 10000)));

For more information about spatio-temporal data types, see Overview.

Move partitioned tables to OSS

Partitioned tables are the most common use case for cold data tiered storage. A typical pattern is to keep recent partitions on fast disk and move expired partitions to OSS.

Move all child tables

Three methods are available to put all partitions in OSS.

• Method 1: Run ALTER TABLE on each child table individually:

  ALTER TABLE prt1_p1 SET TABLESPACE OSS;
  ALTER TABLE prt1_p2 SET TABLESPACE OSS;

• Method 2: Create the parent table in OSS — child tables inherit the tablespace:

  CREATE TABLE prt1 (a int, b int) PARTITION BY RANGE(a) TABLESPACE OSS;
  
  -- Child tables are created in OSS by inheritance.
  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 before creating the 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 tables

If you want to move only expired, infrequently accessed child tables to OSS while leaving hot partitions on disk, change the tablespace of those specific child tables. This does not affect the access performance of the partitioned table and reduces storage costs.

-- prt1 is the parent partitioned table.
-- prt1_p1 is an active child table (stays on disk).
-- prt1_p2 is an expired child table (move to OSS).
ALTER TABLE prt1_p2 SET TABLESPACE OSS;

Query cache usage

After enabling materialized caching, install the polar_monitor extension and query cache usage as needed:

-- Install the polar_monitor extension.
CREATE EXTENSION polar_monitor;

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

The polar_smgrcaches view returns the following fields:

To manage cache state manually:

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

-- Evict the materialized cache from disk.
SELECT polar_evict_smgrcache();

Query and modify cold data

Run standard SQL statements to read and write cold data — no code changes required. Cold data behaves the same as data in regular storage.

Restore cold data to cloud disk

Data stored in OSS is compressed. Before restoring data to cloud disk, make sure sufficient disk space is available. The required space is approximately 1.4–4 times the size of the OSS storage.

Delete cold data

Delete cold tables, indexes, or materialized views the same way you delete regular objects:

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