Enabling Row movement and Updateable Partition Keys

Platform: 
All Platforms

 

Table partitions use simple rules driven by data values in table rows to spread the rows among the partitions. This provides multiple run time and maintenance efficiencies. However, once you have partitioned a table it is sometimes necessary to automatically move rows between the partitions following updates to data values that change which partition the row should reside in. This Quick Tutorial shows how to automatically move data across partitions and maintain partition keys at the same time.

Introduction

This EnterpriseDB Quick Tutorial will help you get started with the Postgres Plus Standard Server or Postgres Plus Advanced Server database products in a Linux, Windows or Mac environment.It is assumed that you have already downloaded and installed Postgres Plus Standard Server or Postgres Plus Advanced Server on your desktop or laptop computer.

This Quick Tutorial is designed to help you expedite your Technical Evaluation of Postgres Plus Standard Server or Postgres Plus Advanced Server. For more informational assets on conducting your evaluation of Postgres Plus, visit the self-service web site, Postgres Plus Open Source Adoption center.

In this Quick Tutorial you will learn how to do the following:

  • enable row movement in a partitioned table
  • enter and change test data to test row movement

Before stepping through this tutorial, you should be familiar with the process of setting up a partitioned table. If you are not familiar with the steps involved in setting up a partitioned table, please see the Tutorial, 'How to Create a Table Partition in Postgres Plus', available at:http://www.enterprisedb.com/resources-community/tutorials-quickstarts.

Usage Note: While the examples in this tutorial are demonstrated in a Windows environment, the steps are the same for the Linux and Mac environments. You will notice slight variations between the operating systems; there are differences in the tools used (e.g. terminal windows and text editors), the use of forward slashes vs. back slashes in path specifications, and the installation directory locations.

Feature Description

What is Row Movement?

Partitioning divides large tables into smaller physical portions to improve query performance, simplify table loading and unloading operations, and conserve money by keeping seldom-used data on less-expensive or slower storage media. The data stored in a partitioned table is logically a part of a parent table, but is physically stored in one or more child tables.

Partitioned tables are created with rules, triggers or a combination of both; the rules and triggers divide the data between the different partitions according to the constraints defined by the rules and triggers. When you partition a table, you define a set of rules that tell the Postgres Plus server to route each row into an appropriate partition.

For example, if you are partitioning a sales history table, you may partition the table by the date on which each transaction is recorded. The transaction date is known as the partition key. Since the partitioning rules control where each row resides (based on the partition key), any change to a partition key value may logically move a row from one partition to another. Row movement is the process of automatically moving such a row from its original partition to the new partition when the partition key changes.

Another example of using row movement to maintain data integrity in a partitioned table would be if you were using a database to manage an employee table; if the table is partitioned based on the department number, and an employee moves to a new department, row movement allows the data to move from on partition to the other.

If row movement is not enabled, and you try to re-assign an employee to a new department, you would likely encounter an error such as:

ERROR:  new row for relation "employees_part1" violates check 
constraint "employees_part1_dept_check"

If you don't enable row movement, you won't be able to update the partition key; hence the error message prevents you from orphaning a row in the wrong partition.

Tutorial Steps

For a detailed description of the steps that show how to move rows across partitions in PostgreSQL, download the PDF:

How to Move Rows across Partitions in Postgres Plus