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.
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
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
- Recommend set constraint_exclusion to
partition, set to on will give query optimizer overheads on all tables.
- Child tables also needs to be
- SET enable_partition_pruning = on; (default is on). Query planner can make smarter choices working with partitioned tables.
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' ;