Feature: Table Partitioning Support

Goal: Technical

Developers: Hengsin, Elaine Tan

Feature Ticket: IDEMPIERE-5943

Description: Table partitioning support for PostgreSQL and Oracle

Support partition by List and Range
Support partition of new and existing table
Support moving of data and constraints from existing table to partitioned table (For PostgreSQL only)
Support auto addition of new partition through scheduled process

Not cover:

List/range partitioning for more than one partition key
Sub-partition

Configuration

Configure the partition information at Table and Column window

Table

Partition: Indicates this is a partitioned table
Create/update partition: Process which create or update table partitions based on the information in the table and column records

AD_ChangeLog

AD_Session

Column

Partition Key: Indicates this is a partition key
Partition Key Sequence: Indicates the order of the partition keys where the lowest number comes first
Partitioning Method: Indicates how the Table is partitioned (List or Range)
- List partitioning - The data distribution is defined by a discrete list of values
- Range Partitioning - The data is distributed based on a range of values
Range Partition Interval: Indicates the interval used in a range partitioning (date or number)
- Examples of date intervals: 1 year; 6 months;
- Examples of number intervals: 5000; 100000;

List Partitioning

Range Partitioning

Execution of the process

The process can be called from the Table and Column window or Menu

Table and Column window

The process can be called from the Create/update partition button on the Table and Column window.

View the table partition info

Read-only. Capture the database partition info from the database to the Table Partition table (i.e. AD_TablePartition). Each Table Partition record represents an actual database partition

Name: The name of the partition
Expression: SQL clause of the partition

Scheduler

A scheduler process called Create/Update Table Partitions is created to run the Create/update partition process daily. This is default to inactive and is for implementation to activate it and select the appropriate interval and timing for a particular instance.
For Oracle, the scheduled process will read auto created partition details from DB and update the X_AD_TablePartition table.
For PostgreSQL, the scheduled process will process new records that goes into default partition, create new partition for those records and move the records from default partition to the newly added partition. The new partition details will be added to the X_AD_TablePartition table.

Future enhancement

Implement second partition key column as sub-partition of first partition key column.

For e.g:

Set C_Order as Partition table
Set C_Order.AD_Client_ID as Partition Key Column with sequence of 10 and Partition Method of List
Set C_Order.Created as Partition Key Column with sequence of 20 and Partition Method of Range
With this configuration, C_Order.AD_Client_ID is the first List partition and C_Order.Created is the Range sub-partition of the C_Order.AD_Client_ID partition