# Partition in Greenplum 7: What’s New

(Co-authored by Alexandra Wang)

Greenplum 7 is a huge milestone for partitioned tables. Besides several improvements and fixes, this is the first Greenplum version that will be aligned with partitioned tables from PostgreSQL world.

A little background: before PostgreSQL 10, table partitioning could be done in very limited form in PostgreSQL, which is basically just a use case of table inheritance. In PostgreSQL 10 and onwards, users can start use declarative syntax to define their partition paradigm. For example:

CREATE TABLE sales (id int, date date, amt decimal(10,2))
PARTITION BY RANGE (date);


On the other hand, table partitioning as we know it today has been in Greenplum for a long time. As part of the merge with PostgreSQL 12, Greenplum 7 has absorbed all the PostgreSQL syntax for table partitioning, while still supporting the Greenplum legacy syntax. As a result, Greenplum 7 has a chance to take the best of both worlds. This blog is going to talk about them, including the additions, removals and trade-offs in Greenplum 7, especially concerning what’s different than prior versions of Greenplum.

This blog will primarily focus on the differences between Greenplum 7 and Greenplum 6. So if you are entirely new to Greenplum (or even PostgreSQL) and have never used partitioning in Greenplum 6 before, you might find these articles which only talk about PostgreSQL partitioning more interesting.

So without further ado, let’s dive right in.

# 1. New Syntax

Before we start to look at what’s new in Greenplum 7, let’s take a look at what’s not new: since PostgreSQL has the same declaration for partition key as Greenplum, which is the PARTITION BY clause, that remains unchanged in Greenplum 7. Moreover, PostgreSQL also has RANGE and LIST partitioning strategies which Greenplum continues to support in its newest version.

One important difference, though, is that Greenplum 7 now allows you to define a partitioned table without defining any child partitions, for example:

CREATE TABLE sales (id int, date date, amt decimal(10,2))
DISTRIBUTED BY (id)
PARTITION BY RANGE (date);


The CREATE TABLE ... PARTITION BY command above creates just the parent partitioned table without any child partition. Child partitions in Greenplum 7 are first-class tables that can be created via separate commands, which will be covered in later sections.

## 1.1. Hash Partitioning Strategy

Besides the existing RANGE and LIST partitioning strategies, Greenplum 7 also supports HASH partitioning. They work the same way as in PostgreSQL. Example to create a hash partitioned table and its child partitions:

-- create a tabled partitioned by hash strategy
CREATE TABLE sales_by_hour (id int, date date, hour int, amt decimal(10,2))
DISTRIBUTED BY (id)
PARTITION BY HASH (hour);

-- every hash partition modulus must be a factor of the next larger modulus
CREATE TABLE sales_by_hour_1 PARTITION OF sales_by_hour FOR VALUES WITH (MODULUS 24, REMAINDER 0);
CREATE TABLE sales_by_hour_2 PARTITION OF sales_by_hour FOR VALUES WITH (MODULUS 24, REMAINDER 1);
CREATE TABLE sales_by_hour_3 PARTITION OF sales_by_hour FOR VALUES WITH (MODULUS 24, REMAINDER 2);
......


## 1.2. New Partition DDL

So the main additions in Greenplum 7 are the following new partition DDLs, same as PostgreSQL. We will talk about them in details later, but let’s take a peek at what they do:

### (1) CREATE TABLE PARTITION OF

For creating a new table and adding it as a new child partition:

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


### (2) ALTER TABLE ATTACH PARTITION

For adding an existing table as a new child partition:

CREATE TABLE feb_sales (LIKE sales);

ALTER TABLE sales ATTACH PARTITION feb_sales
FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');


### (3) ALTER TABLE DETACH PARTITION

For removing a table from the partitioning hierarchy (without dropping the table itself):

ALTER TABLE sales DETACH PARTITION jan_sales;


## 1.3. New Catalog and Helper Functions

Partitioning information is now stored in the upstream pg_partitioned_table catalog, and in additional fields in pg_class (relispartition and relpartbound). You can also use these new helper functions from upstream: pg_partition_ancestors(rel)), pg_partition_root(rel) and pg_partition_tree(rel).

On that note, the old partitioning-related catalog tables, pg_partition and pg_partition_rule are gone, as well as the pg_partition_def() function.

-- New catalog for partitioned tables
select * from pg_partitioned_table where partrelid = 'sales'::regclass;
partrelid | partstrat | partnatts | partdefid | partattrs | partclass | partcollation | partexprs
-----------+-----------+-----------+-----------+-----------+-----------+---------------+-----------
156181 | r         |         1 |         0 | 2         | 3122      | 0             |
(1 row)

-- A convenient helper routine to check the partition hierarchy
select pg_partition_tree('sales');
pg_partition_tree
-----------------------------------
(sales,,f,0)
(jan_sales,sales,t,1)
(sales_1_prt_feb_sales,sales,t,1)
(sales_1_prt_mar_sales,sales,t,1)
(4 rows)


# 2. New Workflows

For an existing Greenplum user, one of the most important things to learn about the new syntaxes is that they provide alternative workflows to what the legacy Greenplum commands used to do. Note that this does not mean that the new syntax is more complex. In fact, it is quite the other way around: the new syntaxes are more general and easy to use. In most cases, it is more straightforward to implement certain partition paradigms, including performing the exactly same tasks that the old commands can do. We will see a few examples below.

However, worth mentioning that the new syntaxes are not replacement of the old. If one understands both groups of syntaxes well, especially the differences that we will talk about later, they can always choose the most suitable commands for their need.

### Create child partition along with parent

Greenplum has been able to create child partitions along with the parent table. For example:

CREATE TABLE sales (id int, date date, amt decimal(10,2))
DISTRIBUTED BY (id)
PARTITION BY RANGE (date)
(PARTITION jan_sales START ('2023-01-01') END ('2023-02-01'),
PARTITION feb_sales START ('2023-02-01') END ('2023-03-01'),
DEFAULT PARTITION other_sales);


PostgreSQL does not have a counterpart command. Instead, it creates the parent partitioned table first and then add the child partitions separately:

CREATE TABLE sales (id int, date date, amt decimal(10,2))
DISTRIBUTED BY (id)
PARTITION BY RANGE (date);

CREATE TABLE jan_sales PARTITION OF sales FOR VALUES FROM ('2023-01-01') TO ('2023-02-01');
CREATE TABLE feb_sales PARTITION OF sales FOR VALUES FROM ('2023-02-01') TO ('2023-03-01');
CREATE TABLE other_sales PARTITION OF sales DEFAULT;


### Create and add child partitions

The ALTER TABLE ... ADD PARTITION and CREATE TABLE ... PARTITION OF both create and add a new child table at the same time.

However, since CREATE TABLE PARTITION OF is a CREATE TABLE command, unlike ADD PARTITION which is a ALTER TABLE subcommand, with CREATE TABLE ... PARTITION OF you can specify more tables creation options. ADD PARTITION in general only inherits whatever the parent table has.

-- CREATE TABLE allows you to specify more options
CREATE TABLE jan_sales PARTITION OF sales FOR VALUES FROM ('2023-01-01') TO ('2023-02-01')
USING ao_row
WITH(compresstype=zlib);

-- ADD PARTITION does the creation but have to specify options in separate commands
ALTER TABLE sales ADD PARTITION jan_sales START ('2023-01-01') END ('2023-02-01');

ALTER TABLE sales_1_prt_jan_sales SET ACCESS METHOD ao_row;
ALTER TABLE sales_1_prt_jan_sales SET WITH (compresstype=zlib);


### Swap existing partition with another table

The EXCHANGE PARTITION command in legacy syntax swaps an existing child partition with a regular table. With new syntaxes one can simply use DETACH PARTITION and ATTACH PARTITION to achieve the same. In fact, internally in Greenplum 7, it is precisely doing DETACH and then ATTACH PARTITION, with some name swapping.

-- 1. Using EXCHANGE PARTITION
ALTER TABLE sales EXCHANGE jan_sales WITH TABLE jan_sales_new;

-- 2. Using ATTACH PARTITION
ALTER TABLE sales DETACH PARTITION jan_sales;
ALTER TABLE sales ATTACH PARTITION jan_sales_new;


### Remove a child partition

It was used to be pretty difficult to remove a child partition without dropping the table. The legacy Greenplum only has the ALTER TABLE ... DROP PARTITION command which drops the table too. You would have to create a dummy table first, exchange the partition you would like to remove with the dummy table, and then drop the swapped partition. Now with the ALTER TABLE ... DETACH PARTITION one can easily perform the same task:

-- Lengthy operations to remove an unwanted child partition w/o dropping it:
CREATE TABLE dummy (LIKE sales);
ALTER TABLE sales EXCHANGE PARTITION archived_sales WITH dummy;
ALTER TABLE sales DROP PARTITION archived_sales;
DROP TABLE dummy;

-- Can just "DETACH PARTITION" now:
ALTER TABLE sales DETACH PARTITION archived_sales;


As you may have noticed, ALTER TABLE ... DROP PARTITION essentially performs the exact same task asDROP TABLE. Then why ALTER TABLE ... DROP PARTITION is still there? That is because the two syntaxes treat the table name differently. See later section of “partition name vs table name”.

### Split a child partition

SPLIT PARTITION is a special command that performs an interesting task: dividing a leaf partition and creating two partitions out of it. It is another syntax without a simple alternative in the PostgreSQL. You have to manually detach the partition and add two partitions corresponding to the splited ranges. But the good news is that if you do not wish to perform the small steps, you can still just use SPLIT PARTITION in Greenplum 7.

Table of Comparison:
Use Case Legacy Syntax Alternative New/Existing Syntax
Create child partition along with parent CREATE TABLE ... (PARTITION ...) CREATE TABLE ... PARTITION BY and CREATE TABLE ... PARTITION OF
Create & add partition ALTER TABLE ... ADD PARTITION CREATE TABLE ... PARTITION OF
Swap existing partition child with regular table ALTER TABLE ... EXCHANGE PARTITION ALTER TABLE ... DETACH PARTITION and ATTACH PARTITION
Remove partition ALTER TABLE ... DROP PARTITION DROP TABLE
Split partition ALTER TABLE ... SPLIT PARTITION DETACH partition and ATTACH new ones separately

In summary, the new syntaxes are less specialized but their generality would be able to implement more complex hierarchies more easily, once you get familiar with the usage.

# 3. Other Differences

## 3.1. Partition name vs. Table name

Historically, the name that one specifies in the Greenplum’s partition DDLs is so-called “partition name” which is not the same as table name. Basically, Greenplum will add special prefix that is corresponding to the parent partition to the child table name. For example, when using the legacy syntax to add partitions:

CREATE TABLE sales (id int, date date, amt decimal(10,2))
DISTRIBUTED BY (id)
PARTITION BY RANGE (date)
(PARTITION jan_sales START ('2023-01-01') END ('2023-02-01'));

ALTER TABLE sales ADD PARTITION feb_sales START ('2023-02-01') END ('2023-03-01');

\d+ sales
Partitioned table "public.sales"
Column |     Type      | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------------+-----------+----------+---------+---------+--------------+-------------
id     | integer       |           |          |         | plain   |              |
date   | date          |           |          |         | plain   |              |
amt    | numeric(10,2) |           |          |         | main    |              |
Partition key: RANGE (date)
Partitions: sales_1_prt_feb_sales FOR VALUES FROM ('2023-02-01') TO ('2023-03-01'),
sales_1_prt_jan_sales FOR VALUES FROM ('2023-01-01') TO ('2023-02-01')
Distributed by: (id)
Access method: heap


As shown above, both child partitions have a prefix sales_1_prt_ to the names we specifies for them (jan_sales and feb_sales). In contrast, the new syntax simply treat the names specified as the table name:

CREATE TABLE sales (id int, date date, amt decimal(10,2))
DISTRIBUTED BY (id)
PARTITION BY RANGE (date);

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

CREATE TABLE feb_sales (LIKE sales);
ALTER TABLE sales ATTACH PARTITION feb_sales
FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

\d+ sales
Partitioned table "public.sales"
Column |     Type      | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------------+-----------+----------+---------+---------+--------------+-------------
id     | integer       |           |          |         | plain   |              |
date   | date          |           |          |         | plain   |              |
amt    | numeric(10,2) |           |          |         | main    |              |
Partition key: RANGE (date)
Partitions: feb_sales FOR VALUES FROM ('2024-02-01') TO ('2024-03-01'),
jan_sales FOR VALUES FROM ('2023-01-01') TO ('2023-02-01')
Distributed by: (id)
Access method: heap


This difference, however, is consistent among the old and new syntaxes themselves. For example, we do not need to specify the prefix when using the legacy DROP PARTITION syntax. But we will need to if we use DETACH PARTITION to do the same:

-- Assuming we have the 'sales' partition with Jan and Feb
-- child partitions, created using legacy syntax.
\d+ sales
Partitioned table "public.sales"
Column |     Type      | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------------+-----------+----------+---------+---------+--------------+-------------
id     | integer       |           |          |         | plain   |              |
date   | date          |           |          |         | plain   |              |
amt    | numeric(10,2) |           |          |         | main    |              |
Partition key: RANGE (date)
Partitions: sales_1_prt_feb_sales FOR VALUES FROM ('2023-02-01') TO ('2023-03-01'),
sales_1_prt_jan_sales FOR VALUES FROM ('2023-01-01') TO ('2023-02-01')
Distributed by: (id)
Access method: heap

-- drop partition 'jan_sales', no problem
ALTER TABLE sales DROP PARTITION jan_sales;

-- but couldn't detach, because there's no table named 'feb_sales'
ALTER TABLE sales DETACH PARTITION feb_sales;
ERROR:  relation "feb_sales" does not exist

-- have to specify full name using DETACH
ALTER TABLE sales DETACH PARTITION sales_1_prt_feb_sales;


Therefore, it is highly recommended, at least for the same partitioned table, to stick to using either the new syntaxes or the legacy syntaxes to avoid name ambiguity.

For convenience let’s use a table to see the difference outright:

Syntax Add prefix or not Legacy or New
ADD PARTITION Yes Legacy
DROP PARTITION Yes Legacy
EXCHANGE PARTITION Yes Legacy
SPLIT PARTITION Yes Legacy
CREATE TABLE (PARTITION ...) Yes Legacy
CREATE TABLE (EVERY ...) Yes Legacy
CREATE TABLE ... PARTITION OF No New
ATTACH PARTITION No New
DETACH PARTITION No New

## 3.2. START|END vs FROM|TO

From the SQL examples that have shown before, you probably have noticed that the new syntax also have different keywords for range partition definition: FOR VALUES FROM ... TO .... The Greenplum legacy syntax has START ... END (). Both definitions will work only in the corresponding legacy or new DDLs:

-- Both these work:
ALTER TABLE sales ADD PARTITION mar_sales
START ('2023-03-01') END ('2023-03-31');

CREATE TABLE mar_sales PARTITION OF sales
FOR VALUES FROM ('2023-03-01') TO ('2023-03-31');

-- But these won't:
ALTER TABLE sales ADD PARTITION mar_sales
FOR VALUES FROM ('2023-03-01') TO ('2023-03-31');

CREATE TABLE mar_sales PARTITION OF sales
START ('2023-03-01') END ('2023-03-31');


The legacy syntax also has EXCLUSIVE and INCLUSIVE keywords for range partitioning. In PostgreSQL, there’s no such thing and the start boundary is always inclusive and end exclusive. Greenplum 7 continues to support EXCLUSIVE|INCLUSIVE by implicitly adding “

” to the start or end range. As a result, they now only work for datatypes that have a suitable “

” operator, like integer and timestamp, but not float or text.

ALTER TABLE sales ADD PARTITION mar_sales
START ('2023-03-01') INCLUSIVE END ('2023-03-31') INCLUSIVE;


## 3.3. Less restricted lock in ATTACH PARTITION

The locking behavior in partition deserves another blog, but one of the most important things that users should be aware is less restricted locking with ATTACH PARTITION. ATTACH PARTITION only requires a Share Update Exclusive Lock on the parent table. This lock type is a relatively less restrictive one that doesn’t conflict with many other queries, including SELECT, INSERT and sometimes UPDATE.

This basically means that, only until Greenplum 7 it becomes possible to add partition to a partition hierarchy without disrupting many normal query executions on the partition (and vice versa). For example:

-- Assuming there's long-running insert
INSERT INTO sales SELECT * FROM ext_sales_data;

-- This will be blocked
ALTER TABLE sales ADD PARTITION march_sales START ('2023-03-01') END ('2023-04-01');

-- This will go through
ALTER TABLE sales ATTACH PARTITION march_sales FOR VALUES FROM ('2023-03-01') TO ('2023-04-01');


Check this demo video to see it in effect.

This table shows what lock is acquired during partition DDLs
Command Highest Lock Type Allowed Query
ADD PARTITION AccessExclusiveLock None
DROP PARTITION AccessExclusiveLock None
EXCHANGE PARTITION AccessExclusiveLock None
SPLIT PARTITION AccessExclusiveLock None
CREATE TABLE ... PARTITION OF AccessExclusiveLock None
ATTACH PARTITION ShareUpdateExclusiveLock SELECT, INSERT, UPDATE*
DETACH PARTITION AccessExclusiveLock None

* When gp_enable_global_deadlock_detector is ON and the table is not append-optimized.

## 3.4. Partition constraints vs Check constraints

Partition boundaries are no longer represented as CHECK constraints. Partition constraints is a completely separate concept now.

-- same partition definition in Greenplum 6 and 7

-- Greenplum 6
\d+ sales_1_prt_jan_sales
Table "public.sales_1_prt_jan_sales"
Column |     Type      | Modifiers | Storage | Stats target | Description
--------+---------------+-----------+---------+--------------+-------------
id     | integer       |           | plain   |              |
date   | date          |           | plain   |              |
amt    | numeric(10,2) |           | main    |              |
Check constraints:
"sales_1_prt_jan_sales_check" CHECK (date >= '2023-01-01'::date AND date < '2023-02-01'::date)
Inherits: sales
Distributed by: (id)

-- Greenplum 7
\d+ jan_sales
Table "public.jan_sales"
Column |     Type      | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------------+-----------+----------+---------+---------+--------------+-------------
id     | integer       |           |          |         | plain   |              |
date   | date          |           |          |         | plain   |              |
amt    | numeric(10,2) |           |          |         | main    |              |
Partition of: sales FOR VALUES FROM ('2023-01-01') TO ('2023-02-01')
Partition constraint: ((date IS NOT NULL) AND (date >= '2023-01-01'::date) AND (date < '2023-02-01'::date))
Distributed by: (id)


## 3.5. Multi-column PARTITION BY

Multi-column list partitioning is no longer supported. As a workaround, you can create a composite type and use that as the partitioning key, e.g.:

-- This is no longer working:
CREATE TABLE foo (a int, b int, c int) PARTITION BY list (b,c);
ERROR:  cannot use "list" partition strategy with more than one column

-- Alternatively:
CREATE TYPE partkey as (b int, c int);
CREATE TABLE foo (a int, b int, c int)
PARTITION BY LIST ((row(b, c)::partkey));


# References

Regarding use of PostgreSQL partitioning: