Handling Very Large Tables in Postgres Using Partitioning

Summary

Very large Postgres tables containing application events or log-like data can degrade query performance, slow index updates, and make vacuum disproportionately long. The article explains table partitioning through inheritance, using a master table, child partitions, CHECK constraints, and a trigger that routes inserts according to created_at; for an events workload with 10 million daily inserts and short-lived data, it uses daily partitions. It then introduces pg_partman, whose create_parent command configures time-based partitioning and whose run_maintenance function creates new partitions and removes old ones according to retention settings. Partitioning can improve performance and simplify deletion of expired data, but it should follow indexing and usage analysis, especially when queries cover a small time range and older data has little value.

Takeaways

1. 1

   Postgres queries against a parent table include its descendant tables by default, while individual child partitions can also be queried directly.

2. 2

   Manual partitioning requires maintaining child-table CHECK constraints, insert-routing triggers, future partitions, and procedures for removing old data.

3. 3

   pg_partman can configure daily time-based partitions with create_parent and automate partition creation and retention cleanup through run_maintenance.