A materialised view is a snapshot. The moment the underlying tables change, that snapshot is out of date. PostgreSQL has never had a good answer for keeping it current — and after years of watching people work around that gap, we decided to close it properly. This is the story of how that turned into two open-source extensions: pg_auto_mv and pg_relay
There are two common ways people keep a materialised view current, and we've never been satisfied with either.
Timer-based refresh — a cron job or scheduler calls REFRESH MATERIALIZED VIEW on a fixed interval. The interval is always a guess. Too short, and you're refreshing a view nobody's looked at, burning CPU and I/O for nothing. Too long, and someone makes a business decision on stale numbers. If the job silently stops running — and scheduled jobs do silently stop running — nobody notices until someone asks why the dashboard looks wrong.
Trigger-based refresh — a trigger on the source table calls REFRESH MATERIALIZED VIEW directly, inside the same transaction that is causing the data to change. This sounds like the obvious fix, and it's the one that catches people out worst. PostgreSQL is happy to run the refresh right there in your transaction — and because it's a fully transactional operation, a rollback correctly undoes it too, so there's no correctness problem. The problem is locking. Without CONCURRENTLY, the refresh takes an ACCESS EXCLUSIVE lock on the view, blocking every reader until your transaction ends. With CONCURRENTLY, readers are fine, but your own transaction still can't commit until the full rebuild finishes. Either way, a fast insert turns into a slow, blocking operation the moment the view is even moderately expensive to rebuild — and the person who discovers this is usually a developer looking at a production incident, not the person who wrote the trigger.
We wanted a third option: refresh the view when the underlying data actually changes, without making the write wait for it. That's a simple sentence and a genuinely hard engineering problem, and it's why this took a properly considered build rather than a weekend trigger hack.
Getting a refresh out of the calling transaction means the work has to happen somewhere else — a background process that notices the change and does the refresh later. That's not a new idea. PostgreSQL has LISTEN/NOTIFY, pg_cron, and pg_background. We've used all three. Each has a real limitation that ruled it out as the foundation for a general-purpose, production-grade tool:
None of that was good enough to build a real product on. So before we could write pg_auto_mv, we had to write pg_relay: a durable, auditable, general-purpose event queue and scheduler for PostgreSQL that works identically everywhere — on-premise, and on every major managed cloud PostgreSQL service. That work started in 2025, and it's turned into something considerably more useful on its own than we originally set out to build.
pg_relay has two parts: a database-side schema (the pgrelay schema — tables, functions, security) and a small standalone Go binary called the Processor that runs alongside your database and does the work.
The event flow is deliberately simple. Your code — a trigger, a function, or an application — calls pgrelay.notify('channel_name', payload). That validates the channel exists and is active, then inserts a row into pgrelay.queue inside your own transaction. If your transaction rolls back, the event never existed. If it commits, the event is guaranteed to be there.
The Processor polls, it doesn't listen. Every second, it runs one cheap check — a single EXISTS query against a partial index, taking around 40 microseconds when the queue is empty. Yes, that is quick, there are 1M microseconds per second. If there's work, it claims a batch with FOR UPDATE SKIP LOCKED, dispatches each event, and — critically — doesn't wait for the next tick if there's more to do. It drains the queue as fast as the database can process it, then goes back to waiting. A burst of a thousand events is processed as fast as your hardware allows, not in batches of one per second. We provide a performance test script for pg_relay so that you can evaluate your own hardware. On a Macbook Pro laptop, expect to see 1,700+ dispatched events per second, on client server hardware, many multiples more should be possible -- and this is just with a single worker.
We tried LISTEN/NOTIFY first, internally, during development. We changed tact. A lost notification during a reconnect meant a delayed event with no indication anything was wrong — exactly the failure mode we were trying to eliminate. Polling every second costs nothing measurable and never silently drops an event. Whilst NOTIFY will rarely ever fail, in theory it can. If the notify_buffers shared memory is exceeded, the NOTIFY may not function. If there is no active LISTENER, the notification will not be captured. Both are minor chances but when they occur, it matters.
Every event is audited. pgrelay.log gets one row per processed event — outcome, timing, and a link back to the source queue row. The payload itself is never logged, so you can ship these logs to any aggregation platform without worrying about leaking event data. Retries are automatic but selective: only SQLSTATE class 40 errors (deadlocks, serialisation failures — the transient ones) get retried, on a 3/5/10-second backoff. Anything else — a constraint violation, a permissions error, a typo in your action SQL — fails immediately and stays failed, because retrying a permanent error just wastes another second finding out it's still broken.
Security is locked down by default. The core tables have all privileges revoked from PUBLIC. Access goes through a small set of SECURITY DEFINER helper functions, each with search_path pinned to prevent function-hijacking attacks. The action SQL you register runs as the pgrelay role under SECURITY INVOKER — it can only touch what that role has been explicitly granted, which means pg_relay itself cannot be used to escalate privileges, by design.
High availability needs no leader election. Run one Processor, or run several — on the primary and on every standby. There's no primary/leader concept; FOR UPDATE SKIP LOCKED handles coordination, so two Processors racing for the same event produce one winner and one clean skip. On a standby, the Processor connects, polls, and finds nothing — because triggers don't fire during WAL replay, so there's never any local work until that node is promoted. The moment it is promoted, the same Processor starts working on its very next tick. No restart, no reconfiguration. It also runs correctly in multi-master setups (BDR, Spock, pgactive) provided you exclude pgrelay.queue from replication — each node needs to process its own local events, not replay someone else's.
Once we'd built a durable, audited, second-resolution event processor and scheduler that runs practically anywhere PostgreSQL runs, it became obvious it was worth far more than one use case.
It can replace pg_cron. Not "is compatible with" — it can replace it. Register a channel, have its action fire itself again with a future timestamp, and you have a recurring job — with second-level precision instead of pg_cron's one-minute floor, and a full audit trail of every single run, success or failure, that pg_cron alone doesn't give you. We use this pattern internally for pg_auto_mv's own housekeeping — its nightly catalog sync runs exactly this way.
It covers pg_background's core use case. Get work out of your calling transaction so a slow or non-critical operation doesn't hold up a fast one — that's what pg_background is for, and pg_relay's durable queue does it, with the audit trail pg_background never had.
It runs on managed cloud with none of the friction pg_background carries there. pg_background is a compiled C extension using dynamic background workers — genuinely unavailable on AWS RDS, Aurora, Azure Flexible Server, and Google Cloud SQL, no exceptions found. pg_cron is actually available on all of those platforms, but only after you set shared_preload_libraries and restart the instance — on some platforms that also means a support ticket or an allow-list change before you can even do that. pg_relay's database-side schema is pure SQL. There's nothing to preload and nothing to place on a server filesystem, so the exact same functionality — the queue, the functions, the audit log, the security model — deploys with CREATE EXTENSION on a self-managed box, or by running the SQL file directly against any of these managed services, with no restart and no preload-library change required. Same schema, same functions, same behaviour, either way. If you've read our piece on why PostgreSQL on Azure Flexible Server can cut your database costs, this is the other half of that story — you get the automation that used to require a self-managed box, on the high-performance managed platform, with nothing lost. We will test it on Microsoft Azure's HorizonDB at a later time, but there appears to be no roadblocks in running pg_relay there too, however with it being a public preview only, we would prefer to confirm properly once it has achieved Generally Available status.
This is also why pg_relay is going to be the foundation for more than pg_auto_mv. We're building a family of extensions on top of it, and pg_auto_mv is the first one out the door.
With pg_relay providing the plumbing, pg_auto_mv is the layer DBAs and developers actually interact with — and this is where materialised views stop being a set-and-forget gamble and start being something you can genuinely rely on for performance.
It doesn't touch your view's definition. Pg_auto_mv doesn't create, alter, or drop materialised views — you manage the DDL exactly as you always have. All it manages is when the refresh happens.
Three timing parameters give you real control, all in seconds:
A busy sales.orders table taking hundreds of writes a second doesn't need a refresh on every insert. Set refresh_lag = 10, max_wait = 60, cooldown = 30, and 500 updates arriving in a two-second burst produce exactly one refresh, ten seconds after the burst ends — with a hard ceiling so a continuously busy table can never starve the view entirely.
Watch tables are selective by design. You tell pg_auto_mv exactly which source tables — and which operations on them — should trigger a refresh. A daily revenue view joining sales orders, order lines, and a customer lookup table almost certainly shouldn't refresh every time a customer's address changes. Register only the tables and events that actually matter, and every other write on every other table in the join is free.
SELECT pgauto_mv.register_mv(
p_mv_name => 'daily_revenue',
p_mv_schema => 'reporting',
p_watch_tables => ARRAY[pgauto_mv.mv_watch('sales.orders', 'UPDATE')],
p_refresh_lag => 10.0,
p_max_wait => 20.0,
p_cooldown => 30.0
);
Watch tables are also optional. A view fed by a nightly bulk load doesn't want a trigger firing 500,000 times during the load. Register it with no watch tables, driven purely by a schedule, a chain, or an explicit refresh_materialised_view_now() call once the load finishes.
Chaining and scheduling cover everything else. Layer views — daily revenue feeding weekly, weekly feeding month-to-date — and chain them so a refresh cascades automatically through every downstream view. Attach a shared, named schedule for the views that need a guaranteed refresh regardless of write activity, as a safety net alongside trigger-driven refresh, or as the only trigger for views that don't need real-time freshness at all.
Nothing about this is a black box. Every trigger firing, every refresh attempt — successful, failed, or deliberately skipped for cooldown — is logged with full detail: duration, source, and error message where relevant. get_mv_problems() proactively flags missing unique indexes and stale registrations. sync_mv() reconciles the registry against the actual database catalog — renamed views, dropped views, lost indexes — automatically, on a schedule, using pg_relay underneath it. clean_mv() rebuilds trigger infrastructure if anything drifts. If a view's watch-table trigger has gone missing or a function's been renamed by someone who didn't know better, you'll find out from a query, not from a support ticket.
Standby Replicas and Multi-master work too, with the same discipline pg_relay requires: materialised views aren't replicated logically, so every node maintains and refreshes its own copy independently, using node-local queues and a fire_on_replica flag that makes watch-table triggers respond to writes arriving via logical replication as well as local ones.
The point of all this: a materialised view in PostgreSQL has always had genuine performance upside — pre-computed joins and aggregates instead of running the same expensive query on every request. What's held people back from using them more aggressively isn't the concept, it's the operational risk of staleness and the blast radius of getting the refresh strategy wrong. With pg_auto_mv, that risk is gone. Materialised views become something you reach for by default when a query is expensive and the data doesn't need to be up to the millisecond — a first-class part of the schema, not a special-case feature you handle with a fragile cron job and hope nobody notices when it breaks.
A single Processor with one worker is fine for most deployments — most channels fire far less than once a second, and the poll-and-drain loop keeps up without breaking a sweat. Larger deployments need more headroom, and pg_relay gives you two independent dials for that, not one.
Workers add parallelism within a single Processor. State --workers N (1–9) and the Processor opens N database connections instead of one, each running its own worker. When the poll finds a batch of pending events, it fans them out across all N workers concurrently rather than processing them sequentially. This matters most when your registered actions aren't trivial — a call to an external HTTP service, a slower query, anything that takes more than a few milliseconds — because that's where a single worker genuinely becomes the bottleneck. For fast, sub-millisecond SQL actions, the ceiling is usually network round-trip time rather than CPU, and there's not much benefit pushing past 4–5 workers on one instance — the overhead of managing more connections starts to outweigh what you gain.
Multiple Processors add both throughput and redundancy. Nothing stops you running several Processor binaries against the same database, each with its own --workers N setting. There's no leader election and no primary Processor — every instance is identical, and FOR UPDATE SKIP LOCKED is what keeps them from double-processing the same event. Two Processors racing for the same row produce one winner and one clean skip, every time. Each claims a session-level advisory-lock instance slot (1–64) purely so its logs are distinguishable — that's the only coordination overhead involved.
In our own testing on 8-vCPU hardware, two Processor instances running two workers each gave the best throughput — better than one instance running four workers on the same box. That's not a universal number, but it's a useful starting point: vertical scaling (more workers per Processor) tends to beat horizontal scaling (multiple Processors) but at least 2 Processors gives you redundancy for when there are re-connections or other issues.
This is also how you get active/active high availability for free. Run one Processor per application server, or one per node in a standby/multi-master topology, each with --workers 2 or more. If one Processor stops — a host reboot, a deploy, a crash — the others keep draining the queue without any operator intervention, and the queue itself doesn't care which Processor eventually claims a given row. There's a built-in performance test script (pg_relay_performance_test.sql) that measures your actual breakeven throughput under load, so rather than guessing at worker or instance counts, you can benchmark your own configuration and scale from a measured baseline instead of a rule of thumb.
Both extensions are released under the MIT licence — free to use, modify, and deploy without restriction.
PostgreSQL 15 through 18 are tested. PostgreSQL 19 will be verified as soon as it ships later this year.
We build on Rocky Linux 9 and test across Apple Silicon Mac, Ubuntu 22.04 and 24.04 LTS, Rocky Linux 8 and 9, and Debian 12. Both extensions are pure SQL/PL/pgSQL with no OS-specific code, so there's no real reason to expect different behaviour on a platform outside that list — pg_relay's Processor binary itself cross-compiles to Linux (x86_64/arm64), macOS (Intel/Apple Silicon), and Windows. We may look at testing on Windows later this year.
Installation is documented for both self-managed and managed cloud deployments — AWS RDS, AWS Aurora, Azure Database for PostgreSQL Flexible Server, and Google Cloud SQL all have dedicated setup guides, alongside the standard CREATE EXTENSION path for on-premise and self-managed instances.
We plan to mirror to GitHub this year. There are user guides and DBA guides in the repository, there are ample details to support you using both for a POC as well as Production use.
PostgreSQL is a community project, and we consider ourselves part of that community, not just consumers of it. Pg_auto_mv and pg_relay are our contribution back — released under MIT, for anyone to use.
Our own background is heavily Oracle. Oracle has long had mature, native answers to both problems these extensions solve: DBMS_SCHEDULER for proper job scheduling with logging built in, and query rewrite plus refresh groups for materialised views that stay current without a DBA gambling on refresh timing. PostgreSQL never had a real equivalent to either, and we wanted it to. So we built one.
That's the same depth behind our piece on migrating Oracle's UTL_MAIL to PostgreSQL, our take on why Microsoft is putting serious engineering weight behind PostgreSQL, and our numbers on what PostgreSQL actually costs against Azure SQL. PostgreSQL is not a side interest for us — it's core to our project and managed service capability, and pg_relay and pg_auto_mv are what that capability looks like when we point it at a problem worth solving properly instead of working around. There is a lot more to build that uses pg_ relay, we will reveal a roadmap at a later time.
If you're carrying materialised views on a fragile cron job, or you've been putting off a PostgreSQL migration because you weren't confident you could replicate what a scheduled job or trigger did for you on Oracle, talk to us.
