Native Partitioning

Background

Partitioning is technique to split large table into smaller ones. We can split large table into smaller chunks based on key. Key could be Range, List, or Hash.

For use cases like time-series or serial-based applications, we can partition incoming data for ranges such as daily or based on any custom format like timestamped ID. Each partition contains only the data according to your range (daily). The partition key controls the size of a partition.

When INSERT or UPDATE on a partitioned table, PostgreSQL will do it on the right partition based on criteria you defined (daily). Each partitioned table partitions will be stored as child table of the parent table.

When reading data from partitioned table, PostgreSQL optimizer examines the WHERE clause and only scan the relevant partitions if possible.

Use partition when table is bigger than memory of database.

Native Partitioning

Also called Declarative Partitioning.

No need to define triggers for INSERT, UPDATE, and DELETE like how I demostrated in Postgres: Partitioning. Operation will go to the right partition. Much less maintenance cost. Supports partition by Range, List, and Hash!