pg_ivm - Extensions for Rapid Materialized View Update

on YouTube ↗45m

" summary

pg_ivm implements Incremental View Maintenance for PostgreSQL (13–17) as an extension that stores IMVs as tables and wires automatic updates with AFTER triggers and transition tables. It rewrites the view query to compute DeltaV and applies changes via INSERT/DELETE/UPDATE, auto-creating indexes when possible and adding auxiliary columns for aggregates. Typical results: a view that takes ~20s to refresh is updated in 20 milliseconds (about 500× faster), another case shows 150× faster. Tradeoffs: per-row update cost, concurrency locking, and limited support (no outer joins yet).

AI-written summary · about

" chapters (10)

1. 0:00Introduction to pg_ivm
2. 2:27Concepts of IVM
3. 6:57Purpose and Overview
4. 10:56Supported features and usage
5. 14:06Installation and command usage
6. 20:07Implementation mechanics
7. 31:50Performance evaluation
8. 34:49Performance trade-offs
9. 36:36Development and future plans
10. 40:02Questions and answers

" related talks

• 48mInside Postgres: why VACUUM mattersRohan Reddy Alleti · Rootconf 2025 Annual Conference
• 01h 08mIncremental View Maintenance 1Dan Olteanu · Logic and Algorithms in Database Theory and AI Boot Camp
• 38mIncremental View Maintenance: Beyond Worst-Case Analysis IXiao Hu · Fine-Grained Complexity, Logic, and Query Evaluation 2023
• 49mIncremental ComputationMihai Budiu · Rootconf Topical Edition on Databases 2026
• 50mSolving PostgreSQL Wicked ProblemsMehboob Alam, Alexander Korotkov · Percona Live 2022
• 02h 00mTutorialAndres Freund, Marco Slot · PGConf Silicon Valley 2015
• 01h 05mIncremental View Maintenance 2Dan Olteanu · Logic and Algorithms in Database Theory and AI Boot Camp
• 21mRecent Advances in Incremental View MaintenanceHaozhe Zhang · Sparse 2024