Supabase Performance: Indexing, Connection Pooling, and the Postgres Settings That Matter

Supabase is Postgres with extras. When a Supabase project gets slow, the answer is almost always Postgres-level — a missing index, a poor query plan, exhausted connections, a statement timeout that needs raising. The Supabase wrapper doesn’t change the fundamentals. Here’s the tuning we do on every project we run.

#Step 1: index what your queries actually read

Every WHERE clause on a tenant-scoped table should hit an index. Every ORDER BY in a paginated query should hit an index. Every JOIN should be on an indexed column. Most SMB projects ship with maybe two indexes and discover the missing ones at 100k rows when queries start taking seconds.

-- Every tenant-scoped table
create index on submissions(site_id, created_at desc);
create index on submissions(site_id, score) where score is not null;
create index on submissions(email);

-- For frequent text search
create index on submissions using gin(to_tsvector('english', message));

#Step 2: read the EXPLAIN output

`EXPLAIN ANALYZE` on any slow query tells you exactly which step is expensive. Look for `Seq Scan` (sequential scan) on tables larger than 10k rows — that’s a missing index. Look for high `actual rows` numbers — that’s a planner estimation error, usually fixed by `ANALYZE` or a better statistics target.

explain analyze
select * from submissions
where site_id = 'bhr' and created_at > now() - interval '30 days'
order by created_at desc
limit 50;

#Step 3: connection pooling

Postgres is bad at high connection counts. Each connection costs a few MB of memory and a process slot. Supabase ships with PgBouncer between your client and Postgres — but you have to use it correctly.

• Use the pooler URL (port 6543) for serverless and edge contexts where connections are short-lived.
• Use the direct URL (port 5432) only for long-running processes or migrations.
• Set a sensible max-pool-size in your client (we use 10 for most Next.js apps).
• Watch the connection count in the Supabase dashboard. Anything spiking near the limit needs the pooler.

#Step 4: statement timeout

A runaway query can block other queries on the same connection. Set a statement timeout on every role to bound the damage.

alter role authenticated set statement_timeout = '8s';
alter role anon set statement_timeout = '5s';
alter role service_role set statement_timeout = '30s';

The application should handle the timeout gracefully — surface a friendly error to the user rather than 500. We wrap every database call in our server actions with try/catch and a generic “please try again” fallback.

#Step 5: vacuum and analyze

Postgres auto-vacuums but the defaults aren’t aggressive enough for high-write tables. Tune `autovacuum_vacuum_scale_factor` per table for anything seeing more than a few thousand UPDATE/DELETE per day.

alter table submissions set (
  autovacuum_vacuum_scale_factor = 0.05,
  autovacuum_analyze_scale_factor = 0.02
);

#Step 6: monitoring the right metrics

Supabase dashboard surfaces query duration, cache hit ratio, connection count, and disk usage. The numbers to watch:

• Cache hit ratio: should be above 99%. If it drops, your working set has outgrown the buffer cache — increase the instance size or add indexes.
• Connection count: should be well below max. If you’re at 80% of max, you need the pooler.
• P95 query duration: should be under 100ms for transactional workloads.
• Disk usage: should grow predictably. Sudden growth often means an index bloat or autovacuum has fallen behind.

#When to scale up

Supabase compute is sized in tiers. The free tier’s 2 shared CPU + 1GB RAM is enough for development. Pro’s default 2 CPU + 4GB RAM covers most SMB workloads. We size up when cache hit ratio drops below 98% or when P95 query time creeps over 200ms. Scaling up is a 30-second click in the dashboard with no downtime.

#Common slow-query patterns

1. Missing index on a WHERE clause column. EXPLAIN shows Seq Scan; add the index.
2. OR queries on multiple columns. Often faster as a UNION of two indexed queries.
3. SELECT * when you only need a few columns. Add a covering index that includes the projected columns.
4. Pagination with OFFSET past 10,000 rows. Switch to keyset pagination (WHERE id > last_id).
5. Subqueries that can be JOINs. Postgres often plans them worse than the JOIN equivalent.

select
  calls,
  total_exec_time / 1000 as total_seconds,
  mean_exec_time as mean_ms,
  query
from pg_stat_statements
order by total_exec_time desc
limit 20;

#What to do when you can’t change the query

Sometimes the slow query comes from a third-party tool or an ORM you don’t own. Two options. First, materialized views: pre-compute the result, refresh on a schedule. Second, a read replica: route reporting queries to a replica so they don’t affect transactional traffic. Supabase Pro doesn’t bundle replicas in the default tier; you’d need a custom plan or a separate read-only project.

#How this lands across FH client work

Every FH project ships with the index set, the role-level statement timeouts, the pooler URLs wired into the env, and the pg_stat_statements weekly check. Cache hit ratio across the client book stays above 99.4%. P95 query time stays under 80ms. We size up exactly one project (the highest-volume retail client) to the 4 CPU / 8GB tier; everyone else runs on the default Pro.

If your Supabase is slow and you’re not sure why, book a consultation — most performance issues are fixable in a half-day audit with a handful of indexes and a pooler config change.