Table Partitioning

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 Range and List
• 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: Sub partition

Limitations:

• PostgreSQL does not support list/range partitioning with more than one partition key

Configuration

Configure the partition information at Table and Column window

Table

• Partition: Indicates this is a partitioned table
• 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
• Create/update partition: Process which create or update table partitions based on the information in the table and column records

List partitioning

Range partitioning

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
• 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.

Menu

The process can be called from the Create/Update Table Partition menu item. The Partitioning Method and Table Name parameters are optional. By default, the process will execute the process for all partitioned table records (i.e. Table > Partition = Yes)

• Partitioning Method: The partitioning method of the table
• Table Name: This is a table name, or a LIKE clause (e.g. AD_%)

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

Auto addition of new partition

A schedule process called Create/Update Table Partitions is created to run the Create/update partition process daily.