Archive Cold Data to OSS via oss_fdw to Cut Storage Costs - PolarDB

oss_fdw is a foreign data wrapper (FDW) extension for PolarDB for PostgreSQL (Compatible with Oracle) that maps Object Storage Service (OSS) directories to foreign tables. It lets you archive and query data in OSS using standard SQL statements.

OSS is a secure, cost-effective, and highly reliable cloud storage service designed to provide 99.995% data availability. It is well-suited for storing historical data, read-only archived data, and cold data to reduce storage costs.

Supported versions

oss_fdw is supported on the following engine versions:

Version	Minimum revision
PolarDB for PostgreSQL (Compatible with Oracle) 2.0	2.0.14.3.0
PolarDB for PostgreSQL (Compatible with Oracle) 1.0	1.1.1

To check your cluster's revision version, run:

SHOW polar_version;

Limitations

oss_fdw foreign tables support SELECT, INSERT, and TRUNCATE only. UPDATE and DELETE are not supported. Once data is written to OSS, it can only be read—not modified.

Install the extension

Before you install the extension, make sure you have:

OSS activated and an OSS bucket created. See What is OSS? to get started.

Install the extension:

CREATE EXTENSION oss_fdw;

Create a foreign server

A foreign server stores your OSS connection details and maps PolarDB to an OSS bucket.

CREATE SERVER ossserver
FOREIGN DATA WRAPPER oss_fdw
OPTIONS (
    host   'oss-cn-xxx.aliyuncs.com',
    bucket 'mybucket',
    id     '<your-access-key-id>',
    key    '<your-access-key-secret>'
);

Option	Description
`host`	OSS endpoint, for example `oss-cn-hangzhou.aliyuncs.com`
`bucket`	Name of your OSS bucket
`id`	Your Alibaba Cloud AccessKey ID
`key`	Your Alibaba Cloud AccessKey secret

Create a foreign table

A foreign table links a SQL table definition to an OSS location. Each INSERT appends a new file to that location. Choose between two mapping modes:

Mode	Option	Behavior
Directory	`dir`	All files are written to a single OSS directory path
Prefix	`prefix`	Files share a common filename prefix

Map to a directory

Create a foreign table mapped to an OSS directory:

CREATE FOREIGN TABLE t1_oss (
    id  INT,
    f   FLOAT,
    txt TEXT
)
SERVER ossserver
OPTIONS (dir 'archive/');

Write data to OSS. Each INSERT creates one new file in the directory:

INSERT INTO t1_oss VALUES (generate_series(1, 100), 0.1, 'hello');

Confirm the file was written:

SELECT * FROM oss_fdw_list_file('t1_oss');

Run a second INSERT and confirm two files now exist:

INSERT INTO t1_oss VALUES (generate_series(1, 100), 0.1, 'hello');

EXPLAIN SELECT COUNT(*) FROM t1_oss;

                              QUERY PLAN
-------------------------------------------------------------------
 Aggregate  (cost=12.07..12.08 rows=1 width=8)
   ->  Foreign Scan on t1_oss  (cost=0.00..11.80 rows=108 width=0)
         Directory on OSS: archive/
         Number Of OSS file: 2
         Total size of OSS file: 2584 bytes
(5 rows)

SELECT COUNT(*) FROM t1_oss;

 count
-------
   200
(1 row)

To delete all OSS files mapped by the foreign table, run TRUNCATE:

TRUNCATE t1_oss;

SELECT COUNT(*) FROM t1_oss;

WARNING:  does not match any file in oss
 count
-------
     0
(1 row)

Map to a prefix

Use the prefix option when you want files to share a common filename prefix rather than a directory path.

CREATE FOREIGN TABLE t2_oss (
    id  INT,
    f   FLOAT,
    txt TEXT
)
SERVER ossserver
OPTIONS (prefix 'prefix/file_');

Each INSERT writes a new file whose name starts with prefix/file_. After two inserts:

INSERT INTO t2_oss VALUES (generate_series(1, 100), 0.1, 'hello');
INSERT INTO t2_oss VALUES (generate_series(1, 100), 0.1, 'hello');

EXPLAIN SELECT COUNT(*) FROM t2_oss;

                              QUERY PLAN
-------------------------------------------------------------------
 Aggregate  (cost=12.07..12.08 rows=1 width=8)
   ->  Foreign Scan on t2_oss  (cost=0.00..11.80 rows=108 width=0)
         Directory on OSS: prefix/file_
         Number Of OSS file: 2
         Total size of OSS file: 2584 bytes
(5 rows)

List OSS files for a foreign table

Use oss_fdw_list_file() to view the files and their sizes for any foreign table. Pass the table name and, optionally, the schema name (default: public).

Create a foreign table and write data to three OSS files:

CREATE FOREIGN TABLE t4_oss (
    id  INT,
    f   FLOAT,
    txt TEXT
)
SERVER ossserver
OPTIONS (dir 'archive_file_list/');

INSERT INTO t4_oss VALUES (generate_series(1, 10000), 0.1, 'hello');
INSERT INTO t4_oss VALUES (generate_series(1, 10000), 0.1, 'hello');
INSERT INTO t4_oss VALUES (generate_series(1, 10000), 0.1, 'hello');

Then list the files:

-- By table name only
SELECT * FROM oss_fdw_list_file('t4_oss');

-- With explicit schema name
SELECT * FROM oss_fdw_list_file('t4_oss', 'public');

                   name                    |  size
-------------------------------------------+--------
 archive_file_list/_t4_oss_783053364762580 | 148894
 archive_file_list/_t4_oss_783053364849053 | 148894
 archive_file_list/_t4_oss_783053366496328 | 148894
(3 rows)

Use this function after every INSERT to confirm data was written successfully.

Set the storage format

Data is written in CSV format by default. Specify format 'csv' explicitly if your workflow requires it to be declared:

CREATE FOREIGN TABLE t3_oss (
    id  INT,
    f   FLOAT,
    txt TEXT
)
SERVER ossserver
OPTIONS (dir 'archive_csv/', format 'csv');

Enable compression

Set compressiontype to reduce OSS storage usage and network transfer at the cost of additional CPU during compression and decompression. Leave compressiontype blank for no compression.

Parameter	Description
`compressiontype`	Compression algorithm. Valid values: `gzip`, `zstd`. Default: blank (no compression)
`compressionlevel`	Compression level. A higher level produces smaller files and reduces network transfer, but uses more CPU during both compression and decompression

Gzip

Valid compression levels: 1–9. Default: 6.

CREATE FOREIGN TABLE t5_oss (
    id  INT,
    f   FLOAT,
    txt TEXT
)
SERVER ossserver
OPTIONS (
    dir              'archive_file_compression/',
    compressiontype  'gzip',
    compressionlevel '9'
);

INSERT INTO t5_oss VALUES (generate_series(1, 10000), 0.1, 'hello');
INSERT INTO t5_oss VALUES (generate_series(1, 10000), 0.1, 'hello');
INSERT INTO t5_oss VALUES (generate_series(1, 10000), 0.1, 'hello');

Compressed files have a .gz extension. Compare file sizes against an uncompressed table to see the reduction:

-- Uncompressed
SELECT * FROM oss_fdw_list_file('t4_oss');

                   name                    |  size
-------------------------------------------+--------
 archive_file_list/_t4_oss_741147680906121 | 148894
 archive_file_list/_t4_oss_741147680965631 | 148894
 archive_file_list/_t4_oss_741147681201236 | 148894
(3 rows)

-- Gzip-compressed
SELECT * FROM oss_fdw_list_file('t5_oss');

                         name                          | size
-------------------------------------------------------+-------
 archive_file_compression/_t5_oss_741147752563794.gz   | 23654
 archive_file_compression/_t5_oss_741147752633713.gz   | 23654
 archive_file_compression/_t5_oss_741147752828680.gz   | 23654
(3 rows)

Zstandard

Zstandard compression requires PolarDB for PostgreSQL (Compatible with Oracle) 2.0, revision version 2.0.14.3.0 or later.

Valid compression levels: -7–22. Default: 6.

CREATE FOREIGN TABLE t6_oss (
    id  INT,
    f   FLOAT,
    txt TEXT
)
SERVER ossserver
OPTIONS (
    dir              'archive_file_zstd/',
    compressiontype  'zstd',
    compressionlevel '9'
);

INSERT INTO t6_oss VALUES (generate_series(1, 10000), 0.1, 'hello');
INSERT INTO t6_oss VALUES (generate_series(1, 10000), 0.1, 'hello');
INSERT INTO t6_oss VALUES (generate_series(1, 10000), 0.1, 'hello');

Compressed files have a .zst extension:

SELECT * FROM oss_fdw_list_file('t6_oss');

                   name                    | size
-------------------------------------------+------
 archive_file_zstd/_t6_oss_748106174612293.zst | 6710
 archive_file_zstd/_t6_oss_748106174700206.zst | 6710
 archive_file_zstd/_t6_oss_748106174866829.zst | 6710
(3 rows)

Remove the extension

DROP EXTENSION oss_fdw;