oss_fdw (OSS foreign data wrapper)

Overview

The oss_fdw extension is a foreign data wrapper (FDW) for PolarDB for PostgreSQL. It maps data in Alibaba Cloud Object Storage Service (OSS) to foreign tables in the database, allowing you to read and write OSS data through standard SQL.

Alibaba Cloud OSS is a massive, secure, cost-effective, and highly reliable cloud storage service that provides up to 99.995% service availability. It offers multiple storage types to help optimize storage costs. Historical data, read-only archive data, and cold data in databases are well suited for storage in OSS to reduce database storage costs.

Prerequisites

• Alibaba Cloud OSS is activated and a bucket is created. For more information, see Alibaba Cloud OSS.

• Supported PolarDB for PostgreSQL versions:

  • PostgreSQL 16 (revision version 2.0.16.6.2.0 or later)

  • PostgreSQL 14 (revision version 2.0.14.5.3.0 or later)

  • PostgreSQL 11 (revision version 2.0.11.2.1.0 or later)

  Note

  You can view the revision version in the console, or run the SHOW polardb_version; statement to check it. If the revision version does not meet the requirements, upgrade the revision version.

Limitations

oss_fdw foreign tables support only SELECT, INSERT, and TRUNCATE operations. UPDATE and DELETE operations are not supported. Therefore, oss_fdw is suitable only for data archiving scenarios: after data is written to OSS, it is read only and is not updated.

Usage

Install the extension

CREATE EXTENSION oss_fdw;

Create a foreign data server

Configure OSS connection information and create a mapping from the PolarDB to an OSS bucket.

Example:

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

Map a foreign table to an OSS directory

1. Create an OSS foreign table in the PolarDB and map it to a directory of the OSS data source configured in Create a foreign data server.

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

2. Import data into the OSS foreign table.

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

   The inserted data is written to files in the archive/ path in OSS. You can query the foreign table as follows.

   • EXPLAIN SELECT COUNT(*) FROM t1_oss;

     Output:

                                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;

     Output:

      count
     -------
        100
     (1 row)

   When you run INSERT on the table again, a new file is created in the same OSS path.

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

   • Query the table data:

     • EXPLAIN SELECT COUNT(*) FROM t1_oss;

       Output:

                                   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;

       Output:

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

   When you run the TRUNCATE command on the table, all mapped files in OSS are removed.

   TRUNCATE t1_oss;

   Query the table data after removal.

   SELECT COUNT(*) FROM t1_oss;

   Output:

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

Map a foreign table to a path prefix

1. Create a foreign table with the prefix option.

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

2. Multiple inserts on this foreign table produce multiple files with the same prefix.

   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;

   Output:

                               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)

   Query the table data:

   SELECT COUNT(*) FROM t2_oss;

   Output:

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

OSS file storage format

oss_fdw supports setting the data storage format in OSS. The default value is CSV. You can also explicitly declare the desired format. When you run INSERT on an OSS foreign table, data is written to OSS files in CSV format.

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

View files corresponding to an OSS foreign table

1. Create an OSS foreign table and run three INSERT statements. Three OSS files are written.

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

2. Use the following function to query files corresponding to the OSS foreign table. Specify the table name and optionally the schema name (defaults to public).

   • Query files corresponding to the OSS foreign table using the default schema name.

     SELECT * FROM oss_fdw_list_file('t4_oss');

     Output:

                        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)

   • Specify a schema name to query files corresponding to the OSS foreign table.

     SELECT * FROM oss_fdw_list_file('t4_oss', 'public');

     Output:

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

You can use the compressiontype parameter to specify the compression algorithm for writing OSS files. The default is empty, which means no compression. Valid values: gzip or zstd.

You can use the compressionlevel parameter to select the compression level. A higher compression level consumes more CPU during compression and decompression. At the same time, the network transfer bytes and the storage space used by the foreign table data in OSS are reduced.

Gzip compression

The Gzip compression level ranges from 1 to 9, with a default of 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');

View the files corresponding to the OSS foreign table. You can see that the file sizes after Gzip compression are significantly smaller than those of the uncompressed foreign table.

• Foreign table file sizes without Gzip compression:

  SELECT * FROM oss_fdw_list_file('t4_oss');

  Output:

                     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)

• Foreign table file sizes after Gzip compression:

  SELECT * FROM oss_fdw_list_file('t5_oss');

  Output:

                          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

The Zstandard compression level ranges from -7 to 22, with a default of 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');

View the files corresponding to the OSS foreign table. You can see that the file sizes after Zstandard compression are significantly smaller than those of the uncompressed foreign table.

• Foreign table file sizes without Zstandard compression:

  SELECT * FROM oss_fdw_list_file('t4_oss');

  Output:

                     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)

• Foreign table file sizes after Zstandard compression:

  SELECT * FROM oss_fdw_list_file('t6_oss');

  Output:

                       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;