Materialized Views vs Pre-Calculated Metrics in Supabase

Three patterns get used interchangeably in conversations about Supabase analytics, and that habit is the root of most bad architectural choices made in this space. A standard view is a saved query with no storage of its own: Postgres re-runs the underlying SQL against live tables every time someone reads from it. Supabase's own documentation on views states it directly: "When you run a view, Postgres executes the underlying query and returns its results. That means a view is always current, because there is nothing cached to fall out of date, but it also means every read costs what the raw query costs, with no savings for having asked the same question yesterday.

A materialized view is a different object. It stores the result of a query to disk rather than re-running the query on each read, so Postgres computes the answer once and holds it until someone explicitly refreshes it. Supabase's blog post on Postgres views frames this as the same SQL surface as a standard view, just with a physical storage layer bolted on. Because that storage is a real physical relation, it can be indexed the way any table can, and that indexing is where its speed advantage over a standard view comes from. The cost is staleness: a materialized view is accurate as of its last refresh, not as of the moment someone queries it.

A pre-calculated metrics table is an ordinary table, created with CREATE TABLE, whose rows get written by application code, database triggers, or scheduled jobs that someone on the team builds and owns. Unlike a materialized view, it takes foreign keys and row-level security natively, with no extra scaffolding required to make either work. The developer owns the entire pipeline that fills it: the database enforces nothing about when or how often the table gets updated. An append-only design, something like a daily_metrics table that gains a new row each day rather than overwriting the last one, turns that row history into an audit trail in its own right.

All three patterns exist to avoid repeating expensive computation, but they each put a different party in charge of deciding how fresh the answer is. A standard view hands that decision to the database, which always recomputes. A materialized view hands it to whoever schedules the refresh job. A metrics table hands it to the application code that writes the rows. That difference in who owns the update contract is the thread the rest of this piece pulls on.

Query pressure from analytics on Postgres transactional tables at scale

Postgres is built and tuned as a row-oriented database for transactional work: inserts, updates, deletes, the steady traffic of an application doing its job. Heavy aggregation queries run against those same live tables compete with that traffic for I/O, and at the wrong moment they can block the very operations the database exists to serve. A multi-table join with grouped aggregates and a handful of CTEs has to scan large stretches of data every single time it runs; running it against raw transactional tables means paying that scan cost in full, repeatedly, with no memory of having paid it an hour before.

A DEV Community guide on this exact problem describes what happens next in direct terms: such queries "can degrade overall database performance and block CRUD operations through locking." A dashboard that aggregates order history or user activity might run acceptably once. Run it a dozen times a day across a handful of team members, automated reports, and now AI agents calling it for context, and the compute cost multiplies in direct proportion to the traffic, until the query that used to return in under a second starts timing out during a board meeting. Supabase's own documentation points to internal dashboards and analytics as the textbook case for pre-computation, precisely because those use cases can tolerate data that's a little behind the live state in exchange for a query that doesn't compete with production writes.

This is not a problem reserved for databases with millions of rows. It appears as soon as the complexity of a query outpaces the simplicity of the data underneath it, and this can happen in a database with a few hundred thousand rows just as easily as one with a billion. Pre-computation itself is close to inevitable once that pressure builds, so the choice that remains is which pattern to pre-compute with, and what owning it actually costs. A governed metrics layer, whether built from materialized views or from pre-calculated tables, moves the analytical load off the transactional database entirely, so that dashboards, reports, and the AI agents increasingly pulling data for context stop competing with production CRUD operations for the same I/O.

The freshness-speed-ownership triangle: what you are choosing between

Every choice among these three patterns is really a choice about who owns the update contract and what freshness guarantee that contract can honestly make. Three dimensions govern the decision: how current the data stays, how fast reads return, and who carries the maintenance burden. Optimizing hard on one dimension tends to cost something on another. That is why no single pattern wins across the board.

On freshness, a standard view is always current, because there is no stored state that can go stale. A materialized view carries bounded staleness: its data is accurate as of the last REFRESH MATERIALIZED VIEW call, and the gap between that refresh and the moment of the query is the staleness window a team has to accept. A pre-calculated metrics table's freshness depends entirely on the write logic behind it. It can run near real time if triggers populate it on every change, or it can lag a full day if a nightly job handles the writes. That freshness contract is set by whoever builds the pipeline, not by the database itself.

On speed, a standard view is exactly as slow as the query underneath it, every single time it runs. Both materialized views and metrics tables support indexing and return fast, predictable results. The speed advantage of a materialized view can be dramatic: a 230x improvement over querying raw tables directly has been documented. That advantage only holds, though, when the refresh strategy is sound. A deep-dive on Postgres materialized views makes the point directly: a materialized view can end up slower than the live query it was meant to replace if it's refreshed too often, because frequent refreshes recreate the exact same load the materialized view was built to avoid.

On ownership, a standard view carries close to no maintenance burden at all, since its definition is declarative and Postgres handles execution on its own. A materialized view carries a moderate burden: someone has to schedule and manage the refresh, and a refresh run either locks the view against reads (in the default, non-concurrent mode) or requires a unique index to run concurrently. A metrics table carries the heaviest burden of the three, because any schema change touching the source tables can require rewriting the logic that populates it.

| Dimension | Standard view | Materialized view | Metrics table | |---|---|---|---| | Freshness | Always current | Accurate as of last refresh | Set by the write logic (triggers, jobs) | | Speed | Same cost as underlying query | Fast, indexable | Fast, indexable | | Ownership burden | Minimal | Moderate (refresh scheduling, locking) | Highest (write pipeline, schema changes) |

The ownership axis is where the real architectural decision ends up living. A standard view offloads all freshness logic to the database. A materialized view requires careful refresh orchestration. A pre-calculated metrics table hands full control to the application layer. In practice, many Supabase teams land on pre-calculating metrics into governed, indexed tables and serving them through a shared data layer, an approach Dreambase builds around, which sidesteps the refresh-versus-locking trade-off entirely and lets both human dashboards and AI agents read from one trusted, already-modeled dataset instead of each maintaining a separate view or competing for the same database resources. The practical heuristic that falls out of all three axes: reach for a standard view when the underlying query is cheap or must reflect the live state, reach for a materialized view when the query is expensive and the audience can tolerate the refresh interval's staleness, and reach for a metrics table when rows need direct writes, a visible audit history, or row-level security without extra workarounds.

RLS across views, materialized views, and metrics tables in Supabase

Row-level security behaves differently across these three patterns in Postgres, and getting that difference wrong in a multi-tenant Supabase project is a security failure, not merely an inconvenience. A standard view, when defined with security_invoker = true, runs under the querying role's own permissions, so the RLS policies on the underlying tables fire exactly as they would on a direct query. Supabase's documentation describes the security_invoker setting as the mechanism that makes this happen: without it, a view runs as its owner, and a view owned by a privileged role can expose data to anyone able to read from it.

Materialized views break this pattern. Postgres has no mechanism for applying RLS policies to materialized views, a limitation baked into the database itself. A database linting tool flags this directly: it warns when a materialized view is exposed to the API, precisely because that exposure bypasses row-level security by default. The workaround involves hiding the materialized view inside a private schema, granting access to it only through a specific Postgres role, and then exposing it through a security-definer function or a security-invoker view that applies the row filtering by hand. That's extra scaffolding required for every materialized view that needs per-user access control, and it has to be built and maintained separately from the view itself.

A pre-calculated metrics table avoids this problem by construction. Because it's a regular table, RLS policies apply to it exactly as they would to any other table in the database, with no workaround required. For multi-tenant Supabase products, where different customers or organizations must never see each other's rows, this gap in materialized views is often enough on its own to tip the decision toward a metrics table, even accepting the cost of owning the write logic that populates it.

AI agents and the stakes of the pattern choice

An AI agent querying a data layer built only with human analysts in mind will find the weakest point of whichever pattern was chosen, and find it faster and more often than any human operator would. Agents don't query with the same discipline a trained analyst brings to a dashboard. An agent asked for a metric may reach for a raw table tool instead of the approved materialized view or metrics table, and return a number that doesn't match the one the governed layer would have produced, a failure mode that amounts to semantic bypass. A pre-calculated metrics table with named, stable columns and clear ownership is a harder target for that failure, because the contract lives in the schema itself, enforced whether or not anyone remembers a convention.

Agents also query at a volume and frequency human analysts never approached. A single workflow might call a tool that retrieves a metric dozens of times, and if that tool sits behind a materialized view, each call risks triggering upstream computation or landing inside a staleness window the agent has no way to reason about. A metrics table, written on its own separate schedule, is cheaper to query under this kind of load, because reading from it never triggers any computation.

Giving an agent access to live, production transactional tables is unsafe regardless of which analytics pattern sits alongside it. The right boundary keeps agents reading from a governed read layer, whether that's a materialized view with the security scaffolding described above or a pre-calculated metrics table, and never from the OLTP tables an application writes to directly. The governance tools that protect human users, row-level security, role-based access, column masking, matter just as much for agents. One industry survey of C-suite and business leaders at large enterprises found that 75% of those leaders ranked security, compliance, and auditability, beyond speed, as the most critical requirements for deploying agents. Because materialized views carry no native RLS support, agents reading from them need the same security-definer workaround described above, built and maintained specifically for machine consumers. A pre-calculated metrics table, governed properly, gives agents a safer read layer by default.

The single-source-of-truth problem both patterns are trying to solve

The deeper risk of an under-governed analytics layer is that different stakeholders, and now different agents, compute the same metric in different ways and end up arguing about the number instead of the decision the number was supposed to inform. When sales, finance, and product each keep their own version of a metric like MRR or net revenue retention, the executive team spends its time reconciling three answers instead of acting on one, and good analytics architecture is supposed to prevent that reconciliation tax.

Materialized views defined independently by separate teams can drift apart without anyone noticing. Two views computing "active users" with slightly different filter logic will each look correct in isolation and still return two different numbers when compared side by side. Nothing in the database stops this from happening. Postgres enforces no rule that two materialized views must define the same metric the same way, so divergence is a matter of when, not if, once more than one person is allowed to define metrics independently.

A pre-calculated metrics table, with one row per metric per period written by a single canonical job, makes the definition part of the schema rather than a convention scattered across several view definitions. Changing how a metric is calculated means changing it in one place, and because the table holds its own row history, the record of old definitions stays visible rather than disappearing the moment someone edits a view. Twelve metrics benefit the most from this kind of single authoritative source: MRR, ARR, net new MRR, churn rate, net revenue retention, CAC, LTV, LTV:CAC ratio, CAC payback period, gross margin, burn rate and runway, and activation rate. Each one gains real value from being defined once, computed once, and stored durably rather than recalculated differently by every team that needs it. An append-only metrics table built this way becomes something closer to an audited financial record: during due diligence, a founder can show not just the current MRR figure but the full history of how that figure was calculated over time, something a materialized view cannot offer without a separate layer of tooling built specifically for that purpose. All three patterns exist to avoid repeating expensive computation, but they differ in who controls the update contract: a standard view makes the database the owner of freshness, a materialized view makes the refresh scheduler the owner, and a pre-calculated metrics table makes the application code the owner. That last arrangement is what lets the pattern scale naturally to serve both human users and AI agents without requiring either one to coordinate around refresh timing.

When materialized views are the right call

Materialized views fit a specific workload shape well, and they turn into a liability the moment that shape is absent or changes faster than the refresh strategy can keep up with. They suit repeated reporting queries with stable patterns: the same aggregation hit over and over by dashboard loads, scheduled exports, and API calls on a predictable rhythm. They suit freshness requirements measured in minutes or hours rather than seconds, where nobody needs the absolute latest row the instant it lands. They suit single-tenant or internal-facing products, where the RLS workaround described earlier is a one-time setup cost rather than something that has to be rebuilt for every new feature. Supabase's own guidance names internal dashboards and analytics as the clearest use case for exactly this reason.

They become a liability for startups shipping schema changes on a weekly cadence, where maintaining refresh jobs, index definitions, and security-definer wrappers across every migration turns into a compounding operational tax. They also become a liability in multi-tenant products where row-level security per customer is a hard product requirement, since the workaround is not impossible to build but has to be rebuilt every time the underlying schema shifts under it. A founder choosing between these patterns is really choosing who owns the definition of a metric and for how long that definition can be trusted to hold, and that choice deserves the same scrutiny as any other piece of core infrastructure.