PolarDB:Enable and use tiered storage for cold data

Version requirements

Individual cold storage for LOB fields is not supported on PostgreSQL 16.

Supported regions

Standard tables, indexes, and materialized views

Move existing objects to cold storage:

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

ALTER TABLE tblname SET TABLESPACE OSS transfers only the table data. To also move the table's indexes, run ALTER INDEX separately for each index.

Create new objects directly in cold 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;
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 session's default tablespace to oss before creating objects.

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

After setting default_tablespace = 'oss', all CREATE TABLE, CREATE INDEX, and CREATE MATERIALIZED VIEW statements in the session create objects directly in the OSS tablespace. To stop creating objects in cold storage, reset the default:

RESET default_tablespace;

LOB fields

Individual cold storage for LOB fields is not supported on PostgreSQL 16.

LOB fields — including blob, text, json, jsonb, array, and spatio-temporal data types — are often large and infrequently updated. You can move only the LOB fields to cold storage while keeping all other columns on local disk.

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

-- Move the LOB field to cold storage.
ALTER TABLE test_large_object ALTER COLUMN val SET (storage_type = 'oss');

-- New data written to this field is now stored in OSS.
INSERT INTO test_large_object (val)
VALUES ((SELECT string_agg(random()::text, ':') FROM generate_series(1, 10000)));

Partitioned tables

Partitioned tables are a common use case for cold storage. You can move all partitions or only specific expired partitions to the OSS tablespace.

Move all partitions to cold storage:

Method 1: Run ALTER TABLE ... SET TABLESPACE OSS on each child partition.

-- prt1 is the parent partitioned table.
-- prt1_p1 and prt1_p2 are child partitions.
ALTER TABLE prt1_p1 SET TABLESPACE OSS;
ALTER TABLE prt1_p2 SET TABLESPACE OSS;

Method 2: Create the parent table in the OSS tablespace. Child partitions inherit the tablespace.

CREATE TABLE prt1 (a int, b int) PARTITION BY RANGE (a) TABLESPACE OSS;

-- Child partitions inherit the OSS tablespace from the parent.
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 default_tablespace = 'oss' before creating 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 only expired partitions to cold storage:

To reduce costs without affecting query performance on active partitions, move only expired (infrequently accessed) child partitions to the OSS tablespace. Active partitions remain on local disk.

-- prt1_p1 is an active child partition — leave it on disk.
-- prt1_p2 is an expired child partition — move it to OSS.
ALTER TABLE prt1_p2 SET TABLESPACE OSS;

Query cold data

DML and DQL statements on objects in the OSS tablespace are fully transparent — no SQL changes or application modifications are required. Standard SELECT, INSERT, UPDATE, and DELETE statements work the same way as on local tables.

Restore cold data to local disk

Data is compressed when transferred to OSS. If you need to move data back to a local disk tablespace, make sure the target disk has enough free space. The space required is typically 1.4 to 4 times the storage space that the data occupies on OSS.

Delete cold data

SQL statements for deleting cold tables, indexes, or materialized views work the same as for local objects:

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