Hash Partitioning Support and Backporting Constraints in Greenplum 6.x

Products

• VMware Tanzu Data
• VMware Tanzu Greenplum
• VMware Tanzu Greenplum / Gemfire

Issue/Introduction

When migrating data from a PostgreSQL  source database where tables are partitioned by Hash, users often attempt to replicate the exact same partitioning structure in Greenplum Database (GPDB) 6.x.

Greenplum 6.x only supports List and Range partitioning. While Hash partitioning is natively supported in Greenplum 7, users frequently ask: Can the Hash partitioning feature from GP7 be backported to Greenplum 6.x?

Environment

Greenplum Database 6.x

Cause

The inability to backport this feature is due to the fundamental differences in the underlying PostgreSQL core versions used by GPDB 6 and GPDB 7:

1. Underlying Core Architecture: Greenplum 6 is built on the PostgreSQL 9.4 core. Greenplum 7 is built on a much newer PostgreSQL 12+ core.

2. Declarative Partitioning: True native declarative hash partitioning was introduced in later versions of PostgreSQL (specifically PG 11). The PostgreSQL 9.4 core (which GP6 uses) lacks the internal parser, executor, and catalog architectural foundation required to support this.

3. System Stability: Attempting to backport a major architectural change like declarative hash partitioning into the GP6 codebase would require a massive rewrite of the query optimizer and execution engine, which would greatly compromise the stability of the Greenplum 6.x release.

Resolution

Hash partitioning cannot be backported from Greenplum 7 to Greenplum 6.x. 

If structural 1:1 parity with the PostgreSQL source is a hard business requirement and workarounds are unacceptable, the recommended path is to begin planning an upgrade to Greenplum 7, which fully supports declarative Hash partitions.