oss_fdw - PolarDB - Alibaba Cloud Documentation Center

This topic describes the overview, limits, and usage of the oss_fdw extension.

Overview

oss_fdw is a foreign data wrapper (FDW) extension of PolarDB for PostgreSQL. It can associate data in Object Storage Service (OSS) with foreign table definitions in PolarDB clusters. This allows you to store data in database tables to OSS while you still can read and write data by executing standard SQL statements.

OSS is a secure, cost-effective, and highly reliable cloud storage service that allows you to store a large amount of data. OSS is designed to provide 99.995% data availability. Historical data, read-only archived data, and cold data in databases are ideal for being stored in OSS to save storage costs.

Prerequisites

OSS is activated and a bucket is created. For more information, see What is OSS?
The oss_fdw extension is supported on the PolarDB for PostgreSQL clusters that run the following engines:
- PostgreSQL 14 (revision version 14.5.3.0 or later)
- PostgreSQL 11 (revision version 1.1.1 or later)
Note
You can execute one of the following statements to view the minor engine version of your PolarDB for PostgreSQL cluster:
- PostgreSQL 14
```
SELECT version();
```
- PostgreSQL 11
```
SHOW polar_version;
```

Limits

oss_fdw foreign tables support only the SELECT, INSERT, and TRUNCATE statements, but not the UPDATE or DELETE statement. Therefore, the extension is only used to archive data: After data is written to OSS, it can only be read, but cannot be updated.

Usage

Install the extension

CREATE EXTENSION oss_fdw;

Create a foreign data server

Configure OSS connection information and create a mapping between PolarDB and the OSS bucket.

Example:

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

Note

Parameters in the statement:

host: the endpoint of OSS.
bucket: the name of the OSS bucket.
id/key: the AccessKey ID and AccessKey secret of your Alibaba Cloud account.

Map a foreign table to an OSS directory

Create an OSS foreign table in PolarDB and map it to the directory for the foreign data source as specified in Create a foreign data server.
```
CREATE FOREIGN TABLE t1_oss (
    id INT,
    f FLOAT,
    txt TEXT
)
SERVER ossserver
OPTIONS (dir 'archive/');
```

Import data to the OSS foreign table.

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

The data inserted into the table is written to the file in the archive/ directory. You can query the foreign table by using the following method:

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)

When the INSERT statement is executed on the table again, the data is written to a new file in the OSS directory.

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)

Execute the TRUNCATE statement to remove all OSS mapping files for the foreign table.

TRUNCATE t1_oss;

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

Map a foreign table to a directory prefix

Create a foreign table by using the prefix option.

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

Multiple files with the same prefix name are created when you execute the INSERT statement on the foreign table.

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)

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

OSS file storage format

oss_fdw allows you to set the format of data stored in OSS. The default value is CSV. You can also explicitly specify the format. When you execute the INSERT statement on the OSS foreign table, data is written in the CSV format to an OSS file.

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

View the files for the OSS foreign table

Create an OSS foreign table and execute the INSERT statement three times to 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');

View the files for the OSS foreign table by using the following function and specifying the table name and schema name of the OSS foreign table (the default value is public):

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

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)

OSS compression

The compressiontype parameter specifies the compression algorithm for writing data to OSS files. This parameter is left empty by default, which indicates that data is not compressed. Valid values: gzip and zstd.

The compressionlevel parameter specifies a compression level. A higher compression level indicates that more CPU is occupied during compression and decompression and that a smaller data volume is transferred over the network and less OSS space is used by the foreign table.

Gzip compression algorithm

Valid values of the compression level for the Gzip compression algorithm: 1 to 9. Default value: 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');

When you view the files of the OSS foreign table, the sizes of Gzip-compressed files are significantly smaller than those of the files that are not compressed.

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 compression algorithm

Note

The Zstandard compression algorithm is supported only on clusters that run PostgreSQL 14 (revision version 14.9.13.0 or later).

Valid values of the compression level for the Zstandard compression algorithm: -7 to 22. Default value: 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');

When you view the files of the OSS foreign table, the sizes of Zstandard-compressed files are significantly smaller than those of the files that are not compressed.

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;