Partitioning is a process of dividing a table into multiple smaller pieces, where each piece of such a database object is called a
partition. This tutorial describes what is
Partitioning and provided an example of
Range partitioning in
Benefits of partitioning
- Query performance could be increased when:
- most of the heavily accessed rows of the table are in a single partition or a small number of partitions.
- queries or updates access a large percentage of a single partition because of sequential scan of that partition instead of using an index and random access reads scattered across the whole table
- Seldom-used data can be migrated to cheaper and slower storage media
- Performing maintenance operations on one or more partitions can be made more quickly.
- Data availability
- when one or some partitions are not available the other partitions still could be available.
There are following partitioning methods which control how data is placed into individual partitions:
Range partitioning is the most common type which is mostly used with dates. It divides table into ranges defined by single or multiple columns. It's important to note that ranges should not be overlapped with each other. In that case an equality operators such as
< are mostly used for verifications.
List partitioning is very similar to
Range partitioning. But
List partition stores only the data which is present in
a set of value lists, instead of one set of ranges of values. In verifications the
values IN (list_of_coma_separated_values) is used.
Hash partitioning data is stored into partitions based on hash of partitioning key. In that case data is distributed to partitions approximately in the same size.
Partitioning in PostgreSQL
PostgreSQL supports basic table partitioning via table inheritance. It means that each partition must be created as a child table of a single parent table. The parent table itself is normally empty and exists just to represent the entire data set.
Partitioning example in PostgreSQL
To create partitioned table in
PostgreSQL the following steps should be performed:
- Create the
mastertable, from which all of the partitions will inherit
- Create several
childtables that each inherit from the
mastertable. Normally, these tables will not add any columns to the set inherited from the master.
- Add table
constraintsto the partition tables to define the allowed key values in each partition.
- For each
partition, create an
indexon the key column(s), as well as any other indexes you might want. (The key index is not strictly necessary, but in most scenarios it is helpful. If you intend the key values to be unique then you should always create a unique or primary-key constraint for each partition.)
- Optionally, define a
ruleto redirect data inserted into the
mastertable to the
- Ensure that the
constraint_exclusionconfiguration parameter is not disabled in
postgresql.conf. If it is, queries will not be optimized as desired.
The example of
Range partitioning in PostgreSQL could be found into the following repository on Github: PostgreSQL partitioning example
SQL script for creating and filling partitions could be found here.