Native Partitioning


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!

List all child tables

SELECT nmsp_parent.nspname AS parent_schema,
       parent.relname      AS parent,
       nmsp_child.nspname  AS child_schema,
       child.relname       AS child
FROM pg_inherits
JOIN pg_class parent
  ON pg_inherits.inhparent = parent.oid
JOIN pg_class child
  ON pg_inherits.inhrelid   = child.oid
JOIN pg_namespace nmsp_parent
  ON nmsp_parent.oid  = parent.relnamespace
JOIN pg_namespace nmsp_child
  ON nmsp_child.oid   = child.relnamespace
WHERE parent.relname='events'