Database Partitioning with a Postgres Database

All order related tables in a Postgres database are partitioned individually.

Note: Refer to the Partition_Drop_Readme.txt file under $AF_HOME/db/postgreSQL/purge for the list of partitioned tables.

A PARTITIONDATE column is added to each table that stores order related information. All these tables are partitioned monthly based on the PARTITIONDATE column.

The following are the advantages of a partitioned database:
  • Data is logically separated

    With partitioning in place, the database administrator has the option to keep each partition in its own tablespace resulting in better space management and improving the total cost of ownership.

  • Purge performance is improved

    You can now drop a partition instead of manually deleting entries from the ORDERS and other related tables for purging. Since in Postgres each partition is represented by individual tables, the drop partition does not impact any other table or partition for the same table. This operation is also very quick compared to manually deleting the rows.

The following is an example of monthly partitioning:
Partition Number Orders Submitted Between (Date and Time)
Partition 1 FROM: 1st of June - 1 a.m.

TO: 1st of July - 12:59 a.m.

Partition 2 FROM: 1st of July - 1 a.m.

TO: 1st of August 12:59 a.m.

Partition 3 FROM: 1st of September - 1 a.m.

TO: 1st of October - 12:59 a.m.

Partition 4 FROM: 1st of October - 1AM

TO: 1st of November - 12:59 a.m.

Partition 5 FROM: 1st of November - 1AM

TO: 1st of December - 12:59 a.m.