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

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.

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

A partitioned table can have a maximum of 32767 partitions for each partition level.

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 significantly 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 on 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.

NOTE

  • If a partitioned table contains a primary key or a column with a UNIQUE constraint, these columns must be included in the partition key.

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

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

DBMS 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. To partition an existing table, follow the steps below:

  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 Child tables section of the output lists the child tables associated with the root table:

                      Append-Only Table "public.sales"
 Column |     Type      | Modifiers | Storage  | Stats target | Description
--------+---------------+-----------+----------+--------------+-------------
 id     | integer       |           | plain    |              |
 date   | date          |           | plain    |              |
 region | text          |           | extended |              |
 amount | numeric(10,2) |           | main     |              |
Compression Type: None
Compression Level: 0
Block Size: 32768
Checksum: t
Child tables: sales_1_prt_2,
              sales_1_prt_3,
              sales_1_prt_4,
              sales_1_prt_other_dates
Distributed by: (id)
Partition by: (date)
Options: appendonly=true

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 Child tables section, the output also includes an Inherits section indicating that this table inherits columns and constraints of the sales table:

                  Append-Only Table "public.sales_1_prt_2"
 Column |     Type      | Modifiers | Storage  | Stats target | Description
--------+---------------+-----------+----------+--------------+-------------
 id     | integer       |           | plain    |              |
 date   | date          |           | plain    |              |
 region | text          |           | extended |              |
 amount | numeric(10,2) |           | main     |              |
Compression Type: None
Compression Level: 0
Block Size: 32768
Checksum: t
Check constraints:
    "sales_1_prt_2_check" CHECK (date >= '2025-01-01'::date AND date < '2025-02-01'::date)
Inherits: sales
Child tables: sales_1_prt_2_2_prt_asia,
              sales_1_prt_2_2_prt_europe,
              sales_1_prt_2_2_prt_other_regions
Distributed by: (id)
Partition by: (region)
Options: appendonly=true

SQL commands

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

pg_catalog.pg_partitions

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

SELECT partitiontablename,
       partitionname,
       partitiontype,
       partitionlevel,
       partitionrank
FROM pg_catalog.pg_partitions
WHERE schemaname = 'public'
  AND tablename = 'sales';

The result might look as follows:

             partitiontablename              | partitionname | partitiontype | partitionlevel | partitionrank
---------------------------------------------+---------------+---------------+----------------+---------------
 sales_1_prt_2                               |               | range         |              0 |             1
 sales_1_prt_3                               |               | range         |              0 |             2
 sales_1_prt_4                               |               | range         |              0 |             3
 sales_1_prt_other_dates                     | other_dates   | range         |              0 |
 sales_1_prt_2_2_prt_asia                    | asia          | list          |              1 |
 sales_1_prt_2_2_prt_europe                  | europe        | list          |              1 |
 sales_1_prt_2_2_prt_other_regions           | other_regions | list          |              1 |
 sales_1_prt_3_2_prt_asia                    | asia          | list          |              1 |
 sales_1_prt_3_2_prt_europe                  | europe        | list          |              1 |
 sales_1_prt_3_2_prt_other_regions           | other_regions | list          |              1 |
 sales_1_prt_4_2_prt_asia                    | asia          | list          |              1 |
 sales_1_prt_4_2_prt_europe                  | europe        | list          |              1 |
 sales_1_prt_4_2_prt_other_regions           | other_regions | list          |              1 |
 sales_1_prt_other_dates_2_prt_asia          | asia          | list          |              1 |
 sales_1_prt_other_dates_2_prt_europe        | europe        | list          |              1 |
 sales_1_prt_other_dates_2_prt_other_regions | other_regions | list          |              1 |
(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;

  • partitionname — a partition name that is given at creation time and can be referred to in the ALTER TABLE commands;

  • partitiontype — a partition type;

  • partitionlevel — a partition level in the hierarchy (starting with 0 for partitions at the first level);

  • 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.pg_partition_templates

To show all subpartitions created with a subpartition template, run the SQL query against the pg_catalog.pg_partition_templates system view:

SELECT schemaname,
       tablename,
       partitionname,
       partitiontype,
       partitionlevel,
       partitionrank
FROM pg_catalog.pg_partition_templates;

The result should look like this:

 schemaname | tablename | partitionname | partitiontype | partitionlevel | partitionrank
------------+-----------+---------------+---------------+----------------+---------------
 public     | sales     | asia          | list          |              1 |
 public     | sales     | europe        | list          |              1 |
 public     | sales     | other_regions | list          |              1 |
(3 rows)

pg_catalog.pg_partition_columns

To show all partition key columns, get information from the pg_catalog.pg_partition_columns system view:

SELECT *
FROM pg_catalog.pg_partition_columns;

The result should look like this:

 schemaname | tablename | columnname | partitionlevel | position_in_partition_key
------------+-----------+------------+----------------+---------------------------
 public     | sales     | date       |              0 |                         1
 public     | sales     | region     |              1 |                         1
 public     | sales     | region     |              1 |                         1
(3 rows)

pg_catalog.pg_partition

To display the partition levels along with the corresponding tables, use the pg_catalog.pg_partition system catalog table:

SELECT parent_table.relname,
       partition_info.parkind,
       partition_info.parlevel,
       partition_info.paristemplate
FROM pg_catalog.pg_partition AS partition_info
         LEFT JOIN pg_class AS parent_table ON parent_table.oid = partition_info.parrelid;

The result might look like this:

 relname | parkind | parlevel | paristemplate
---------+---------+----------+---------------
 sales   | l       |        1 | f
 sales   | l       |        1 | t
 sales   | r       |        0 | f
(3 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 (RANK (1))
        ADD DEFAULT PARTITION other_regions;

ALTER TABLE sales
    ALTER PARTITION FOR (RANK (2))
        ADD DEFAULT PARTITION other_regions;

ALTER TABLE sales
    ALTER PARTITION FOR (RANK (3))
        ADD DEFAULT PARTITION other_regions;
NOTE

You can get the ranks of partitions in the pg_catalog.pg_partitions system view.

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

SELECT partitiontablename,
       partitionname,
       partitiontype,
       partitionlevel,
       partitionrank
FROM pg_catalog.pg_partitions
WHERE schemaname = 'public'
  AND tablename = 'sales';

The output should include the other_dates and other_regions partitions:

          partitiontablename          | partitionname | partitiontype | partitionlevel | partitionrank
--------------------------------------+---------------+---------------+----------------+---------------
 sales_1_prt_1                        |               | range         |              0 |             1
 sales_1_prt_2                        |               | range         |              0 |             2
 sales_1_prt_3                        |               | range         |              0 |             3
 sales_1_prt_other_dates              | other_dates   | range         |              0 |
 sales_1_prt_1_2_prt_asia             | asia          | list          |              1 |
 sales_1_prt_1_2_prt_europe           | europe        | list          |              1 |
 sales_1_prt_1_2_prt_other_regions    | other_regions | list          |              1 |
 sales_1_prt_2_2_prt_asia             | asia          | list          |              1 |
 sales_1_prt_2_2_prt_europe           | europe        | list          |              1 |
 sales_1_prt_2_2_prt_other_regions    | other_regions | list          |              1 |
 sales_1_prt_3_2_prt_asia             | asia          | list          |              1 |
 sales_1_prt_3_2_prt_europe           | europe        | list          |              1 |
 sales_1_prt_3_2_prt_other_regions    | other_regions | list          |              1 |
 sales_1_prt_other_dates_2_prt_asia   | asia          | list          |              1 |
 sales_1_prt_other_dates_2_prt_europe | europe        | list          |              1 |
(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 (RANK (4))
        ADD PARTITION africa VALUES ('Africa');

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

SELECT partitiontablename,
       partitionname,
       partitiontype,
       partitionlevel,
       partitionrank
FROM pg_catalog.pg_partitions
WHERE schemaname = 'public'
  AND tablename = 'sales';

The result includes the sales_1_prt_apr_2025 and sales_1_prt_apr_2025_2_prt_africa partitions:

        partitiontablename         | partitionname | partitiontype | partitionlevel | partitionrank
-----------------------------------+---------------+---------------+----------------+---------------
 sales_1_prt_jan_2025              | jan_2025      | range         |              0 |             1
 sales_1_prt_feb_2025              | feb_2025      | range         |              0 |             2
 sales_1_prt_mar_2025              | mar_2025      | range         |              0 |             3
 sales_1_prt_apr_2025              | apr_2025      | range         |              0 |             4
 sales_1_prt_apr_2025_2_prt_asia   | asia          | list          |              1 |
 sales_1_prt_apr_2025_2_prt_europe | europe        | list          |              1 |
 sales_1_prt_apr_2025_2_prt_africa | africa        | list          |              1 |
 sales_1_prt_feb_2025_2_prt_asia   | asia          | list          |              1 |
 sales_1_prt_feb_2025_2_prt_europe | europe        | list          |              1 |
 sales_1_prt_jan_2025_2_prt_asia   | asia          | list          |              1 |
 sales_1_prt_jan_2025_2_prt_europe | europe        | list          |              1 |
 sales_1_prt_mar_2025_2_prt_asia   | asia          | list          |              1 |
 sales_1_prt_mar_2025_2_prt_europe | europe        | list          |              1 |
(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 — only 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 partitionname,
       partitionboundary
FROM pg_catalog.pg_partitions
WHERE schemaname = 'public'
  AND tablename = 'sales';

The result looks like this:

  partitionname  |                                           partitionboundary
-----------------+--------------------------------------------------------------------------------------------------------
 jan_2025        | PARTITION jan_2025 START ('2025-01-01'::date) END ('2025-02-01'::date) WITH (appendonly='true')
 feb_2025        | PARTITION feb_2025 START ('2025-02-01'::date) END ('2025-03-01'::date) WITH (appendonly='true')
 mar_1to15_2025  | PARTITION mar_1to15_2025 START ('2025-03-01'::date) END ('2025-03-16'::date) WITH (appendonly='true')
 mar_16to31_2025 | PARTITION mar_16to31_2025 START ('2025-03-16'::date) END ('2025-04-01'::date) WITH (appendonly='true')
(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 partitionname,
       partitionboundary
FROM pg_catalog.pg_partitions
WHERE schemaname = 'public'
  AND tablename = 'sales';

The result should look like this:

 partitionname |                                        partitionboundary
---------------+-------------------------------------------------------------------------------------------------
 jan_2025      | PARTITION jan_2025 START ('2025-01-01'::date) END ('2025-02-01'::date) WITH (appendonly='true')
 feb_2025      | PARTITION feb_2025 START ('2025-02-01'::date) END ('2025-03-01'::date) WITH (appendonly='true')
 mar_2025      | PARTITION mar_2025 START ('2025-03-01'::date) END ('2025-04-01'::date) WITH (appendonly='true')
 apr_2025      | PARTITION apr_2025 START ('2025-04-01'::date) END ('2025-05-01'::date) WITH (appendonly='true')
 other_dates   | DEFAULT PARTITION other_dates  WITH (appendonly='true')
(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,
       partitionboundary
FROM pg_catalog.pg_partitions
WHERE schemaname = 'public'
  AND tablename = 'sales';

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

            partitiontablename            |                                        partitionboundary
------------------------------------------+-------------------------------------------------------------------------------------------------
 sales_1_prt_jan_2025                     | PARTITION jan_2025 START ('2025-01-01'::date) END ('2025-02-01'::date) WITH (appendonly='true')
 sales_1_prt_feb_2025                     | PARTITION feb_2025 START ('2025-02-01'::date) END ('2025-03-01'::date) WITH (appendonly='true')
 sales_1_prt_mar_2025                     | PARTITION mar_2025 START ('2025-03-01'::date) END ('2025-04-01'::date) WITH (appendonly='true')
 sales_1_prt_apr_2025                     | PARTITION apr_2025 START ('2025-04-01'::date) END ('2025-05-01'::date)
 sales_1_prt_apr_2025_2_prt_asia          | SUBPARTITION asia VALUES('Asia')
 sales_1_prt_apr_2025_2_prt_europe        | SUBPARTITION europe VALUES('Europe')
 sales_1_prt_apr_2025_2_prt_africa        | SUBPARTITION africa VALUES('Africa')
 sales_1_prt_apr_2025_2_prt_other_regions | DEFAULT SUBPARTITION other_regions
 sales_1_prt_feb_2025_2_prt_asia          | SUBPARTITION asia VALUES('Asia') WITH (appendonly='true')
 sales_1_prt_feb_2025_2_prt_europe        | SUBPARTITION europe VALUES('Europe') WITH (appendonly='true')
 sales_1_prt_jan_2025_2_prt_asia          | SUBPARTITION asia VALUES('Asia') WITH (appendonly='true')
 sales_1_prt_jan_2025_2_prt_europe        | SUBPARTITION europe VALUES('Europe') WITH (appendonly='true')
 sales_1_prt_mar_2025_2_prt_asia          | SUBPARTITION asia VALUES('Asia') WITH (appendonly='true')
 sales_1_prt_mar_2025_2_prt_europe        | SUBPARTITION europe VALUES('Europe') WITH (appendonly='true')
(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 a 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 to 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 a 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

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                   | table | gpadmin | append only | 160 kB  |
 public | sales_1_prt_feb_2025    | table | gpadmin | append only | 803 kB  |
 public | sales_1_prt_jan_2025    | table | gpadmin | append only | 854 kB  |
 public | sales_1_prt_mar_2025    | table | gpadmin | heap        | 1280 kB |
 public | sales_1_prt_other_dates | table | gpadmin | append only | 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 new partition names, use the following query:

SELECT partitiontablename,
       partitionname,
       partitiontype,
       partitionlevel,
       partitionrank
FROM pg_catalog.pg_partitions
WHERE schemaname = 'public'
  AND tablename = 'globalsales';

The result should look like this:

           partitiontablename            | partitionname | partitiontype | partitionlevel | partitionrank
-----------------------------------------+---------------+---------------+----------------+---------------
 globalsales_1_prt_jan_2025              | jan_2025      | range         |              0 |             1
 globalsales_1_prt_feb_2025              | feb_2025      | range         |              0 |             2
 globalsales_1_prt_mar_2025              | mar_2025      | range         |              0 |             3
 globalsales_1_prt_feb_2025_2_prt_asia   | asia          | list          |              1 |
 globalsales_1_prt_feb_2025_2_prt_europe | europe        | list          |              1 |
 globalsales_1_prt_jan_2025_2_prt_asia   | asia          | list          |              1 |
 globalsales_1_prt_jan_2025_2_prt_europe | europe        | list          |              1 |
 globalsales_1_prt_mar_2025_2_prt_asia   | asia          | list          |              1 |
 globalsales_1_prt_mar_2025_2_prt_europe | europe        | list          |              1 |

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 to 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,
       partitionname,
       partitionrank
FROM pg_catalog.pg_partitions
WHERE schemaname = 'public'
  AND tablename = 'sales';

The result should look like this:

        partitiontablename         | partitionname | partitionrank
-----------------------------------+---------------+---------------
 sales_1_prt_jan_2025              | jan_2025      |             1
 sales_1_prt_feb_2025              | feb_2025      |             2
 sales_1_prt_mar_2025              | mar_2025      |             3
 sales_1_prt_feb_2025_2_prt_asia   | asia          |
 sales_1_prt_feb_2025_2_prt_europe | europe        |
 sales_1_prt_jan_2025_2_prt_asia   | asia          |
 sales_1_prt_jan_2025_2_prt_europe | europe        |
 sales_1_prt_mar_2025_2_prt_asia   | asia          |
 sales_1_prt_mar_2025_2_prt_europe | europe        |
(9 rows)

Check a 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 a 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 pg_partitions:

SELECT partitiontablename,
       partitionname,
       partitionrank
FROM pg_catalog.pg_partitions
WHERE schemaname = 'public'
  AND tablename = 'sales';

The result should look as follows:

        partitiontablename         | partitionname | partitionrank
-----------------------------------+---------------+---------------
 sales_1_prt_feb_2025              | feb_2025      |             1
 sales_1_prt_mar_2025              | mar_2025      |             2
 sales_1_prt_feb_2025_2_prt_asia   | asia          |
 sales_1_prt_feb_2025_2_prt_europe | europe        |
 sales_1_prt_mar_2025_2_prt_asia   | asia          |
 sales_1_prt_mar_2025_2_prt_europe | europe        |
(6 rows)
Previous
Distribution
Next
Sequences