In this story we will going to discuss about table partition in PostgreSQL. Partition is splitting what is logically one large table into smaller physical pieces.
As huge amounts of data are stored in databases, performance and scaling get affected. Partitioning helps as it can divide large tables into smaller tables hence reducing memory swap problems and table scans, finally increasing the performance.
Horizontal and Vertical partition are two main categories of partitioning.
Partitioning can provide several benefits:
-
Query performance can be improved dramatically
-
Bulk loads and deletes can be accomplished by adding or removing partitions without adding overhead
Types of Partitions belongs to horizontal partitioning
Important
-
Both the primary key and all unique keys must include partition key in their definition. The partition structure itself must guarantee that there are not duplicates in different partitions
-
Partitions are not created automatically, so make sure you have a default partition
- Ensure that the enable_partition_pruning configuration parameter is not disabled in
postgresql.conf. If it is, queries will not be optimized as desired - DEFAULT partition is important feature, which stores records that don’t belongs to any other partition defined.
Let’s discuss about each type of partition in up coming stories. Thank you.