The partition hierarchy is often large and complex, and many times need hybrid table properties among different partitions. It is important to understand the recursion behavior in order to get the right partition paradigm that one would like it to be.
Similar to our previous blog, this blog is an introduction to partitioning in Greenplum 7 with a special focus on the new syntaxes it adds.
1. The ONLY keyword
Firstly, similar to prior Greenplum versions, the ALTER TABLE command that is executed on a partitioned table will recurse into its child partitions, i.e. doing the same modification on the child partitions too.
But one can always specify the ONLY keyword if no recursion is intended. For example assuming we would like to change the access method of future partitions, but don’t want it to apply to the existing ones. We can simply do the following:
-- Assuming partitioned table 'sales' and all
-- of its child partitions are heap tables.
ALTER TABLE ONLY sales SET ACCESS METHOD ao_row;
The partitioned table will be set with the desired access method:
\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: jan_sales FOR VALUES FROM ('2023-01-01') TO ('2023-02-01')
Distributed by: (id)
Access method: ao_row
But the existing child partition of it, will not be affected:
\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)
Access method: heap
GRANT ONLY / REVOKE ONLY
Also in Greenplum 7, you can even specify the ONLY keyword for GRANT/REVOKE, which is not there in PostgreSQL. Note that, Greenplum historically has had the behavior that GRANT|REVOKE on a parent partitioned table would recurse into its child partitions, which is also different than PostgreSQL. Greenplum 7 keeps that behavior, but is adding the ONLY option to provide the flexibility if one does not wish to recurse.
A simple illustration of the usage:
-- Let's say at some point you want two roles with read permission
-- on our 'sale' partitioned table, but one for existing partitions
-- and another for future partitions.
-- 1. Grant only on the parent table for just future partitions.
GRANT SELECT ON ONLY sales TO role_that_can_read_only_future_partitions;
-- 2. W/o "ONLY", this will grant for all existing partitions.
-- Then, revoke only for parent to limit permission for future partitions.
GRANT SELECT ON sales TO roles_that_can_read_only_existing_partitions;
REVOKE SELECT ON ONLY sales FROM roles_that_can_read_only_existing_partitions
2. Creating a new child table
In general, creating a new table as a child partition will inherit all of its parent table’s properties. For example, let’s still start with our sales table:
CREATE TABLE sales (id int, date date, amt decimal(10,2))
USING ao_row
DISTRIBUTED BY (id)
PARTITION BY RANGE (date);
We can use two types of syntaxes to create a new table as a child partition:
-- Create child partition using the new Greenplum 7 syntax
CREATE TABLE jan_sales PARTITION OF sales FOR VALUES FROM ('2023-01-01') TO ('2023-02-01');
-- Create child partition using the legacy syntax
ALTER TABLE sales ADD PARTITION feb_sales START ('2023-02-01') END ('2023-03-01');
Both child partitions will inherit the parent table’s access method which is append-optimized row-oriented (ao_row):
\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: jan_sales FOR VALUES FROM ('2023-01-01') TO ('2023-02-01'),
sales_1_prt_feb_sales FOR VALUES FROM ('2023-02-01') TO ('2023-03-01')
Distributed by: (id)
Access method: ao_row
\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))
Compression Type: None
Compression Level: 0
Block Size: 32768
Checksum: t
Distributed by: (id)
Access method: ao_row
\d+ sales_1_prt_feb_sales
Table "public.sales_1_prt_feb_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-02-01') TO ('2023-03-01')
Partition constraint: ((date IS NOT NULL) AND (date >= '2023-02-01'::date) AND (date < '2023-03-01'::date))
Compression Type: None
Compression Level: 0
Block Size: 32768
Checksum: t
Distributed by: (id)
Access method: ao_row
SPLIT PARTITION
SPLIT PARTITION is a special case where it creates new child partitions out from an existing child partition. In that case, the new partitions will inherit not from the parent partitioned table, but the splitted child. For example, let’s say for some reason we have made the feb_sales partition in our example to have different access method than the parent:
ALTER TABLE sales_1_prt_feb_sales SET ACCESS METHOD ao_column;
Now we split it into two new partitions:
ALTER TABLE sales
SPLIT PARTITION feb_sales AT ('2023-02-15') INTO
(partition feb_first_half, partition feb_second_half);
Then the new partitions will have different access method than its parent (note the original feb_sales partition is gone):
\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: jan_sales FOR VALUES FROM ('2023-01-01') TO ('2023-02-01'),
sales_1_prt_feb_first_half FOR VALUES FROM ('2023-02-01') TO ('2023-02-15'),
sales_1_prt_feb_second_half FOR VALUES FROM ('2023-02-15') TO ('2023-03-01')
Distributed by: (id)
Access method: ao_row
\d+ sales_1_prt_feb_first_half
Table "public.sales_1_prt_feb_first_half"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Compression Type | Compression Level | Block Size | Description
--------+---------------+-----------+----------+---------+---------+--------------+------------------+-------------------+------------+-------------
id | integer | | | | plain | | none | 0 | 32768 |
date | date | | | | plain | | none | 0 | 32768 |
amt | numeric(10,2) | | | | main | | none | 0 | 32768 |
Partition of: sales FOR VALUES FROM ('2023-02-01') TO ('2023-02-15')
Partition constraint: ((date IS NOT NULL) AND (date >= '2023-02-01'::date) AND (date < '2023-02-15'::date))
Distributed by: (id)
Access method: ao_column
Options: blocksize=32768, compresslevel=0, compresstype=none, checksum=true
This inheritance behavior in SPLIT PARTITION remains the same as Greenplum 6.
3. Attaching an existing table
Then let’s consider the case when we do not create a new table, but just attach an existing table as the child partition. In that case, the original table properties are generally preserved after becoming a child partition. For example, say we initially to have a recent_sales table that we keep updating frequently, and it is using the heap access method by default:
CREATE TABLE recent_sales (id int, date date, amt decimal(10,2));
\d+ recent_sales;
Table "public.recent_sales"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------------+-----------+----------+---------+---------+--------------+-------------
id | integer | | | | plain | |
date | date | | | | plain | |
amt | numeric(10,2) | | | | main | |
Distributed by: (id)
Access method: heap
At some point we want to attach this table to the sales partitioned table. After that, it will still maintain its original table properties, including the access method:
ALTER TABLE sales ATTACH PARTITION recent_sales
FOR VALUES FROM ('2023-12-01') TO ('2030-12-31');
\d+ recent_sales
Table "public.recent_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-12-01') TO ('2030-12-31')
Partition constraint: ((date IS NOT NULL) AND (date >= '2023-12-01'::date) AND (date < '2030-12-31'::date))
Distributed by: (id)
Access method: heap
EXCHANGE PARTITION
Another special case, EXCHANGE PARTITION attaches an existing outside table to be the child partition. There are a few changes in Greenplum 7 regarding how the new child partition inherits table properties in this case.
As mentioned in our previous blog, EXCHANGE PARTITION in Greenplum 7 now composes of DETACH PARTITION and ATTACH PARTITION. As a result, the EXCHANGE PARTITION is now more alike ATTACH PARTITION for inheritance behavior. Here is a detailed list of them:
- Table owner:
EXCHANGE PARTITIONnow doesn’t require the partition-to-be table to have the same owner as the parent table. - Index:
EXCHANGE PARTITIONnow doesn’t require the partition-to-be to have the same index as the parent. The command will create one if it’s missing. - Table constraint:
EXCHANGE PARTITIONnow requires the partition-to-be to have whatever constraint its parent has.
Summary
In general, most of ALTER TABLE and GRANT|REVOKE commands will recurse into its child partitions unless ONLY keyword is specified. And, whether a new child partition inherits from the parent or not will be decided by whether it is created or attached: if created it will inherit from the parent, otherwise it maintains its own original properties.