Use partitioned foreign tables in AnalyticDB for PostgreSQL V7.0 - AnalyticDB

AnalyticDB for PostgreSQL V7.0 supports partitioned foreign tables. If the WHERE clause of an SQL statement contains the partition column of a partitioned table, the amount of data that must be scanned is significantly reduced. This helps improve query performance.

Differences of partitioned foreign tables between AnalyticDB for PostgreSQL V6.0 and V7.0

AnalyticDB for PostgreSQL V6.0 supports only LIST partitioning. AnalyticDB for PostgreSQL V7.0 supports LIST partitioning, RANGE partitioning, and HASH partitioning.
The syntax of partitioned foreign tables in AnalyticDB for PostgreSQL V7.0 is more concise than the syntax in AnalyticDB for PostgreSQL V6.0.

Create an OSS server and a user mapping to the OSS server

Before you use OSS foreign tables, you must create an OSS server and a user mapping to the OSS server.

For more information about how to create an OSS server, see the "Create an OSS server" section of the Use OSS foreign tables for data lake analysis topic.
For more information about how to create a user mapping to the OSS server, see the " Create a user mapping to the OSS server" section of the Use OSS foreign tables for data lake analysis topic.

Create a parent table for a partitioned foreign table

Before you create a partitioned foreign table in AnalyticDB for PostgreSQL V7.0, you must create an empty partitioned table of a specific partitioning type as the parent table of the partitioned foreign table. Syntax:

CREATE TABLE <table_name> ( <column1> <data_type>, <column2> <data_type>, ...)
...
PARTITION BY { RANGE | LIST | HASH } ( <column_name> | <expression>);

For more information about partitioned tables, see Define table partitioning.

For example, create a partitioned table named ossfdw_parttable. The pt parameter specifies the partition column.

RANGE partitioning

CREATE TABLE ossfdw_parttable(
    key TEXT,
    value BIGINT,
    pt TEXT,
    retion TEXT
)
DISTRIBUTED BY (key)
PARTITION BY RANGE(pt);

HASH partitioning

CREATE TABLE ossfdw_parttable(
    key TEXT,
    value BIGINT,
    pt TEXT,
    retion TEXT
)
DISTRIBUTED BY (key)
PARTITION BY HASH(pt);

LIST partitioning

CREATE TABLE ossfdw_parttable(
    key TEXT,
    value BIGINT,
    pt TEXT,
    retion TEXT
)
DISTRIBUTED BY (key)
PARTITION BY LIST(pt);

Create a partitioned foreign table

CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name ( [
  { column_name data_type [ OPTIONS ( option 'value' [, ... ] ) ] [ COLLATE collation ] [ column_constraint [ ... ] ]
    | table_constraint }
    [, ... ]
] )
[ INHERITS ( parent_table [, ... ] ) ]
  SERVER server_name
[ OPTIONS ( option 'value' [, ... ] ) ]

CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name
  PARTITION OF parent_table [ (
  { column_name [ WITH OPTIONS ] [ column_constraint [ ... ] ]
    | table_constraint }
    [, ... ]
) ]
{ FOR VALUES partition_bound_spec | DEFAULT }
  SERVER server_name
[ OPTIONS ( option 'value' [, ... ] ) ]

Syntax of column_constraint:

[ CONSTRAINT constraint_name ]
{ NOT NULL |
  NULL |
  CHECK ( expression ) [ NO INHERIT ] |
  DEFAULT default_expr |
  GENERATED ALWAYS AS ( generation_expr ) STORED }

Syntax of table_constraint:

[ CONSTRAINT constraint_name ]
CHECK ( expression ) [ NO INHERIT ]

Syntax of partition_bound_spec:

IN ( partition_bound_expr [, ...] ) |
FROM ( { partition_bound_expr | MINVALUE | MAXVALUE } [, ...] )
  TO ( { partition_bound_expr | MINVALUE | MAXVALUE } [, ...] ) |
WITH ( MODULUS numeric_literal, REMAINDER numeric_literal )

For example, create partitioned foreign tables in a parent partitioned table.

RANGE partitioning

CREATE FOREIGN TABLE ossfdw_parttable_pt_202301
 PARTITION OF ossfdw_parttable
 FOR VALUES FROM ('2023-01-01') TO ('2023-02-01')
 SERVER oss_serv
 OPTIONS (log_errors 'true', segment_reject_limit '10', dir 'ossfdw_parttable/202301/', format 'csv', DELIMITER '|');

CREATE FOREIGN TABLE ossfdw_parttable_pt_202302
 PARTITION OF ossfdw_parttable
 FOR VALUES FROM ('2023-02-01') TO ('2023-03-01')
 SERVER oss_serv
 OPTIONS (log_errors 'true', segment_reject_limit '10', dir 'ossfdw_parttable/202302/', format 'csv', DELIMITER '|');

HASH partitioning

CREATE FOREIGN TABLE ossfdw_parttable_pt_202301
 PARTITION OF ossfdw_parttable
 FOR VALUES WITH (modulus 8, remainder 0)
 SERVER oss_serv
 OPTIONS (log_errors 'true', segment_reject_limit '10', dir 'ossfdw_parttable/202301/', format 'csv', DELIMITER '|');

CREATE FOREIGN TABLE ossfdw_parttable_pt_202302
 PARTITION OF ossfdw_parttable
 FOR VALUES WITH (modulus 8, remainder 0)
 SERVER oss_serv
 OPTIONS (log_errors 'true', segment_reject_limit '10', dir 'ossfdw_parttable/202302/', format 'csv', DELIMITER '|');

LIST partitioning

CREATE FOREIGN TABLE ossfdw_parttable_pt_202301
 PARTITION OF ossfdw_parttable
 FOR VALUES IN ('2023-01')
 SERVER oss_serv
 OPTIONS (log_errors 'true', segment_reject_limit '10', dir 'ossfdw_parttable/202301/', format 'csv', DELIMITER '|');

CREATE FOREIGN TABLE ossfdw_parttable_pt_202302
 PARTITION OF ossfdw_parttable
 FOR VALUES IN ('2023-02')
 SERVER oss_serv
 OPTIONS (log_errors 'true', segment_reject_limit '10', dir 'ossfdw_parttable/202302/', format 'csv', DELIMITER '|');

Create a level-2 partitioned foreign table

Level-2 partitioned foreign tables can be created by using the same syntax as level-1 partitioned foreign tables. Make sure that you specify the lowest-level partition as a foreign table. Use the syntax of partitioned internal tables to create a parent table and a level-1 partitioned table, and use the syntax of partitioned foreign tables to create a level-2 partitioned table.

For information about the syntax of partitioned internal tables, see Define table partitioning. For information about the syntax of partitioned foreign tables, see the "Create a partitioned foreign table" section of this topic.

For example, perform the following steps to create a LIST partitioned table:

Create a parent LIST partitioned table.

CREATE TABLE ossfdw_parttable(
    key TEXT,
    value BIGINT,
    pt TEXT,
    region TEXT
)
DISTRIBUTED BY (key)
PARTITION BY LIST(pt);

Create a level-1 partitioned table.

CREATE TABLE ossfdw_parttable_pt_202301
 PARTITION OF ossfdw_parttable
 FOR VALUES IN ('2023-01')
 PARTITION BY LIST(region);

CREATE TABLE ossfdw_parttable_pt_202302
 PARTITION OF ossfdw_parttable
 FOR VALUES IN ('2023-02')
 PARTITION BY LIST(region);

Create a level-2 partitioned table.

CREATE FOREIGN TABLE ossfdw_parttable_pt_202301_hangzhou
 PARTITION OF ossfdw_parttable_pt_202301
 FOR VALUES IN ('hangzhou')
 SERVER oss_serv
 OPTIONS (log_errors 'true', segment_reject_limit '10', dir 'ossfdw_parttable/202301/hangzhou/', format 'csv', DELIMITER '|');

CREATE FOREIGN TABLE ossfdw_parttable_pt_202301_beijing
 PARTITION OF ossfdw_parttable_pt_202301
 FOR VALUES IN ('beijing')
 SERVER oss_serv
 OPTIONS (log_errors 'true', segment_reject_limit '10', dir 'ossfdw_parttable/202301/beijing/', format 'csv', DELIMITER '|');

CREATE FOREIGN TABLE ossfdw_parttable_pt_202302_hangzhou
 PARTITION OF ossfdw_parttable_pt_202302
 FOR VALUES IN ('hangzhou')
 SERVER oss_serv
 OPTIONS (log_errors 'true', segment_reject_limit '10', dir 'ossfdw_parttable/202302/hangzhou/', format 'csv', DELIMITER '|');

CREATE FOREIGN TABLE ossfdw_parttable_pt_202302_beijing
 PARTITION OF ossfdw_parttable_pt_202302
 FOR VALUES IN ('beijing')
 SERVER oss_serv
 OPTIONS (log_errors 'true', segment_reject_limit '10', dir 'ossfdw_parttable/202302/beijing/', format 'csv', DELIMITER '|');

Modify partitions

ALTER FOREIGN TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
    action [, ... ]
ALTER FOREIGN TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
    RENAME [ COLUMN ] column_name TO new_column_name
ALTER FOREIGN TABLE [ IF EXISTS ] name
    RENAME TO new_name
ALTER FOREIGN TABLE [ IF EXISTS ] name
    SET SCHEMA new_schema

The action parameter specifies one of the following actions:

    ADD [ COLUMN ] column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]
    DROP [ COLUMN ] [ IF EXISTS ] column_name [ RESTRICT | CASCADE ]
    ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type [ COLLATE collation ]
    ALTER [ COLUMN ] column_name SET DEFAULT expression
    ALTER [ COLUMN ] column_name DROP DEFAULT
    ALTER [ COLUMN ] column_name { SET | DROP } NOT NULL
    ALTER [ COLUMN ] column_name SET STATISTICS integer
    ALTER [ COLUMN ] column_name SET ( attribute_option = value [, ... ] )
    ALTER [ COLUMN ] column_name RESET ( attribute_option [, ... ] )
    ALTER [ COLUMN ] column_name SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
    ALTER [ COLUMN ] column_name OPTIONS ( [ ADD | SET | DROP ] option ['value'] [, ... ])
    ADD table_constraint [ NOT VALID ]
    VALIDATE CONSTRAINT constraint_name
    DROP CONSTRAINT [ IF EXISTS ]  constraint_name [ RESTRICT | CASCADE ]
    DISABLE TRIGGER [ trigger_name | ALL | USER ]
    ENABLE TRIGGER [ trigger_name | ALL | USER ]
    ENABLE REPLICA TRIGGER trigger_name
    ENABLE ALWAYS TRIGGER trigger_name
    SET WITHOUT OIDS
    INHERIT parent_table
    NO INHERIT parent_table
    OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
    OPTIONS ( [ ADD | SET | DROP ] option ['value'] [, ... ])

For example, perform the following steps to disable the error log feature for a partitioned foreign table:

Run the \d+ command on psql to query the information about the partitioned foreign table.

\d+ ossfdw_parttable_pt_202302_beijing

The value of the log_errors parameter is on. Sample output:

                       Foreign table "public.ossfdw_parttable_pt_202302_beijing"
 Column |  Type  | Collation | Nullable | Default | FDW options | Storage  | Stats target | Description
--------+--------+-----------+----------+---------+-------------+----------+--------------+-------------
 key    | text   |           |          |         |             | extended |              |
 value  | bigint |           |          |         |             | plain    |              |
 pt     | text   |           |          |         |             | extended |              |
 region | text   |           |          |         |             | extended |              |
Partition of: ossfdw_parttable_pt_202302 FOR VALUES IN ('beijing')
Partition constraint: ((pt IS NOT NULL) AND (pt = '2023-02'::text) AND (region IS NOT NULL) AND (region = 'beijing'::text))
Server: oss_serv
FDW options: (log_errors 'on', segment_reject_limit '10', dir 'ossfdw_parttable/202302/beijing/', format 'csv', delimiter '|')

Change the value of the log_errors parameter to off.

ALTER FOREIGN TABLE ossfdw_parttable_pt_202302_beijing OPTIONS(SET log_errors 'off');

Run the \d+ command on psql to query the information about the partitioned foreign table again.

\d+ ossfdw_parttable_pt_202302_beijing

The value of the log_errors parameter is changed to off. Sample output:

                       Foreign table "public.ossfdw_parttable_pt_202302_beijing"
 Column |  Type  | Collation | Nullable | Default | FDW options | Storage  | Stats target | Description
--------+--------+-----------+----------+---------+-------------+----------+--------------+-------------
 key    | text   |           |          |         |             | extended |              |
 value  | bigint |           |          |         |             | plain    |              |
 pt     | text   |           |          |         |             | extended |              |
 region | text   |           |          |         |             | extended |              |
Partition of: ossfdw_parttable_pt_202302 FOR VALUES IN ('beijing')
Partition constraint: ((pt IS NOT NULL) AND (pt = '2023-02'::text) AND (region IS NOT NULL) AND (region = 'beijing'::text))
Server: oss_serv
FDW options: (log_errors 'off', segment_reject_limit '10', dir 'ossfdw_parttable/202302/beijing/', format 'csv', delimiter '|')

Detach a partitioned foreign table from the parent table

Partitioned foreign tables can be detached by using the same syntax as partitioned tables. You can detach a child partitioned table from the parent table. Syntax:

ALTER TABLE <table_name1>
DETACH PARTITION <table_name2>;

The following table describes the parameters.

Parameter	Description
`table_name1`	The parent table.
`table_name2`	The child partitioned table that you want to detach.

For example, a partitioned table named ossfdw_parttable_pt_202302 contains two child partitioned tables. To detach the ossfdw_parttable_pt_202302_beijing table from the partitioned table, perform the following steps:

Run the \d+ command on psql to query the information about the partitioned table.

\d+ ossfdw_parttable_pt_202302

The partitioned table contains two child partitioned tables named beijing and hangzhou. Sample output:

                  Partitioned table "public.ossfdw_parttable_pt_202302"
 Column |  Type  | Collation | Nullable | Default | Storage  | Stats target | Description
--------+--------+-----------+----------+---------+----------+--------------+-------------
 key    | text   |           |          |         | extended |              |
 value  | bigint |           |          |         | plain    |              |
 pt     | text   |           |          |         | extended |              |
 region | text   |           |          |         | extended |              |
Partition of: ossfdw_parttable FOR VALUES IN ('2023-02')
Partition constraint: ((pt IS NOT NULL) AND (pt = '2023-02'::text))
Partition key: LIST (region)
Partitions: ossfdw_parttable_pt_202302_beijing FOR VALUES IN ('beijing'),
            ossfdw_parttable_pt_202302_hangzhou FOR VALUES IN ('hangzhou')
Distributed by: (key)
Access method: heap

Detach the ossfdw_parttable_pt_202302_beijing table from the ossfdw_parttable_pt_202302 table.

ALTER TABLE ossfdw_parttable_pt_202302 DETACH PARTITION ossfdw_parttable_pt_202302_beijing;

Run the \d+ command on psql to query the information about the partitioned table again.

\d+ ossfdw_parttable_pt_202302

The partitioned table contains only one child partitioned table named hangzhou. Sample output:

                  Partitioned table "public.ossfdw_parttable_pt_202302"
 Column |  Type  | Collation | Nullable | Default | Storage  | Stats target | Description
--------+--------+-----------+----------+---------+----------+--------------+-------------
 key    | text   |           |          |         | extended |              |
 value  | bigint |           |          |         | plain    |              |
 pt     | text   |           |          |         | extended |              |
 region | text   |           |          |         | extended |              |
Partition of: ossfdw_parttable FOR VALUES IN ('2023-02')
Partition constraint: ((pt IS NOT NULL) AND (pt = '2023-02'::text))
Partition key: LIST (region)
Partitions: ossfdw_parttable_pt_202302_hangzhou FOR VALUES IN ('hangzhou')
Distributed by: (key)
Access method: heap

Attach a table to a partitioned table as a child partitioned foreign table

Tables can be attached to a partitioned table as child partitioned foreign tables or child partitioned tables by using the same syntax. You can attach a table to a partitioned table as a child partitioned table or a default partition. Syntax:

ALTER TABLE <table_name1>
ATTACH PARTITION <table_name2>
{ FOR VALUES <partition_bound> | DEFAULT };

For example, perform the following steps to attach the ossfdw_parttable_pt_202302_beijing table to a partitioned table named ossfdw_parttable_pt_202302 as a child partitioned table:

Run the \d+ command on psql to query the information about the partitioned table.

\d+ ossfdw_parttable_pt_202302

The partitioned table contains only one child partitioned table named hangzhou. Sample output:

                  Partitioned table "public.ossfdw_parttable_pt_202302"
 Column |  Type  | Collation | Nullable | Default | Storage  | Stats target | Description
--------+--------+-----------+----------+---------+----------+--------------+-------------
 key    | text   |           |          |         | extended |              |
 value  | bigint |           |          |         | plain    |              |
 pt     | text   |           |          |         | extended |              |
 region | text   |           |          |         | extended |              |
Partition of: ossfdw_parttable FOR VALUES IN ('2023-02')
Partition constraint: ((pt IS NOT NULL) AND (pt = '2023-02'::text))
Partition key: LIST (region)
Partitions: ossfdw_parttable_pt_202302_hangzhou FOR VALUES IN ('hangzhou')
Distributed by: (key)
Access method: heap

Attach the ossfdw_parttable_pt_202302_beijing table to the ossfdw_parttable_pt_202302 partitioned table as a child partitioned table. Specify beijing in the FOR VALUES clause.
```
ALTER TABLE ossfdw_parttable_pt_202302 ATTACH PARTITION ossfdw_parttable_pt_202302_beijing FOR VALUES IN ('beijing');
```

Run the \d+ command on psql to query the information about the partitioned table again.

\d+ ossfdw_parttable_pt_202302

The partitioned table contains two child partitioned tables named beijing and hangzhou. Sample output:

                  Partitioned table "public.ossfdw_parttable_pt_202302"
 Column |  Type  | Collation | Nullable | Default | Storage  | Stats target | Description
--------+--------+-----------+----------+---------+----------+--------------+-------------
 key    | text   |           |          |         | extended |              |
 value  | bigint |           |          |         | plain    |              |
 pt     | text   |           |          |         | extended |              |
 region | text   |           |          |         | extended |              |
Partition of: ossfdw_parttable FOR VALUES IN ('2023-02')
Partition constraint: ((pt IS NOT NULL) AND (pt = '2023-02'::text))
Partition key: LIST (region)
Partitions: ossfdw_parttable_pt_202302_beijing FOR VALUES IN ('beijing'),
            ossfdw_parttable_pt_202302_hangzhou FOR VALUES IN ('hangzhou')
Distributed by: (key)
Access method: heap

Delete a partitioned foreign table

You can execute the DROP FOREIGN TABLE statement to delete a partitioned foreign table. Syntax:

DROP FOREIGN TABLE <table_name>;

For example, perform the following steps to delete a child partitioned table named ossfdw_parttable_pt_202301_hangzhou:

Run the \d+ command on psql to query the information about the partitioned table.

\d+ ossfdw_parttable_pt_202301

The partitioned table contains two child partitioned tables named beijing and hangzhou. Sample output:

                  Partitioned table "public.ossfdw_parttable_pt_202301"
 Column |  Type  | Collation | Nullable | Default | Storage  | Stats target | Description
--------+--------+-----------+----------+---------+----------+--------------+-------------
 key    | text   |           |          |         | extended |              |
 value  | bigint |           |          |         | plain    |              |
 pt     | text   |           |          |         | extended |              |
 region | text   |           |          |         | extended |              |
Partition of: ossfdw_parttable FOR VALUES IN ('2023-02')
Partition constraint: ((pt IS NOT NULL) AND (pt = '2023-02'::text))
Partition key: LIST (region)
Partitions: ossfdw_parttable_pt_202302_beijing FOR VALUES IN ('beijing'),
            ossfdw_parttable_pt_202302_hangzhou FOR VALUES IN ('hangzhou')
Distributed by: (key)
Access method: heap

Delete the ossfdw_parttable_pt_202301_hangzhou child partitioned table.
```
DROP FOREIGN TABLE ossfdw_parttable_pt_202301_hangzhou;
```

Run the \d+ command on psql to query the information about the partitioned table again.

\d+ ossfdw_parttable_pt_202301

The partitioned table contains only one child partitioned table named beijing. Sample output:

                  Partitioned table "public.ossfdw_parttable_pt_202301"
 Column |  Type  | Collation | Nullable | Default | Storage  | Stats target | Description
--------+--------+-----------+----------+---------+----------+--------------+-------------
 key    | text   |           |          |         | extended |              |
 value  | bigint |           |          |         | plain    |              |
 pt     | text   |           |          |         | extended |              |
 region | text   |           |          |         | extended |              |
Partition of: ossfdw_parttable FOR VALUES IN ('2023-01')
Partition constraint: ((pt IS NOT NULL) AND (pt = '2023-01'::text))
Partition key: LIST (region)
Partitions: ossfdw_parttable_pt_202301_beijing FOR VALUES IN ('beijing')
Distributed by: (key)
Access method: heap