Supabase Performance Tuning
Overview
Systematically improve Supabase query and database performance across three layers: PostgreSQL engine (indexes, query plans, materialized views), Supabase infrastructure (Supavisor connection pooling, Edge Functions, read replicas), and client SDK patterns (column selection, pagination, RPC functions). Every technique here is measurable — run EXPLAIN ANALYZE before and after to confirm the improvement.
Prerequisites
- Supabase project (local or hosted) with
@supabase/supabase-js v2+ installed
- Supabase CLI installed (
npx supabase --version to verify)
- Access to the SQL Editor in the Supabase Dashboard or a direct Postgres connection
pg_stat_statements extension enabled (Step 1 covers this)
Instructions
Step 1: Diagnose — Find What Is Slow
Start every performance effort with data. Enable pg_stat_statements and run the Supabase CLI diagnostics to identify bottlenecks before optimizing.
Enable the stats extension:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
Find the slowest queries by average execution time:
SELECT
query,
calls,
mean_exec_time::numeric(10,2) AS avg_ms,
total_exec_time::numeric(10,2) AS total_ms,
rows
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;
Check index usage and cache hit rates with the Supabase CLI:
npx supabase inspect db index-usage
npx supabase inspect db cache-hit
npx supabase inspect db table-sizes
Inspect active connections for pooling issues:
SELECT state, count(*), max(age(now(), state_change)) AS max_age
FROM pg_stat_activity
WHERE datname = current_database()
GROUP BY state;
If idle connections exceed your plan's limit or active queries show high max_age, connection pooling (Step 2) and query optimization (Step 3) are the priority.
Step 2: Indexes and Query Plans
Indexes are the single highest-impact optimization. Use EXPLAIN ANALYZE to read query plans, then create targeted indexes.
Read a query plan:
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM users WHERE email = 'alice@example.com';
Look for Seq Scan on large tables — that means no index is being used. After adding an index, the plan should show Index Scan or Index Only Scan.
Create a basic index:
CREATE INDEX idx_users_email ON users(email);
Create a composite index for multi-column filters:
CREATE INDEX idx_orders_user_created
ON orders(user_id, created_at DESC);
Create a partial index to cover a common filter pattern:
CREATE INDEX idx_todos_user_incomplete
ON todos(user_id, inserted_at DESC)
WHERE is_complete = false;
Find missing indexes on foreign keys (common source of slow JOINs):
SELECT
tc.table_name,
kcu.column_name AS fk_column,
'CREATE INDEX idx_' || tc.table_name || '_' || kcu.column_name
|| ' ON public.' || tc.table_name || '(' || kcu.column_name || ');' AS fix
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
ON tc.constraint_name = kcu.constraint_name
LEFT JOIN pg_indexes i
ON i.tablename = tc.table_name
AND i.indexdef LIKE '%' || kcu.column_name || '%'
WHERE tc.constraint_type = 'FOREIGN KEY'
AND tc.table_schema = 'public'
AND i.indexname IS NULL;
Find unused indexes (candidates for removal to reduce write overhead):
SELECT schemaname, relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC;
Always use CREATE INDEX CONCURRENTLY on production tables to avoid locking writes during index creation.
Step 3: Client SDK and Infrastructure Optimization
Optimize the Supabase JS client calls, then leverage infrastructure features for scale.
Select only needed columns — avoid select('*'):
import { createClient } from '@supabase/supabase-js'
const supabase = createClient(
process.env.SUPABASE_URL!,
process.env.SUPABASE_ANON_KEY!
)
const { data } = await supabase.from('users').select('*')
const { data } = await supabase.from('users').select('id, name, avatar_url')
Paginate with .range() instead of loading all rows:
const { data: page1 } = await supabase
.from('products')
.select('id, name, price')
.order('created_at', { ascending: false })
.range(0, 49)
const { data: page2 } = await supabase
.from('products')
.select('id, name, price')
.order('created_at', { ascending: false })
.range(50, 99)
Use RPC functions to push complex logic to Postgres:
CREATE OR REPLACE FUNCTION get_dashboard_stats(org_id uuid)
RETURNS json AS $$
SELECT json_build_object(
'total_users', (SELECT count(*) FROM users WHERE organization_id = org_id),
'active_projects', (SELECT count(*) FROM projects WHERE organization_id = org_id AND status = 'active'),
'tasks_completed_30d', (SELECT count(*) FROM tasks t
JOIN projects p ON p.id = t.project_id
WHERE p.organization_id = org_id
AND t.completed_at > now() - interval '30 days')
);
$$ LANGUAGE sql STABLE;
const { data } = await supabase.rpc('get_dashboard_stats', {
org_id: 'your-org-uuid'
})
Create materialized views for expensive aggregations:
CREATE MATERIALIZED VIEW leaderboard AS
SELECT
u.id,
u.username,
count(t.id) AS tasks_completed,
rank() OVER (ORDER BY count(t.id) DESC) AS rank
FROM users u
LEFT JOIN tasks t ON t.assignee_id = u.id AND t.status = 'done'
GROUP BY u.id, u.username;
CREATE UNIQUE INDEX idx_leaderboard_user ON leaderboard(id);
REFRESH MATERIALIZED VIEW CONCURRENTLY leaderboard;
Configure connection pooling with Supavisor:
import { Pool } from 'pg'
const pool = new Pool({
connectionString: 'postgres://postgres.[ref]:[pwd]@aws-0-[region].pooler.supabase.com:6543/postgres',
max: 5,
idleTimeoutMillis: 10000,
})
Use Edge Functions for compute-heavy operations close to data:
import { createClient } from 'https://esm.sh/@supabase/supabase-js@2'
Deno.serve(async (req) => {
const supabase = createClient(
Deno.env.get('SUPABASE_URL')!,
Deno.env.get('SUPABASE_SERVICE_ROLE_KEY')!
)
const { data } = await supabase.rpc('get_dashboard_stats', {
org_id: (await req.json()).org_id
})
return new Response(JSON.stringify(data), {
headers: { 'Content-Type': 'application/json' }
})
})
Enable read replicas on Pro+ plans for read-heavy workloads — route analytics and reporting queries to the replica to offload the primary.
Output
After completing these steps, you will have:
- Diagnostic baseline from
pg_stat_statements, index-usage, and cache-hit
- Targeted indexes on slow query columns, foreign keys, and common filter patterns
- Query plans verified with
EXPLAIN ANALYZE showing Index Scan instead of Seq Scan
- Client queries optimized with column selection, pagination, and joined queries
- RPC functions and materialized views for expensive server-side aggregations
- Connection pooling configured via Supavisor for serverless deployments
- Edge Functions deployed for compute-heavy operations near the database
Error Handling
| Symptom | Cause | Fix |
|---|
Seq Scan in EXPLAIN output on large table | Missing index on filtered/sorted column | CREATE INDEX on the column(s) in the WHERE/ORDER BY clause |
PGRST000: could not connect to server | Connection pool exhausted | Switch to Supavisor pooled connection string; reduce max pool size in serverless |
Slow RLS policies (visible in pg_stat_statements) | Subquery in policy evaluates per row | Refactor to security definer function or use EXISTS instead of IN |
| Response payloads > 1MB | select('*') returning all columns/rows | Use .select('col1, col2') and .range() for pagination |
| Stale materialized view data | View not refreshed after writes | Set up pg_cron or a cron Edge Function to run REFRESH MATERIALIZED VIEW CONCURRENTLY |
cache-hit ratio below 99% | Working set exceeds RAM (shared_buffers) | Upgrade compute add-on or optimize queries to access fewer pages |
| High latency on aggregation endpoints | Aggregation computed live on every request | Move to materialized view or RPC function; cache at the Edge Function layer |
Examples
Before/after index optimization:
EXPLAIN (ANALYZE) SELECT * FROM orders WHERE customer_id = 'abc-123';
CREATE INDEX idx_orders_customer ON orders(customer_id);
EXPLAIN (ANALYZE) SELECT * FROM orders WHERE customer_id = 'abc-123';
Client query optimization — eliminating N+1:
const { data: projects } = await supabase.from('projects').select('id, name')
for (const project of projects!) {
const { data: tasks } = await supabase
.from('tasks').select('*').eq('project_id', project.id)
}
const { data } = await supabase
.from('projects')
.select('id, name, tasks(id, title, status)')
.eq('organization_id', orgId)
Resources
Next Steps
- For RLS policy design, see
supabase-rls-policies
- For cost optimization, see
supabase-cost-tuning
- For real-time subscriptions, see
supabase-realtime
