How to Run Analytics Queries on Supabase Postgres Without Slowing Production

Separate analytical work from application work on Supabase Postgres: find the costly queries, isolate them on a replica, change the engine, or serve recurring analytics from governed snapshots.

Separate analytical work from application work on Supabase Postgres: find the costly queries, isolate them on a replica, change the engine, or serve recurring analytics from governed snapshots.

How to

2026-11-20

8 min read

Intended first value

You leave knowing which queries hurt production, what a read replica and pg_duckdb really buy on Supabase, and when to move recurring analytics to governed datasets.

To run analytics queries on Supabase Postgres without slowing production, separate analytical work from application work. How far you separate them depends on the workload. A statement_timeout keeps occasional queries safe on the primary, a read replica isolates heavy queries, a columnar engine changes how they execute, and governed snapshots take recurring analytics off the live query path entirely, while giving people and AI agents the same metric definitions. Dreambase handles that last step for Supabase teams: it serves dashboards, reports, and agents from governed datasets, so recurring analytics never touch the database your customers use.

Why Postgres is great at transactions and poorly suited to analytical scans

Postgres stores data by row. That makes it fast at the single-row reads, inserts, and updates that keep an application running. Analytical queries ask for different work: they scan large parts of tables and aggregate values across millions of rows, the job columnar engines are built for. This is an engine-design difference, and no Supabase configuration changes it.

The conflict shows up when both workloads share an instance. They compete for the same CPU, memory, and I/O. A broad analytical scan takes resources that transactional queries need, so application requests slow down while the database builds a report. It doesn't matter whether the analytical consumer is a dashboard, an analyst, or an agent.

What breaks when analytics queries run on a production Supabase database

Connections. Supavisor's transaction mode is built for short-lived connections and doesn't keep session state between transactions. BI tools that rely on session features such as prepared statements often need session mode or a direct connection instead, which leaves the dashboard holding long-lived connections on the database that serves your application.

JSONB extraction. JSONB columns that made development fast are opaque to charting tools, so every consumer writes its own extraction logic. Five slightly different extraction queries produce five slightly different numbers.

Repeated queries. N+1 patterns turn a cheap call into an expensive one because it runs thousands of times. The signal is call volume, not per-call latency.

Compute creep. As the events table grows, dashboard scans need more work, so teams move to a larger instance to keep reports as fast as before. The bigger instance serves the BI tool's growth, not the application's. More compute relieves the symptom without changing the workload that caused it.

How to find the queries doing the damage with pg_stat_statements and EXPLAIN

Start with workload statistics, not with the SQL that feels slow. The most expensive queries are often not the ones engineers notice. pg_stat_statements records statistics for every normalized query, and Supabase enables it by default (if it isn't on, enable it from Database → Extensions or with create extension pg_stat_statements).

Rank by overall cost first:

  • total_exec_time shows which queries consume the most time across the workload.

  • mean_exec_time shows which queries are slow per call.

  • calls exposes repetition, including N+1 patterns that averages hide.

  • rows and shared_blks_hit versus shared_blks_read show how much work each query really does.

Set a measurement boundary so regressions don't blend into history. Reset counters at deploy time with select pg_stat_statements_reset();, let an hour of production traffic run, and you see what the new version actually does. Without that boundary, a regression shipped last week reads as normal.

Once the statistics point at a query, run EXPLAIN (ANALYZE, BUFFERS) on it. The shared read count shows disk I/O; repeated cache misses on hot data usually mean a scan where you expected indexed access. A sequential scan on a growing hot table is a common finding, and CREATE INDEX CONCURRENTLY adds the missing index without blocking writes. But a query that genuinely aggregates most of a table isn't missing an index. It's an analytical query, and the real decision is where it should run.

To keep catching these after a fix, our Supabase observability guide covers slow-query and connection monitoring. Dreambase Observability runs a daily check across performance, indexing, security, errors, and capacity on every connected project, so a scan that starts to hurt shows up as a recommendation instead of a slowdown.

The honest cost of staying on the primary, and what a read replica buys

A larger primary gives the shared workload more headroom, but every dashboard query that grows with the events table can force another upgrade. A read replica instead gives analytical queries their own instance and connection pool, isolated from application traffic. Supabase offers replicas on the Pro, Team, and Enterprise plans, for projects on at least a Small compute size.

That isolation costs real money. A Supabase replica runs at the same compute size as the primary and is billed separately, and plan compute credits don’t apply to it. Its disk is provisioned at 1.25 times the primary’s to hold WAL archives and billed on top, and replicas aren’t covered by the Spend Cap. In practice a replica roughly doubles database spend, so it’s worth it when protecting production is worth that much.

Plan for three constraints:

  • Permissions carry over. A replica copies the primary's grants, including over-broad ones. Lock down the role your analytics tools use.

  • Long queries conflict with replication. Postgres may cancel replica queries that conflict with incoming changes. Give most analytical roles an aggressive statement_timeout. For legitimately long analytics, hot_standby_feedback = on avoids cancellations at the cost of bloat on the primary, and a higher max_standby_streaming_delay allows longer queries at the cost of more replication lag. On Supabase you set both with the CLI's postgres-config command.

  • Freshness. Replication is asynchronous, so a replica always trails the primary by some replication lag, which Supabase charts in seconds on the Database Reports page. That's fine for dashboards but not for operational reads that must see the latest write.

A replica also keeps the same row-store tables and planner, so a scan of millions of rows is still a scan of millions of rows. It doesn't normalize JSONB or bring Stripe and CRM data into a shared model. Those need a change in execution or data architecture.

Adding a columnar engine with pg_duckdb

pg_duckdb is an open-source Postgres extension, built by DuckDB Labs with Hydra and MotherDuck, that embeds DuckDB's vectorized engine inside Postgres. With SET duckdb.force_execution = true;, analytical queries on regular Postgres tables run through DuckDB, and large aggregations can run much faster. It can also read Parquet from S3-compatible storage, so logs, events, and archives outside Postgres are queryable from SQL.

The catch for Supabase teams: pg_duckdb is not on Supabase's list of supported extensions, so you can't install it on a Supabase Cloud project. It's an option for self-hosted Supabase or for a separate Postgres instance you replicate into. The deployment rule is the same either way: run it on a dedicated replica, never on the primary, so a resource-hungry DuckDB query only hurts the replica.

This path avoids running a separate analytical system, but it doesn't unify external business sources or give agents governed metric definitions.

Supabase is also building its own analytical storage. Analytics Buckets keep large datasets in Apache Iceberg so analytical work stays off the primary. They’re in alpha, and since managed replication into them has ended, you bring your own ingestion pipeline. For managed replication today, Supabase points to external replication into BigQuery, which means running a warehouse alongside your project.

Why governed Parquet snapshots are safer than any live query path for recurring analytics

Recurring dashboards and KPIs ask the same questions over and over. A live query, even on a replica, re-derives the answer every time. A pre-modeled dataset computes the result once and serves it many times, which changes the unit of analytics from a query to a reusable, governed result.

Parquet snapshots queried with DuckDB combine columnar execution with separation from the source. The application database is read when data is snapshotted, not when someone opens a dashboard, so analytical traffic never holds live connections against production.

Modeling also settles meaning before data reaches consumers. JSONB fields become typed, named columns, so no chart needs its own extraction logic. Metrics like churn or active users get one definition, computed in one place, and people and agents ask for the same measure instead of rebuilding it from raw tables.

This is how Dreambase works for Supabase teams. Dreambase is the application layer on top of your analytics infrastructure: it snapshots connected Postgres data into pre-modeled, governed datasets stored as Parquet and queried with DuckDB, with no production schema changes, no ETL pipeline to build, and no separate warehouse to operate. The design is read-only and snapshot-first, so dashboards, reports, and agents answer questions from stored datasets instead of production.

Why agents querying production Postgres carry risks that agents querying governed datasets don't

Agents amplify every risk of human analytics. They query at machine speed, generate arbitrary SQL, and don't limit themselves. An MCP server pointed straight at production exposes the live schema, can surface PII, and can't guarantee generated queries stay within safe resource limits. The protocol gives agents access; it doesn't give you workload isolation or a governed interpretation of the data.

The Model Context Protocol, introduced by Anthropic in late 2024, is now the common way agents reach tools and data. That makes the boundary behind the MCP server the thing that matters: a server wired to raw production tables and one exposing governed datasets present very different surfaces to an agent (more on MCP security).

Text-to-SQL gives an agent access; defined metrics give it meaning. Dreambase exposes its primitives (metrics, datasets, triggers, and domains) as tools through one Dreambase MCP server. An agent asks for a named metric, gets the same pre-calculated result a human dashboard shows, and its request never reaches production Postgres.

How Dreambase compares with a replica plus a BI tool, or a warehouse

Each path makes a different trade, and it’s worth being clear about which one you’re making.

  • Read replica plus a BI tool: near-live data, a little behind the primary, and any ad hoc SQL you want. It costs a second database at full compute, large scans are still row-store scans, and each dashboard defines its own metrics.

  • A warehouse such as BigQuery: scales to any volume and joins every source. It’s another system to run, model, and pay for, and someone has to own the pipeline into it.

  • Dreambase: governed datasets on Parquet and DuckDB with no warehouse to operate, one definition per metric for people and agents, and agents that read through the Dreambase MCP instead of production. Datasets refresh daily from the source, so they aren’t the right place for reads that must see the latest write; keep those on the primary or a replica.

If agents will touch this data, lock down what each role can reach first. Our Supabase Row Level Security guide covers testing policies as real clients.

Choosing the right path: from small datasets to agent-ready analytics

Diagram: Four Paths: From Direct Access to Governed Agentic Analytics. Visualizes: Show a decision flow across four escalating levels of analytical separation from production Postgres.
  1. Query the primary directly only when the dataset is small, queries are light and infrequent, and the analytics role has a statement_timeout.

  2. Add a read replica when analytical queries become heavy, unpredictable, or written by someone other than the person on call. Its job is to keep analytical load off the database your customers use.

  3. Add a columnar engine when large aggregations are still slow on the replica and you control the Postgres instance (self-hosted Supabase or a separate replica), since pg_duckdb isn't available on Supabase Cloud.

  4. Move to governed datasets when analytics run on a schedule, several people need the same metrics, JSONB normalization keeps costing you, or agents are querying data. Dreambase covers this path for Supabase teams, replacing a replica-plus-BI-tool-plus-semantic-layer stack with one data layer that serves dashboards, Slack reports, and MCP-connected agents from the same pre-calculated source.

Production Postgres should serve the application. Everything else should work from a replica, a snapshot, or a governed model. How far you move analytics away from production depends on whether you need load isolation, a different engine, or reusable metrics that people and agents share.

Start for free to connect your Supabase project and move recurring analytics off production, or meet with the team to talk through your setup.

NEXT STEP

Analytics on Supabase Without Moving Data to a Warehouse

Once analytics are off your production path, see how to get warehouse-grade analytics on Supabase without building or running a warehouse.