pg_ivm
activeby SRA OSS (IVM Development Group) · since 2022 · also known as pg-ivm, Incremental View Maintenance for PostgreSQL
A Postgres extension that keeps materialized views updated incrementally inside the same transaction.
Overview
pg_ivm adds Incrementally Maintainable Materialized Views (IMMVs) to PostgreSQL. You create one with a function call, and triggers apply only the delta of each INSERT, UPDATE or DELETE to the view within the same transaction, instead of a full REFRESH MATERIALIZED VIEW. It comes from SRA OSS, whose developers have long proposed IVM for core Postgres, and supports PostgreSQL 13 through 19 with inner and outer joins, DISTINCT, and some aggregates. It is the simplest open-source option for always-fresh views in plain Postgres, though write overhead and query restrictions mean it is not a general streaming engine.
Strengths & trade-offs
Strengths
- +Views stay consistent with base tables in the same transaction
- +No external service: just a Postgres extension
- +Supports PostgreSQL 13 to 19
- +Permissive PostgreSQL license
Trade-offs
- −Adds trigger overhead to every write on base tables
- −Limited SQL support (no window functions, limited aggregates and subqueries)
- −Not available on many managed Postgres services
Great for
- +Always-fresh summary tables for dashboards
- +Replacing periodic REFRESH MATERIALIZED VIEW jobs
Think twice for
- −Write-heavy tables with complex views
- −Streaming from Kafka or multiple databases
Capabilities
Native or official first-party support · 5 of 25 tracked
Scale & consistency
- Comfortable data size
- Small · ≤ 100 GB
- Horizontal scaling
- None
- Multi-region
- No
- Transactions
- Full ACID
- Consistency
- Strong
- Schema
- Strict
- Architecture
- Single node, Primary replica, Client server
- Query languages
- SQL
Runs inside a single Postgres instance; maintenance cost is paid on every write to base tables.
Deployment
Ecosystem
Alternatives to pg_ivm
An incremental view engine that keeps complex SQL results fresh inside your existing Postgres or MySQL.
pg-ivm vs epsio →A Postgres-compatible operational data warehouse that keeps SQL views incrementally up to date.
pg-ivm vs materialize →An incremental SQL engine grounded in DBSP theory that updates any SQL query from changes only.
pg-ivm vs feldera →A wire-compatible SQL cache for Postgres and MySQL that keeps cached queries fresh from replication.
pg-ivm vs readyset →The extensible, standards-first open-source relational database.
pg-ivm vs postgresql →In-memory relational database that runs your server logic as WASM modules and streams changes to clients.
pg-ivm vs spacetimedb →pg_ivm FAQ
What is pg_ivm?
pg_ivm is a relational database by SRA OSS (IVM Development Group), first released in 2022. pg_ivm adds Incrementally Maintainable Materialized Views (IMMVs) to PostgreSQL. You create one with a function call, and triggers apply only the delta of each INSERT, UPDATE or DELETE to the view within the same transaction, instead of a full REFRESH MATERIALIZED VIEW. It comes from SRA OSS, whose developers have long proposed IVM for core Postgres, and supports PostgreSQL 13 through 19 with inner and outer joins, DISTINCT, and some aggregates. It is the simplest open-source option for always-fresh views in plain Postgres, though write overhead and query restrictions mean it is not a general streaming engine.
Is pg_ivm open source?
Yes. pg_ivm is open source. License: PostgreSQL License.
Does pg_ivm support ACID transactions?
Yes. pg_ivm supports full ACID transactions, with strong consistency.
Does pg_ivm scale horizontally?
pg_ivm does not scale horizontally; it runs on a single node, so you scale it up rather than out. It is comfortable at small scale (≤ 100 GB).
How can I run pg_ivm?
You can self-host it.
What is pg_ivm best for?
pg_ivm is a strong fit for always-fresh summary tables for dashboards and replacing periodic REFRESH MATERIALIZED VIEW jobs. Think twice for write-heavy tables with complex views.
What are the best alternatives to pg_ivm?
Common alternatives to pg_ivm include Epsio, Materialize, Feldera, Readyset and PostgreSQL. The right choice depends on your workload, scale and hosting; WhichDB's smart match ranks them against your requirements.
How popular is pg_ivm?
pg_ivm ranks #363 of 844 databases on the WhichDB Score (6.4/100), and #116 among relational databases. The score combines public adoption signals such as package downloads, GitHub activity, job posts and developer discussion.