Optimising your Database for Analytics
summary
Uses PostgreSQL and pgbench to simulate OLTP plus OLAP mixed workloads, generating ~15 million rows with scale factor 100 and measuring an analytics query that initially does external merge files and takes ~8 seconds. Raising work_mem (to 100MB then 1GB) cuts time to ~6s, adding an expression index to precompute a+ b drops it to ~5.5s, converting to a stored generated column reaches ~3s, and a materialized view hits ~2s. Recommends standby or logical replication to run analytics without impacting primary.
AI-written summary · about
chapters (10)
- 0:00Introduction and background
- 1:12OLTP vs OLAP workloads
- 4:27Mixed workload challenges
- 7:21Setting up test environment
- 13:05Optimizing analytics queries
- 18:47Memory and time management
- 21:18Indexing strategies for analytics
- 23:24Pre-calculating and pre-aggregating data
- 25:59Separate analytics databases
- 28:08Summary and key takeaways
related talks
29mPerformance Archaeology - 20 years of improvements
31mDemanding the impossible rigorous database benchm
50mPerformance Archaeology
27mBuilding a PostgreSQL data warehouse
35mAccelerating PostgreSQL Full Table Scan OLAP queries
19mFrom OLTP to OLAP: Is PostgreSQL Eating Analytics Too?
41mUsing Databases to Pull Your Application Weight
53mVisualizing Postgres I O Performance for Development