Materialized Views in Supabase for Pre-Calculated Metrics
Pre-computed metrics stay fast while queries stay fresh with strategic refresh schedules.
Materialized views in Supabase let a team pre-compute an expensive query once and serve the result many times. The distinction sounds small until a dashboard, a scheduled report, and an AI agent are all hitting the same aggregate at once, at which point it becomes the difference between a database that holds up and one that doesn't.
What a materialized view actually is, and how it differs from a standard view in Postgres
A view in Postgres is a saved query. It has no data of its own: every time something calls it, Postgres runs the underlying SQL fresh and hands back whatever the live tables say at that moment. A materialized view runs that same query once and writes the result to disk, so calling it again doesn't recompute anything. It just reads what's already there.
Both object types rely on Postgres's rule system to define what the view represents, but the rule plays a different role in each case. For a standard view, the rule is the whole mechanism: it fires on every reference. For a materialized view, the rule only runs at creation and at refresh time, to populate the stored result. After that, a query against a materialized view behaves exactly like a query against a table: the SELECT syntax is identical, and the execution plan reflects a table scan, not a live computation walking through joins and aggregates. The storage is the entire distinction. A standard view has none; a materialized view does.
That stored result is also a snapshot frozen at the moment of the last refresh, not a live mirror of the tables it was built from. The speed gain comes at a cost: a materialized view gives a fast answer that is only as fresh as its last refresh. That trade-off, speed now in exchange for staleness later, determines when a materialized view is the right tool.
When complex analytics queries degrade transactional performance
The queries that benefit most from materialization tend to be the ones with the heaviest machinery behind them: multiple joins across tables, nested CTEs, filters layered on aggregates, window functions computing running totals or rankings. Run directly against live tables, these queries don't just take time to execute. They compete for the same locks that ordinary CRUD operations need, and a long-running read can block writes that have nothing to do with analytics.
The cost compounds with repetition. A query that takes a few seconds once is an annoyance. The same query run by a dashboard owner checking in several times a day, by an automated report firing on a schedule, and by AI agents issuing the identical aggregate at high frequency, becomes a sustained load problem. Consider a storefront owner who wants a month-on-month performance report: the summary has to include only successful orders, exclude anything with a processed refund, and join across at least three tables to get there. Computed on demand, this is both expensive and slow. Computed once every few hours and stored as a materialized view, the same report reads back about as fast as a plain table scan.
The practical test for whether a query deserves this treatment is narrow and specific: does running it on demand visibly degrade API response times, or does it risk blocking other CRUD operations while it runs? If the answer is yes, and the people or systems consuming the result can tolerate an answer that's hours old rather than seconds old, the trade-off favors pre-computation. If neither condition holds, a materialized view adds staleness and maintenance for no real gain.
Creating, refreshing, and dropping a materialized view in Supabase
The SQL is small. CREATE MATERIALIZED VIEW mv_name AS <query>; runs the query once and stores the result under that name; the view holds those results until something explicitly tells it to refresh. DROP MATERIALIZED VIEW mv_name; removes it. Everything that matters operationally happens in between, at refresh time.
REFRESH MATERIALIZED VIEW mv_name; rebuilds the stored result from scratch, but it takes an ACCESS EXCLUSIVE lock while it works. The view is unreadable for the duration of the rebuild. REFRESH MATERIALIZED VIEW CONCURRENTLY mv_name; avoids that lock and lets reads continue during the rebuild, but it comes with a requirement: a unique index on the view, functioning much like a primary key would on an ordinary table. Neither mode is incremental. Postgres recomputes the entire underlying query on every refresh regardless of how much source data actually changed, so the choice between the two isn't about efficiency so much as availability. Blocking refresh is simpler to set up but briefly takes the view offline. Concurrent refresh keeps the view available throughout but demands the extra index and more resources to run.
Supabase's index_advisor extension works directly with materialized views, recommending indexes for queries run against them and identifying which tables and columns a view is obscuring, which makes it a useful tool for building out that concurrent-refresh index correctly. Getting the underlying query right in the first place still matters. A materialized view stores the output of a query; it doesn't fix a badly written one. If the query behind the view is inefficient, that inefficiency is visible every time the view refreshes, just less often than it would be on every call.
Scheduling refreshes with pg_cron and setting a freshness SLA
A materialized view with no refresh schedule is a trap waiting to spring. It looks like a table, it answers like a table, and it grows stale silently, with nothing in the response telling the person reading a dashboard that the numbers in front of them stopped updating three days ago.
pg_cron solves the scheduling half of that problem by running inside PostgreSQL itself. There's no network hop to a separate scheduler, no external authentication to configure, and full access to whatever functions and extensions the database already has, which makes it the natural way to schedule REFRESH MATERIALIZED VIEW calls inside a Supabase project. Timing the refresh matters beyond just picking an interval: running the rebuild during low-traffic windows keeps a resource-intensive refresh from competing with the application's own load.
At real production scale, these jobs stack up. One documented Supabase project serving multiple businesses runs 38 total pg_cron jobs, 12 of which are maintenance jobs covering vacuum, materialized view refresh, log rotation, API key expiry checks, and edge function error rate monitoring. The pattern composes cleanly as more views and more maintenance needs get added.
Scheduling the job is only half the discipline. A freshness SLA needs an owner: someone watching cron.job_run_details, someone who gets alerted when a refresh fails or simply doesn't run. Without that, a failed job produces a stale dashboard with no visible signal that anything went wrong. This is the same idea behind Data Timeliness SLA Compliance, whether expected data deliveries arrive within an agreed window, applied at the scale of a single database object. For a materialized view, the agreed window is the refresh interval, and someone has to actually own it rather than assume the cron job will flag its own failure.
Access control on materialized views: what RLS does and does not cover
Row Level Security does not apply to materialized views in current versions of Postgres. RLS policies attach to tables, and standard views can inherit that protection by setting security_invoker = true, which checks the querying role's permissions and applies the underlying tables' RLS policies at query time. A materialized view has no such mechanism. It bypasses RLS entirely, because the data it serves was computed once, under whatever permissions existed at refresh time, and stored as a flat result with no connection back to row-level policy.
This produces a specific and documented failure: a dashboard grants SELECT on a materialized view to authenticated users, and clients query that view directly through the API, reading aggregated data the interface never intended to expose. The underlying rows might be fully protected by RLS, and the leak happens anyway, because the aggregate itself was never subject to those policies. The fix is to revoke client-side grants on the view, move access behind a backend endpoint, and refresh the data server-side where permissions are enforced in application logic at the database layer.
Where RLS-equivalent behavior is still needed, the workaround has two parts. The materialized view should live in a non-public schema, something like analytics, with all grants revoked from public, anon, and authenticated roles. Access should run through a security-definer function that enforces user-scoped filtering, exposed to client roles in place of the view itself.
Supabase's Advisors already check for this. The lint 0016_materialized_view_in_api flags materialized views exposed in the API schema, and running the Security Advisor from Studio, MCP, the CLI, or the Management API will surface the misconfiguration before it turns into an incident.
Naming, schema placement, and metric definitions that make materialized views a shared source of truth
Most disagreements over startup metrics are definitional. If MRR means booked subscriptions on one dashboard and recognized revenue on another, no amount of dashboard tooling fixes that, because the dashboard isn't where the disagreement lives. The organization simply never agreed on what the word means.
A materialized view can enforce that agreement in a way a shared spreadsheet or a slide never will. Once mrr, cac, and churn_rate exist as named SQL objects in an analytics schema, every consumer of those numbers, human or automated, is querying the same computation rather than reimplementing its own version of the logic. The schema itself carries meaning: putting analytics views in a dedicated schema like analytics, separate from the transactional public schema, makes access control explicit and signals to anyone reading the database what that object is for. Naming the views to match the business vocabulary, mv_mrr_monthly, mv_active_users_daily, makes their purpose legible without anyone having to open the SQL to find out what they compute.
The test for whether this discipline is actually working is simple: if two people on the same team can answer the same KPI question with different logic and get different numbers, the organization doesn't have a metric. It has an argument waiting to happen. A named, versioned view sitting in a governed schema settles that argument by making one definition the only one anyone can query.
How AI agents interact with materialized views
Agents query differently than people do. A person checks a dashboard a handful of times a day. An agent can issue the same aggregate query repeatedly across many tasks in a short window, and if every one of those calls hits live transactional tables, the load compounds in a way human usage patterns never would.
Frequency isn't the only risk. An agent given raw access to production tables, with no semantic layer telling it what a metric means, will construct its own definition of that metric from whatever columns and filters look plausible. It will do this confidently, and the wrong answer it produces will look, in format, exactly like a correct one. A materialized view in a governed analytics schema removes that ambiguity: the computation is fixed before the agent ever touches it, the definition is canonical, and there's no path by which the agent redefines the metric by choosing a different set of filters on the fly.
The design principle that follows from this is minimum surface area. Agents should be handed access to pre-computed, approved datasets, not open query privileges over production tables. MCP is an interface layer rather than a guarantee of correctness: routing data to an agent through MCP doesn't make that data accurate, it just makes it reachable. The accuracy still has to come from the governed materialized view sitting underneath the MCP tool. A well-built MCP tool for analytics reflects that, exposing operations like retrieving a metric's value, checking a view's freshness, or comparing a current value against a baseline, rather than handing the agent raw SQL execution against live tables.
The limits of materialized views in Supabase
The pattern has real limits, and they become relevant as data volume and team size grow. Refreshes are never incremental in Postgres: the entire underlying query reruns on every refresh, so the cost scales with how much data exists, not with how much of it actually changed since the last run. A view over a modest table refreshes cheaply; the same view over billions of rows does not, regardless of how small the day's changes were.
Supabase Realtime doesn't support subscriptions on materialized views, so any team that needs live push updates to a frontend has to fall back on a regular table updated by a trigger or a scheduled job, which brings back the write-side complexity a materialized view was meant to sidestep. RLS, as covered above, is absent by default and requires the security-definer and schema-isolation workarounds to approximate what a standard view gets for free.
None of these limits are reasons to avoid the pattern. They're reasons to know where it ends. Exactly where that line falls depends on query volume, team size, how fresh the data needs to be, and whether refresh cost is measurably dragging on production performance, and no single number applies to every team. For most Supabase founders and small teams, materialized views combined with pg_cron scheduling and a carefully governed schema are enough infrastructure to produce reliable, board-ready metrics without hiring a data team or standing up a separate warehouse. The honest moment to reach for something more comes when the maintenance cost of keeping that pattern coherent, the refreshes, the lint checks, the schema discipline, starts to outweigh the cost of adopting a dedicated analytics layer instead.
