Open search page
Hello, I’m DocuDroid!
Open Ask AI page
Submitting feedback
Thank you for rating our AI Search!
We would be grateful if you could share your thoughts so we can improve our AI Search for you and other readers.
GitHub

Partitioning: classic syntax

Andrey Aksenov

Table partitioning is a technique used to manage very large tables — such as fact tables — by dividing them into smaller pieces called partitions. This approach can significantly improve query performance, as the Greengage DB optimizer can target only the relevant partitions instead of scanning the entire table.

Partitioning is defined at table creation by specifying a partition key: one or more columns used to determine how the data is divided across partitions.

TIP

Starting with version 7, Greengage DB also supports declarative partitioning syntax derived from PostgreSQL. Declarative partitioning is recommended for new partitioned table definitions because it provides greater flexibility. Learn more in the Partitioning topic.

Introduction to table partitioning

Table partitioning overview

Partitioning and table distribution serve different purposes in Greengage DB. Partitioning divides a large table into smaller pieces to improve query performance and simplify maintenance tasks such as archiving or dropping old data. Distribution, on the other hand, determines how data (in both partitioned and non-partitioned tables) is spread across segments to support parallel query execution. Partitioning does not affect data distribution across segments.

Tables are partitioned at creation time using the PARTITION BY clause, optionally followed by SUBPARTITION BY for multi-level partitioning. Partitioning creates a top-level (or parent) table with one or more levels of sub-tables (or child tables). Internally, Greengage DB creates an inheritance relationship between the top-level table and its underlying partitions, similar to the functionality of the INHERITS clause.

Greengage DB uses the partitioning criteria defined during table creation to generate individual partitions. Each partition is created with a distinct CHECK constraint that specifies the range of data it can contain. The query optimizer uses these constraints to determine which table partitions to scan to satisfy a given query predicate.

The following example shows a single-level partitioned sales table with monthly partitions for the first quarter of 2025. Each partition corresponds to a specific date range defined by the partitioning rules:

sales
   ├─── sales_1_prt_jan_2025    (date >= '2025-01-01' AND date < '2025-02-01')
   ├─── sales_1_prt_feb_2025    (date >= '2025-02-01' AND date < '2025-03-01')
   └─── sales_1_prt_mar_2025    (date >= '2025-03-01' AND date < '2025-04-01')

The following example illustrates a two-level partitioned sales table. Each monthly partition is further subpartitioned by the region column, dividing the data into the asia and europe subpartitions:

sales
   ├─── sales_1_prt_jan_2025                      (date >= '2025-01-01' AND date < '2025-02-01')
   │    ├─── sales_1_prt_jan_2025_2_prt_asia          (region = 'Asia')
   │    └─── sales_1_prt_jan_2025_2_prt_europe        (region = 'Europe')
   ├─── sales_1_prt_feb_2025                      (date >= '2025-02-01' AND date < '2025-03-01')
   │    ├─── sales_1_prt_feb_2025_2_prt_asia          (region = 'Asia')
   │    └─── sales_1_prt_feb_2025_2_prt_europe        (region = 'Europe')
   └─── sales_1_prt_mar_2025                      (date >= '2025-03-01' AND date < '2025-04-01')
        ├─── sales_1_prt_mar_2025_2_prt_asia          (region = 'Asia')
        └─── sales_1_prt_mar_2025_2_prt_europe        (region = 'Europe')

To insert data into a partitioned table, you typically target either the root partitioned table (sales in the example above) or one of its leaf child partitions (for example, sales_1_prt_jan_2025_2_prt_asia and sales_1_prt_jan_2025_2_prt_europe). Keep the following in mind when inserting data:

  • You can insert data into the root table (for example, sales) created with the CREATE TABLE command. Greengage DB automatically routes the data to the correct child partition based on the partitioning rules.

  • You can insert data directly into a leaf partition (for example, sales_1_prt_jan_2025_2_prt_europe) as long as the data fits that partition’s criteria.

  • Inserting data into a non-leaf or intermediate partition (for example, sales_1_prt_jan_2025) is not allowed and causes an error.

Note that the top-level and intermediate tables — such as sales, sales_1_prt_jan_2025, sales_1_prt_feb_2025, and sales_1_prt_mar_2025 — remain empty after inserting the data.

TIP

The best practice for loading data into partitioned tables is using partition exchange.

Decide whether to use partitioning

Use partitioning if all or most of these points can be applied to a table:

  • There is a large table of facts.

    Large fact tables with millions of rows are good candidates for partitioning. In contrast, smaller tables with only thousands of rows or fewer are unlikely to benefit from partitioning.

  • You are not satisfied with the current query performance.

    Apply partitioning only when query performance on the table does not meet the required response times.

  • There is a column that allows the table to be divided into approximately equal-sized partitions.

    Choose a partition key that results in partitions with roughly the same number of rows. The more evenly the table is divided into small chunks, the greater the performance benefits you can get from partitioning. For example, dividing a table into 10 evenly sized partitions can improve query performance by up to 10x — assuming the partition key is used in query predicates (see the following item below).

  • Most queries targeted for optimization use the partition key in their predicates.

    Partitioning improves performance only if the query optimizer can select partitions based on the query predicates. If a query scans all partitions, it may perform worse than querying a non-partitioned table. Make sure that your query plans contain partition elimination.

  • There are business requirements for retaining historical data.

    Partitioning is well-suited for managing time-based data retention. For example, if only the last 12 months of data need to be kept, you can drop the oldest partition and load new data into a new one.

Avoid creating more partitions than necessary. Excessive partitioning can negatively impact system operations such as vacuuming, segment recovery, cluster expansion, disk usage checks, and others.

Syntax overview

To define the partitioning strategy, use the PARTITION BY clause in the CREATE TABLE command. To partition a table:

  1. Decide on the partition design: date range, numeric range, or list of values.

  2. Select one or more columns to use as the partition key.

  3. Decide on the number of partitioning levels needed. For example, you can create a range-partitioned table by month and then subpartition each monthly partition by sales region.

The simplified syntax of the CREATE TABLE command with single-level partitioning is as follows:

CREATE TABLE <table_name> (
  <column_definitions>
)
[ PARTITION BY <partition_type> (<column>)
  (
      <partition_spec>
  )]

The main arguments are:

  • partition_type — specifies the type of partitioning: LIST (based on a list of values) or RANGE (based on a numeric or date range).

  • column — defines the column used for partitioning.

  • partition_spec — defines the individual partitions to be created. Common clauses include:

    • START, END, and EVERY — used for defining range partitions.

    • VALUES — specifies the values for list partitions.

    • DEFAULT PARTITION — captures any rows that do not match the specified partition criteria; applicable to all partition types. Note that the query optimizer always scans the default partition, which may slow down the overall scan time if this partition contains data.

Range partitioning

Range partitioning divides data based on numeric or date/timestamp ranges. Use the PARTITION BY RANGE clause to specify the partition key column, then define partition intervals using the START and END keywords. The INCLUSIVE and EXCLUSIVE modifiers control whether the boundary values are included in the range. By default, START values are inclusive, and END values are exclusive.

There are two ways to define range partitions — automatic and manual.

Generate partitions automatically

To generate partitions automatically, use the START and END clauses to specify the boundaries of the entire range. Then, specify the partition size with the EVERY clause:

  • For date ranges, this clause requires the interval unit such as day, month, or year. Example: EVERY (INTERVAL '1 month').

  • For numeric ranges, provide a numeric step value. Example: EVERY (100).

Greengage DB automatically creates the necessary partitions by dividing the entire range into equal intervals based on the specified increment.

PARTITION BY RANGE (<column>)
    (
    START ([<datatype>] '<start_value>') [INCLUSIVE | EXCLUSIVE]
        END ([<datatype>] '<end_value>') [INCLUSIVE | EXCLUSIVE] ]
        EVERY ([<datatype>] [<number> | INTERVAL] '<interval_value>') ]
    );

Define partitions manually

An alternative approach to defining a partitioned table is to specify each partition individually. In this case, provide a comma-separated list of partition definitions within the parentheses following the PARTITION BY RANGE clause.

Each partition definition begins with the PARTITION <partition_name> clause, where <partition_name> is a custom identifier used in the partition’s full name instead of an automatically generated number.

Following the partition name, use the standard START and END clauses to define the range boundaries for each partition. Note that only the last partition requires the END value; it is optional for all others.

PARTITION BY RANGE (<column>)
    (
    PARTITION <partition_name>
        START ([<datatype>] '<start_value>') [INCLUSIVE | EXCLUSIVE]
        END ([<datatype>] '<end_value>') [INCLUSIVE | EXCLUSIVE],
    [PARTITION <partition_name> ...]
    );

List partitioning

List partitioning divides data based on a predefined set of values for the partition key. Use the PARTITION BY LIST clause to specify the partition key column, followed by a comma-separated list of partition definitions enclosed in parentheses.

Each partition definition follows this template:

PARTITION <partition_name> VALUES (<list_value>[, ...])

where:

  • <partition_name> is a custom identifier used instead of an automatically generated number in the full partition name;

  • <list_value> represents one or more partition key values assigned to that partition.

Multi-level partitioning

Multi-level partitioning combines range and/or list partitioning across multiple levels. This lets you divide first-level partitions into second-level subpartitions, which can be divided further if needed.

Use the PARTITION BY clause (with RANGE or LIST) to define the partition key at the first level. For all subsequent levels, use the SUBPARTITION BY clause (also with RANGE or LIST). Subpartition definitions follow the same format as partitions, except the keyword SUBPARTITION is used instead of PARTITION.

There are two ways to define subpartitions — manually or with a subpartition template.

Manual definition of subpartitions

In this approach, every partition and subpartition is defined explicitly. First, specify all partition keys using the PARTITION BY and SUBPARTITION BY clauses. Then, list the partition and subpartition specifications.

The hierarchy of partitions and subpartitions is represented by nested parentheses, reflecting their level in the partitioning structure.

CREATE TABLE <table_name> (
  <column_definitions>
)
[ PARTITION BY <partition_type> (<column>)
   [ SUBPARTITION BY <partition_type> (<column1>) ]
      [ SUBPARTITION BY <partition_type> (<column2>) ]
         [...]
  ( <partition_spec>
     [ ( <subpartition_spec_column1>
          [ ( <subpartition_spec_column2>
               [...] ) ] ) ],
  [ <partition_spec>
     [ ( <subpartition_spec_column1>
        [ ( <subpartition_spec_column2>
             [...] ) ] ) ], ]
    [...]
  ) ]

Subpartition templates

In this approach, subpartitions for each hierarchy level are defined once using the SUBPARTITION TEMPLATE clause, placed immediately after the corresponding SUBPARTITION BY clause. Using a subpartition template ensures that all partitions share a consistent structure, including any partitions added in the future.

CREATE TABLE <table_name> (
  <column_definitions>
)
[ PARTITION BY <partition_type> (<column>)
  {  [ SUBPARTITION BY <partition_type> (<column1>)
       SUBPARTITION TEMPLATE ( <template_spec> ) ]
          [ SUBPARTITION BY partition_type (<column2>)
            SUBPARTITION TEMPLATE ( <template_spec> ) ]
              [...]  }
  ( <partition_spec> ) ]

Create a partitioned table

Prerequisites

To execute commands described in the following sections, connect to the Greengage DB master host using psql as described in Connect to Greengage DB via psql. Then, create a new database and connect to it:

CREATE DATABASE marketplace;
\c marketplace

Date range partitioning

The command below creates a range-partitioned table with monthly partitions for Q1 2025 and includes a default partition for dates beyond these partitions:

CREATE TABLE sales
(
    id     INT,
    date   DATE,
    amount DECIMAL(10, 2)
)
    WITH (appendoptimized = true)
    DISTRIBUTED BY (id)
    PARTITION BY RANGE (date)
        (
        START (DATE '2025-01-01') INCLUSIVE
            END (DATE '2025-04-01') EXCLUSIVE
            EVERY (INTERVAL '1 month'),
        DEFAULT PARTITION other_dates
        );

The following command creates the same partitioned sales table but defines each partition manually:

CREATE TABLE sales
(
    id     INT,
    date   DATE,
    amount DECIMAL(10, 2)
)
    WITH (appendoptimized = true)
    DISTRIBUTED BY (id)
    PARTITION BY RANGE (date)
        (
        PARTITION jan_2025 START (DATE '2025-01-01') INCLUSIVE,
        PARTITION feb_2025 START (DATE '2025-02-01') INCLUSIVE,
        PARTITION mar_2025 START (DATE '2025-03-01') INCLUSIVE
            END (DATE '2025-04-01') EXCLUSIVE,
        DEFAULT PARTITION other_dates
        );

Numeric range partitioning

The following command creates a range-partitioned sales table based on the amount column:

CREATE TABLE sales
(
    id     INT,
    date   DATE,
    amount DECIMAL(10, 2)
)
    WITH (appendoptimized = true)
    DISTRIBUTED BY (id)
    PARTITION BY RANGE (amount)
        (
        START (0)
            END (1000)
            EVERY (250),
        DEFAULT PARTITION out_of_range
        );

This command creates the sales table with manually defined range partitions:

CREATE TABLE sales
(
    id     INT,
    date   DATE,
    amount DECIMAL(10, 2)
)
    WITH (appendoptimized = true)
    DISTRIBUTED BY (id)
    PARTITION BY RANGE (amount)
        (
        PARTITION low_amounts START (0),
        PARTITION medium_amounts START (100),
        PARTITION high_amounts START (500)
            END (1000),
        DEFAULT PARTITION out_of_range);

List partitioning

The SQL statement below creates the sales table partitioned by region using list partitioning. It defines explicit partitions for Asia and Europe, along with a default partition for all other regions:

CREATE TABLE sales
(
    id     INT,
    date   DATE,
    region TEXT,
    amount DECIMAL(10, 2)
)
    WITH (appendoptimized = true)
    DISTRIBUTED BY (id)
    PARTITION BY LIST (region)
        (
        PARTITION asia VALUES ('Asia'),
        PARTITION europe VALUES ('Europe'),
        DEFAULT PARTITION other_regions
        );

Multi-level partitioning

This command creates a two-level partitioned sales table: it uses automatic range partitioning by date at the first level and a SUBPARTITION TEMPLATE to define list subpartitions by region at the second level:

CREATE TABLE sales
(
    id     INT,
    date   DATE,
    region TEXT,
    amount DECIMAL(10, 2)
)
    WITH (appendoptimized = true)
    DISTRIBUTED BY (id)
    PARTITION BY RANGE (date)
        SUBPARTITION BY LIST (region)
            SUBPARTITION TEMPLATE
            (
            SUBPARTITION asia VALUES ('Asia'),
            SUBPARTITION europe VALUES ('Europe'),
            DEFAULT SUBPARTITION other_regions
            )
        (
        START (DATE '2025-01-01') INCLUSIVE
            END (DATE '2025-04-01') EXCLUSIVE
            EVERY (INTERVAL '1 month'),
        DEFAULT PARTITION other_dates
        );

The following command creates a two-level partitioned sales table with manually defined monthly partitions by date, each containing list subpartitions by region:

CREATE TABLE sales
(
    id     INT,
    date   DATE,
    region TEXT,
    amount DECIMAL(10, 2)
)
    WITH (appendoptimized = true)
    DISTRIBUTED BY (id)
    PARTITION BY RANGE (date)
        SUBPARTITION BY LIST (region)
        (
        PARTITION jan_2025 START (DATE '2025-01-01') INCLUSIVE (
        SUBPARTITION asia VALUES ('Asia'),
        SUBPARTITION europe VALUES ('Europe'),
        DEFAULT SUBPARTITION other_regions
            ),
        PARTITION feb_2025 START (DATE '2025-02-01') INCLUSIVE (
        SUBPARTITION asia VALUES ('Asia'),
        SUBPARTITION europe VALUES ('Europe'),
        DEFAULT SUBPARTITION other_regions
            ),
        PARTITION mar_2025 START (DATE '2025-03-01') INCLUSIVE (
        SUBPARTITION asia VALUES ('Asia'),
        SUBPARTITION europe VALUES ('Europe'),
        DEFAULT SUBPARTITION other_regions
            ),
        DEFAULT PARTITION other_dates (
        SUBPARTITION asia VALUES ('Asia'),
        SUBPARTITION europe VALUES ('Europe'),
        DEFAULT SUBPARTITION other_regions
            )
        );

The command below creates the sales table with three levels of partitioning:

  • automatic range partitioning by date;

  • list subpartitioning by region;

  • range subpartitioning by amount.

SUBPARTITION TEMPLATE is used at both subpartition levels.

CREATE TABLE sales
(
    id     INT,
    date   DATE,
    region TEXT,
    amount DECIMAL(10, 2)
)
    WITH (appendoptimized = true)
    DISTRIBUTED BY (id)
    PARTITION BY RANGE (date)
        SUBPARTITION BY LIST (region)
            SUBPARTITION TEMPLATE
            (
            SUBPARTITION asia VALUES ('Asia'),
            SUBPARTITION europe VALUES ('Europe'),
            DEFAULT SUBPARTITION other_regions
            )
        SUBPARTITION BY RANGE (amount)
            SUBPARTITION TEMPLATE
            (
            START (0)
                END (1000)
                EVERY (100),
            DEFAULT SUBPARTITION out_of_range
            )
        (
        START (DATE '2025-01-01') INCLUSIVE
            END (DATE '2025-04-01') EXCLUSIVE
            EVERY (INTERVAL '1 month'),
        DEFAULT PARTITION other_dates
        );

Partition an existing table

Table partitioning can be defined only at creation. If you need a partitioned version of an existing table, create a new partitioned table and populate it with the original data:

  1. Create a new partitioned table:

    CREATE TABLE sales_partitioned
(
    LIKE sales
)
    WITH (appendoptimized = true)
    PARTITION BY RANGE (date)
        (
        START (DATE '2025-01-01') INCLUSIVE
            END (DATE '2025-04-01') EXCLUSIVE
            EVERY (INTERVAL '1 month'),
        DEFAULT PARTITION other_dates
        );

  2. Load the original table data into the new table:

    INSERT INTO sales_partitioned
SELECT *
FROM sales;

  3. Drop the original table:

    DROP TABLE sales;

  4. Rename the new table with the original table’s name:

    ALTER TABLE sales_partitioned
    RENAME TO sales;
NOTE

After creating a new table, you need to reapply any table permissions. Learn more in Roles and privileges.

View partitioning information

The partitioned table below serves as an example for demonstrating how to view partitioning information.

CREATE TABLE sales
(
    id     INT,
    date   DATE,
    region TEXT,
    amount DECIMAL(10, 2)
)
    WITH (appendoptimized = true)
    DISTRIBUTED BY (id)
    PARTITION BY RANGE (date)
        SUBPARTITION BY LIST (region)
            SUBPARTITION TEMPLATE
            (
            SUBPARTITION asia VALUES ('Asia'),
            SUBPARTITION europe VALUES ('Europe'),
            DEFAULT SUBPARTITION other_regions
            )
        (
        START (DATE '2025-01-01') INCLUSIVE
            END (DATE '2025-04-01') EXCLUSIVE
            EVERY (INTERVAL '1 month'),
        DEFAULT PARTITION other_dates
        );

psql meta-commands

The \d+ meta-command below shows information about the sales table:

\d+ sales

The Partitions section of the output lists the child tables associated with the root table:

                                Partitioned table "public.sales"
 Column |     Type      | Collation | Nullable | Default | Storage  | Stats target | Description
--------+---------------+-----------+----------+---------+----------+--------------+-------------
 id     | integer       |           |          |         | plain    |              |
 date   | date          |           |          |         | plain    |              |
 region | text          |           |          |         | extended |              |
 amount | numeric(10,2) |           |          |         | main     |              |
Partition key: RANGE (date)
Partitions: sales_1_prt_2 FOR VALUES FROM ('2025-01-01') TO ('2025-02-01'), PARTITIONED,
            sales_1_prt_3 FOR VALUES FROM ('2025-02-01') TO ('2025-03-01'), PARTITIONED,
            sales_1_prt_4 FOR VALUES FROM ('2025-03-01') TO ('2025-04-01'), PARTITIONED,
            sales_1_prt_other_dates DEFAULT, PARTITIONED
Distributed by: (id)
Access method: ao_row

You can execute the same command for a child table to see its child tables used for subpartitioning:

\d+ sales_1_prt_2

In addition to the Partitions section, the output also includes the Partition of section indicating that this table inherits columns and constraints of the sales table:

                            Partitioned table "public.sales_1_prt_2"
 Column |     Type      | Collation | Nullable | Default | Storage  | Stats target | Description
--------+---------------+-----------+----------+---------+----------+--------------+-------------
 id     | integer       |           |          |         | plain    |              |
 date   | date          |           |          |         | plain    |              |
 region | text          |           |          |         | extended |              |
 amount | numeric(10,2) |           |          |         | main     |              |
Partition of: sales FOR VALUES FROM ('2025-01-01') TO ('2025-02-01')
Partition constraint: ((date IS NOT NULL) AND (date >= '2025-01-01'::date) AND (date < '2025-02-01'::date))
Partition key: LIST (region)
Partitions: sales_1_prt_2_2_prt_asia FOR VALUES IN ('Asia'),
            sales_1_prt_2_2_prt_europe FOR VALUES IN ('Europe'),
            sales_1_prt_2_2_prt_other_regions DEFAULT
Distributed by: (id)
Access method: ao_row

System catalogs

To get information on partitioned tables in the current database, use one of the ways described below.

pg_catalog.pg_partitioned_table

To view all partitioned tables, run the SQL query against the pg_partitioned_table table:

SELECT pg_class.relname               AS partition_table_name,
       pg_attribute.attname           AS column_in_partition_key,
       class2.relname                 AS default_partition,
       pg_partitioned_table.partstrat AS partition_type
FROM pg_partitioned_table
         INNER JOIN pg_class ON pg_class.oid = pg_partitioned_table.partrelid
         INNER JOIN pg_attribute ON pg_attribute.attnum IN (SELECT unnest(pg_partitioned_table.partattrs)) AND
                                    pg_attribute.attrelid = pg_class.oid
         LEFT JOIN pg_class class2 ON class2.oid = pg_partitioned_table.partdefid
ORDER BY pg_class.relname;

Result:

  partition_table_name   | column_in_partition_key |              default_partition              | partition_type
-------------------------+-------------------------+---------------------------------------------+----------------
 sales                   | date                    | sales_1_prt_other_dates                     | r
 sales_1_prt_2           | region                  | sales_1_prt_2_2_prt_other_regions           | l
 sales_1_prt_3           | region                  | sales_1_prt_3_2_prt_other_regions           | l
 sales_1_prt_4           | region                  | sales_1_prt_4_2_prt_other_regions           | l
 sales_1_prt_other_dates | region                  | sales_1_prt_other_dates_2_prt_other_regions | l
(5 rows)

The command returns the following columns:

  • partition_table_name — a name of the table that is used to access the partition directly in DML commands.

  • column_in_partition_key — a column used as a partition key.

  • default_partition — the default partition name (if any).

  • partition_type — a partition type (r — range, l — list).

Note that leaf partitions are not included into the query result.

gp_toolkit.gp_partitions

NOTE

Starting with Greengage DB 7, the gp_toolkit.gp_partitions system view replaces the pg_catalog.pg_partitions view.

To show the partition design of the specified table, run the SQL query against the gp_toolkit.gp_partitions system view:

SELECT partitiontablename,
       partitiontype,
       partitionlevel,
       partitionrank
FROM gp_toolkit.gp_partitions
WHERE tablename = 'sales'
ORDER BY partitionlevel,
         partitionrank;

The result might look as follows:

             partitiontablename              | partitiontype | partitionlevel | partitionrank
---------------------------------------------+---------------+----------------+---------------
 sales_1_prt_2                               | range         |              1 |             1
 sales_1_prt_3                               | range         |              1 |             2
 sales_1_prt_4                               | range         |              1 |             3
 sales_1_prt_other_dates                     | range         |              1 |
 sales_1_prt_2_2_prt_asia                    | list          |              2 |
 sales_1_prt_2_2_prt_europe                  | list          |              2 |
 sales_1_prt_2_2_prt_other_regions           | list          |              2 |
 sales_1_prt_3_2_prt_asia                    | list          |              2 |
 sales_1_prt_3_2_prt_europe                  | list          |              2 |
 sales_1_prt_3_2_prt_other_regions           | list          |              2 |
 sales_1_prt_4_2_prt_asia                    | list          |              2 |
 sales_1_prt_4_2_prt_europe                  | list          |              2 |
 sales_1_prt_4_2_prt_other_regions           | list          |              2 |
 sales_1_prt_other_dates_2_prt_asia          | list          |              2 |
 sales_1_prt_other_dates_2_prt_europe        | list          |              2 |
 sales_1_prt_other_dates_2_prt_other_regions | list          |              2 |
(16 rows)

The table contains the following columns:

  • partitiontablename — a name of the table that is used to access the partition directly in DML commands.

  • partitiontype — a partition type.

  • partitionlevel — a partition level in the hierarchy.

  • partitionrank — a rank of the partition compared to other partitions of the same level (starting with 1). Defined only for range partitions.

You can also use the partitionboundary column to get partition specifications.

pg_catalog.gp_partition_template

NOTE

The pg_partition_templates view is removed in Greengage DB 7.

For partitioned tables created using the classic syntax, the gp_partition_template system catalog table describes the relationship between a partitioned table and the subpartition template defined at each level of the hierarchy.

To view the template in a more readable form, use the pg_get_expr() helper function with gp_partition_template, for example:

SELECT level, pg_get_expr(template, relid)
FROM gp_partition_template
WHERE relid::regclass::text = 'sales';

Result:

 level |                                                             pg_get_expr
-------+-------------------------------------------------------------------------------------------------------------------------------------
     1 | SUBPARTITION TEMPLATE(DEFAULT SUBPARTITION other_regions, SUBPARTITION asia VALUES ('Asia'), SUBPARTITION europe VALUES ('Europe'))
(1 row)

System functions

You can use the following functions to obtain information about the partitioned tables:

pg_partition_tree(regclass)

Returns one row for each table or index in the partition hierarchy of the specified partitioned table or partitioned index. The result includes the partition name, its immediate parent, a flag indicating whether it is a leaf partition, and its level in the hierarchy. The level value starts at 0 for the root, 1 for its direct partitions, 2 for their partitions, and so on.

Return type: setof record

pg_partition_ancestors(regclass)

Returns all ancestor relations of the specified partition, including the partition itself.

Return type: setof regclass

pg_partition_root(regclass)

Returns the top-level parent of the partition hierarchy to which the specified relation belongs.

Return type: regclass

The following command displays the partition hierarchy for the intermediate sales_1_prt_2 table:

SELECT * FROM pg_partition_tree('sales_1_prt_2');

Result:

               relid               |  parentrelid  | isleaf | level
-----------------------------------+---------------+--------+-------
 sales_1_prt_2                     | sales         | f      |     0
 sales_1_prt_2_2_prt_other_regions | sales_1_prt_2 | t      |     1
 sales_1_prt_2_2_prt_asia          | sales_1_prt_2 | t      |     1
 sales_1_prt_2_2_prt_europe        | sales_1_prt_2 | t      |     1
(4 rows)

Maintain partitioned tables

Add a default partition

CREATE TABLE sales
(
    id     INT,
    date   DATE,
    region TEXT,
    amount DECIMAL(10, 2)
)
    WITH (appendoptimized = true)
    DISTRIBUTED BY (id)
    PARTITION BY RANGE (date)
        SUBPARTITION BY LIST (region)
            SUBPARTITION TEMPLATE
            (
            SUBPARTITION asia VALUES ('Asia'),
            SUBPARTITION europe VALUES ('Europe')
            )
        (
        START (DATE '2025-01-01') INCLUSIVE
            END (DATE '2025-04-01') EXCLUSIVE
            EVERY (INTERVAL '1 month')
        );

You can add a default partition to a partition design with the ALTER TABLE command:

ALTER TABLE sales
    ADD DEFAULT PARTITION other_dates;

If your partition design is multi-level, each level in the hierarchy must have a default partition:

ALTER TABLE sales
    ALTER PARTITION FOR ('2025-01-01')
        ADD DEFAULT PARTITION other_regions;

ALTER TABLE sales
    ALTER PARTITION FOR ('2025-02-01')
        ADD DEFAULT PARTITION other_regions;

ALTER TABLE sales
    ALTER PARTITION FOR ('2025-03-01')
        ADD DEFAULT PARTITION other_regions;
NOTE

Starting with Greengage DB 7, the FOR (RANK(<value>)) clause is no longer supported.

To check that the default partitions are added, execute the following query:

SELECT partitiontablename,
       partitiontype,
       partitionlevel,
       partitionrank
FROM gp_toolkit.gp_partitions
WHERE tablename = 'sales'
ORDER BY partitionlevel,
         partitionrank;

The output should include the sales_1_prt_other_dates and sales_1_prt_*_other_regions tables:

          partitiontablename          | partitiontype | partitionlevel | partitionrank
--------------------------------------+---------------+----------------+---------------
 sales_1_prt_1                        | range         |              1 |             1
 sales_1_prt_2                        | range         |              1 |             2
 sales_1_prt_3                        | range         |              1 |             3
 sales_1_prt_other_dates              | range         |              1 |
 sales_1_prt_1_2_prt_asia             | list          |              2 |
 sales_1_prt_1_2_prt_europe           | list          |              2 |
 sales_1_prt_1_2_prt_other_regions    | list          |              2 |
 sales_1_prt_2_2_prt_asia             | list          |              2 |
 sales_1_prt_2_2_prt_europe           | list          |              2 |
 sales_1_prt_2_2_prt_other_regions    | list          |              2 |
 sales_1_prt_3_2_prt_asia             | list          |              2 |
 sales_1_prt_3_2_prt_europe           | list          |              2 |
 sales_1_prt_3_2_prt_other_regions    | list          |              2 |
 sales_1_prt_other_dates_2_prt_asia   | list          |              2 |
 sales_1_prt_other_dates_2_prt_europe | list          |              2 |
(15 rows)

Add a new partition

CREATE TABLE sales
(
    id     INT,
    date   DATE,
    region TEXT,
    amount DECIMAL(10, 2)
)
    WITH (appendoptimized = true)
    DISTRIBUTED BY (id)
    PARTITION BY RANGE (date)
        SUBPARTITION BY LIST (region)
            SUBPARTITION TEMPLATE
            (
            SUBPARTITION asia VALUES ('Asia'),
            SUBPARTITION europe VALUES ('Europe')
            )
        (
        PARTITION jan_2025 START (DATE '2025-01-01') INCLUSIVE ,
        PARTITION feb_2025 START (DATE '2025-02-01') INCLUSIVE ,
        PARTITION mar_2025 START (DATE '2025-03-01') INCLUSIVE
            END (DATE '2025-04-01') EXCLUSIVE
        );

You can add a partition to a partition design with the ALTER TABLE command. If the original partition design includes subpartitions defined by a subpartition template, the newly added partition is subpartitioned according to this template.

The following command adds a new range partition for April 2025 to the sales table:

ALTER TABLE sales
    ADD PARTITION apr_2025 START (DATE '2025-04-01')
        END (DATE '2025-05-01') EXCLUSIVE;

When you add a subpartition to an existing partition, you can specify the partition to alter:

ALTER TABLE sales
    ALTER PARTITION FOR ('2025-04-01')
        ADD PARTITION africa VALUES ('Africa');

To check that new partitions are added, execute the following query:

SELECT partitiontablename,
       partitiontype,
       partitionlevel,
       partitionrank
FROM gp_toolkit.gp_partitions
WHERE tablename = 'sales'
ORDER BY partitionlevel,
         partitionrank;

The result includes the sales_1_prt_apr_2025 and sales_1_prt_apr_2025_2_prt_africa partitions:

        partitiontablename         | partitiontype | partitionlevel | partitionrank
-----------------------------------+---------------+----------------+---------------
 sales_1_prt_jan_2025              | range         |              1 |             1
 sales_1_prt_feb_2025              | range         |              1 |             2
 sales_1_prt_mar_2025              | range         |              1 |             3
 sales_1_prt_apr_2025              | range         |              1 |             4
 sales_1_prt_jan_2025_2_prt_asia   | list          |              2 |
 sales_1_prt_jan_2025_2_prt_europe | list          |              2 |
 sales_1_prt_feb_2025_2_prt_asia   | list          |              2 |
 sales_1_prt_feb_2025_2_prt_europe | list          |              2 |
 sales_1_prt_mar_2025_2_prt_asia   | list          |              2 |
 sales_1_prt_mar_2025_2_prt_europe | list          |              2 |
 sales_1_prt_apr_2025_2_prt_africa | list          |              2 |
 sales_1_prt_apr_2025_2_prt_asia   | list          |              2 |
 sales_1_prt_apr_2025_2_prt_europe | list          |              2 |
(13 rows)
NOTE

You cannot add a partition to a partition design that has a default partition. You must split the default partition to add a partition. Learn more in Split the default partition.

Split a partition

Split the specified partition

CREATE TABLE sales
(
    id     INT,
    date   DATE,
    amount DECIMAL(10, 2)
)
    WITH (appendoptimized = true)
    DISTRIBUTED BY (id)
    PARTITION BY RANGE (date)
        (
        PARTITION jan_2025 START (DATE '2025-01-01') INCLUSIVE ,
        PARTITION feb_2025 START (DATE '2025-02-01') INCLUSIVE ,
        PARTITION mar_2025 START (DATE '2025-03-01') INCLUSIVE
            END (DATE '2025-04-01') EXCLUSIVE
        );

Splitting a partition divides an existing partition into two new ones. You can split a partition using the ALTER TABLE command. The split value you specify goes into the latter partition.

Note that only the lowest-level partitions — the ones that actually contain data — can be split. For single-level partitioning, you can split both range and list partitions, while for multi-level partitioning, you can only split range partitions.

Execute the following command to split a monthly partition into two: the first for dates March 1–15 and the second for March 16–31:

ALTER TABLE sales
    SPLIT PARTITION FOR ('2025-03-01')
        AT ('2025-03-16')
        INTO (PARTITION mar_1to15_2025, PARTITION mar_16to31_2025);

To see the updated partition design of the table, run the following SQL query:

SELECT partitiontablename,
       partitionrangestart,
       partitionrangeend
FROM gp_toolkit.gp_partitions
WHERE tablename = 'sales';

The result looks like this:

     partitiontablename      | partitionrangestart | partitionrangeend
-----------------------------+---------------------+-------------------
 sales_1_prt_jan_2025        | '2025-01-01'        | '2025-02-01'
 sales_1_prt_feb_2025        | '2025-02-01'        | '2025-03-01'
 sales_1_prt_mar_1to15_2025  | '2025-03-01'        | '2025-03-16'
 sales_1_prt_mar_16to31_2025 | '2025-03-16'        | '2025-04-01'
(4 rows)

Split the default partition

CREATE TABLE sales
(
    id     INT,
    date   DATE,
    amount DECIMAL(10, 2)
)
    WITH (appendoptimized = true)
    DISTRIBUTED BY (id)
    PARTITION BY RANGE (date)
        (
        PARTITION jan_2025 START (DATE '2025-01-01') INCLUSIVE ,
        PARTITION feb_2025 START (DATE '2025-02-01') INCLUSIVE ,
        PARTITION mar_2025 START (DATE '2025-03-01') INCLUSIVE
            END (DATE '2025-04-01') EXCLUSIVE,
        DEFAULT PARTITION other_dates
        );

To split the default partition, use the SPLIT DEFAULT PARTITION clause:

ALTER TABLE sales
    SPLIT DEFAULT PARTITION
        START ('2025-04-01') INCLUSIVE
        END ('2025-05-01') EXCLUSIVE
        INTO (PARTITION apr_2025, DEFAULT PARTITION);

To see the updated partition design, run the following SQL query:

SELECT partitiontablename,
       partitionrangestart,
       partitionrangeend
FROM gp_toolkit.gp_partitions
WHERE tablename = 'sales';

The result should look like this:

   partitiontablename    | partitionrangestart | partitionrangeend
-------------------------+---------------------+-------------------
 sales_1_prt_jan_2025    | '2025-01-01'        | '2025-02-01'
 sales_1_prt_feb_2025    | '2025-02-01'        | '2025-03-01'
 sales_1_prt_mar_2025    | '2025-03-01'        | '2025-04-01'
 sales_1_prt_apr_2025    | '2025-04-01'        | '2025-05-01'
 sales_1_prt_other_dates |                     |
(5 rows)

Modify a subpartition template

CREATE TABLE sales
(
    id     INT,
    date   DATE,
    region TEXT,
    amount DECIMAL(10, 2)
)
    WITH (appendoptimized = true)
    DISTRIBUTED BY (id)
    PARTITION BY RANGE (date)
        SUBPARTITION BY LIST (region)
            SUBPARTITION TEMPLATE
            (
            SUBPARTITION asia VALUES ('Asia'),
            SUBPARTITION europe VALUES ('Europe')
            )
        (
        PARTITION jan_2025 START (DATE '2025-01-01') INCLUSIVE ,
        PARTITION feb_2025 START (DATE '2025-02-01') INCLUSIVE ,
        PARTITION mar_2025 START (DATE '2025-03-01') INCLUSIVE
            END (DATE '2025-04-01') EXCLUSIVE
        );

Use the SET SUBPARTITION TEMPLATE clause to modify the subpartition template of a partitioned table. Partitions added after you set a new subpartition template have the new partition design. Existing partitions are not modified.

The following example alters the subpartition template of the sales table:

ALTER TABLE sales
    SET SUBPARTITION TEMPLATE
        (SUBPARTITION asia VALUES ('Asia'),
         SUBPARTITION europe VALUES ('Europe'),
         SUBPARTITION africa VALUES ('Africa'),
         DEFAULT SUBPARTITION other_regions);

When you add a new date-range partition, it should include the new regional list subpartition for Africa:

ALTER TABLE sales
    ADD PARTITION apr_2025 START (DATE '2025-04-01')
        END (DATE '2025-05-01') EXCLUSIVE;

To see the updated partition design, run the following SQL query:

SELECT partitiontablename,
       partitiontype,
       partitionlevel,
       partitionrank
FROM gp_toolkit.gp_partitions
WHERE tablename = 'sales'
ORDER BY partitionlevel,
         partitionrank;

The result should include the subpartitions asia, europe, africa, and a default partition named other_regions:

            partitiontablename            | partitiontype | partitionlevel | partitionrank
------------------------------------------+---------------+----------------+---------------
 sales_1_prt_jan_2025                     | range         |              1 |             1
 sales_1_prt_feb_2025                     | range         |              1 |             2
 sales_1_prt_mar_2025                     | range         |              1 |             3
 sales_1_prt_apr_2025                     | range         |              1 |             4
 sales_1_prt_jan_2025_2_prt_asia          | list          |              2 |
 sales_1_prt_jan_2025_2_prt_europe        | list          |              2 |
 sales_1_prt_feb_2025_2_prt_asia          | list          |              2 |
 sales_1_prt_feb_2025_2_prt_europe        | list          |              2 |
 sales_1_prt_mar_2025_2_prt_asia          | list          |              2 |
 sales_1_prt_mar_2025_2_prt_europe        | list          |              2 |
 sales_1_prt_apr_2025_2_prt_africa        | list          |              2 |
 sales_1_prt_apr_2025_2_prt_asia          | list          |              2 |
 sales_1_prt_apr_2025_2_prt_europe        | list          |              2 |
 sales_1_prt_apr_2025_2_prt_other_regions | list          |              2 |
(14 rows)

Exchange a partition

CREATE TABLE sales
(
    id     INT,
    date   DATE,
    amount DECIMAL(10, 2)
)
    WITH (appendoptimized = true)
    DISTRIBUTED BY (id)
    PARTITION BY RANGE (date)
        (
        PARTITION jan_2025 START (DATE '2025-01-01') INCLUSIVE ,
        PARTITION feb_2025 START (DATE '2025-02-01') INCLUSIVE ,
        PARTITION mar_2025 START (DATE '2025-03-01') INCLUSIVE
            END (DATE '2025-04-01') EXCLUSIVE,
        DEFAULT PARTITION other_dates
        );

Partition exchange enables you to swap a table partition with another table with the same structure, commonly called a staging table. After the exchange, the partition contains the data from the staging table, which holds the original partition data.

This feature is useful for data loading, as inserting data directly into partitioned tables can be inefficient. Additionally, partition exchange can be used to change the storage type of existing partitions.

To demonstrate partition exchange, first add sample data to the sales table:

INSERT INTO sales (id, date, amount)
SELECT gs.id,
       DATE '2025-01-01' + (gs.id % 59),
       round((random() * 1000)::NUMERIC, 2)
FROM generate_series(1, 40000) AS gs(id);

Check that the table corresponding to the mar_2025 partition does not contain data:

SELECT count(*)
FROM sales_1_prt_mar_2025;

Create a table with the same structure as the partitioned table but with another storage type:

CREATE TABLE stg_mar_2025
(
    LIKE sales
)
    WITH (appendoptimized = false);

Insert sample data into the table:

INSERT INTO stg_mar_2025 (id, date, amount)
SELECT gs.id,
       DATE '2025-03-01' + (gs.id % 31),
       round((random() * 1000)::NUMERIC, 2)
FROM generate_series(1, 20000) AS gs(id);

To exchange a partition, use the ALTER TABLE command with the EXCHANGE PARTITION clause:

ALTER TABLE sales
    EXCHANGE PARTITION FOR (DATE '2025-03-01')
        WITH TABLE stg_mar_2025 WITH VALIDATION;

Check that the table corresponding to the mar_2025 partition now contains data:

SELECT count(*)
FROM sales_1_prt_mar_2025;

The result should look like this:

 count
-------
 20000
(1 row)

Finally, check that the storage type of the partition has changed as well:

\dt+ sales*

The result should show that the storage type for sales_1_prt_mar_2025 is heap:

                                        List of relations
 Schema |          Name           |       Type        |  Owner  | Storage |  Size   | Description
--------+-------------------------+-------------------+---------+---------+---------+-------------
 public | sales                   | partitioned table | gpadmin | ao_row  | 0 bytes |
 public | sales_1_prt_feb_2025    | table             | gpadmin | ao_row  | 771 kB  |
 public | sales_1_prt_jan_2025    | table             | gpadmin | ao_row  | 822 kB  |
 public | sales_1_prt_mar_2025    | table             | gpadmin | heap    | 1280 kB |
 public | sales_1_prt_other_dates | table             | gpadmin | ao_row  | 160 kB  |
(5 rows)
IMPORTANT

Before exchanging the default partition, make sure the data in the staging table is valid and does not belong to any child partitions. Otherwise, queries that use GPORCA may return wrong results or cause data corruption after UPDATE or DELETE operations.

Rename a partition

CREATE TABLE sales
(
    id     INT,
    date   DATE,
    region TEXT,
    amount DECIMAL(10, 2)
)
    WITH (appendoptimized = true)
    DISTRIBUTED BY (id)
    PARTITION BY RANGE (date)
        SUBPARTITION BY LIST (region)
            SUBPARTITION TEMPLATE
            (
            SUBPARTITION asia VALUES ('Asia'),
            SUBPARTITION europe VALUES ('Europe')
            )
        (
        START (DATE '2025-01-01') INCLUSIVE
            END (DATE '2025-04-01') EXCLUSIVE
            EVERY (INTERVAL '1 month')
        );

This command renames the top-level parent table:

ALTER TABLE sales
    RENAME TO globalsales;

Note that it also automatically updates the prefixes of all its child table names.

To change a partition name, use the ALTER TABLE command with the RENAME PARTITION clause:

ALTER TABLE globalsales
    RENAME PARTITION FOR ('2025-01-01') TO jan_2025;

ALTER TABLE globalsales
    RENAME PARTITION FOR ('2025-02-01') TO feb_2025;

ALTER TABLE globalsales
    RENAME PARTITION FOR ('2025-03-01') TO mar_2025;

To check the new partition names, use the following query:

SELECT DISTINCT partitiontablename,
                partitiontype,
                partitionlevel,
                partitionrank
FROM gp_toolkit.gp_partitions
WHERE tablename = 'globalsales'
ORDER BY partitionlevel, partitionrank NULLS LAST, partitiontablename;

The result should look like this:

           partitiontablename            | partitiontype | partitionlevel | partitionrank
-----------------------------------------+---------------+----------------+---------------
 globalsales_1_prt_jan_2025              | range         |              1 |             1
 globalsales_1_prt_feb_2025              | range         |              1 |             2
 globalsales_1_prt_mar_2025              | range         |              1 |             3
 globalsales_1_prt_feb_2025_2_prt_asia   | list          |              2 |
 globalsales_1_prt_feb_2025_2_prt_europe | list          |              2 |
 globalsales_1_prt_jan_2025_2_prt_asia   | list          |              2 |
 globalsales_1_prt_jan_2025_2_prt_europe | list          |              2 |
 globalsales_1_prt_mar_2025_2_prt_asia   | list          |              2 |
 globalsales_1_prt_mar_2025_2_prt_europe | list          |              2 |
(9 rows)

Truncate a partition

CREATE TABLE sales
(
    id     INT,
    date   DATE,
    region TEXT,
    amount DECIMAL(10, 2)
)
    WITH (appendoptimized = true)
    DISTRIBUTED BY (id)
    PARTITION BY RANGE (date)
        SUBPARTITION BY LIST (region)
            SUBPARTITION TEMPLATE
            (
            SUBPARTITION asia VALUES ('Asia'),
            SUBPARTITION europe VALUES ('Europe')
            )
        (
        PARTITION jan_2025 START (DATE '2025-01-01') INCLUSIVE ,
        PARTITION feb_2025 START (DATE '2025-02-01') INCLUSIVE ,
        PARTITION mar_2025 START (DATE '2025-03-01') INCLUSIVE
            END (DATE '2025-04-01') EXCLUSIVE
        );

To delete all data from the specified partition without dropping the partition itself, use the ALTER TABLE command with the TRUNCATE PARTITION clause.

Insert sample data into the table:

INSERT INTO sales (id, date, region, amount)
SELECT gs.id,
       DATE '2025-01-01' + (gs.id % 90),
       CASE WHEN gs.id % 2 = 0 THEN 'Asia' ELSE 'Europe' END,
       round((random() * 1000)::NUMERIC, 2)
FROM generate_series(1, 40000) AS gs(id);

View information about the available partitions:

SELECT partitiontablename,
       partitiontype,
       partitionlevel,
       partitionrank
FROM gp_toolkit.gp_partitions
WHERE tablename = 'sales'
ORDER BY partitionlevel,
         partitionrank;

The result should look like this:

        partitiontablename         | partitiontype | partitionlevel | partitionrank
-----------------------------------+---------------+----------------+---------------
 sales_1_prt_jan_2025              | range         |              1 |             1
 sales_1_prt_feb_2025              | range         |              1 |             2
 sales_1_prt_mar_2025              | range         |              1 |             3
 sales_1_prt_jan_2025_2_prt_asia   | list          |              2 |
 sales_1_prt_jan_2025_2_prt_europe | list          |              2 |
 sales_1_prt_feb_2025_2_prt_asia   | list          |              2 |
 sales_1_prt_feb_2025_2_prt_europe | list          |              2 |
 sales_1_prt_mar_2025_2_prt_asia   | list          |              2 |
 sales_1_prt_mar_2025_2_prt_europe | list          |              2 |
(9 rows)

Check the row count in the sales_1_prt_jan_2025 table corresponding to an intermediate partition:

SELECT count(*)
FROM sales_1_prt_jan_2025;

The result might look like this:

 count
-------
 13794

Then, check that the sales_1_prt_feb_2025_2_prt_asia table corresponding to a leaf partition also contains data:

SELECT count(*)
FROM sales_1_prt_feb_2025_2_prt_asia;

To truncate the intermediate jan_2025 partition, use the following command:

ALTER TABLE sales
    TRUNCATE PARTITION jan_2025;

To truncate the asia subpartition in the feb_2025 partition, use the following SQL request:

ALTER TABLE sales
    ALTER PARTITION feb_2025
        TRUNCATE PARTITION asia;

Then, again use the SELECT count(*) command to check that the row count is 0 for both partitions.

Drop a partition

To drop the specified partition, use the ALTER TABLE command with the DROP PARTITION clause. The following example drops the jan_2025 partition from the sales table created in the Truncate a partition section:

ALTER TABLE sales
    DROP PARTITION jan_2025;

Check that the partition does not exist anymore by querying gp_toolkit.gp_partitions:

SELECT partitiontablename,
       partitiontype,
       partitionlevel,
       partitionrank
FROM gp_toolkit.gp_partitions
WHERE tablename = 'sales'
ORDER BY partitionlevel,
         partitionrank;

The result should look as follows:

        partitiontablename         | partitiontype | partitionlevel | partitionrank
-----------------------------------+---------------+----------------+---------------
 sales_1_prt_feb_2025              | range         |              1 |             1
 sales_1_prt_mar_2025              | range         |              1 |             2
 sales_1_prt_feb_2025_2_prt_asia   | list          |              2 |
 sales_1_prt_feb_2025_2_prt_europe | list          |              2 |
 sales_1_prt_mar_2025_2_prt_asia   | list          |              2 |
 sales_1_prt_mar_2025_2_prt_europe | list          |              2 |
(6 rows)

Limitations

General limitations

The following Greengage DB partitioned table limitations apply:

  • A partitioned table supports up to 32767 partitions per level.

  • Tables defined with the DISTRIBUTED REPLICATED policy cannot be partitioned.

  • If a partitioned table defines a PRIMARY KEY or UNIQUE constraint, the constraint must include all columns of the partition key. A unique index may exclude partition key columns; however, uniqueness is enforced only within individual partitions and not across the entire partitioned table.

  • The GPORCA query optimizer supports uniform multi-level partitioned tables. If GPORCA is enabled (the default) and a multi-level partitioned table is not uniform, Greengage DB uses the PostgreSQL planner to execute queries against the table.

External table partition limitations

  • An external table partition is a read-only external table. Operations that attempt to modify data in an external table partition return an error:

    • INSERT, UPDATE, and DELETE operations on an external table partition return an error.

    • TRUNCATE operations on an external table partition return an error.

    • COPY operations cannot write to a partitioned table if it includes an external table partition.

    • COPY operations that read from a partitioned table return an error if the table contains an external table partition, unless the IGNORE EXTERNAL PARTITIONS clause is specified. If the clause is specified, external table partitions are skipped.

      To use COPY with a partitioned table that has a leaf child partition that is an external table, use an SQL query instead. For example, if the sales table contains a leaf child partition that is an external table, the following command sends the data to stdout:

      COPY (SELECT * FROM sales) TO STDOUT;

  • The following operations are supported only if no data in an external table partition is modified:

    • Adding or dropping a column.

    • Changing the data type of a column.

  • The following ALTER PARTITION operations are not supported if a partitioned table contains an external table partition:

    • Setting a subpartition template.

    • Altering partition properties.

    • Creating a default partition.

    • Setting a distribution policy.

    • Setting or dropping a NOT NULL constraint on a column.

    • Adding or dropping constraints.

    • Splitting an external partition.

  • The gpbackup utility does not back up data from a leaf child partition if it is a readable external table.

Previous
Partitioning
Next
ggrebalance