(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 the articles in References at the end of this post 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);
-- Add partition individually
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.
You can also still split a default partition which is a common scenario where data are inserted into default partition first and added to declared partitions later. But it is worth noting that, if the default partition does not contain any data, it might be better to simply to ATTACH PARTITION to add new partitions because ATTACH PARTITION has less restricted lock type (see more in Section 3.3 later). If the default partition has data, it is likely that ATTACH PARTITION would have error because the data in default partition break the new partition constraint.
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:
- PostgreSQL documentation on table partitioning: https://www.postgresql.org/docs/12/ddl-partitioning.html
- PostgreSQL partitioning tutorial: https://www.youtube.com/watch?v=oJj-pltxBUM
- “Beginner’s Guide to Table Partitioning In PostgreSQL”: https://medium.com/swlh/beginners-guide-to-table-partitioning-in-postgresql-5a014229042