Tutorial
summary
Claims that PostgreSQL can replace multi-stage stacks by delivering real-time dashboards using materialized views, triggers and the HyperLogLog extension on the GitHub Archive dataset (~600k events/day ≈ 1.6GB/day, month preload ~19–22GB). Naive refreshes and per-row triggers make queries take minutes and drop ingest from ~2000 rows/s/core to ~380; switching to a queue of deltas with a probabilistic refresh materialized view concurrently style flush restores throughput to ~1,700–1,900 rows/s and yields tiny materialized tables (3MB) with HLL ≈1.8% error — effective but tradeoffs remain.
AI-written summary · about
chapters (27)
- 0:00Start
- 0:01PostgreSQL
- 0:20Tutorial Logistics
- 2:39Exercise file names
- 6:28Use-case: Analytical dashboards
- 8:05Different aggregates
- 9:14Real-time Analytics
- 11:30Exercise: Github Events Data
- 13:31Analyzing raw data
- 14:09Querying JSONB
- 15:22Exercise: Load Github Events data
- 19:15Setting up materialized views
- 22:58When to use materialized views
- 24:25Caching aggregates
- 25:21Exercise: Cached Aggregates
- 26:33Exercise: Updated Cached Aggregates
- 28:53Exercise: Set Up Trigger
- 29:32Trigger Exercise
- 32:54Problems with the simple approach
- 36:09Caching older aggregates
- 37:28Aggregate Change Queue
- 42:42Exercise: Change Queue
- 44:25Exercise: Flush Queue Table 1/5
- 52:46Querying the views
- 53:24Exercise: Create the View
- 53:44Loading Data
- 56:49Cascading aggregates
related talks
31mMaking Data Dance
43mJust Use Postgres.
34mAnalytics and AI with PostgreSQL - Mission Possible!
49mPostgreSQL, The Time-Series Database You Actually Want
51mPostgreSQL, The Time-Series Database You Want
27mBuilding a PostgreSQL data warehouse
31mUsing Databases to pull your applications weight
56mLightning Talks