Archive database data to Object Storage Service for SQL queries-oss_fdw - PolarDB - Alibaba Cloud - PolarDB

oss_fdw is a foreign data wrapper (FDW) extension for PolarDB for Oracle that maps Object Storage Service (OSS) data to foreign tables in your PolarDB cluster. Once mapped, read and write data using standard SQL — no custom ETL pipelines needed.

OSS provides 99.995% data availability and is suited for historical data, read-only archived data, and cold data you want to offload from your database to reduce storage costs.

Limitations

oss_fdw foreign tables support the following SQL operations:

Operation	Supported
`SELECT`	Yes
`INSERT`	Yes
`TRUNCATE`	Yes
`UPDATE`	No
`DELETE`	No

Because UPDATE and DELETE are not supported, oss_fdw is intended for append-only archival: once data is written to OSS, it can be read but not modified.

Additional constraints:

Zstandard compression requires PostgreSQL 14 (revision version 14.9.13.0 or later).
Each INSERT execution creates a new file in the OSS directory — existing files are never updated.
TRUNCATE removes all OSS files mapped to the foreign table.

Prerequisites

Before you begin, ensure that you have:

An active OSS bucket. For setup instructions, see What is OSS?
The AccessKey ID and AccessKey Secret of your Alibaba Cloud account

Install the extension

CREATE EXTENSION oss_fdw;

Create a foreign server

Create a foreign server to define the OSS connection for your PolarDB cluster.

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

Foreign server parameters:

Parameter	Description
`host`	OSS endpoint URL
`bucket`	OSS bucket name
`id`	AccessKey ID of your Alibaba Cloud account
`key`	AccessKey Secret of your Alibaba Cloud account

Create a foreign table

A foreign table maps a PolarDB table definition to an OSS directory or file prefix. Use the dir option to map to a directory, or the prefix option to group files by a shared name prefix.

Foreign table options:

Option	Description	Required
`dir`	OSS directory path for the foreign table	One of `dir` or `prefix`
`prefix`	File name prefix; all matching files are included in queries	One of `dir` or `prefix`
`format`	File format. Default: `csv`	No
`compressiontype`	Compression algorithm: `gzip` or `zstd`. Default: no compression	No
`compressionlevel`	Compression level. Gzip: 1–9 (default 6). Zstandard: -7–22 (default 6)	No

Map to a directory

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

Insert data — each INSERT writes to a new file under archive/:

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

Query the table as you would any regular table:

EXPLAIN SELECT COUNT(*) FROM t1_oss;
                           QUERY PLAN
-----------------------------------------------------------------
 Aggregate  (cost=6.54..6.54 rows=1 width=8)
   ->  Foreign Scan on t1_oss  (cost=0.00..6.40 rows=54 width=0)
         Directory on OSS: archive/
         Number Of OSS file: 1
         Total size of OSS file: 1292 bytes
(5 rows)

SELECT COUNT(*) FROM t1_oss;
 count
-------
   100
(1 row)

Running INSERT again creates a second file in the same directory:

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

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 files mapped to a foreign table, use TRUNCATE:

TRUNCATE t1_oss;
TRUNCATE TABLE

SELECT COUNT(*) FROM t1_oss;
WARNING:  does not match any file in oss
 count
-------
     0
(1 row)

Map to a file prefix

Use the prefix option when you want to group files by a shared path prefix rather than a directory.

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

Each INSERT creates a new file whose name starts with prefix/file_:

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

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

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)

SELECT COUNT(*) FROM t2_oss;
 count
-------
   200
(1 row)

Configure file format

The default file format is CSV. Specify it explicitly with the format option:

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

List files for a foreign table

Use oss_fdw_list_file() to inspect the OSS files associated with a foreign table.

Create a foreign table and insert data across multiple INSERT executions:

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. Pass the table name alone, or include the schema name (default: public):

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)

SELECT * FROM oss_fdw_list_file('t4_oss', 'public');
                   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)

Configure compression

Use compressiontype and compressionlevel to compress data written to OSS. Higher compression levels reduce file size and network transfer volume at the cost of more CPU usage during reads and writes.

Parameter	Description	Valid values	Default
`compressiontype`	Compression algorithm	`gzip`, `zstd`	None (no compression)
`compressionlevel`	Compression level	Gzip: 1–9 / Zstandard: -7–22	6

Gzip

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');

Comparing file sizes confirms the compression ratio:

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)

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 PostgreSQL 14 (revision version 14.9.13.0 or later).

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');

Zstandard achieves significantly smaller file sizes compared to uncompressed data:

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)

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;