PolarDB:Read and write external data files by using oss_fdw

Prerequisites

• OSS is activated and a bucket is created. For more information, see What is OSS?

• Your PolarDB for PostgreSQL cluster runs one of the following engine 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)

Limitations

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

Foreign table options reference

When you create an OSS foreign table, use the following options to control how data is stored and organized in OSS:

Install the extension

CREATE EXTENSION oss_fdw;

Create a foreign data server

Define the OSS connection and map your PolarDB cluster to an OSS bucket.

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

The server options are:

Map a foreign table to an OSS directory

1. Create an OSS foreign table mapped to a directory.

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

2. Insert data into the foreign table. The data is written to a file in the archive/ directory. Query the foreign table to verify:

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

      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)

3. Each subsequent INSERT creates a new file in the same 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)

4. Run TRUNCATE to remove all OSS files mapped to 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

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. Each INSERT creates a new file with the specified 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;
                                  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)

Specify the storage format

oss_fdw stores data in CSV format by default. Specify the format explicitly with the format option. When you run INSERT on an OSS foreign table, data is written in the specified format to an OSS file.

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

List files for an OSS foreign table

1. Create a foreign table and run three INSERT statements 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');

2. Call oss_fdw_list_file() with the table name and, optionally, the schema name (defaults to public) to list the associated files.

      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)

Compress data with gzip or Zstandard

The compressiontype option specifies the compression algorithm for data written to OSS. By default, no compression is applied. Valid values: gzip and zstd.

The compressionlevel option sets the compression level. Higher levels produce smaller files and reduce network transfer volume, but consume more CPU during compression and decompression.

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

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)

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

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;