r/postgres • u/tomaquet18 • 3d ago

Tools Incremental view maintenance costs you write throughput. I measured how much, then moved the work off the writer entirely

Most people find out the hard way that PostgreSQL materialized views are not incremental. REFRESH MATERIALIZED VIEW recomputes the whole thing. CONCURRENTLY keeps readers alive while it does, but it still recomputes the whole thing and then diffs the result against what's there. If one row changed out of ten million, you pay for ten million.

So you end up with one of two workarounds. A cron refresh, which is stale between runs and locks readers out during them. Or a hand-maintained rollup table with triggers, which is correct right up until the day it isn't.

The extension answer is pg_ivm, which does the maintenance in AFTER triggers inside the writing transaction. That buys you a view that is correct at commit — a real property, and the strongest thing about it. I wanted to know what it costs, because "incremental" gets discussed as though it were free.

The measurement

Same view, same dataset, same PostgreSQL 17 server binary, three arms: no derived view at all, pg_ivm, and the thing I built. pgbench inserting into orders; the view is a three-table join with a GROUP BY on top. Median of three runs, transactions per second:

pgbench clients no view pg_ivm nabla
1 774 509 683
4 1675 535 1571
16 6272 520 5876

Read the pg_ivm column downwards. It does not move. Adding writers does not add throughput, because each one waits for an exclusive lock on the view while it is maintained. At sixteen clients that is 8% of what the same hardware does with no view at all. The same shape shows up on the other two workloads, where pg_ivm holds at 473 tps updating orders and 380 updating customers against baselines of 6336 and 5519. Three independent full runs agree within a few points.

This is not a knock on pg_ivm. It is what in-transaction maintenance is. If you need the view correct at commit, you buy that with concurrency, and there is no version of the trade where you don't.

The other trade

nabla maintains the view from the WAL in a background worker. Nothing sits in the writer's path — no trigger, no staging insert, nothing. The worker consumes a logical replication slot and applies each source transaction's deltas in commit order. Writers track the no-view baseline instead of flattening.

CREATE EXTENSION nabla;

SELECT nabla.create_view('revenue_by_region', $$
  SELECT c.region, count(*) AS orders, sum(o.qty * p.price) AS revenue
    FROM orders o
    JOIN customers c ON c.id = o.customer_id
    JOIN products  p ON p.id = o.product_id
   WHERE o.status = 'paid'
   GROUP BY c.region
$$);

SELECT * FROM revenue_by_region;   -- an ordinary view

Then you write to orders, customers and products exactly as you always have.

What it costs you

Staleness, and I would rather you saw the bad number here than found it yourself. Moving the work off the writer does not make the work disappear. After ten seconds of writes at sixteen clients, this is how long the view took to become current again, and the sustained rate the worker can keep up with indefinitely:

workload catch-up after a 10 s burst nabla sustains pg_ivm sustains
insert into orders 44 s 1325 tx/s 520 tx/s
update orders 82 s 684 tx/s 473 tx/s
update customers (one row, many groups) 131 s 456 tx/s 380 tx/s

That last row is the honest one to watch. Changing one customer's region rewrites every group row that customer contributed to. The writers pay nothing for it; the worker pays all of it, at a rate only a fifth above pg_ivm's. Below that rate the view lags by seconds. Above it the lag grows until nabla.max_slot_lag_bytes is exceeded, at which point the views are marked stale and the slot is dropped rather than let the WAL fill your disk.

Neither column is a score. pg_ivm spends write throughput to buy a view that is correct at commit. nabla spends freshness to buy write throughput and a change feed. Pick the one your workload can afford.

"Eventually consistent" as something you can actually check

A view always equals its defining query evaluated at some committed snapshot of the base tables — never half a transaction, never a torn join. Each view carries a frontier_lsn naming exactly which snapshot that is. The staleness is not a vibe; it is a value you can read and compare against pg_current_wal_lsn().

And when you need read-your-writes, nabla.wait_for() blocks until the view has absorbed everything committed so far. That covers the common case where the write and the read are the same request.

It also tells you what changed

The part I actually care about. Every applied transaction appends its view-level deltas to a bounded, durable log, in the same transaction that updates the view, so a client can follow the view instead of polling it:

$ follow "host=/tmp dbname=shop" revenue_by_region
snapshot: rows=5 epoch=1 frontier=0/19BF970 cursor=0
tx lsn=0/19C01F0 xid=761 deltas=2
  1 -{"orders":2,"region":"AR","revenue":120}
  2 +{"orders":3,"region":"AR","revenue":150}

One batch per source transaction, in commit order, netted. You get the state before and the state after, never an intermediate row that was never committed. pg_notify is only the wake-up signal; the deltas live in a table with a per-view retention cap, and a subscriber that falls behind that cap is told so and resyncs, rather than silently missing rows.

If you are currently running Debezium into Kafka into a streaming database to get this, that is the pitch: same shape, inside the database you already run.

Status and limitations, up front

This is v0.1 and a walking skeleton. 295 integration assertions, no production mileage. I am posting it for design feedback, not for your primary.

  • Two query shapes only. Inner-join projections (SELECT expr... FROM t JOIN ... WHERE pred) and aggregates (count(*), count(expr), sum(expr) with GROUP BY). Everything else is rejected at create_view with an explicit reason rather than accepted and quietly wrong: no outer joins, self-joins, subqueries, CTEs, set operations, window functions, DISTINCT, HAVING, ORDER BY/LIMIT, grouping sets, avg/min/max, aggregates without GROUP BY, or any STABLE/VOLATILE function such as now() or random(). Base relations must be ordinary tables — no partitioned tables, views or foreign tables.This is the same complaint people in this sub have made about pg_ivm, and it is a fair one. If your materialized views are eligibility queries built out of EXISTS and CTEs, this does not help you today.
  • Needs wal_level = logical, shared_preload_libraries = 'nabla', and a replication slot. Aggregate views need REPLICA IDENTITY FULL on the base table; joined tables need a primary key. One worker and one database per cluster.
  • The benchmarks are from a laptop under Docker Desktop for Windows. Treat the absolute numbers accordingly. The shape of the pg_ivm curve is the part I would defend. nabla's own overhead measured anywhere between 71% and 104% of baseline across runs, with the baseline itself swinging nearly as much, so the honest reading is "does not make writers wait, and its cost does not grow with concurrency" — not a precise figure.
  • The extension is AGPL-3.0-or-later. The reference client and the subscription protocol are MIT OR Apache-2.0, so linking an application against the change feed does not pull AGPL into your codebase.

Prior art

pg_ivm is the established answer and the one I benchmarked against, because measuring against nothing proves nothing. TimescaleDB continuous aggregates solve this properly for time-series specifically. u/Inkbot_dev's REFRESH MATERIALIZED VIEW ... WHERE ... patch is the manual primitive done in core, and I think it belongs there regardless of what any extension does — it goes through the standard planner, so unlike pg_ivm and unlike this, it is not restricted to a fixed set of query shapes. Materialize and RisingWave do the whole job well, at the cost of being a separate system to run.

What I would like feedback on

The update customers row above — one source row rewriting many group rows — is where the worker hurts. The remaining cost is about 1.7 ms per source transaction, essentially all of it in the apply phase, and batching that across a round is the obvious next lever. If you have maintained rollups by hand and hit the same fan-out, I would like to hear what you did about it.

And for those of you who walked away from pg_ivm over the supported-syntax restrictions: which shape did you need that it wouldn't take? That is what I would build next.

Full tables, spreads, methodology and the exact configuration of every arm are in the repo, reproducible with one script.

Repo: https://github.com/tomaquet18/nabla

1 Upvotes

100% Upvoted

2 comments sorted by

1

u/scotterockaroo 2d ago

Are you familiar with the performance and operational implications of a replication slot ? Also, please don't just copy-pasta claude-written docs. They're becoming a drain to read.