The pitfalls of partitioning Postgres yourself
3 days ago
- #Partitioning
- #Postgres
- #Database Optimization
- Hatchet manually runs ANALYZE on Postgres tables despite having autovacuum enabled due to production incidents and nuances in Postgres's autoanalyze implementation.
- Hatchet, a durable queue built on Postgres, initially stored tasks in a single table but faced performance issues at scale (~200 million rows), leading to time-based partitioning.
- Postgres's built-in partitioning was chosen over extensions like pg_partman and TimescaleDB for compatibility with any Postgres provider.
- A custom partitioning system was implemented, focusing on daily rolling partitions for tables like v1_task and v1_task_event.
- Initial rollout was smooth, but queries slowed down over time, especially those involving JOINs, due to incorrect query planner statistics.
- Manual ANALYZE on the parent partitioned table resolved the issue, as autovacuum does not analyze partitioned tables directly.
- Lessons learned include using DETACH PARTITION...CONCURRENTLY to reduce lock contention and manually ANALYZE parent partition tables periodically.