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.
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!
- Each partition inherits primary keys, indexes, foreign keys, check constraints, references
- Delete partition table using
DETACH PARTITION. - Recommend set constraint_exclusion to
partition, set to on will give query optimizer overheads on all tables. - Child tables also needs to be
VACUUMorANALYZE - SET enable_partition_pruning = on; (default is on). Query planner can make smarter choices working with partitioned 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'
;