Troubleshooting slow queries on Supabase

What makes Supabase different

Supabase is managed Postgres with three layers on top: Row Level Security (RLS) enforced at the database level, Supavisor for connection pooling, and branching for preview environments. Each layer adds query overhead that does not show up in a vanilla Postgres installation.

If you are used to debugging Postgres directly, you may be looking in the wrong place. This guide covers the Supabase-specific path from symptom to fix.

Step 1: enable pg_stat_statements

pg_stat_statements records cumulative statistics for every distinct query shape. On Supabase it is available but not always enabled on older projects.

Check whether it is enabled:

SELECT name, setting FROM pg_settings WHERE name = 'shared_preload_libraries';

If the output does not include pg_stat_statements, enable it in the Supabase dashboard under Settings > Database > Extensions, then restart the database. On newer Supabase projects it is on by default.

Once enabled:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT
  LEFT(query, 120)   AS query_snippet,
  calls,
  ROUND(mean_exec_time::numeric, 2) AS mean_ms,
  ROUND(total_exec_time::numeric, 2) AS total_ms,
  ROUND(stddev_exec_time::numeric, 2) AS stddev_ms,
  rows
FROM pg_stat_statements
WHERE query NOT ILIKE '%pg_stat_statements%'
ORDER BY total_exec_time DESC
LIMIT 10;

The query with the highest total_exec_time is your first target. Look at mean_exec_time and stddev_exec_time together: high stddev means the query is sometimes fast and sometimes slow, which usually indicates lock contention or cache eviction.

Step 2: read the query plan

Copy the slow query and run EXPLAIN ANALYZE on it. On Supabase, run this from the SQL Editor, not from your application connection:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT *
FROM public.messages
WHERE user_id = 'abc123'
  AND created_at > NOW() - INTERVAL '7 days'
ORDER BY created_at DESC
LIMIT 50;

Key things to look for in the output:

Seq Scan on a large table

Seq Scan on messages  (cost=0.00..48201.00 rows=12 width=512) (actual time=234.5..8201.3 rows=12 loops=1)
  Filter: ((user_id = 'abc123') AND (created_at > '2026-05-27 00:00:00'))
  Rows Removed by Filter: 2400012

"Rows Removed by Filter: 2400012" means the planner read 2.4 million rows to return 12. You need an index on (user_id, created_at).

Buffers: hit vs read

Buffers: shared hit=128 read=24801

hit = pages served from shared_buffers (RAM). read = pages read from disk. A high read count on a query that should be cache-warm means the working set is larger than shared_buffers, or something else is evicting your pages.

Step 3: check for RLS overhead

This is the most common Supabase-specific slow query cause that standard Postgres guides miss.

When RLS is enabled on a table and a user executes a query, Postgres appends the RLS policy condition to the WHERE clause of every query on that table. A policy like:

CREATE POLICY "users see own rows" ON public.messages
  USING (auth.uid() = user_id);

becomes an invisible additional filter on every SELECT, UPDATE, and DELETE. If auth.uid() is evaluated as a subquery for each row, the cost compounds.

To check whether RLS is the culprit, compare plan costs with and without RLS:

-- As your application role (RLS enforced)
SET ROLE authenticated;
EXPLAIN (ANALYZE, BUFFERS)
SELECT COUNT(*) FROM public.messages WHERE created_at > NOW() - INTERVAL '1 day';

-- Reset and compare as postgres (superuser bypasses RLS)
RESET ROLE;
EXPLAIN (ANALYZE, BUFFERS)
SELECT COUNT(*) FROM public.messages WHERE created_at > NOW() - INTERVAL '1 day';

If the authenticated role plan is significantly slower, RLS policy evaluation is the overhead.

Fix: index the RLS predicate column

If your RLS policy filters on user_id, make sure there is an index on user_id:

CREATE INDEX CONCURRENTLY IF NOT EXISTS messages_user_id_idx
  ON public.messages (user_id);

Fix: use security definer functions

For complex policies, move the logic into a SECURITY DEFINER function. This runs as the function owner (bypassing RLS on the referenced tables) and Postgres can optimize it as a single function call rather than an inline subquery.

Step 4: check for branching artifacts

Supabase branching creates a new Postgres instance for each preview branch. When you run a migration on a branch and then merge it, the migration runs against production. But if you test queries on a branch and the branch data is sparse, the planner's statistics are based on a small dataset.

After a migration that adds an index or changes a column type, run:

ANALYZE public.messages;

This updates the planner statistics for that table immediately without waiting for autovacuum. If the branch had 100 rows and production has 5 million, the planner will make different decisions until ANALYZE runs.

Also check: on a fresh branch, pg_stat_statements starts empty. If you are timing queries on a branch to decide whether to add an index, the branch data volume may not be representative.

Step 5: check connection overhead from Supavisor

Supabase's connection pooler (Supavisor) runs in transaction pooling mode by default. This is the right default — it allows many clients to share a small number of Postgres connections. But transaction mode disables some features:

• SET LOCAL statements do not persist across transactions.
• Prepared statements with PREPARE / EXECUTE do not work (use parameterized queries instead).
• LISTEN / NOTIFY require the direct port (5432), not the pooler port (6543).

If your queries are slow specifically via the pooler port but fast via the direct port, check whether your ORM is using session-level features that are incompatible with transaction pooling.

Check which port you are connecting to:

SELECT inet_server_port();

For queries that must use session-level features (e.g., advisory locks for job queues), connect via the direct port (5432) and use a small, dedicated connection pool for those specific operations.

Step 6: check autovacuum backlog

Dead tuples accumulate on every table that receives UPDATEs or DELETEs. Autovacuum reclaims them, but it has a cost delay setting that throttles its I/O impact.

If a high-traffic table has a large dead tuple count, sequential scans become slower because they read dead tuples that autovacuum has not yet reclaimed. Check:

SELECT
  relname,
  n_live_tup,
  n_dead_tup,
  ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
  last_autovacuum,
  last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

If a table has more than 20% dead tuples and last_autovacuum is more than a few hours ago, run VACUUM manually:

VACUUM (ANALYZE, VERBOSE) public.messages;

This is a non-blocking operation (it does not acquire an ACCESS EXCLUSIVE lock) and can be run during business hours.

Common Supabase slow query patterns and fixes

insightral and Supabase

insightral's collector connects via a read-only role (the insightral grant SQL creates one if you do not have one). On Supabase, use the direct connection string (port 5432) for the collector — it needs access to system views like pg_stat_statements, pg_stat_user_tables, and pg_stat_activity, which are not affected by transaction pooling mode.

insightral runs all of the detection queries above on every 5-minute poll and fires a Slack alert when it finds sequential scan ratios above 80%, dead tuple counts above 20%, or connection pool saturation above 80%.

Summary

1. Enable pg_stat_statements and find the highest-cost queries by total_exec_time. 2. Run EXPLAIN (ANALYZE, BUFFERS) on the top offenders and look for Seq Scan with high "Rows Removed by Filter". 3. Check for RLS overhead by comparing plans with and without your application role. 4. After merges from branches, run ANALYZE to give the planner accurate statistics. 5. For connection issues: use the pooler port (6543) for application queries, the direct port (5432) for migrations and session-level operations. 6. Check dead tuple ratios on high-write tables and run VACUUM ANALYZE when dead_pct exceeds 20%.