Analytics on Supabase Without Moving Data to a Warehouse

You can run real analytics on Supabase, including funnels, retention, revenue metrics, and dashboards, straight from Postgres without a warehouse or an ETL pipeline. What makes it work is three things: shared metric definitions, scheduled precomputation, and a deliberate choice about where heavy queries run. A separate analytical layer earns its place when questions need data from outside Postgres. A warehouse earns its place only when write volume, analytical complexity, and a team to run it all show up together.

This guide covers what Postgres handles well, what to precompute, how to write the core metrics without SQL mistakes, when to add a columnar layer, and how to give AI agents safe access to the numbers.

Why Supabase teams reach for a warehouse they don't need yet

The analytics gap shows up right when a Supabase product starts working. Customers want to see their own data, and founders need answers about activation, retention, and revenue. Supabase gives you operational telemetry: logs, database reports, and auth metrics. Its Analytics Buckets add Apache Iceberg-backed columnar storage. None of that is a business analytics layer with agreed metrics and dashboards.

That gap makes the warehouse pitch persuasive. But a warehouse-plus-BI stack adds another system, another bill, and a data function to operate it. A governed layer built around Postgres can give you modeled metrics and dashboards long before that investment pays off.

What Postgres does well for analytics, and where it stops

Postgres handles indexed lookups, small aggregations, and well-modeled analytical queries well. Trouble usually comes from how the workload is built, not from Postgres itself. JSONB is a good example: it's flexible for application development, but most charting tools need custom extraction logic to turn nested values into usable columns.

Scan-heavy analytics is a different problem. Large scans compete with live traffic for CPU and memory on a row-oriented instance, so buying more compute to serve a BI tool can still leave your app slower. A read replica isolates some of that traffic, but it roughly doubles database spend and keeps the same row-oriented execution. Neither one answers a question that needs data that isn't in Postgres. At high write throughput, replica lag adds another limit, but most early teams hit repeated analytical scans long before that.

What to precompute: materialized views and pg_cron first

Daily signups, cohort revenue, and activation rates usually have definitions stable enough to calculate ahead of time. A materialized view stores the result, pg_cron refreshes it on a schedule, and dashboards read the stored aggregation instead of scanning raw tables on every request. Freshness becomes an explicit choice: an hourly refresh suits most business questions, while a second-by-second operational display is a different workload.

For an example users table with a created_at timestamp:

CREATE MATERIALIZED VIEW daily_signups AS
SELECT date_trunc('day', created_at AT TIME ZONE 'UTC') AS signup_day,
       count(*) AS signups
FROM users
GROUP BY 1;

SELECT cron.schedule(
  'refresh-daily-signups',
  '0 * * * *',
  'REFRESH MATERIALIZED VIEW daily_signups'
);

CREATE MATERIALIZED VIEW daily_signups AS
SELECT date_trunc('day', created_at AT TIME ZONE 'UTC') AS signup_day,
       count(*) AS signups
FROM users
GROUP BY 1;

SELECT cron.schedule(
  'refresh-daily-signups',
  '0 * * * *',
  'REFRESH MATERIALIZED VIEW daily_signups'
);

CREATE MATERIALIZED VIEW daily_signups AS
SELECT date_trunc('day', created_at AT TIME ZONE 'UTC') AS signup_day,
       count(*) AS signups
FROM users
GROUP BY 1;

SELECT cron.schedule(
  'refresh-daily-signups',
  '0 * * * *',
  'REFRESH MATERIALIZED VIEW daily_signups'
);

The table and timezone are illustrative. The pattern moves the scan off the dashboard request path while keeping it inside the database.

The definition has to come before the stored result. If two people disagree about what counts as a conversion, faster precomputation just spreads inconsistent answers sooner. Building scheduled, governed aggregations by hand is the same foundation Dreambase provides as pre-calculated metrics for both people and agents.

How to build funnels, retention, and revenue metrics without SQL that lies

Funnels measure users moving through a defined sequence. Establish each user's progress first, then aggregate the user-level results. COUNT(DISTINCT user_id) FILTER (WHERE event_name = 'activation') counts users who reached a step without counting repeat events as extra people. Dropping DISTINCT and counting event rows inflates completion. If order matters, each step must also come after the previous qualifying step before a user gets credit.

Retention needs a cohort date and an activity date that follow the same timezone rule. Normalize both timestamps before converting to dates, for example (event_at AT TIME ZONE 'UTC')::date, then count distinct returning users by cohort and elapsed day. The mistake to avoid is mixing conventions, which can push an 11 PM activity into a different retention day than the business intended.

Revenue retention starts from a fixed set of paying customers at the start of the period. Aggregate opening and closing revenue to one row per customer, join within that starting cohort, then calculate Gross Revenue Retention (GRR, excludes expansion) and Net Revenue Retention (NRR, includes it). NRR above 100% means expansion from existing customers outweighs their churn and contraction.

These patterns still depend on business choices. Pick the keystone event that drives growth and document exactly what qualifies. A shared calculation stops each stakeholder from quietly substituting their own event, window, or revenue basis. Correct SQL and governed metrics solve different failure modes, and you need both.

Where pg_duckdb fits

Some questions stay scan-heavy even after the common metrics are precomputed. When the data already lives in Postgres, pg_duckdb embeds DuckDB's columnar engine so large aggregations run faster without exporting data. Setting SET duckdb.force_execution = true; routes a query through that engine, and the extension can also read Parquet files in S3-compatible storage through the same SQL interface.

The catch is deployment control. pg_duckdb works on self-hosted Supabase, but managed Supabase projects don't allow arbitrary extensions, so hosted teams can't take this path. They need a separate execution layer once precomputation stops being enough.

When a separate analytics layer is the right move

If a question has to join application records with Stripe revenue, CRM contacts, or marketing attribution, a lightweight analytical layer is justified: a columnar copy of your application data that can join those other sources. Supabase Analytics Buckets give you S3-backed Iceberg storage for this, but storage alone still needs a query engine and governed metrics on top.

Write volume is the other trigger. When sustained writes generate heavy WAL, replicas fall behind while WAL piles up on the primary and eats disk. A dashboard reading a lagging replica shows stale numbers no matter how fast the query runs, and refreshing more often can't fix a schedule that's already falling behind.

What metric governance means before any dashboard or agent touches the data

Five people querying the same source with slightly different logic get five different numbers. Standardizing the connection doesn't standardize the meaning. A semantic layer sits between the data and its consumers so dashboards, reports, and agents all use the same calculation.

In practice, decide what an active user is, the conversion window, what a retention day means, and the revenue basis, then encode those once in a view, materialized table, or governed dataset so every consumer inherits them. Dreambase organizes this as a Data Catalog, Datasets, and Metrics that sit under its dashboards, and indexes your schema so generated queries understand the data model. It doesn't remove the human job of deciding what a metric means.

How AI agents should access Supabase analytics safely through MCP

Agents that query production Postgres directly compete for connections and compute, run into row-level security, and get raw rows with no agreed definitions. The safer pattern is an MCP server over a governed analytical copy. Model Context Protocol, introduced by Anthropic in November 2024, is the open standard for connecting AI apps to tools and data. For analytics, an agent should see approved metrics, reports, and fields, not unrestricted SQL against live tables.

Scaling MCP servers doesn't change database capacity. Adding server instances leaves the production connection pool as the bottleneck, so agent traffic belongs on the analytical copy. Set permissions and exposed datasets at that boundary.

Dreambase is one way to do this for Supabase teams: agents query modeled Parquet datasets through DuckDB over a single MCP server, so production Postgres never serves agent queries. Columnar datasets scan fast and keep token use down, and the same governed backend serves agents and people without tying metric definitions to one front end. That's headless analytics, in the same sense as a headless CMS.

When a warehouse earns its place

A warehouse earns its place when sustained write throughput defeats precomputation and multi-source requirements make the lightweight layer warehouse-like anyway. At tens of millions of inserted rows a day across many tables, index maintenance, vacuum overhead, and WAL buildup compound. A dedicated data team is part of the cost. Without one, a warehouse just reproduces metric disagreements on more infrastructure.

Most Supabase startups and growing SaaS teams aren't there, and one slow query isn't the signal. The order stays the same: governed Postgres analytics first, a lightweight columnar layer when cross-source joins require it, and a warehouse when scale and team capacity justify it.

How to get analytics and dashboards for your Supabase project

1. Start with one business question. Pick the first one that needs a dependable answer, and define its active user, conversion, retention day, or revenue figure before writing the query.

2. Encode the definition once. Put it in a view or governed dataset so every dashboard and report uses the same logic.

3. Precompute what repeats. Use materialized views and pg_cron for expensive, recurring calculations, with a refresh interval that matches how fresh the answer needs to be.

4. Keep heavy reads off production. Point dashboards and BI tools at precomputed results or an analytical copy, never through a service account that bypasses RLS.

5. Add a columnar layer for cross-source questions. When you need Stripe, CRM, or attribution data, join it in a lightweight analytical copy instead of standing up a warehouse.

6. Give agents governed access only. Point their MCP server at governed, precomputed datasets so no agent traffic reaches production Postgres.

Dreambase connects to Supabase, models governed datasets and metrics, and serves dashboards, AI-generated reports, and MCP access without schema changes, a warehouse, or an ETL pipeline. Every analytics setup starts with a short call, so meet with the team to get your metrics defined and running. Teams that settle what "reliable" means before an agent arrives can hand that agent a number they trust.