Database Reference
In-Depth Information
Partitioning
The process of dividing the tables into smaller manageable parts is called
partitioning
, and these smaller manageable parts are called
partitions
.
In the process of partitioning, we divide one logical big table into multiple physical
smaller parts.
Before actually creating the partitions, let's understand why we need partitions.
The irst and most demanding reason to use partitions in a database is to increase
the performance of the database. This is achieved by partition-wise joins; if a user's
queries perform a lot of full-table scans, partitioning will help vastly, because
partitions will limit the scope of this search. The second important reason to partition
is ease of managing large tables.
Partitioning always helps manage large objects. Although it is applicable to objects
of any size, the advantages are more apparent in large tables. When a user recreates
an index on a nonpartitioned table, the only option is to build the entire index in
one statement. On the other hand, if the table is partitioned, the user can rebuild
partitions of the local indexes one at a time.
In this chapter, we will learn the following topics:
• Why we need partitions
• Different types of partitions in PostgreSQL
Creating a table partition
PostgreSQL supports table partitioning through table inheritance, which means
every partition will be created as a child table of a single parent table. Partitioning
is performed in such a way that every child table inherits a single parent table.
The parent table will be empty; it exists just to describe the whole dataset.
Currently in PostgreSQL, partitioning can be implemented in
range partitioning
or
list partitioning
.
