Use partitioned foreign tables in AnalyticDB for PostgreSQL V7.0-AnalyticDB(AnalyticDB)-阿里云帮助中心

AnalyticDB for PostgreSQL V7.0 supports partitioned foreign tables backed by Object Storage Service (OSS). When a query's WHERE clause filters on a partition column, the database skips non-matching partitions and scans only the relevant data. This partition pruning significantly reduces the amount of data read from OSS and improves query performance.

V7.0 expands on V6.0 in two ways: it adds RANGE and HASH partitioning (V6.0 supports only LIST partitioning), and it uses a more concise DDL syntax.

Prerequisites

Before you begin, make sure you have:

An OSS server created in AnalyticDB for PostgreSQL. See Create an OSS server
A user mapping to the OSS server. See Create a user mapping to the OSS server

How it works

Each partitioned foreign table maps to a specific OSS path prefix defined by the dir option. When you create a partition, you specify both its partition bound and its OSS directory. For example, a RANGE partition covering January 2023 might map to ossfdw_parttable/202301/ in OSS. All objects under that prefix belong to that partition.

Your OSS directory structure must match your partition definitions. A typical layout for monthly date partitions looks like:

ossfdw_parttable/
├── 202301/    ← partition ossfdw_parttable_pt_202301
├── 202302/    ← partition ossfdw_parttable_pt_202302
└── ...

For two-level partitions, nest the directories:

ossfdw_parttable/
├── 202301/
│   ├── hangzhou/    ← ossfdw_parttable_pt_202301_hangzhou
│   └── beijing/     ← ossfdw_parttable_pt_202301_beijing
└── 202302/
    ├── hangzhou/
    └── beijing/

Create a parent table

Before creating partitioned foreign tables, create an empty partitioned internal table to serve as the parent. This table defines the schema and partitioning strategy but holds no data itself.

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

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

The following examples create a parent table named ossfdw_parttable with pt as the partition column.

RANGE partitioning:

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

LIST partitioning:

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

HASH partitioning:

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

Create partitioned foreign tables

Each partitioned foreign table is a child of the parent table and maps to an OSS directory. Two syntax forms are supported:

Form 1 — column list with INHERITS:

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' [, ... ] ) ]

Form 2 — PARTITION OF (most common):

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' [, ... ] ) ]

Where column_constraint is:

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

Where table_constraint is:

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

Where partition_bound_spec is:

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

Examples

All examples use PARTITION OF to create child foreign tables under the ossfdw_parttable parent. The dir option specifies the OSS path prefix for each partition.

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

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

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

Query with partition pruning

After creating the partitioned foreign tables, filter on the partition column in a WHERE clause. AnalyticDB for PostgreSQL scans only the matching partitions and skips the rest:

SELECT key, value
FROM ossfdw_parttable
WHERE pt >= '2023-01-01' AND pt < '2023-02-01';

This query reads only the objects under ossfdw_parttable/202301/ in OSS, skipping all other partitions.

Create two-level partitioned foreign tables

Two-level partitioning lets you partition data along two dimensions — for example, by month and then by region. The lowest-level partitions must be foreign tables; the parent and intermediate levels use internal table syntax.

The workflow is:

Create the top-level parent table (CREATE TABLE).
Create level-1 internal partitioned tables (CREATE TABLE ... PARTITION OF).
Create level-2 foreign tables (CREATE FOREIGN TABLE ... PARTITION OF).

The following example partitions first by pt (month), then by region.

Step 1: Create the parent table.

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

Step 2: Create level-1 internal partitioned tables.

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

Step 3: Create level-2 foreign tables.

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

Manage partitions

Modify FDW options

Use ALTER FOREIGN TABLE to change the FDW options of a partitioned foreign table:

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 supports the following operations:

    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'] [, ... ])

Example: disable the error log for a partition

Check the current FDW options for the partition:

\d+ ossfdw_parttable_pt_202302_beijing

The output shows log_errors 'on':

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

Set log_errors to off:

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

Confirm the change:

\d+ ossfdw_parttable_pt_202302_beijing

The output now shows log_errors 'off':

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 partition

Detach a child partitioned table from its parent using the same syntax as for internal partitioned tables:

ALTER TABLE <table_name1>
DETACH PARTITION <table_name2>;

Parameter	Description
`table_name1`	The parent table
`table_name2`	The child partitioned table to detach

Example: detach `ossfdw_parttable_pt_202302_beijing` from `ossfdw_parttable_pt_202302`

Check the current partitions:

\d+ ossfdw_parttable_pt_202302

The output shows two child partitions, beijing and hangzhou:

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 beijing partition:

ALTER TABLE ossfdw_parttable_pt_202302 DETACH PARTITION ossfdw_parttable_pt_202302_beijing;

Confirm the result:

\d+ ossfdw_parttable_pt_202302

Only hangzhou remains:

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 partition

Attach a table to a partitioned table as a child partition:

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

Example: attach `ossfdw_parttable_pt_202302_beijing` back to `ossfdw_parttable_pt_202302`

Check the current partitions:

\d+ ossfdw_parttable_pt_202302

Only hangzhou exists:

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 beijing:

ALTER TABLE ossfdw_parttable_pt_202302 ATTACH PARTITION ossfdw_parttable_pt_202302_beijing FOR VALUES IN ('beijing');

Confirm the result:

\d+ ossfdw_parttable_pt_202302

Both beijing and hangzhou are now present:

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

Drop a partition

Drop a partitioned foreign table with DROP FOREIGN TABLE:

DROP FOREIGN TABLE <table_name>;

Example: drop `ossfdw_parttable_pt_202301_hangzhou`

Check the current partitions:

\d+ ossfdw_parttable_pt_202301

Two child partitions exist — beijing and hangzhou:

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'),
            ossfdw_parttable_pt_202301_hangzhou FOR VALUES IN ('hangzhou')
Distributed by: (key)
Access method: heap

Drop the hangzhou partition:

DROP FOREIGN TABLE ossfdw_parttable_pt_202301_hangzhou;

Confirm the result:

\d+ ossfdw_parttable_pt_202301

Only beijing remains:

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

Prerequisites

How it works

Create a parent table

Create partitioned foreign tables

Examples

Query with partition pruning

Create two-level partitioned foreign tables

Manage partitions

Modify FDW options

Detach a partition

Attach a partition

Drop a partition

What's next