To avoid recalculating an entire PostgreSQL materialized view after every change, consider pg_ivm, an extension that incrementally maintains supported views with triggers as base-table rows change. That can keep derived data current without a full refresh, but shifts work into write transactions. It is not a guarantee of real-time latency or better performance for every workload: query eligibility, write overhead, locking, and tenant authorization all need to be evaluated against your application.
What incremental view maintenance changes
A PostgreSQL materialized view stores the result of a query. With the ordinary REFRESH MATERIALIZED VIEW command, PostgreSQL reruns the defining query and replaces the stored contents. The PostgreSQL 17 documentation states: “REFRESH MATERIALIZED VIEW completely replaces the contents of a materialized view.”
REFRESH MATERIALIZED VIEW CONCURRENTLY addresses reader availability during a refresh; it does not turn the refresh into delta maintenance. It still recomputes the view, requires an eligible unique index, and only one refresh at a time can run for a given materialized view.
Incremental view maintenance (IVM) takes another approach: update the stored result to reflect changes to its inputs rather than rerunning the whole defining query. The PostgreSQL-specific option covered here is pg_ivm. It creates an incrementally maintainable materialized view (IMMV) and uses triggers to maintain it when base tables change. Since that maintenance happens as part of the modifying transaction, fresher analytics can mean slower writes.
#1 Best Overall
When pg_ivm is a reasonable fit
Start with the exact analytics query, not with the assumption that any materialized view can be made incremental. The extension supports a subset of SQL, including common joins, DISTINCT, built-in aggregates such as count, sum, avg, min, and max, plus some subquery and CTE forms subject to restrictions. Support is not equivalent to arbitrary SQL support; check the current project README for the precise forms supported by the extension release you plan to deploy.
IVM is most promising to investigate when the query is eligible and changes to the base data are small relative to the maintained result. It is less attractive if trigger work would put unacceptable load or latency on a busy write path. Neither “real-time” freshness nor multi-tenant scale is established by the extension’s design documentation alone: those are service goals to test with your own data and transaction patterns.
Rank #2
Compare the maintenance choices
| Approach | Freshness and work placement | Potential fit | Costs and checks |
|---|---|---|---|
| Ordinary materialized view with scheduled refresh | The refresh reruns the defining query and replaces the stored contents; the schedule determines how stale the result can become. | Staleness is acceptable and keeping base-table writes simple matters. | Each refresh fully recomputes the result. CONCURRENTLY requires an eligible unique index and does not permit overlapping refreshes on the same view. |
pg_ivm IMMV |
Triggers maintain the derived result in the transaction that changes a base table. | The view definition is supported and the changed data is small enough that incremental work may suit the workload. | Expect additional write work and possible locking. Check SQL restrictions, indexes, aggregate edge cases, isolation behavior, extension-version compatibility, and tenant visibility. |
| Custom rollups or application-maintained summaries | Not established by the PostgreSQL and pg_ivm sources discussed here. |
May be explored if built-in refresh and pg_ivm do not meet requirements. |
Requires its own design and evidence for correctness, retries, idempotence, and tenant isolation; it is not a validated recommendation here. |
Compare options using the freshness and consistency your product actually requires, the fraction and shape of changed data, SQL compatibility, write latency and throughput, lock contention and transaction isolation, index and storage overhead, tenant authorization, recovery procedures, and support for your PostgreSQL and extension versions.
Evaluate a pg_ivm view before adopting it
- Check the real query definition. Compare every construct in the analytics query with the current
pg_ivmproject README and the release you will install. Do not assume a query is eligible because it works as a normal materialized view. - Plan indexes for maintenance. Incremental updates need suitable indexes to find affected derived rows. The extension documentation describes automatic unique-index creation only where possible, so confirm the index behavior for your view rather than assuming it will be supplied.
- Test write-side performance. Measure the statements that modify base tables, including realistic bursts and concurrency, as well as analytics reads. Trigger maintenance can make base-table updates slower even when it avoids a later full refresh.
- Exercise aggregate edge cases. Deleting the row that supplies a group’s current minimum or maximum can require recalculation from base tables for the affected group. For
sumandavg, the project README warns againstrealanddouble precisionbecause of limited precision and recommendsnumeric. - Test application transaction patterns. The project documentation describes locking on the IMMV under
READ COMMITTED, and errors when maintenance cannot safely account for concurrent changes underREPEATABLE READorSERIALIZABLE. Verify behavior using the isolation levels and concurrent writers your application actually uses. - Verify tenant visibility and authorization. The extension documents that base-table rows hidden from the materialized-view owner by row-level security (RLS) are excluded from the IMMV. RLS policy changes made after the IMMV is created do not retroactively update its contents; refresh or recreate the IMMV as appropriate. Treat this as a correctness constraint, not proof that one shared view is safe for every tenant authorization model.
- Include recovery and replication in the plan. The project says its internal metadata is excluded from
pg_dumpand documents usingpg_ivm_dump_metadatabefore a dump or upgrade, then restoring metadata afterward. Validate the procedure for your installed version. Its README also says logical replication is not supported for maintaining IMMVs at subscribers.
Interpret example timings cautiously
The pg_ivm project README gives one illustrative pgbench example: an update took 9.052 ms without an IMMV and 15.448 ms with one, while a full refresh of the ordinary view took 20,575.721 ms (about 20.576 seconds). These are timings from that particular project example, not independent or generally applicable performance statistics. The retrieved README excerpt does not state a publication year or enough benchmark methodology to predict behavior for another workload.
The Tool Desk
Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →For a multi-tenant deployment, benchmark with representative tenant sizes, skew, write rates, query shapes, concurrency, and transaction isolation. Measure both how quickly the derived result is available and the impact of its maintenance on the writes that keep it current. A single shared view versus separate tenant views is an architectural choice the available documentation does not settle universally; decide from your isolation requirements and measured workload rather than assuming one layout will scale best.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
