PolarDB:Independent cold storage for LOB fields

Scenario

LOB fields are variable-length fields of the blob, text, json, jsonb, anyarray, or spatio-temporal data type. For more information about the spatio-temporal data types provided by Ganos, see Models. A LOB field occupies a large amount of storage and is not frequently accessed. This topic describes how to extract a LOB field from a base table and convert the LOB field to cold data while keeping the data accessible like regular data. This separates the cold data and hot data in the table by field.

Procedure

1. Create a table that contains LOB fields.

   In this example, a table that contains a field of the text type is created. Use the same method to create other types of LOB fields.

   db01=>CREATE TABLE blob_table(id serial, val text);

2. Convert the LOB field to cold data.

   db01=>alter table blob_table alter column val set (storage_type='oss');

3. Insert data to the table and view the storage location of the inserted data.

   -- Insert data to the table. Now the val field is stored in OSS.
   db01=>INSERT INTO blob_table(val) VALUES((SELECT string_agg(random()::text, ':') FROM generate_series(1, 100000)));
   
   -- View the storage location of the val field.
   db01=> WITH tmp as (select 'pg_toast_'||b.oid||'_'||c.attnum as tblname from pg_class b, pg_attribute c where b.relname='blob_table' and c.attrelid=b.oid and c.attname='val') select t.spcname as storage_type from pg_tablespace t, pg_class r, tmp m where r.relname = m.tblname and t.oid=r.reltablespace;
    storage_type 
   --------------
    oss
   (1 row)

   Note

   The inserted data is stored in OSS only after you convert the field to cold data. The storage location of the data that exists before the cold data conversion remains unchanged.