Designing the Right Schema to Power Heap
summary
Heap installs a snippet that captures every click and UI event so analyses are retroactive; that forces redesigns of the schema from normalized tables to per-user JSON arrays to a denormalized events table with partial indexes per event definition and later extracting ~45 common columns. The dataset runs hundreds of terabytes, earlier array approach hits MVCC bloat and Postgres 1GB field limits, the partial‑index design scales customers from ~5M to supports 30M sessions/month, but costs CPU to maintain ~1,500–2,000 event indexes.
AI-written summary · about
chapters (22)
- 0:00Introduction
- 5:42Analytics is an iterative process
- 13:39Observations
- 16:04Normal Form
- 17:12Pros
- 19:00Cons
- 20:20User Partition
- 20:52User Analysis
- 23:59User Analysis Pros
- 28:12Hacks
- 30:49Ideal Cases
- 32:05Bloat
- 33:19Problems
- 34:25Bloating
- 36:13Partial Index
- 39:19Funnel Events
- 40:03Performance
- 41:20Limitations
- 46:29CPU Savings
- 47:42Outleave
- 49:23Partitioning
- 51:01PostgreSQL Wishlist
related talks
02h 00mTutorial
23mLessons from Optimizing Citus Queries at Heap
48mBuilding Petabyte-Scale PostgreSQL Deployments
54mReducing Costs and Improving Performance With Data Modeling in Postgres
53mIndexing Internals for Developers & DBAs
25mData-intensive PostgreSQL: Three ways to scale
48mAdvanced Postgres Schema Design (For Scalable Application Development) bySehrope Sarkuni
52mBulk data processing and PostgreSQL thingy