Supabase Performance Optimization: Speed Up Your Database Queries

Why Supabase Queries Slow Down

A freshly migrated database often starts fast, then slows as data grows. The culprits are almost always the same: missing indexes, N+1 query patterns, too many concurrent connections, or fetching more data than needed. Here's how to fix each one.

1. Identify Slow Queries First

Enable pg_stat_statements in Supabase to track query performance:

-- In Supabase SQL Editor
SELECT
  query,
  calls,
  round(mean_exec_time::numeric, 2) AS avg_ms,
  round(total_exec_time::numeric, 2) AS total_ms,
  rows
FROM pg_stat_statements
WHERE query NOT LIKE '%pg_stat%'
ORDER BY mean_exec_time DESC
LIMIT 20;

2. Add the Right Indexes

Indexes are the biggest performance lever. Use EXPLAIN ANALYZE to confirm you're hitting sequential scans:

EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 'abc' ORDER BY created_at DESC LIMIT 10;

If you see Seq Scan instead of Index Scan, add an index:

-- Composite index for filter + sort pattern
CREATE INDEX CONCURRENTLY idx_orders_user_created
ON orders(user_id, created_at DESC);

-- Partial index for filtered queries (only pending orders)
CREATE INDEX CONCURRENTLY idx_orders_pending
ON orders(created_at DESC)
WHERE status = 'pending';

-- Full-text search index
CREATE INDEX idx_posts_search
ON posts USING GIN(to_tsvector('english', title || ' ' || content));

3. Select Only What You Need

Avoid select('*') in production. Fetching all columns transfers unnecessary data:

// Bad: fetches all columns including large content fields
const { data } = await supabase.from('posts').select('*')

// Good: fetch only what the list view needs
const { data } = await supabase
  .from('posts')
  .select('id, title, slug, excerpt, created_at, author')

4. Fix N+1 Query Patterns

N+1 is when you fetch a list, then query related data for each item separately. Use Supabase's JOIN syntax instead:

// Bad: N+1 (1 query for orders + N queries for each user)
const orders = await supabase.from('orders').select('*')
for (const order of orders.data) {
  const user = await supabase.from('users').select('name').eq('id', order.user_id)
}

// Good: single query with JOIN
const { data } = await supabase
  .from('orders')
  .select('*, users(name, email)')

5. Implement Query Caching

For data that doesn't change frequently, cache at the application layer:

// Next.js: Cache with revalidation
import { unstable_cache } from 'next/cache'

const getPublicPosts = unstable_cache(
  async () => {
    const { data } = await supabase
      .from('posts')
      .select('id, title, slug, excerpt, created_at')
      .eq('published', true)
      .order('created_at', { ascending: false })
    return data
  },
  ['public-posts'],
  { revalidate: 300 } // Cache for 5 minutes
)

// In a Server Component:
const posts = await getPublicPosts()

6. Use Connection Pooling Correctly

# Use the pooler URL (port 6543) for your application
# This routes through PgBouncer and reuses connections

DATABASE_URL=postgresql://postgres.ref:[pass]@pooler.supabase.com:6543/postgres

# Use the direct URL only for migrations
DIRECT_URL=postgresql://postgres.ref:[pass]@db.supabase.com:5432/postgres

7. Paginate Large Result Sets

// Never fetch all rows from a large table
const { data, count } = await supabase
  .from('orders')
  .select('*', { count: 'exact' })
  .range(0, 19) // First 20 rows
  .order('created_at', { ascending: false })

8. Use Database Functions for Complex Logic

Move complex multi-step operations into PostgreSQL functions to reduce round-trips:

-- PostgreSQL function
CREATE OR REPLACE FUNCTION get_user_dashboard(p_user_id UUID)
RETURNS JSON AS $$
BEGIN
  RETURN json_build_object(
    'orders', (SELECT count(*) FROM orders WHERE user_id = p_user_id),
    'total_spent', (SELECT sum(total) FROM orders WHERE user_id = p_user_id)
  );
END;
$$ LANGUAGE plpgsql;

-- Call from client
const { data } = await supabase.rpc('get_user_dashboard', { p_user_id: userId })