Optimize Supabase API performance with caching, batching, and connection pooling.
Use when experiencing slow API responses, implementing caching strategies,
or optimizing request throughput for Supabase integrations.
Trigger with phrases like "supabase performance", "optimize supabase",
"supabase latency", "supabase caching", "supabase slow", "supabase batch".
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
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:
# Which indexes are actually being used?
npx supabase inspect db index-usage
# What percentage of queries are served from cache vs disk?
npx supabase inspect db cache-hit
# Tables consuming the most space
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 = '[email protected]';
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:
-- Optimizes: WHERE user_id = ? AND created_at > ? ORDER BY created_at DESC
CREATE INDEX idx_orders_user_created
ON orders(user_id, created_at DESC);
Create a partial index to cover a common filter pattern:
-- Only indexes incomplete todos — much smaller and faster than full-table index
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!
)
// BAD: fetches every column, large payloads
const { data } = await supabase.from('users').select('*')
// GOOD: only the columns you need
const { data } = await supabase.from('users').select('id, name, avatar_url')
Paginate with .range() instead of loading all rows:
Use RPC functions to push complex logic to Postgres:
-- Create a server-side function for an expensive aggregation
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;
// One network call instead of three separate queries
const { data } = await supabase.rpc('get_dashboard_stats', {
org_id: 'your-org-uuid'
})
Create materialized views for expensive aggregations:
-- Precompute a leaderboard instead of recalculating on every request
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 an index on the materialized view
CREATE UNIQUE INDEX idx_leaderboard_user ON leaderboard(id);
-- Refresh on a schedule (e.g., via pg_cron or a cron Edge Function)
REFRESH MATERIALIZED VIEW CONCURRENTLY leaderboard;
Configure connection pooling with Supavisor:
// For serverless environments (Vercel, Netlify, Cloudflare Workers):
// Use the pooled connection string with transaction mode
// Dashboard → Settings → Database → Connection string → "Transaction mode"
// The JS SDK uses PostgREST (HTTP) which has its own pooling — no config needed.
// Direct Postgres clients (Prisma, Drizzle, pg) need the pooled string:
import { Pool } from 'pg'
const pool = new Pool({
connectionString: 'postgres://postgres.[ref]:[pwd]@aws-0-[region].pooler.supabase.com:6543/postgres',
max: 5, // Keep low in serverless — Supavisor manages the upstream pool
idleTimeoutMillis: 10000,
})
Use Edge Functions for compute-heavy operations close to data:
// supabase/functions/generate-report/index.ts
// Edge Functions run in the same region as your database — low latency
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')!
)
// Heavy aggregation runs next to the database, not in the user's browser
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:
-- Before: 450ms, Seq Scan
EXPLAIN (ANALYZE) SELECT * FROM orders WHERE customer_id = 'abc-123';
-- Seq Scan on orders (cost=0.00..15234.00 rows=50 width=128) (actual time=0.015..450.123 rows=50 loops=1)
CREATE INDEX idx_orders_customer ON orders(customer_id);
-- After: 0.8ms, Index Scan
EXPLAIN (ANALYZE) SELECT * FROM orders WHERE customer_id = 'abc-123';
-- Index Scan using idx_orders_customer on orders (cost=0.42..8.44 rows=50 width=128) (actual time=0.025..0.812 rows=50 loops=1)
Client query optimization — eliminating N+1:
// BAD: N+1 — one query per project (10 projects = 11 queries)
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)
}
// GOOD: Single query with embedded join (1 query total)
const { data } = await supabase
.from('projects')
.select('id, name, tasks(id, title, status)')
.eq('organization_id', orgId)
