Analytical Query Performance on Supabase Postgres

Analytical query performance on Supabase Postgres degrades in patterns that can be named, traced, and fixed. It does not fail at random. A handful of mechanical realities, repeated across millions of query executions, account for nearly every slowdown a team will encounter.

Postgres tables are built for transactional work: frequent inserts, updates, deletes, and fast lookups of single rows by key. Analytical queries ask something structurally different of the same storage engine. They scan large stretches of rows, aggregate across them, and join against history. That mismatch between what the table was built to do and what the query is asking it to do is the mechanical root of almost every performance complaint that follows.

The most common failure mode is the sequential scan. Without an index, Postgres has no way to find the rows that satisfy a filter except by reading every row in the table, and the cost of doing so does not grow politely as the table grows. It compounds. A sequential scan on a large, frequently queried table burns CPU and disk I/O on every single call, and at a high enough call volume, one missing index is enough to saturate an entire instance.

The I/O side of that story has its own mechanics. Postgres stores both tables and indexes in 8 KB pages. When a query needs a page already sitting in shared memory, that's a buffer hit, cheap and fast. When it needs a page that has to be pulled off disk, that's a buffer read, and buffer reads cost orders of magnitude more than hits. A query that reports high shared-read counts is paying that disk cost on every single execution, not just the first one.

Row-level security introduces a separate and independent failure mode. A naively written policy that runs a correlated subquery for every row, checking team membership by joining inside the policy itself, can make an otherwise reasonable query orders of magnitude slower than the same query written to resolve the relevant auth value once and compare it against indexed columns.

A third pattern is the N+1 problem. No single query in this pattern looks slow. Each one returns in a few milliseconds and would pass any spot check. The damage comes from volume: the signal to watch is a high call count paired with an acceptable mean latency, not the presence of any individual slow query. A team hunting for the one query that's dragging the database down will walk right past this pattern, because nothing about it looks dramatic in isolation.

How to find the queries that need fixing

Diagnosing any of these failure modes starts with data about the whole workload, not a guess about which query feels slow. Engineering intuition about "the slow query" is rarely the one that costs the most in aggregate, and chasing it first wastes the time that should go toward the actual offenders.

The tool for this is pg_stat_statements, an extension that ships with Postgres itself and requires no additional installation on Supabase. It keeps a running record, for every normalized query the server has executed, of how many times it has been called, its total execution time, its mean execution time, and the balance of buffer hits versus disk reads behind each one. Three ways of sorting that table answer three different questions. Sorting by total_exec_time surfaces the queries costing the most in aggregate, the right lens when the database is running hot or CPU usage is elevated across the board. Sorting by calls identifies N+1 candidates: queries that are individually cheap but fire often enough to account for a meaningful share of total database work. Resetting the counters at deploy time with SELECT pg_stat_statements_reset(); and letting an hour of production traffic accumulate afterward gives a clean before-and-after boundary; without that boundary, a regression that shipped the week before simply reads as normal, indistinguishable from the baseline it has already corrupted.

Once pg_stat_statements has named a specific query, EXPLAIN (ANALYZE, BUFFERS) explains why that query is slow. Plain EXPLAIN asks the planner for its intended plan without running the query at all, which makes it useful for write statements and for confirming an index will actually be used before committing to it. EXPLAIN ANALYZE goes further by executing the query and reporting actual timings next to the planner's estimates. A wide gap between estimated and actual row counts is a reliable sign that the table's statistics have drifted out of date. Adding BUFFERS to the call adds hit and read counts to that output, and shared read, the count of 8 KB pages pulled from disk, is the direct indicator of how much a query's I/O cost actually is.

Reading the plan itself takes a little orientation: it is a tree, read from the innermost indented node outward, with the top-level node representing the final operation and every nested node beneath it a step feeding into that result. A handful of node names recur constantly. Seq Scan means Postgres read every row in the table and is usually the target of optimization. Index Scan means Postgres used an index to locate the matching rows efficiently. Bitmap Heap Scan is an efficient middle path for moderate row counts, using a bitmap built from an index rather than reading the full table or doing a direct index scan. For teams less comfortable reading raw plan output, a managed database dashboard can run EXPLAIN directly without requiring a separate database client, which lowers the barrier to running this diagnostic at all, though it doesn't replace the work of actually reading what the plan returns.

Supabase's index_advisor extension automates a first pass over this process: point it at a query and it suggests indexes that would improve the plan without requiring the kind of manual plan-reading fluency described above. Teams that want continuous visibility rather than a one-time diagnosis have another option in pganalyze, which connects to Supabase and collects query and schema statistics on an ongoing basis. Its Log Insights feature adds structured analysis of Postgres logs, covering slow queries, lock waits, and autovacuum events, through a separate log drain. Setting that up calls for a dedicated monitoring user and a collector running on infrastructure the team controls, and for Log Insights specifically, that collector also needs to expose a stable, publicly reachable OTLP endpoint. That's a meaningfully bigger operational commitment than running pg_stat_statements queries by hand, and the choice between them is really a choice about whether the team wants point-in-time diagnosis or a standing monitoring system.

Fixing sequential scans and I/O costs with indexes

Teams hesitate to index a live table because they picture a lock that stalls every write until the index finishes building. CREATE INDEX CONCURRENTLY removes that specific fear: it builds the index without blocking writes against the table, which makes the operational risk of indexing a production table lower than most teams assume going in.

Once that blocker is out of the way, the fix for most sequential scans is simply the right index on the right column. Indexing a column used in a filter or a join can speed up retrieval by an order of magnitude, but which index type gets used matters as much as whether one exists. The B-tree index, Postgres's default, is the correct choice for equality and range queries across most column types, and it covers the overwhelming majority of analytical filtering patterns a team will run into.

Indexes are not free, though. Every insert, update, and delete against a table has to update every index defined on it, so each index is a standing trade between faster reads and slower writes. That trade should be made deliberately: if adding an index doesn't measurably reduce the cost of the query plan it was meant to help, it should come back out. A dead index pays the full write penalty on every mutation while returning no benefit on read, which makes it pure cost sitting on the table.

When an index exists and the query still runs a sequential scan anyway, the planner's statistics are the next thing to check. Postgres maintains internal statistics about the contents of each table, and the planner relies on those statistics to decide whether an index scan or a sequential scan will be cheaper for a given query. When the estimated row counts in a plan diverge sharply from the actual row counts EXPLAIN ANALYZE reports, those statistics have likely drifted out of date, and the planner is making its decision on a stale picture of the table. Running ANALYZE on the affected table refreshes that picture. It is the step that explains the otherwise confusing case where a perfectly reasonable index exists, matches the query's filter exactly, and still goes unused.

Fixing RLS performance without weakening security

RLS performance problems almost always trace back to how a policy expression is written. Rewriting the policy to the same security intent, expressed in a cheaper form, is almost always sufficient to resolve the slowdown, with no weakening of what the policy actually protects.

The single highest-impact fix is wrapping JWT functions like auth.uid() and auth.jwt() inside a SELECT subexpression. That small change causes the query optimizer to treat the result as an initPlan, evaluated once for the whole query. The pattern is safe specifically because the return value of auth.uid() doesn't change based on which row is being evaluated: wrapping it only defers and caches a value that was always going to be the same across every row in the query anyway. One caution applies here: because functions used inside RLS policies can be called from the API, any wrapped function whose result would constitute a security leak if exposed belongs in an alternate schema.

Join-based policies need a different rewrite. The pattern auth.uid() IN (SELECT user_id FROM team_user WHERE team_user.team_id = table.team_id) is correlated, meaning Postgres runs that subquery fresh against every row of the outer table. Restructured as team_id IN (SELECT team_id FROM team_user WHERE user_id = (SELECT auth.uid())), the policy resolves the user's full set of permitted team IDs exactly once and then filters the target table against that set, replacing a per-row lookup. On a large table, that rewrite alone collapses execution time dramatically, because the cost stops scaling with the number of rows in the table being filtered. Teams working with a very large permitted set, where the resolved list of IDs itself runs into the thousands, should treat that as a case calling for its own separate analysis, since the subquery's own cost starts to matter at that scale. Moving the join query into a SECURITY DEFINER function avoids running RLS a second time against the join table itself, a real source of overhead whenever that join table is also protected by its own policies.

Indexing matters here just as much as it does for sequential scans. Adding an index on whatever column an RLS policy condition actually checks, when that column isn't already a primary key or otherwise unique, can deliver more than a hundredfold improvement on large tables: a policy using auth.uid() = user_id benefits enormously from a plain index on user_id. Storing role information inside a JWT's app_metadata as a custom claim is another way to cut cost out of the check entirely, since the role is then read straight off the token rather than queried from a roles table on every row evaluated.

All of these fixes assume RLS is doing the job it was designed for: enforcing per-user, per-row access inside an application's normal request pattern. Analytical queries ask a different kind of question of the same policies, and that mismatch is where the next layer of friction begins.

Where Postgres and RLS create structural friction for analytics

Even a Postgres instance that has been fully tuned, indexed correctly, and freed of every N+1 and correlated-subquery problem still runs into a limit that no query rewrite fixes. Analytics and live application traffic sharing the same database raises this limit: the schema built to protect the application is, by design, the same schema that creates friction for a BI workload trying to scan across it, and the BI workload's broad scans compete directly with live application traffic for the same finite compute.

Row-level security was designed for the application's own access pattern: per-user, per-row enforcement, checked close to the data, on behalf of one authenticated person at a time. Analytics work almost never fits that pattern, because an analytics role nearly always needs to see across users, not through the lens of any single one. That leaves a team with two options, and neither is without cost. One is granting a role that bypasses RLS outright, which is a legitimate security exception but one that has to be reviewed and audited on an ongoing basis, since it is a standing hole in an otherwise enforced model. The other is writing analytics-shaped policies directly into the production auth model, which solves the immediate problem by coupling two concerns, application security and analytical access, that function better as separate systems maintained separately.

Neither choice is a mistake on its own. Both carry an operational cost that grows over time as the schema changes, as the team changes, and as more people need to run more kinds of analytical queries against data that was never modeled with that use in mind. Postgres tables are built for transactional work: frequent small writes, point lookups, strict consistency per row. Analytical work asks for something else entirely: processing large volumes of historical data, running aggregations across all of it, controlling storage costs as that history grows, and keeping all of that activity from degrading the live application sitting on the same instance. Recognizing that boundary is itself the useful outcome of all the diagnostic and tuning work described above. The fixes in this piece cover failures, and the fixes are real and worth doing, but the tension between a security model built for an application and a workload built for analysis is the shape of the problem, marking the line past which indexing and policy rewrites stop being the answer and a separate, purpose-built data layer starts to be the right one.