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

A partitioning declaration includes the partitioning method described below, plus a list of columns or expressions to be used as the partition key.

Introduction to table partitioning

Table partitioning overview

Greengage DB supports the following forms of table partitioning:

Range partitioning

A table is divided into ranges based on a partition key column (or columns). Ranges do not overlap with each other. This method is commonly used for dates or numeric identifiers.

Range boundaries are:

  • inclusive at the lower bound;

  • exclusive at the upper bound.

For example, with ranges 1 — 10 and 10 — 20, the value 10 belongs to the second range.

List partitioning

A table is divided by explicitly assigning key values to each partition. This is useful for discrete categories such as sales regions or product lines.

Hash partitioning

A table is divided using a hash function on the partition key.

Each partition is defined by a modulus and a remainder. A row is placed into the partition where:

hash(partition_key) % modulus = remainder

This method helps distribute data evenly when no natural grouping exists.

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_2025_01    (date >= '2025-01-01' AND date < '2025-02-01')
   ├─── sales_2025_02    (date >= '2025-02-01' AND date < '2025-03-01')
   └─── sales_2025_03    (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_2025_01               (date >= '2025-01-01' AND date < '2025-02-01')
   │    ├─── sales_2025_01_asia          (region = 'Asia')
   │    └─── sales_2025_01_europe        (region = 'Europe')
   ├─── sales_2025_02               (date >= '2025-02-01' AND date < '2025-03-01')
   │    ├─── sales_2025_02_asia          (region = 'Asia')
   │    └─── sales_2025_02_europe        (region = 'Europe')
   └─── sales_2025_03               (date >= '2025-03-01' AND date < '2025-04-01')
        ├─── sales_2025_03_asia          (region = 'Asia')
        └─── sales_2025_03_europe        (region = 'Europe')

A partitioned table itself is a virtual table and does not store data. Data is stored in its partitions, which are regular tables associated with the partitioned table. Each partition holds a subset of rows defined by its partition bounds. When you insert data into a partitioned table, each row is automatically routed to the appropriate partition based on the partition key values. If a row’s partition key is updated and no longer fits the original partition, the row is automatically moved to the correct partition.

Partitions can themselves be defined as partitioned tables, enabling subpartitioning. All partitions must have the same columns as their parent table. However, each partition can have its own indexes, constraints, and default values, independent of other partitions.

It is not possible to convert a regular table into a partitioned table, or a partitioned table into a regular table. However, you can attach an existing regular or partitioned table as a partition of a partitioned table, or detach a partition to make it a standalone table. These operations can simplify and speed up many maintenance tasks.

Partitions can also be foreign tables. However, in this case, it is the user’s responsibility to ensure that the data in the foreign table follows the partitioning rules.

Decide whether to use partitioning

Use partitioning if all or most of the following points apply 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 a 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 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 other administrative tasks.

Choose the partitioning syntax

Greengage DB 7 and later retain most of the partitioning syntax from earlier versions — known as the classic syntax. It also introduces support for a declarative partitioning syntax derived from PostgreSQL.

The classic syntax is provided for backward compatibility with earlier Greengage DB versions. It is suitable for homogeneous partitioned tables, where all partitions are at the same leaf level and follow the same partitioning rule. If you are familiar with Greengage DB 6 partitioning or already have tables defined using the classic syntax, you can continue to use it.

The following table compares key features to help you choose the most appropriate syntax for your data model.

Feature Classic syntax Declarative syntax

Heterogeneous partition hierarchy

Not supported — all leaf partitions must be at the same level

Supported. Leaf partitions can exist at different levels. Individual child tables can use different partition columns and partitioning strategies

Expressions in partition key

Not supported

Supported

Multi-column range partitioning

Not supported

Supported

Multi-column list partitioning

Supported (via composite type)

Not supported

Hash partitioning

Not supported

Supported

Adding a partition

Adding a partition acquires an ACCESS EXCLUSIVE lock on the parent table

Attaching a partition acquires a less restrictive SHARE UPDATE EXCLUSIVE lock on the parent table

Removing a partition

Dropping a partition deletes all data it contains. To preserve the data, swap the partition with a staging table first

A partition can be detached directly, preserving its data as a standalone table

Subpartition templating

Supported — child table definitions stay consistent with the parent by default

Not supported — you are responsible for keeping table definitions consistent

Partition maintenance

Operations on partitions go through the parent table, requiring knowledge of the partition hierarchy

Partitions are managed directly, without requiring knowledge of the partition hierarchy

Create a partitioned table

To execute commands described in the following sections, connect to the Greengage DB coordinator 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

You partition a table when you create it with the CREATE TABLE command. To partition a table:

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

  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.

  4. Create the partitioned table.

  5. Create partitions.

After a partition is created, Greengage DB routes incoming data from the root table to the appropriate partition based on its constraints.

Subpartitions can use the same partition key columns as their parent. However, you must ensure that each subpartition’s bounds fit within the bounds of its parent. Greengage DB does not validate this automatically.

You can reuse the same column for multiple partition levels, such as partitioning by month and subpartitioning by day.

When designing partitions, prefer the most granular level that makes sense for your data. For example, partitioning by day creates 365 partitions per year, which is often simpler than a multi-level year → month → day hierarchy.

A deeper partition hierarchy can improve query planning performance, but a flatter design usually provides better execution performance.

Date range partitioning

The following example creates a range-partitioned table using the PARTITION BY RANGE clause. The partition key is the date column:

CREATE TABLE sales
(
    id     INT,
    date   DATE,
    amount DECIMAL(10, 2)
)
    USING ao_row
    DISTRIBUTED BY (id)
    PARTITION BY RANGE (date);

The next statements define monthly partitions covering the first quarter of 2025:

CREATE TABLE sales_2025_01
    PARTITION OF sales
        FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

CREATE TABLE sales_2025_02
    PARTITION OF sales
        FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');

CREATE TABLE sales_2025_03
    PARTITION OF sales
        FOR VALUES FROM ('2025-03-01') TO ('2025-04-01');

Finally, a default partition is created to store rows that fall outside the defined range partitions:

CREATE TABLE sales_other_dates
    PARTITION OF sales DEFAULT;

Numeric range partitioning

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

CREATE TABLE sales
(
    id     INT,
    date   DATE,
    amount DECIMAL(10, 2)
)
    USING ao_row
    DISTRIBUTED BY (id)
    PARTITION BY RANGE (id);

The following statements define partitions that split the data into ranges of sales identifiers:

CREATE TABLE sales_0_100000
    PARTITION OF sales
        FOR VALUES FROM (0) TO (100000);

CREATE TABLE sales_100000_200000
    PARTITION OF sales
        FOR VALUES FROM (100000) TO (200000);

CREATE TABLE sales_200000_300000
    PARTITION OF sales
        FOR VALUES FROM (200000) TO (300000);

List partitioning

The following example creates a list-partitioned sales table based on the region column:

CREATE TABLE sales
(
    id     INT,
    date   DATE,
    region TEXT,
    amount DECIMAL(10, 2)
)
    USING ao_row
    DISTRIBUTED BY (id)
    PARTITION BY LIST (region);

The following statements define partitions for specific regions:

CREATE TABLE sales_asia
    PARTITION OF sales
        FOR VALUES IN ('Asia');

CREATE TABLE sales_europe
    PARTITION OF sales
        FOR VALUES IN ('Europe');

Hash partitioning

The following example creates a hash-partitioned sales table using the id column as the partition key:

CREATE TABLE sales
(
    id     INT,
    date   DATE,
    region TEXT,
    amount DECIMAL(10, 2)
)
    USING ao_row
    DISTRIBUTED BY (id)
    PARTITION BY HASH (id);

The following statements define hash partitions using a modulus of 4, which distributes rows evenly across four partitions based on the hash value of the id column:

CREATE TABLE sales_prt_1
    PARTITION OF sales
        FOR VALUES WITH (MODULUS 4, REMAINDER 0);

CREATE TABLE sales_prt_2
    PARTITION OF sales
        FOR VALUES WITH (MODULUS 4, REMAINDER 1);

CREATE TABLE sales_prt_3
    PARTITION OF sales
        FOR VALUES WITH (MODULUS 4, REMAINDER 2);

CREATE TABLE sales_prt_4
    PARTITION OF sales
        FOR VALUES WITH (MODULUS 4, REMAINDER 3);

Multi-level partitioning

The following example creates a multi-level partitioned sales table. The first level uses RANGE partitioning on the date column, and each date partition is further subdivided using LIST partitioning on the region column.

The base table defines the overall partitioning strategy:

CREATE TABLE sales
(
    id     INT,
    date   DATE,
    region TEXT,
    amount DECIMAL(10, 2)
)
    USING ao_row
    DISTRIBUTED BY (id)
    PARTITION BY RANGE (date);

The first-level partitions divide the data into monthly ranges for Q1 2025:

CREATE TABLE sales_2025_01
    PARTITION OF sales
        FOR VALUES FROM ('2025-01-01') TO ('2025-02-01')
    PARTITION BY LIST (region);

CREATE TABLE sales_2025_02
    PARTITION OF sales
        FOR VALUES FROM ('2025-02-01') TO ('2025-03-01')
    PARTITION BY LIST (region);

CREATE TABLE sales_2025_03
    PARTITION OF sales
        FOR VALUES FROM ('2025-03-01') TO ('2025-04-01')
    PARTITION BY LIST (region);

Each monthly partition is then further partitioned by region:

  • January:

    CREATE TABLE sales_2025_01_asia
    PARTITION OF sales_2025_01
        FOR VALUES IN ('Asia');

CREATE TABLE sales_2025_01_europe
    PARTITION OF sales_2025_01
        FOR VALUES IN ('Europe');

  • February:

    CREATE TABLE sales_2025_02_asia
    PARTITION OF sales_2025_02
        FOR VALUES IN ('Asia');

CREATE TABLE sales_2025_02_europe
    PARTITION OF sales_2025_02
        FOR VALUES IN ('Europe');

  • March:

    CREATE TABLE sales_2025_03_asia
    PARTITION OF sales_2025_03
        FOR VALUES IN ('Asia');

CREATE TABLE sales_2025_03_europe
    PARTITION OF sales_2025_03
        FOR VALUES IN ('Europe');

Load data into a partitioned table

After the partitioned table structure is created, the root partitioned table contains no data. When data is inserted into the root table, Greengage DB routes each row to the appropriate leaf partition based on the partitioning rules. In a multi-level partition hierarchy, only the lowest-level (leaf) partitions store data.

Greengage DB rejects rows that cannot be mapped to any leaf partition, causing the load operation to fail. To prevent rejection of such rows during data loading, define the partition hierarchy with a default partition. Any rows that do not match the constraints of existing partitions are then stored in the default partition. For more information, see Add a default partition.

At runtime, the query optimizer scans the partition hierarchy and uses partition constraints to determine which child partitions must be accessed to satisfy the query conditions. If a default partition exists, it is always included in the scan. As a result, a default partition that contains data may increase the overall scan time and reduce query performance.

When you use COPY or INSERT to load data into a parent table, Greengage DB automatically routes each row to the appropriate leaf partition.

A common approach for loading data into partitioned tables is to use an intermediate staging table: load data into the staging table first, then attach it to the partition hierarchy. See Add a new partition for more information.

Partition pruning

Partition pruning is a query optimization technique that improves performance for partitioned tables. You can enable or disable partition pruning in the PostgreSQL-based planner using the enable_partition_pruning server configuration parameter.

When partition pruning is enabled, the planner analyzes the query’s WHERE clause and the partition definitions to determine whether a partition can contain matching rows. If it can prove that a partition cannot satisfy the query conditions, it excludes (prunes) that partition from the execution plan, reducing the amount of data scanned.

Using the EXPLAIN command together with the enable_partition_pruning configuration parameter, you can observe how partition pruning affects query plans. The following example demonstrates the difference between a query plan with partition pruning enabled and disabled, using the sales table defined in Multi-level partitioning.

First, insert sample data into the sales 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);

The following query is executed with partition pruning enabled:

EXPLAIN (COSTS OFF)
SELECT count(*),
       sum(amount)
FROM sales
WHERE date >= DATE '2025-02-01'
  AND date < DATE '2025-02-03'
  AND region = 'Asia';

With partition pruning enabled, the planner eliminates irrelevant partitions and scans only the required data (Seq Scan on sales_2025_02_asia):

                                                       QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate
   ->  Gather Motion 4:1  (slice1; segments: 4)
         ->  Partial Aggregate
               ->  Seq Scan on sales_2025_02_asia
                     Filter: ((date >= '2025-02-01'::date) AND (date < '2025-02-03'::date) AND (region = 'Asia'::text))
 Optimizer: Postgres-based planner
(6 rows)

Next, partition pruning is disabled for the session:

SET enable_partition_pruning = off;

The same query is executed again:

EXPLAIN (COSTS OFF)
SELECT count(*),
       sum(amount)
FROM sales
WHERE date >= DATE '2025-02-01'
  AND date < DATE '2025-02-03'
  AND region = 'Asia';

Without partition pruning, the planner includes all partitions in the execution plan, even those that cannot satisfy the query conditions:

                                                          QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate
   ->  Gather Motion 4:1  (slice1; segments: 4)
         ->  Partial Aggregate
               ->  Append
                     ->  Seq Scan on sales_2025_01_asia
                           Filter: ((date >= '2025-02-01'::date) AND (date < '2025-02-03'::date) AND (region = 'Asia'::text))
                     ->  Seq Scan on sales_2025_01_europe
                           Filter: ((date >= '2025-02-01'::date) AND (date < '2025-02-03'::date) AND (region = 'Asia'::text))
                     ->  Seq Scan on sales_2025_02_asia
                           Filter: ((date >= '2025-02-01'::date) AND (date < '2025-02-03'::date) AND (region = 'Asia'::text))
                     ->  Seq Scan on sales_2025_02_europe
                           Filter: ((date >= '2025-02-01'::date) AND (date < '2025-02-03'::date) AND (region = 'Asia'::text))
                     ->  Seq Scan on sales_2025_03_asia
                           Filter: ((date >= '2025-02-01'::date) AND (date < '2025-02-03'::date) AND (region = 'Asia'::text))
                     ->  Seq Scan on sales_2025_03_europe
                           Filter: ((date >= '2025-02-01'::date) AND (date < '2025-02-03'::date) AND (region = 'Asia'::text))
 Optimizer: Postgres-based planner
(17 rows)

Partition pruning is based solely on constraints derived from partition keys, not on indexes. For this reason, indexes on partition key columns are not required for pruning to work.

Whether to create an index on a partition depends on the expected query patterns. If queries typically scan only a small portion of a partition, an index can improve performance. However, if queries usually scan most or all of the partition, an index is unlikely to provide any benefit and may add unnecessary overhead.

Partition pruning can occur not only during query planning, but also at execution time. This is useful when partition-relevant values are not known at planning time. Examples include parameters supplied in a PREPARE statement, values returned from subqueries, or parameter values used on the inner side of a nested loop join. In these cases, additional partitions can be eliminated dynamically as the query executes.

Partition pruning during execution can take place at the following stages:

  • During query plan initialization.

    At this stage, partition pruning can be applied using parameter values that are already known when the plan starts executing. Partitions eliminated during initialization do not appear in the query’s EXPLAIN or EXPLAIN ANALYZE output. The number of partitions removed at this stage can be observed via the Subplans Removed property in the EXPLAIN output.

  • During query execution.

    Partition pruning can also occur dynamically while the query is running, using values that are only available at runtime. This includes values returned from subqueries and parameters used in nested loop joins. Because these values may change during execution, partition pruning is re-evaluated whenever the relevant runtime parameters change.

    To determine whether partitions were pruned during execution, inspect the loops property in the EXPLAIN ANALYZE output. Different subplans may show different loop counts depending on how often they were executed or pruned. Some subplans may appear as (never executed) if they were fully pruned at runtime.

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.

CREATE TABLE sales
(
    id     INT,
    date   DATE,
    amount DECIMAL(10, 2)
)
    USING ao_row
    DISTRIBUTED BY (id);
INSERT INTO sales (id, date, amount)
SELECT gs.id,
       DATE '2025-01-01' + (gs.id % 90),
       round((random() * 1000)::NUMERIC, 2)
FROM generate_series(1, 40000) AS gs(id);

  1. Create a new partitioned table:

    CREATE TABLE sales_partitioned
(
    LIKE sales
)
    USING ao_row
    DISTRIBUTED BY (id)
    PARTITION BY RANGE (date);
    CREATE TABLE sales_2025_01 PARTITION OF sales_partitioned
    FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

CREATE TABLE sales_2025_02 PARTITION OF sales_partitioned
    FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');

CREATE TABLE sales_2025_03 PARTITION OF sales_partitioned
    FOR VALUES FROM ('2025-03-01') TO ('2025-04-01');

CREATE TABLE other_dates PARTITION OF sales_partitioned DEFAULT;

  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 to 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)
)
    USING ao_row
    DISTRIBUTED BY (id)
    PARTITION BY RANGE (date);

CREATE TABLE sales_2025_01
    PARTITION OF sales
        FOR VALUES FROM ('2025-01-01') TO ('2025-02-01')
    PARTITION BY LIST (region);

CREATE TABLE sales_2025_02
    PARTITION OF sales
        FOR VALUES FROM ('2025-02-01') TO ('2025-03-01')
    PARTITION BY LIST (region);

CREATE TABLE sales_2025_03
    PARTITION OF sales
        FOR VALUES FROM ('2025-03-01') TO ('2025-04-01')
    PARTITION BY LIST (region);

CREATE TABLE sales_2025_01_asia
    PARTITION OF sales_2025_01
        FOR VALUES IN ('Asia');

CREATE TABLE sales_2025_01_europe
    PARTITION OF sales_2025_01
        FOR VALUES IN ('Europe');

CREATE TABLE sales_2025_02_asia
    PARTITION OF sales_2025_02
        FOR VALUES IN ('Asia');

CREATE TABLE sales_2025_02_europe
    PARTITION OF sales_2025_02
        FOR VALUES IN ('Europe');

CREATE TABLE sales_2025_03_asia
    PARTITION OF sales_2025_03
        FOR VALUES IN ('Asia');

CREATE TABLE sales_2025_03_europe
    PARTITION OF sales_2025_03
        FOR VALUES IN ('Europe');

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_2025_01 FOR VALUES FROM ('2025-01-01') TO ('2025-02-01'), PARTITIONED,
            sales_2025_02 FOR VALUES FROM ('2025-02-01') TO ('2025-03-01'), PARTITIONED,
            sales_2025_03 FOR VALUES FROM ('2025-03-01') TO ('2025-04-01'), 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_2025_02

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_2025_02"
 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-02-01') TO ('2025-03-01')
Partition constraint: ((date IS NOT NULL) AND (date >= '2025-02-01'::date) AND (date < '2025-03-01'::date))
Partition key: LIST (region)
Partitions: sales_2025_02_asia FOR VALUES IN ('Asia'),
            sales_2025_02_europe FOR VALUES IN ('Europe')
Distributed by: (id)
Access method: ao_row

System catalogs

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

pg_catalog.pg_partitioned_table

To view all partitioned tables that use columns as partition keys, 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                    |                   | r
 sales_2025_01        | region                  |                   | l
 sales_2025_02        | region                  |                   | l
 sales_2025_03        | region                  |                   | l
(4 rows)

The command returns the following columns:

  • partition_table_name — the 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 in the query result.

gp_toolkit.gp_partitions

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_2025_01        | range         |              1 |             1
 sales_2025_02        | range         |              1 |             2
 sales_2025_03        | range         |              1 |             3
 sales_2025_01_asia   | list          |              2 |
 sales_2025_01_europe | list          |              2 |
 sales_2025_02_asia   | list          |              2 |
 sales_2025_02_europe | list          |              2 |
 sales_2025_03_asia   | list          |              2 |
 sales_2025_03_europe | list          |              2 |
(9 rows)

The output contains the following columns:

  • partitiontablename — the 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.

System functions

You can use the following functions to obtain information about 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 relation passed as the argument, 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_2025_02 table:

SELECT * FROM pg_partition_tree('sales_2025_02');

Result:

        relid         |  parentrelid  | isleaf | level
----------------------+---------------+--------+-------
 sales_2025_02        | sales         | f      |     0
 sales_2025_02_asia   | sales_2025_02 | t      |     1
 sales_2025_02_europe | sales_2025_02 | t      |     1
(3 rows)

Maintain partitioned tables

Partition management is primarily based on the following core operations:

  • CREATE TABLE …​ PARTITION OF — create a new partition and attach it to a partitioned table.

  • ALTER TABLE …​ ATTACH PARTITION — attach an existing table as a partition.

  • ALTER TABLE …​ DETACH PARTITION — detach a partition from a partitioned table.

  • ALTER INDEX …​ ATTACH PARTITION — attach an index to a partitioned table.

Add a new partition

CREATE TABLE sales
(
    id     INT,
    date   DATE,
    amount DECIMAL(10, 2)
)
    USING ao_row
    DISTRIBUTED BY (id)
    PARTITION BY RANGE (date);

CREATE TABLE sales_2025_01 PARTITION OF sales
    FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

CREATE TABLE sales_2025_02 PARTITION OF sales
    FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');

CREATE TABLE sales_2025_03 PARTITION OF sales
    FOR VALUES FROM ('2025-03-01') TO ('2025-04-01');

New partitions are created to accommodate data that does not fit into existing partitions, such as a new time period or a new sales region. A partition can be added either at the time the partitioned table is created or when the table is already in place.

Create and attach a partition immediately

The following example creates a new partition directly as part of the partitioned table definition using CREATE TABLE …​ PARTITION OF:

CREATE TABLE sales_2025_04 PARTITION OF sales
    FOR VALUES FROM ('2025-04-01') TO ('2025-05-01');

Create a table and attach it as a partition

In some cases, it is preferable to create a table outside the partition hierarchy, load or validate data, and attach it later as a partition. This approach allows data to be prepared before it becomes visible in the partitioned table.

The following example creates a standalone table using the structure of the parent table:

CREATE TABLE sales_2025_04
(
    LIKE sales
)
    USING ao_row;

Before attaching the table as a partition, a CHECK constraint can be added to match the target partition range. This helps the system avoid scanning the table during validation:

ALTER TABLE sales_2025_04
    ADD CONSTRAINT sales_2025_04_chk
        CHECK (
            (date IS NOT NULL)
                AND (date >= DATE '2025-04-01')
                AND (date < DATE '2025-05-01')
            );

At this stage, data can be loaded, or additional preparation steps can be performed before the table is attached to the partition hierarchy.

Once the table is ready, it can be attached to the partitioned table:

ALTER TABLE sales
    ATTACH PARTITION sales_2025_04
        FOR VALUES FROM ('2025-04-01') TO ('2025-05-01');

The following query checks that the new partition has been added:

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

Expected result:

 partitiontablename | partitionrangestart | partitionrangeend
--------------------+---------------------+-------------------
 sales_2025_01      | '2025-01-01'        | '2025-02-01'
 sales_2025_02      | '2025-02-01'        | '2025-03-01'
 sales_2025_03      | '2025-03-01'        | '2025-04-01'
 sales_2025_04      | '2025-04-01'        | '2025-05-01'
(4 rows)

The ATTACH PARTITION command requires a SHARE UPDATE EXCLUSIVE lock on the partitioned table.

Before executing the command, it is recommended to define a CHECK constraint on the table being attached that matches the target partition definition. When such a constraint exists, the system can skip a full table scan during validation. Without this constraint, Greengage DB scans the entire table to verify that all rows satisfy the partition bounds, while holding an ACCESS EXCLUSIVE lock on the table being attached. After a successful attach operation, the CHECK constraint can be removed, as it is no longer required. If the table being attached is itself partitioned, Greengage DB recursively validates its subpartitions until it reaches leaf partitions or encounters a suitable CHECK constraint.

If the partitioned table includes a default partition, it is also recommended to define a CHECK constraint that excludes the range of the partition being attached. Without this constraint, Greengage DB scans the default partition to ensure it does not contain rows that should belong to the new partition. This operation is performed while holding an ACCESS EXCLUSIVE lock on the default partition. If the default partition is itself partitioned, Greengage DB recursively applies the same validation logic to its subpartitions, following the same rules as described above.

Add a default partition

CREATE TABLE sales
(
    id     INT,
    date   DATE,
    amount DECIMAL(10, 2)
)
    USING ao_row
    DISTRIBUTED BY (id)
    PARTITION BY RANGE (date);

CREATE TABLE sales_2025_01 PARTITION OF sales
    FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

CREATE TABLE sales_2025_02 PARTITION OF sales
    FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');

CREATE TABLE sales_2025_03 PARTITION OF sales
    FOR VALUES FROM ('2025-03-01') TO ('2025-04-01');

The DEFAULT keyword designates a partition as the default partition. When data does not fall within the bounds of any defined partition, Greengage DB routes it to the default partition.

If no default partition is defined and incoming data does not match any partition constraint, Greengage DB rejects the data and returns an error. Defining a default partition ensures that such rows are still accepted and stored in the table.

A partitioned table can have only one default partition. You can add a default partition either during table creation or afterward.

Create and attach a default partition immediately

To designate a partition as the default partition at creation time:

CREATE TABLE sales_other_dates
    PARTITION OF sales DEFAULT;

Create a table and attach it as a default partition

To designate an existing table as the default partition:

CREATE TABLE sales_other_dates
(
    LIKE sales
)
    USING ao_row;

ALTER TABLE sales
    ATTACH PARTITION sales_other_dates DEFAULT;

The following query verifies that the default partition has been added:

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

Expected result:

 partitiontablename | partitionisdefault | partitionrangestart | partitionrangeend
--------------------+--------------------+---------------------+-------------------
 sales_2025_01      | f                  | '2025-01-01'        | '2025-02-01'
 sales_2025_02      | f                  | '2025-02-01'        | '2025-03-01'
 sales_2025_03      | f                  | '2025-03-01'        | '2025-04-01'
 sales_other_dates  | t                  |                     |
(4 rows)

Indexing partitioned tables

CREATE TABLE sales
(
    id        INT,
    date      DATE,
    amount    DECIMAL(10, 2),
    category  TEXT NOT NULL
)
    USING ao_row
    DISTRIBUTED BY (id)
    PARTITION BY RANGE (date);

CREATE TABLE sales_2025_01 PARTITION OF sales
    FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

CREATE TABLE sales_2025_02 PARTITION OF sales
    FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');

CREATE TABLE sales_2025_03 PARTITION OF sales
    FOR VALUES FROM ('2025-03-01') TO ('2025-04-01');

INSERT INTO sales (id, date, amount, category)
SELECT gs.id,
       DATE '2025-01-01' + (gs.id % 90),
       round((random() * 1000)::NUMERIC, 2),
       categories[(random() * 9)::int + 1]
FROM generate_series(1, 100000) AS gs(id),
     LATERAL (VALUES (ARRAY[
         'Retail',
         'Online',
         'Wholesale',
         'Subscription',
         'Enterprise',
         'Gov',
         'Finance',
         'Health',
         'Education',
         'Energy'
         ])) AS c(categories);

Creating an index on the key column(s) of a partitioned table automatically creates corresponding indexes on all existing partitions. Any partitions created or attached later will also receive matching indexes.

The following example creates an index on the partitioned sales table:

CREATE INDEX sales_category_idx
    ON sales USING bitmap (category);

Greengage DB automatically creates corresponding indexes on each partition. To verify that the indexes have been created on both the parent table and its partitions, use the \di meta-command:

\di sales*

The output shows a partitioned index defined on the parent table and individual indexes on each partition:

                                 List of relations
 Schema |            Name            |       Type        |  Owner  |     Table
--------+----------------------------+-------------------+---------+---------------
 public | sales_2025_01_category_idx | index             | gpadmin | sales_2025_01
 public | sales_2025_02_category_idx | index             | gpadmin | sales_2025_02
 public | sales_2025_03_category_idx | index             | gpadmin | sales_2025_03
 public | sales_category_idx         | partitioned index | gpadmin | sales
(4 rows)

An index or unique constraint defined on a partitioned table is virtual, similar to the table itself. The actual index data is stored in the corresponding indexes on the individual partitions.

Creating indexes at the partitioned table level is convenient because it ensures that all existing and future partitions are indexed consistently. To reduce lock contention when creating indexes, you can use CREATE INDEX …​ ON ONLY on the partitioned table:

CREATE INDEX sales_category_idx
    ON ONLY sales USING bitmap (category);

Such an index is initially marked as invalid, and Greengage DB does not automatically create corresponding indexes on the partitions. You can then create indexes on each partition individually:

CREATE INDEX sales_2025_01_categories_idx
    ON sales_2025_01 USING bitmap (category);
CREATE INDEX sales_2025_02_categories_idx
    ON sales_2025_02 USING bitmap (category);
CREATE INDEX sales_2025_03_categories_idx
    ON sales_2025_03 USING bitmap (category);

After creating the indexes, attach them to the parent index using ALTER INDEX …​ ATTACH PARTITION:

ALTER INDEX sales_category_idx
    ATTACH PARTITION sales_2025_01_categories_idx;
ALTER INDEX sales_category_idx
    ATTACH PARTITION sales_2025_02_categories_idx;
ALTER INDEX sales_category_idx
    ATTACH PARTITION sales_2025_03_categories_idx;

Once the indexes for all partitions are attached, the parent index is automatically marked as valid.

Check index usage

After you create an index, collect table statistics:

ANALYZE;

The following example uses EXPLAIN to show how Greengage DB executes a query against a partitioned table and whether the query uses an index:

EXPLAIN (COSTS OFF)
SELECT SUM(amount) sum_amount
FROM sales
WHERE category = 'Online'
  AND date >= DATE '2025-03-10'
  AND date < DATE '2025-03-15';

Result:

                                                         QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate
   ->  Gather Motion 4:1  (slice1; segments: 4)
         ->  Partial Aggregate
               ->  Dynamic Bitmap Heap Scan on sales
                     Number of partitions to scan: 1 (out of 3)
                     Recheck Cond: (category = 'Online'::text)
                     Filter: ((category = 'Online'::text) AND (date >= '2025-03-10'::date) AND (date < '2025-03-15'::date))
                     ->  Dynamic Bitmap Index Scan on sales_category_idx
                           Index Cond: (category = 'Online'::text)
 Optimizer: GPORCA
(10 rows)

In this example:

  • Number of partitions to scan: 1 (out of 3) — indicates that partition elimination occurred. Based on the date filter, Greengage DB scans only the partition that contains rows for the specified date range instead of scanning all table partitions.

  • Dynamic Bitmap Index Scan on sales_category_idx — indicates that the optimizer uses the sales_category_idx index to identify matching rows.

Exchange a partition

CREATE TABLE sales
(
    id     INT,
    date   DATE,
    amount DECIMAL(10, 2)
)
    USING ao_row
    DISTRIBUTED BY (id)
    PARTITION BY RANGE (date);

CREATE TABLE sales_2025_01 PARTITION OF sales
    FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

CREATE TABLE sales_2025_02 PARTITION OF sales
    FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');

CREATE TABLE sales_2025_03 PARTITION OF sales
    FOR VALUES FROM ('2025-03-01') TO ('2025-04-01');

Exchanging a partition replaces an existing partition with another table that has a compatible structure. This operation is performed by detaching the current partition and attaching a new table in its place.

Create a staging table to use as the replacement partition:

CREATE TABLE stg_sales_2025_03
(
    LIKE sales
);

Detach the existing partition from the partitioned table:

ALTER TABLE sales
    DETACH PARTITION sales_2025_03;

Attach the new table as a partition with the same bounds:

ALTER TABLE sales
    ATTACH PARTITION stg_sales_2025_03
        FOR VALUES FROM ('2025-03-01') TO ('2025-04-01');

Verify that the new table is now part of the partition hierarchy:

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

Result:

 partitiontablename | partitionrangestart | partitionrangeend
--------------------+---------------------+-------------------
 sales_2025_01      | '2025-01-01'        | '2025-02-01'
 sales_2025_02      | '2025-02-01'        | '2025-03-01'
 stg_sales_2025_03  | '2025-03-01'        | '2025-04-01'
(3 rows)

Rename a partition

You rename a partition in the same way as a regular table, using the ALTER TABLE …​ RENAME TO command.

The following example renames the staging table that is used as a partition in the Exchange a partition section:

ALTER TABLE stg_sales_2025_03
    RENAME TO sales_2025_03;

Verify the updated partition names:

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

Result:

 partitiontablename | partitionrangestart | partitionrangeend
--------------------+---------------------+-------------------
 sales_2025_01      | '2025-01-01'        | '2025-02-01'
 sales_2025_02      | '2025-02-01'        | '2025-03-01'
 sales_2025_03      | '2025-03-01'        | '2025-04-01'
(3 rows)

Truncate a partition

CREATE TABLE sales
(
    id     INT,
    date   DATE,
    amount DECIMAL(10, 2)
)
    USING ao_row
    DISTRIBUTED BY (id)
    PARTITION BY RANGE (date);

CREATE TABLE sales_2025_01 PARTITION OF sales
    FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

CREATE TABLE sales_2025_02 PARTITION OF sales
    FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');

CREATE TABLE sales_2025_03 PARTITION OF sales
    FOR VALUES FROM ('2025-03-01') TO ('2025-04-01');

Truncating a partition works in the same way as truncating a regular table. If the partition has subpartitions, Greengage DB automatically truncates all underlying subpartitions as well.

Truncate a specific partition:

TRUNCATE ONLY sales_2025_01;

Truncate the entire partitioned table:

TRUNCATE sales;

Drop a partition

One way to remove historical data is to drop a partition that is no longer required:

DROP TABLE sales_2025_01;

This operation can remove large volumes of data efficiently because it does not delete rows individually. Note that this command requires an ACCESS EXCLUSIVE lock on the parent table.

Alternatively, you can remove a partition from the partitioned table while keeping it available as a standalone table. To do this, use ALTER TABLE …​ DETACH PARTITION to detach the partition from the partitioned table.

Limitations

General limitations

The following Greengage DB partitioned table limitations apply:

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

  • Greengage DB does not support partitioning of replicated tables (tables created with the DISTRIBUTED REPLICATED distribution policy).

  • The GPORCA query optimizer does not support uniform multi-level partitioned tables. If GPORCA is enabled (default) and a table is multi-level partitioned, queries are executed using the PostgreSQL-based planner.

  • The gpbackup utility does not back up data from a leaf partition if the partition is an external or foreign table.

  • To create a unique or primary key constraint on a partitioned table, partition key columns must not contain expressions or function calls, and the constraint must include all partition key columns. This is required because uniqueness is enforced only within individual partitions; cross-partition uniqueness must be guaranteed by the partitioning design itself.

  • Exclusion constraints cannot be defined across an entire partitioned table. They can only be defined on individual leaf partitions. This is due to the inability to enforce cross-partition constraints.

  • Temporary and permanent objects cannot be mixed within the same partition hierarchy. If the parent table is permanent, all partitions must also be permanent, and the same applies to temporary tables. When using temporary partitioned tables, all objects in the hierarchy must belong to the same session.

Inheritance model of partitioned tables

Individual partitions are linked to their partitioned table using inheritance internally. However, not all features of general table inheritance are supported for declaratively partitioned tables or their partitions. In particular, a partition cannot have any parent other than its partitioned table, and a table cannot inherit from both a partitioned table and a regular table. Partitioned tables and their partitions do not share an inheritance hierarchy with regular tables.

Since a partition hierarchy (the partitioned table and its partitions) is implemented using inheritance, tableoid and standard inheritance rules apply as described in the PostgreSQL documentation on Inheritance, with the following exceptions:

  • Partitions cannot define columns that are not present in the parent table. Columns cannot be specified when creating a partition using CREATE TABLE, and partitions cannot be altered to add columns using ALTER TABLE. A table can be attached as a partition using ALTER TABLE …​ ATTACH PARTITION only if its column definition exactly matches the parent table.

  • CHECK and NOT NULL constraints on a partitioned table are always inherited by all partitions. Greengage DB does not allow CHECK constraints marked NO INHERIT on partitioned tables. A NOT NULL constraint cannot be dropped from a partition column if it exists on the parent table.

  • The ONLY keyword can be used to add or drop constraints on a partitioned table only when no partitions exist. Once partitions are created, using ONLY results in an error. In that case, constraints must be managed directly on individual partitions (if they are not defined on the parent).

  • Because a partitioned table itself contains no data, TRUNCATE ONLY on a partitioned table is not supported and returns an error.

Limitations for external and foreign leaf partitions

When a leaf partition is an external or foreign table, the following limitations hold:

  • If an external or foreign table used as a partition is not writable or the user lacks permission to modify it, data modification commands (INSERT, UPDATE, DELETE, or TRUNCATE) on that partition fail with an error.

  • The COPY command cannot copy data to a partitioned table that updates an external or foreign table partition.

  • The COPY command that reads from a partitioned table returns an error if it encounters an external or foreign table partition, unless the IGNORE EXTERNAL PARTITIONS clause is specified. When this clause is used, Greengage DB skips external and foreign table partitions and does not copy data from them.

    To use the COPY command against a partitioned table with a leaf partition that is an external or foreign table, use an SQL expression rather than the partitioned table name to copy the data. For example, if the sales table contains a leaf partition that is an external table, this command sends the data to stdout:

    COPY (SELECT * FROM sales) TO stdout;

  • If an external or foreign table partition is not writable or the user does not have permission to write to the table, Greengage DB returns an error on the following operations:

    • Adding or dropping a column.

    • Changing the data type of a column.

Unsupported ALTER TABLE operations (classic syntax)

These ALTER TABLE …​ ALTER PARTITION operations are not supported when a partitioned table contains an external or foreign table partition:

  • Setting a subpartition template.

  • Altering the 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.

Previous
Distribution
Next
Partitioning: classic syntax