create partition table

Table Partitioning in PostgreSQL for Data Retention and Archiving

PostgreSQL supports basic table partitioning. Partitioning refers to splitting what is logically one large table into smaller physical pieces.

Benefits of table partitioning:-

• Query performance can be improved dramatically in certain situations, particularly when most of the heavily accessed rows of the table are in a single partition or a small number of partitions. Partitioning effectively substitutes for the upper tree levels of indexes, making it more likely that the heavily-used parts of the indexes fit in memory.
• When queries or updates access a large percentage of a single partition, performance can be improved by using a sequential scan of that partition instead of using an index, which would require random-access reads scattered across the whole table.
• Bulk loads and deletes can be accomplished by adding or removing partitions, if the usage pattern is accounted for in the partitioning design. Dropping an individual partition using DROP TABLE, or doing ALTER TABLE DETACH PARTITION, is far faster than a bulk operation. These commands also entirely avoid the VACUUM overhead caused by a bulk DELETE.
• Seldom-used data can be migrated to cheaper and slower storage media.

Note: These benefits will normally be only when a table would be very large, and the partitioned table itself is a virtual table having no storage of its own, and it is not possible to turn a regular table into a partitioned table or vice versa.

Partitioning methods:

• Range Partitioning
  The table is partitioned into ranges defined by a key column or set of columns, with no overlap between the ranges of values assigned to different partitions. For example partition by date ranges.
• List Partitioning
  The table is partitioned by explicitly listing which key value(s) appear in each partition.
• Hash Partitioning
  The table is partitioned by specifying a modulus and a remainder for each partition. Each partition will hold the rows for which the hash value of the partition key divided by the specified modulus will produce the specified remainder.

Suppose we have a database for a restaurant and our database has a very large order table, and we know that most queries will access just the last month’s, quarter’s or year’s data. To reduce the amount of old data that needs to be stored, we decide to keep only the most recent 3 years worth of data. At the beginning of each year we will remove the oldest year’s data. In this situation we can use partitioning to help us meet all of our different requirements for the order table.

So let’s see how easy it is to implement table partitioning for data retention and archiving.

create partition table

PARTITION BY RANGE tells PostgreSQL that we are partitioning this table by range using column order_date.

Let’s create some partitions each partition should hold one year’s worth of data, to match the requirement of deleting one year’s data at a time. So the commands might look like:

add partitions

Alright, so our orders table is now partitioned by year.

partitions

Note: After creating partitions any data inserted into the order table will be further redirected to one of its partitions based on the order_date column, and inserting data into the order table that does not map to one of the existing partitions will cause an error; an appropriate partition must be added manually.

So what happens when data grows and we need to either remove old data or archive it?
The simplest option for removing old data is to drop the partition that is no longer necessary:

drop partition

This can very quickly delete millions of records because it doesn’t have to individually delete every record. Note however that the above command requires taking an ACCESS EXCLUSIVE lock on the parent table.

While this approach can help you get rid of old data quickly, often preferable option is to remove the partition from the partitioned table but retain access to it as a table in its own right by detaching the partition of the table.

detach partition

and it is easy to return any partition from the partitioned table by attaching the partition of the table.

attach partition

The ATTACH PARTITION command requires taking a SHARE UPDATE EXCLUSIVE lock on the partitioned table.

For more information about how to table partitioning, see Chapter 5. Data Definition, 5.11. Table Partitioning