Partitioning Large Tables

Table partitioning in VMware Greenplum enables you to logically divide large tables, such as fact tables, into smaller, more manageable pieces. Partitioning tables can improve query performance when it allows the Greenplum Database query optimizer to scan only the data needed to satisfy a given query instead of scanning the complete contents of a large table.

Partitioning does not change the physical distribution of table data across the segments. Table distribution is physical: Greenplum Database physically divides partitioned tables and non-partitioned tables across segments to enable parallel query processing. Table partitioning is logical: Greenplum Database logically divides big tables to improve query performance and facilitate data warehouse maintenance tasks, such as rolling old data out of the data warehouse.

Parent topic: Defining Database Objects

Why Table Partitioning?

Partitioning can provide several benefits:

Dramatically improves query performance in certain situations, particularly when most of the heavily accessed rows of the table are in a single partition or a small number of partitions. Partitioning effectively substitutes for the upper tree levels of indexes, making it more likely that the heavily-used parts of the indexes fit in memory.
When queries or updates access a large percentage of a single partition, you can improve performance by using a sequential scan of that partition instead of using an index, which would require random-access reads scattered across the whole table.
When the partitioning design accounts for the usage pattern, you can perform bulk loads and deletes by adding or removing partitions. Dropping an individual partition using DROP TABLE, or doing ALTER TABLE DETACH PARTITION, is far faster than a bulk operation. These commands also entirely prevent the VACUUM overhead caused by a bulk DELETE.
You can migrate seldom-used data to cheaper and slower storage media.

These benefits will normally be worthwhile only when a table would otherwise be very large. The exact point at which a table will benefit from partitioning depends on the application, although a rule of thumb is that the size of the table should exceed the physical memory of the database server.

About the Table Partitioning Methods

Greenplum Database supports the following forms of table partitioning:

Range Partitioning: The table is partitioned into "ranges" defined by a key column or set of columns, with no overlap between the ranges of values assigned to different partitions. For example, one might partition by date ranges, or by ranges of identifiers for particular business objects. Each range's bounds are understood as being inclusive at the lower end and exclusive at the upper end. For example, if one partition's range is from 1 to 10, and the next one's range is from 10 to 20, the value 10 belongs to the second partition not the first.
List Partitioning: The table is partitioned by explicitly listing which key value(s) appear in each partition, for example sales territory or product line.
Hash Partitioning: The table is partitioned by specifying a modulus and a remainder for each partition. Each partition holds the rows for which the hash value of the partition key divided by the specified modulus produces the specified remainder.

The following figure illustrates a multi-level partition design utilizing both date-range and list partitioning.

Example Multi-level Partition Design

About Table Partitioning in Greenplum Database

Greenplum Database allows you to declare that a table is divided into partitions. The table that is divided is a partitioned table. The declaration includes the partitioning method as described above, plus a list of columns or expressions to be used as the partition key.

The partitioned table itself is a "virtual" table having no storage of its own. Instead, the storage belongs to partitions, which are otherwise ordinary tables associated with the partitioned table. Each partition stores a subset of the data as defined by its partition bounds. Greenplum Database routes all rows inserted into a partitioned table to the appropriate partitions based on the values of the partition key column(s). Greenplum will move a row to a different partition if you update the partition key and the key no longer satisfies the partition bounds of its original partition.

You initially partition tables during creation by specifying the PARTITION BY or PARTITION OF clause to the CREATE TABLE command. When you created a partitioned table, you create a top-level root partitioned table with one or more levels of child tables. The table at the level immediately above a child table is the parent. A table at the highest level in a partition hierarchy is a leaf partition. A leaf partition has no child tables, and leaf partitions in the same partition hierarchy may reside at different depths.

Greenplum uses the partition criteria (boundaries) defined during table creation to create each partition with a distinct partition constraint, which limits the data that table can contain. The query optimizers use partition constraints to determine which table partitions to scan to satisfy a given query predicate.

A partition itself may be defined as a partitioned table, resulting in sub-partitioning. Although all partitions must have the same columns as their partitioned parent, partitions may have their own indexes, constraints, and default values, distinct from those of other partitions. Refer to the CREATE TABLE command reference for more details on creating partitioned tables and partitions.

Each partition's definition must specify bounds that correspond to the partitioning method and partition key of the parent. Greenplum returns an error if the bounds of a new partition's values overlap with those in one or more existing partitions. Partitions are in every way normal Greenplum Database tables (or, possibly, external or foreign tables). You can specify a tablespace and storage parameters for each partition separately.

Because partitions themselves are independent tables, if a table that is part of a partition hierarchy itself is writable, you can write to it through its parent also, as long as granted the correct permission.

You cannot turn a regular table into a partitioned table or vice-versa. However, it is possible to add an existing regular or partitioned table as a partition of a partitioned table, or to remove a partition from a partitioned table turning it into a standalone table; these operations can simplify and speed up many maintenance processes. Refer to the ALTER TABLE command reference to learn more about the syntax for these operations.

While a partition can also be an external table or foreign table, take extra care because it becomes your responsibility to ensure that the contents of the table satisfy the partitioning rule. There are other restrictions for foreign tables as well; refer to the CREATE FOREIGN TABLE command reference for more information.

Deciding on a Table Partitioning Strategy

Not all hash-distributed or randomly distributed tables are good candidates for partitioning. If the answer is yes to all or most of the following questions, table partitioning is a viable database design strategy for improving query performance. If the answer is no to most of the following questions, table partitioning is not the right solution for that table. Always test your design strategy to ensure that query performance improves as expected.

Is the table large enough? Large fact tables are good candidates for table partitioning. If you have millions or billions of records in a table, you may see performance benefits from logically breaking that data up into smaller chunks. For smaller tables with only a few thousand rows or less, the administrative overhead of maintaining the partitions may outweigh any performance benefits that you might see.
Are you experiencing unsatisfactory performance? As with any performance tuning initiative, a table should be partitioned only if queries against that table are producing slower response times than desired.
Do your query predicates have identifiable access patterns? Examine the WHERE clauses of your query workload and look for table columns that are consistently used to access data. For example, if most of your queries tend to look up records by date, then a monthly or weekly date-partitioning design might be beneficial. Or if you tend to access records by region, consider a list-partitioning design to divide the table by region.
Does your data warehouse maintain a window of historical data? Another consideration for partition design is your organization's business requirements for maintaining historical data. For example, your data warehouse may require that you keep data for the past twelve months. If the data is partitioned by month, you can easily drop the oldest monthly partition from the warehouse and load current data into the most recent monthly partition.
Can the data be divided into somewhat equal parts based on some defining criteria? Choose partitioning criteria that will divide your data as evenly as possible. If the partitions contain a relatively equal number of records, query performance improves based on the number of partitions created. For example, by dividing a large table into 10 partitions, a query will run 10 times faster than it would against the unpartitioned table, provided that the partitions are designed to support the query's criteria.

Do not create more partitions than are needed. Creating too many partitions can slow down management and maintenance jobs, such as vacuuming, recovering segments, expanding the cluster, checking disk usage, and others.

Partitioning does not improve query performance unless the query optimizer can eliminate partitions based on the query predicates. Queries that scan every partition run slower than if the table were not partitioned, so avoid partitioning if few of your queries achieve partition elimination. Check the explain plan for queries to make sure that partitions are eliminated. See Query Profiling for more about partition elimination.

Caution
Be very careful with multi-level partitioning, as the number of partition files can grow very quickly. For example, if a table is partitioned by both day and city, and there are 1,000 days of data and 1,000 cities, the total number of partitions is one million. Column-oriented tables store each column in a physical table, so if this table has 100 columns, the system would be required to manage 100 million files for the table, for each segment.

Before settling on a multi-level partitioning strategy, consider a single level partition with bitmap indexes. Indexes slow down data loads, so performance testing with your data and schema is recommended to decide on the best strategy.

Choosing the Partitioning Syntax

Greenplum Database 7 retains most aspects of the partitioning syntax of prior versions of Greenplum, referred to as the classic syntax. Version 7 also introduces support for a modern syntax, derived from the PostgreSQL declarative partitioning syntax.

The classic syntax is provided for backwards compatibility with previous Greenplum versions. It is appropriate for a homogenous partitioned table, where all partitions are at the same leaf level and have the same partition rule.

Note
The modern syntax is less specialized and easier to use, and is recommended for new users and new partitioned table definitions.

The classic and modern partitioning syntaxes are alternatives, you choose the one that meets your needs.

If you are familiar with the Greenplum 6 partitioning syntax or already have partitioned tables that were defined using this syntax, you may choose to continue using the classic syntax. (Refer to About Changes to Table Partitioning in Greenplum 7 for more information about partitioning syntax and behavior changes introduced in version 7.)

The following table provides a feature comparison to help you choose the syntax most appropriate for your data model:

Feature	Classic Syntax	Modern Syntax
Heterogeneous partition hierarchy	Not supported. All leaf partitions are at the same level.	Supported. Leaf partitions are permitted at different levels. You can specify different partitioning rules for individual child tables, including different partition columns and different 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 the table contents. Must create a dummy table and swap.	Supported. Can directly detach a partition, retaining the table contents.
Sub-partition templating	Supported. The definitions of the parent and child tables are consistent by default.	Not supported. You ensure that the table definitions are consistent.
Partition maintenance	You operate on a child table via the parent, requiring knowledge of the partition hierarchy.	You operate directly on the child table, no knowledge of the partition hierarchy is required.

Important
After creation, operate on the partition hierarchy using the CREATE TABLE and ALTER TABLE clauses identified for the syntax that you chose. VMware does not recommend mixing and matching the classic and modern partitioning syntaxes for partition maintenance operations.

Creating a Partitioned Table

You partition a table when you create it with the CREATE TABLE command. Before you create the table, you should:

Choose the partition method: date range, numeric range, list of values, hash.
Choose the column(s) on which to partition the table.
Determine the appropriate number of partitions and partition levels. For example, you may choose to create a table that is date-range partitioned by month, and then sub-partition the monthly partitions by sales region.
Create the partitioned table.
Create the partitions.

You are not required to manually create table constraints describing the partition boundary conditions for partitions, Greenplum creates such constraints automatically.

After you create a partition, Greenplum directs to the partition any data inserted into the root partitioned table that matches this partition's partition constraints. The partition key specified may overlap with the parent's partition key, although take care when specifying the bounds of a sub-partition such that the set of data it accepts constitutes a subset of what the partition's own bounds allow; Greenplum does not check whether that's really the case.

You can use the same partition key column to create sub-partitions if necessary, for example, to partition by month and then sub-partition by day. Consider partitioning by the most granular level. For example, for a table partitioned by date, you can partition by day and have 365 daily partitions, rather than partition by year then sub-partition by month then sub-partition by day. A multi-level design can reduce query planning time, but a flat partition design typically runs faster.

Important
The examples in the next section use the modern partitioning syntax. Refer to Examples Using the Classic Partitioning Syntax for information about the classic partitioning syntax and parallel examples.

Creating Partitioned Tables with the Modern Syntax

The following subsections provide modern syntax examples for creating tables with various partition designs.

Defining a Date-Range Partitioned Table

A date-range partitioned table uses a date or timestamp column as the partition key column.

Note
The modern partitioning syntax supports specifying multiple partition key columns for range partitioned tables.

Example Requirements: In this example, you construct a database for a large ice cream company. The company measures peak temperatures every day as well as ice cream sales in each region. The main use of the table is to prepare online reports for management, so most queries will access just the last week's, month's, or quarter's data. To reduce the amount of old data that needs to be stored, retain only the most recent 2 years worth of data. At the beginning of each month, remove the oldest month's data.

Create a range-partitioned table named measurement that is partitioned on a logdate column:

CREATE TABLE measurement (
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int )
DISTRIBUTED BY (city_id)
PARTITION BY RANGE (logdate);

Create partitions. Each partition should hold one month's worth of data, to match the requirement of deleting one month's data at a time.

CREATE TABLE measurement_y2021m01 PARTITION OF measurement
  FOR VALUES FROM ('2021-01-01') TO ('2021-02-01');

CREATE TABLE measurement_y2021m02 PARTITION OF measurement
  FOR VALUES FROM ('2021-02-01') TO ('2021-03-01');

CREATE TABLE measurement_y2021m03 PARTITION OF measurement
  FOR VALUES FROM ('2021-03-01') TO ('2021-04-01');

...

CREATE TABLE measurement_y2021m11 PARTITION OF measurement
  FOR VALUES FROM ('2021-11-01') TO ('2021-12-01');

CREATE TABLE measurement_y2022m12 PARTITION OF measurement
  FOR VALUES FROM ('2022-12-01') TO ('2022-01-01')
  TABLESPACE fasttablespace;

CREATE TABLE measurement_y2023m01 PARTITION OF measurement
  FOR VALUES FROM ('2023-01-01') TO ('2023-02-01')
  WITH (parallel_workers = 4)
  TABLESPACE fasttablespace;

Recall that adjacent partitions can share a bound value, since Greenplum Database treats range upper bounds as exclusive bounds.

Any data inserted into measurement is redirected to the appropriate partition based on the logdate column. Greenplum returns an error when data is inserted into the parent table that does not map to one of the existing partitions.

In this example scenario, a new partition will be created each month. You might write a script that generates the required DDL automatically.

Defining a Numeric-Range Partitioned Table

A numeric-range partitioned table uses a numeric-type column as the partition key column. The modern syntax supports specifying multiple partition key columns for range partitioned tables.

For example, create a numeric-range partitioned table named numpart that is partitioned on a year column:

CREATE TABLE numpart (id int, rank int, year int, color char(1), count int)
  DISTRIBUTED BY (id)
  PARTITION BY RANGE (year);

Create the partitions for four years of data:

CREATE TABLE numpart_y2019 PARTITION OF numpart FOR VALUES FROM (2019) TO (2020);
CREATE TABLE numpart_y2020 PARTITION OF numpart FOR VALUES FROM (2020) TO (2021);
CREATE TABLE numpart_y2021 PARTITION OF numpart FOR VALUES FROM (2021) TO (2022);
CREATE TABLE numpart_y2022 PARTITION OF numpart FOR VALUES FROM (2022) TO (2023);

Defining a List Partitioned Table

A list partitioned table can use any data type column that allows equality comparisons as its partition key column.

Note
The modern syntax allows only a single column as the partition key for a list partition.

You must declare a partition specification for every list value that you create.

The following example creates a partitioned table named listpart that is list-partitioned on a color column:

CREATE TABLE listpart (id int, rank int, year int, color char(1), count int) 
  DISTRIBUTED BY (id)
  PARTITION BY LIST (color);

Create the partitions:

CREATE TABLE listpart_red PARTITION OF listpart FOR VALUES IN ('r');
CREATE TABLE listpart_green PARTITION OF listpart FOR VALUES IN ('g');
CREATE TABLE listpart_blue PARTITION OF listpart FOR VALUES IN ('b');
CREATE TABLE listpart_other PARTITION OF listpart DEFAULT;

Note that the listpart_other table is created with the DEFAULT keyword. This identifies the table as the default partition; Greenplum routes any data that falls outside of the boundaries of all partitions to this table. See Adding a Default Partition for more information.

Defining a Hash Partitioned Table

Note
You can create a hash partitioned table only with the modern partitioning syntax.

A hash partitioned table uses a single hashable column as its partition key column. For hash partitions, you must declare a partition specification for every partition (modulus/remainder combination) that you want to create.

For example, create a table named hpt that is partitioned by the hash of the text column c:

CREATE TABLE hpt (a int, b int, c text) PARTITION BY HASH(c);

Create the partitions:

CREATE TABLE hpt_p1 PARTITION OF hpt FOR VALUES WITH (MODULUS 3, REMAINDER 0);
CREATE TABLE hpt_p2 PARTITION OF hpt FOR VALUES WITH (MODULUS 3, REMAINDER 1);
CREATE TABLE hpt_p3 PARTITION OF hpt FOR VALUES WITH (MODULUS 3, REMAINDER 2);

Insert some data into the partitioned table, and then display the number of rows in each partition:

INSERT INTO hpt SELECT i, i, to_char(i/50, 'FM0000') FROM generate_series(0, 599, 2) i;
SELECT 'hpt_p1' AS partition, count(*) AS row_count FROM hpt_p1
  UNION ALL SELECT 'hpt_p2', count(*) FROM hpt_p2
  UNION ALL SELECT 'hpt_p3', count(*) FROM hpt_p3;

Defining a Multi-level Partitioned Table

You can create a multi-level partition hierarchy by sub-partitioning partitions.

The following modern syntax commands create a two-level partition design similar to that shown in the figure at the beginning of this topic.

Create a table named msales that is partitioned by year:

CREATE TABLE msales (trans_id int, year int, amount decimal(9,2), region text) 
  DISTRIBUTED BY (trans_id)
  PARTITION BY RANGE (year);

Create the yearly partitions, which are themselves partitioned by region:

CREATE TABLE msales_2021 PARTITION OF msales FOR VALUES FROM (2021) TO (2022)
  PARTITION BY LIST (region);
CREATE TABLE msales_2022 PARTITION OF msales FOR VALUES FROM (2022) TO (2023)
  PARTITION BY LIST (region);
CREATE TABLE msales_2023 PARTITION OF msales FOR VALUES FROM (2023) TO (2024)
  PARTITION BY LIST (region);

Create the region partitions:

CREATE TABLE msales_2021_usa PARTITION OF msales_2021 FOR VALUES IN ('usa');
CREATE TABLE msales_2021_asia PARTITION OF msales_2021 FOR VALUES IN ('asia');
CREATE TABLE msales_2021_europe PARTITION OF msales_2021 FOR VALUES IN ('europe');
CREATE TABLE msales_2022_usa PARTITION OF msales_2022 FOR VALUES IN ('usa');
CREATE TABLE msales_2022_asia PARTITION OF msales_2022 FOR VALUES IN ('asia');
CREATE TABLE msales_2022_europe PARTITION OF msales_2022 FOR VALUES IN ('europe');
CREATE TABLE msales_2023_usa PARTITION OF msales_2023 FOR VALUES IN ('usa');
CREATE TABLE msales_2023_asia PARTITION OF msales_2023 FOR VALUES IN ('asia');
CREATE TABLE msales_2023_europe PARTITION OF msales_2023 FOR VALUES IN ('europe');

Partitioning an Existing Table

Tables can be partitioned only at creation. If you have an existing table that you want to partition, you must create a partitioned table, load the data from the original table to the new table, drop the original table, and rename the new table to the original table name. You must also re-grant any table permissions.

For example:

CREATE TABLE msales2 (LIKE msales) PARTITION BY RANGE (year);
-- create the partitions
INSERT INTO msales2 SELECT * FROM msales;
DROP TABLE msales;
ALTER TABLE msales2 RENAME TO msales;
GRANT ALL PRIVILEGES ON msales TO admin;
GRANT SELECT ON msales TO guest;

Note
The LIKE clause does not copy over partition structures when you use it to create a new table.

Loading Data into a Partitioned Table

After you create the partitioned table structure, the top-level root partitioned table is empty. When you insert data into the root, Greenplum routes the data to the bottom-level leaf partitions. In a multi-level partition design, only the leaf partitions at the bottom-most levels of the hierarchy will contain data.

Greenplum rejects rows that cannot be mapped to a leaf partition, and the load fails. To avoid rejection of unmapped rows at load time, define your partition hierarchy with a DEFAULT partition. Any rows that do not match the partition constraint of all partitions load into the DEFAULT partition. Refer to Adding a Default Partition for more information.

At runtime, the query optimizer scans the entire partition hierarchy and uses the partition constraints to determine which of the child partitions to scan to satisfy the query's conditions. Because Greenplum always scans the DEFAULT partition (if your hierarchy has one), DEFAULT partitions that contain data can slow down the overall scan time.

When you use COPY or INSERT to load data into a parent table, Greenplum automatically routes the data to the correct leaf partition, just as it does with a regular table.

A best practice for loading data into partitioned tables is to create an intermediate staging table, load it, and then attach it into your partition hierarchy. See Adding a Partition.

Verifying the Partition Strategy

When a table is partitioned based on the query predicate, you can use EXPLAIN to verify that the query optimizer scans only the relevant data to examine the query plan.

For example, suppose a table named msales is year-range partitioned and sub-partitioned by region. For the following query:

EXPLAIN SELECT * FROM msales WHERE year='2021' AND region='usa';

The query plan for this query should show a table scan of only the following tables:

the default partition returning 0-1 rows (if your partition design has one)
the 2021 partition (msales_2021) returning 0-1 rows
the USA region sub-partition (msales_2021_usa) returning some number of rows.

The following example shows the relevant portion of the query plan.

                                  QUERY PLAN                                   
-------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..228.52 rows=1 width=54)
   ->  Seq Scan on msales_2021_usa  (cost=0.00..228.50 rows=1 width=54)
         Filter: ((year = 2021) AND (region = 'usa'::text))
 Optimizer: Postgres-based planner
(4 rows)

Ensure that the query optimizer does not scan unnecessary partitions or sub-partitions (for example, scans of months or regions not specified in the query predicate), and that scans of the top-level tables return 0-1 rows.

Troubleshooting Selective Partition Scanning

The following limitations can result in a query plan that shows a non-selective scan of your partition hierarchy.

The query optimizer can selectively scan partitioned tables only when the query contains a direct and simple restriction of the table using immutable operators such as: =, < , <=, >, >=, and <>.
Selective scanning recognizes STABLE and IMMUTABLE functions, but does not recognize VOLATILE functions within a query. For example, WHERE clauses such as date > CURRENT_DATE cause the query optimizer to selectively scan partitioned tables, but time > TIMEOFDAY does not.

About Viewing Your Partition Design

Partitioning information is stored in the pg_partitioned_table system catalog, and in additional fields in the pg_class (relispartition and relpartbound) catalog.

You can also use the following functions to obtain information about the partitioned tables in the database:

Name	Return Type	Description
pg_partition_tree(regclass)	setof record	Lists information about tables or indexes in a partition hierarchy for a given partitioned table or partitioned index, with one row for each partition. Information provided includes the name of the partition, the name of its immediate parent, a `boolean` value indicating if the partition is a leaf, and an `integer` identifying its level in the hierarchy. The value of level begins at `0` for the input table or index in its role as the root of the partition hierarchy, `1` for its partitions, `2` for their partitions, and so on.
pg_partition_ancestors(regclass)	setof regclass	Lists the ancestor relations of the given partition, including the partition itself.
pg_partition_root(regclass)	regclass	Returns the topmost parent of the partition hierarchy to which the given relation belongs.

For example, the following command displays the partition inheritance structure of the msales table:

SELECT * FROM pg_partition_tree( 'msales' );

Viewing a Classic Syntax Sub-Partition Template

For a partitioned table that you create using the classic syntax, the gp_partition_template system catalog table describes the relationship between the partitioned table and the sub-partition template defined at each level in the partition hierarchy.

Use the pg_get_expr() helper function with gp_partition_template to get a more user-friendly view of the template, as follows:

SELECT pg_get_expr(template, relid) FROM gp_partition_template 
  WHERE relid = '<table_name>'::regclass;

Constructing a pg_partitions-Equivalent Query

Refer to Migrating Partition Maintenance Scripts to the New Greenplum 7 Partitioning Catalogs for information about mapping Greenplum 6 partitioning catalogs to the new definitions in Greenplum 7.

About Partition Pruning

Partition pruning is a query optimization technique that improves performance for declaratively partitioned tables.

You can enable or disable partition pruning for the Postgres-based planner by setting the enable_partition_pruning server configuration parameter.

As an example:

SET enable_partition_pruning = on;                 -- the default
SELECT count(*) FROM measurement WHERE logdate >= DATE '2021-01-01';

Without partition pruning, the above query would scan each of the partitions of the measurement table. With partition pruning enabled, the planner or query optimizer examines the definition of each partition to prove that the partition need not be scanned because it could not contain any rows meeting the query's WHERE clause. When the planner or query optimizer can prove this, it excludes (prunes) the partition from the query plan.

Using the EXPLAIN command and the enable_partition_pruning configuration parameter, it's possible to show the difference between a plan for which partitions have been pruned and one for which they have not. A typical unoptimized plan from the Postgres-based planner for this type of table setup is:

SET enable_partition_pruning = off;
EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2021-01-01';

                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Aggregate  (cost=188.76..188.77 rows=1 width=8)
   ->  Append  (cost=0.00..181.05 rows=3085 width=0)
         ->  Seq Scan on measurement_y2021m01  (cost=0.00..33.12 rows=617 width=0)
               Filter: (logdate >= '2021-01-01'::date)
         ->  Seq Scan on measurement_y2021m02  (cost=0.00..33.12 rows=617 width=0)
               Filter: (logdate >= '2021-01-01'::date)
...
         ->  Seq Scan on measurement_y2023m01  (cost=0.00..33.12 rows=617 width=0)
               Filter: (logdate >= '2021-01-01'::date)

Some or all of the partitions might use index scans instead of full-table sequential scans, but the point is that there is no need to scan the older partitions to answer this query. With partition pruning enabled, a significantly cheaper plan is generated that delivers the same answer:

SET enable_partition_pruning = on;
EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';

                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Aggregate  (cost=37.75..37.76 rows=1 width=8)
   ->  Seq Scan on measurement_y2021m01  (cost=0.00..33.12 rows=617 width=0)
         Filter: (logdate >= '2021-01-01'::date)

Note that partition pruning is driven only by the constraints defined implicitly by the partition keys, not by the presence of indexes. Therefore it isn't necessary to define indexes on the key columns. Whether you should create an index for a given partition depends on whether you expect that queries that scan the partition will generally scan a large part of the partition or just a small part. An index will be helpful in the latter case but not the former.

Partition pruning can be performed not only during the planning of a given query, but also during its execution. This is useful because it can allow more partitions to be pruned when clauses contain expressions whose values are not known at query planning time, for example, parameters defined in a PREPARE statement, using a value obtained from a subquery, or using a parameterized value on the inner side of a nested loop join. Partition pruning during execution can be performed at any of the following times:

During initialization of the query plan. Partition pruning can be performed here for parameter values which are known during the initialization phase of execution. Partitions which are pruned during this stage will not show up in the query's EXPLAIN or EXPLAIN ANALYZE. It is possible to determine the number of partitions which were removed during this phase by observing the "Subplans Removed" property in the EXPLAIN output.
During actual execution of the query plan. Partition pruning may also be performed here to remove partitions using values which are only known when running the actual query. This includes values from subqueries and values from run-time parameters such as those from parameterized nested loop joins. Since the value of these parameters may change many times during the execution of the query, Greenplum Database performs partition pruning whenever one of the run-time parameters being used by partition pruning changes. Determining whether partitions were pruned during this phase requires careful inspection of the loops property in the EXPLAIN ANALYZE output. Subplans corresponding to different partitions may have different values for it depending on how many times each of them was pruned during execution. Some may be shown as (never executed) if they were pruned every time.

Partition Maintenance

Normally, the set of partitions established when initially defining the table is not intended to remain static. Common scenarios include removing partitions holding old data and periodically adding new partitions for new data. One of the biggest advantages of partitioning is precisely that it allows you to perform these tasks nearly instantaneously by manipulating the partition structure, rather than physically moving around large amounts of data.

Important
The examples in the next section use the modern partitioning syntax. Refer to Partition Maintenance with the Classic Syntax for information about the maintenance activities and commands for partitioned tables that you created using the classic partitioning syntax.

Partition Maintenance with the Modern Syntax

To maintain a partitioned table that you created with the modern syntax, you use the ALTER TABLE command against the top-level root partitioned table or the partition itself.

Adding a Partition

You add a new partition to handle new data. You can add a partition to a partition hierarchy both at the time of, or after, table creation.

To add to the partition hierarchy during table creation, use the CREATE TABLE ... PARTITION OF command. For example:

CREATE TABLE msales_mfeb20 PARTITION OF msales
    FOR VALUES FROM ('2020-02-01') TO ('2020-03-01');

Alternatively, it is sometimes more convenient to create the new table outside of the partition structure, and make it a proper partition later. This allows data to be loaded, checked, and transformed prior to it appearing in a partitioned table. The CREATE TABLE ... LIKE command form avoids having to repeat the parent table's definition:

-- create like the parent table
CREATE TABLE msales_mfeb20 (LIKE msales INCLUDING DEFAULTS INCLUDING CONSTRAINTS);

-- add constraints
ALTER TABLE msales_mfeb20 ADD CONSTRAINT y2020m02
   CHECK ( logdate >= DATE '2020-02-01' AND logdate < DATE '2020-03-01' );

-- add data or other prep work

To add this existing table to a partition hierarchy, use ALTER TABLE ... ATTACH PARTITION: For example:

-- attach to the partition hierarchy, specifying the boundaries/constraints
ALTER TABLE msales ATTACH PARTITION msales_mfeb20
  FOR VALUES FROM ('2020-02-01') TO ('2020-03-01');

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

Before running the ATTACH PARTITION command, consider creating a CHECK constraint on the table to be attached that matches the expected partition constraint, as illustrated above. If you do this, the system skips the scan which is otherwise required to validate the implicit partition constraint. Without the CHECK constraint, Greenplum Database scans the table to validate the partition constraint while holding an ACCESS EXCLUSIVE lock on that partition. Be sure to drop the now-redundant CHECK constraint after the ATTACH PARTITION completes. If the table being attached is itself a partitioned table, then Greenplum recursively locks and scans each of its sub-partitions until it encounters either a suitable CHECK constraint or reaches the leaf partitions.

Similarly, if the partitioned table has a DEFAULT partition, consider creating a CHECK constraint that excludes the to-be-attached partition's constraint. If you do not do this, Greenplum scans the DEFAULT partition to verify that it contains no records which should be located in the partition being attached. Greenplum performs this operation whiling holding an ACCESS EXCLUSIVE lock on the DEFAULT partition. If the DEFAULT partition is itself a partitioned table, then Greenplum recursively checks each of its partitions in the same way as the table being attached, as mentioned above.

Indexing Partitioned Tables

Creating an index on the key column(s) of a partitioned table automatically creates a matching index on each partition, and any partitions that you create or attach later will also have such an index. An index or unique constraint declared on a partitioned table is "virtual" in the same way that the partitioned table is: the actual data is in child indexes on the individual partition tables.

Creating indexes on partitioned tables so that they are applied automatically to the entire hierarchy is very convenient; not only will the existing partitions be indexed, but any partitions that are created in the future will be indexed as well. To avoid long lock times, you can CREATE INDEX ON ONLY the partitioned table. Such an index is marked invalid, and Greenplum does not apply the index automatically to the partitions. You can create the indexes on partitions individually, and then attach to the index on the parent using ALTER INDEX .. ATTACH PARTITION. After indexes for all partitions are attached to the parent index, the parent index is marked valid automatically. For example:

CREATE INDEX measurement_usls_idx ON ONLY measurement (unitsales);

CREATE INDEX measurement_usls_202102_idx
    ON measurement_y2021m02 (unitsales);
ALTER INDEX measurement_usls_idx
    ATTACH PARTITION measurement_usls_202102_idx;
...

You can use this technique with UNIQUE and PRIMARY KEY constraints too; the indexes are created implicitly when the constraint is created. Example:

ALTER TABLE ONLY measurement ADD UNIQUE (city_id, logdate);

ALTER TABLE measurement_y2021m02 ADD UNIQUE (city_id, logdate);
ALTER INDEX measurement_city_id_logdate_key
    ATTACH PARTITION measurement_y2021m02_city_id_logdate_key;
...

Renaming a Partition

You rename a partition in a partition hierarchy in the same way you rename a table, using the ALTER TABLE ... RENAME TO command.

ALTER TABLE msales_mfeb17 RENAME TO msales_month_feb17;

Adding a Default Partition

The DEFAULT keyword identifies a partition as the default partition. When it encounters data that falls outside of the boundaries of all partitions, Greeplum Database routes the data to the default partition. Greenplum will reject incoming data if it does not match the constraint of any partition and there is no default partition defined, and returns an error. Identifying a default partition ensures that incoming data that does not match a partition is inserted into the partitioned table.

A partitioned table can only have one default partition. You can add a default partition to a partition hierarchy during or after table creation.

To identify a partition as a default partition when you create the table:

CREATE TABLE msales_other PARTITION OF msales DEFAULT;

To identify a table as a default partition after table creation:

-- previously created table that has the same schema as the root
CREATE TABLE msales_other (LIKE msales);

-- attach to the partition hierarchy
ALTER TABLE msales ATTACH PARTITION msales_other DEFAULT;

Dropping a Partition

The simplest option for removing old data is to drop the partition that is no longer necessary:

DROP TABLE measurement_y2020m02;

This operation can very quickly delete millions of records because it doesn't have to individually delete every record. Note, however, that the above command requires taking an ACCESS EXCLUSIVE lock on the parent table.

Another option is to remove the partition from the partitioned table but retain access to it as a table in its own right. See Detaching a Partition below.

Detaching a Partition

You can detach a partition from your partition hierarchy using the ALTER TABLE ... DETACH PARTITION command. Detaching a partition removes it from the partition hierarchy, but does not drop the table. Detaching a partition that has sub-partitions automatically detaches those partitions as well.

For range partitions, it is common to detach the older partitions to roll old data out of the data warehouse. For example:

ALTER TABLE msales DETACH PARTITION msales_2021;

Detaching allows further operations to be performed on the data before it is dropped. For example, this may be an opportune time to back up the data using COPY or similar tools. You might also choose to aggregate data into smaller formats, perform other data manipulations, or run reports.

Truncating a Partition

Truncating a partition is the same as truncating any table. When you truncate a partition that is partitioned itself, Greenplum Database automatically truncates the sub-partitions as well.

Truncate a partition:

TRUNCATE ONLY msales_other;

Truncate the whole partitioned table:

TRUNCATE msales;

Exchanging a Partition

Exchanging a partition swaps one table in place of an existing partition. To exchanging a partition, DETACH the original partition and then ATTACH the new partition. You can exchange partitions only at the lowest level of your partition hierarchy (only partitions that contain data can be exchanged).

You cannot exchange a partition with a replicated table. Exchanging a partition with a partitioned table or a non-leaf child partition of a partitioned table is also not supported.

Note
Greenplum Database always validates the data against the partition constraint. You must ensure that the data in the table that you are attaching is valid against the constraints on the partition.

ALTER TABLE msales DETACH PARTITION msales_2021;
ALTER TABLE msales ATTACH PARTITION msales_2021_new FOR VALUES FROM (2021) TO (2022);

Multi-column List Partitioning

Greenplum 6 supports multi-column list partitioning. However, this feature is no longer supported in Greenplum 7. The Postgres 12 merge in Greenplum 7 introduced significant changes to the partitioning Data Definition Language (DDL). For more information, see Choosing the Partitioning Syntax.

Here is an example of a multi-column list partition in Greenplum 6:

CREATE TABLE part (
    id INT,
    date_col DATE,
    b TEXT,
    c TEXT
)
PARTITION BY RANGE (date_col)
    SUBPARTITION BY LIST (b, c)
      SUBPARTITION TEMPLATE (
        SUBPARTITION aa VALUES('a', 'a'),
        SUBPARTITION ab VALUES('a', 'b'),
        SUBPARTITION ba VALUES('b', 'a'),
        SUBPARTITION bb VALUES('b', 'b')
      )
(START ('01-01-2020') END ('01-01-2022') EVERY (interval '1 year'));

To address this syntax change, consider the following two options:

Option 1: Multi-level List Partitioning

Instead of using multi-column list partitions, subpartition the subpartition. In the example below, subpartition by b text and then subpartition by c text. This creates the same number of leaf partitions as with multi-column list partitioning in Greenplum 6, however, it will create some extra interior partitions for the first level of list partitioning.

CREATE TABLE part (
    id INT,
    date_col DATE,
    b TEXT,
    c TEXT
)
PARTITION BY RANGE (date_col)
    SUBPARTITION BY LIST (b)
        SUBPARTITION TEMPLATE (
            SUBPARTITION a VALUES('a'),
            SUBPARTITION b VALUES('b')
        )
        SUBPARTITION BY LIST (c)
            SUBPARTITION TEMPLATE (
                SUBPARTITION a VALUES('a'),
                SUBPARTITION b VALUES('b')
            )
(START ('01-01-2020') END ('01-01-2022') EVERY (INTERVAL '1 year'));

-- See the hierarchy:
SELECT * FROM gp_toolkit.gp_partitions;

Option 2: Composite Type List Partitioning

You can achieve the same result by using modern syntax and a composite type as follows:

-- Create composite type
CREATE TYPE text_composite AS (b TEXT, c TEXT);

-- Create root
CREATE TABLE part (
    id INT,
    date_col DATE,
    val text_composite
) PARTITION BY RANGE (date_col);

-- Create level 1 partitions
CREATE TABLE part_2020 PARTITION OF part 
    FOR VALUES FROM ('01-01-2020') TO ('12-31-2020') 
    PARTITION BY LIST (val);

CREATE TABLE part_2021 PARTITION OF part 
    FOR VALUES FROM ('01-01-2021') TO ('12-31-2021') 
    PARTITION BY LIST (val);

-- Create level 2 partitions (list)
CREATE TABLE part_2020_aa PARTITION OF part_2020 
    FOR VALUES IN (ROW('a', 'a'));
CREATE TABLE part_2020_ab PARTITION OF part_2020 
    FOR VALUES IN (ROW('a', 'b'));
CREATE TABLE part_2020_ba PARTITION OF part_2020 
    FOR VALUES IN (ROW('b', 'a'));
CREATE TABLE part_2020_bb PARTITION OF part_2020 
    FOR VALUES IN (ROW('b', 'b'));

CREATE TABLE part_2021_aa PARTITION OF part_2021 
    FOR VALUES IN (ROW('a', 'a'));
CREATE TABLE part_2021_ab PARTITION OF part_2021 
    FOR VALUES IN (ROW('a', 'b'));
CREATE TABLE part_2021_ba PARTITION OF part_2021 
    FOR VALUES IN (ROW('b', 'a'));
CREATE TABLE part_2021_bb PARTITION OF part_2021 
    FOR VALUES IN (ROW('b', 'b'));

-- See the hierarchy:
SELECT * FROM gp_toolkit.gp_partitions;

Comparing the Two Options

The table below outlines the pros and cons of both options:

	Option 1: Multi-level List Partitioning	Option 2: Composite Type List Partitioning
Pros	Queries (DML, SELECT) do not need to change.	Number of partitions stays the same as legacy multi-column.
Cons	More interior partitions.	Queries (SELECTs, DML) referencing the composite list partition key will have to change.

Best Practices

Choose the table partitioning strategy carefully, as the performance of query planning and execution can be negatively affected by poor design.

One of the most critical design decisions you must make is choosing the column or columns by which you partition your data. The best choice is often to partition by the column or set of columns which most commonly appear in WHERE clauses of queries being run on the partitioned table. Greenplum can use WHERE clauses that are compatible with the partition bound constraints to prune unneeded partitions. However, you may be forced into making other decisions by requirements for the PRIMARY KEY or a UNIQUE constraint. Removal of unwanted data is also a factor to consider when you plann your partitioning strategy. An entire partition can be detached fairly quickly, so it may be beneficial to design the partition strategy in such a way that all data that you wnat to remove at once is located in a single partition.

Choosing the target number of partitions that the table should be divided into is also a critical decision. Not having enough partitions may mean that indexes remain too large and that data locality remains poor, which could result in low cache hit ratios. However, dividing the table into too many partitions can also cause issues. Too many partitions can result in longer query planning times and higher memory consumption during both query planning and execution, as further described below. When choosing how to partition your table, it's also important to consider what changes may occur in the future. For example, if you choose to have one partition per customer and you currently have a small number of large customers, consider the implications if in several years you instead find yourself with a large number of small customers. In this case, it may be better to choose to partition by HASH and choose a reasonable number of partitions rather than trying to partition by LIST and hoping that the number of customers does not increase beyond what it is practical to partition the data by.

Sub-partitioning can be useful to further divide partitions that are expected to become larger than other partitions. Another option is to use range partitioning with multiple columns in the partition key. Either of these can easily lead to excessive numbers of partitions, so restraint is advisable.

It is important to consider the overhead of partitioning during query planning and execution. The query planner and optimizer are generally able to handle partition hierarchies with up to a few thousand partitions fairly well, provided that typical queries allow the query planner to prune all but a small number of partitions. Planning times become longer and memory consumption becomes higher when more partitions remain after the planner performs partition pruning. This is particularly true for the UPDATE and DELETE commands. Another reason for concern about a large number of partitions is that the server's memory consumption may grow significantly over time, especially if many sessions touch large numbers of partitions. That's because each partition requires its metadata to be loaded into the local memory of each session that touches it.

If your workloads are performing data warehousing tasks, it can make sense to use a larger number of partitions than with an OLTP type workload. Generally, in data warehouses, query planning time is less of a concern as the majority of processing time is spent during query execution. With either of these two types of workload, it is important to make the right decisions early, as re-partitioning large quantities of data can be painfully slow. Simulations of the intended workload are often beneficial for optimizing the partitioning strategy. Never just assume that more partitions are better than fewer partitions, nor vice-versa.

Limitations

Take note of the following Greenplum Database partitioned table limitations:

A partitioned table can have a maximum of 32,767 partitions at each level.
Greenplum does not support partitioning replicated tables (tables created with the DISTRIBUTED REPLICATED distribution policy).
The Greenplum query optimizer (GPORCA) does not support uniform multi-level partitioned tables. If GPORCA is enabled (the default) and the partitioned table is multi-level, Greenplum Database runs queries against the table with the Postgres-based planner.
The Greenplum Database gpbackup utility does not back up data from a leaf partition of a partitioned table when the leaf partition is an external or foreign table.
To create a unique or primary key constraint on a partitioned table, the partition keys must not include any expressions or function calls and the constraint's columns must include all of the partition key columns. This limitation exists because the individual indexes making up the constraint can only directly enforce uniqueness within their own partitions; the partition structure itself must guarantee that there are not duplicates in different partitions.
There is no way to create an exclusion constraint spanning the whole partitioned table. You can put such a constraint only on each leaf partition individually. This limitation stems from not being able to enforce cross-partition restrictions.
Mixing temporary and permanent relations in the same partition hierarchy is not allowed. If the partitioned table is permanent, so must be its partitions and likewise if the partitioned table is temporary. When using temporary relations, all members of the partition hierarchy must be from the same session.

Individual partitions are linked to their partitioned table using inheritance behind the scenes. However, it is not possible to use all of the generic features of inheritance with declaratively partitioned tables or their partitions, as discussed below. Notably, a partition cannot have any parents other than the partitioned table it is a partition of, nor can a table inherit from both a partitioned table and a regular table. Partitioned tables and their partitions never share an inheritance hierarchy with regular tables.

Since a partition hierarchy consisting of the partitioned table and its partitions is still an inheritance hierarchy, tableoid and all of the normal rules of inheritance apply as described in Inheritance in the PostgreSQL documentation, with a few exceptions:

Partitions cannot have columns that are not present in the parent. It is not possible to specify columns when creating partitions with CREATE TABLE, nor is it possible to add columns to partitions after-the-fact using ALTER TABLE. Tables may be added as a partition with ALTER TABLE ... ATTACH PARTITION only if their columns exactly match the parent.
Both CHECK and NOT NULL constraints of a partitioned table are always inherited by all of its partitions. Greenplum does not permit CHECK constraints that are marked NO INHERIT to be created on partitioned tables. You cannot drop a NOT NULL constraint on a partition's column if the same constraint is present in the parent table.
Using ONLY to add or drop a constraint on only the partitioned table is supported as long as there are no partitions. Once partitions exist, using ONLY will result in an error. Instead, constraints on the partitions themselves can be added and (if they are not present in the parent table) dropped.
As a partitioned table does not have any data itself, attempts to use TRUNCATE ONLY on a partitioned table always return an error.

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

If the external or foreign table partition is not writable or the user does not have permission to write to the table, commands that attempt to modify data in the external or foreign partition (INSERT, DELETE, UPDATE, or TRUNCATE) return an error.
A COPY command cannot copy data to a partitioned table that updates an external or foreign table partition.
A COPY command that attempts to copy from an external or foreign table partition returns an error unless you specify the IGNORE EXTERNAL PARTITIONS clause. When you specify the clause, Greenplum Database does not copy data from external or foreign table partitions.

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 table my_sales contains a leaf partition that is an external table, this command sends the data to stdout:
```
COPY (SELECT * from my_sales ) TO stdout
```
VACUUM commands skip external and foreign table partitions.
If the external or foreign table partition is not writable or the user does not have permission to write to the table, Greenplum returns an error on the following operations:
- Adding or dropping a column.
- Changing the data type of a column.

These (classic syntax) ALTER TABLE ... ALTER PARTITION operations are not supported when the partitioned table contains an external or foreign table partition:

Setting a sub-partition template.
Altering the partition properties.
Creating a default partition.
Setting a distribution policy.
Setting or dropping a NOT NULL constraint of column.
Adding or dropping constraints.
Splitting an external partition.