Monday, July 6, 2020

How to use table partitioning to scale PostgreSQL


https://www.enterprisedb.com/postgres-tutorials/how-use-table-partitioning-scale-postgresql

Benefits of partitioning 

  • PostgreSQL declarative partitioning is highly flexible and provides good control to users. Users can create any level of partitioning based on need and can modify, use constraints, triggers, and indexes on each partition separately as well as on all partitions together.
  • Query performance can be increased significantly compared to selecting from a single large table.
  • Partition-wise-join and partition-wise-aggregate features increase complex query computation performance as well.
  • Bulk loads and data deletion can be much faster, as based on user requirements these operations can be performed on individual partitions.
  • Each partition can contain data based on its frequency of use and so can be stored on media that may be cheaper or slower for low-use data.

When to use partitioning 

Most benefits of partitioning can be enjoyed when a single table is not able to provide them. So we can say that if a lot of data is going to be written on a single table at some point, users need partitioning. Apart from data, there may be other factors users should consider, like update frequency of the data, use of data over a time period, how small a range data can be divided, etc. With good planning and taking all factors into consideration, table partitioning can give a great performance boost and scale your PostgreSQL to larger datasets.

How to use partitioning 

As of PostgreSQL12 release List, Range, Hash and combinations of these partition methods at different levels are supported.

No comments:

Post a Comment