PostgreSQL: Native Partitioning

Official Postgres 12 document

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

  • 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 VACUUM or ANALYZE
  • SET enable_partition_pruning = on; (default is on). Query planner can make smarter choices working with partitioned tables.