Supabase Performance Optimization: Best Practices

-- Identify slow queries first
-- Check queries taking > 100ms

-- Single column index
create index posts_user_id_idx on posts(user_id);

-- This speeds up queries like:
-- select * from posts where user_id = 'some-id';

-- Composite index (multiple columns)
create index posts_status_created_idx on posts(status, created_at desc);

-- Speeds up queries filtering by status and ordering by date:
-- select * from posts where status = 'published' order by created_at desc;

-- Partial index (conditional)
create index posts_published_idx on posts(created_at desc)
  where published = true;

-- Only indexes published posts, smaller and faster

-- Text search index (GIN)
create index posts_search_idx on posts using gin(to_tsvector('english', title || ' ' || content));

-- Speeds up full-text search

-- Unique index
create unique index users_email_idx on users(email);

-- Ensures uniqueness and speeds up lookups

-- View existing indexes
select
  schemaname,
  tablename,
  indexname,
  indexdef
from pg_indexes
where schemaname = 'public'
order by tablename, indexname;

-- Check index usage
select
  schemaname,
  tablename,
  indexname,
  idx_scan,  -- Number of times used
  idx_tup_read,  -- Tuples read
  idx_tup_fetch  -- Tuples fetched
from pg_stat_user_indexes
where schemaname = 'public'
order by idx_scan desc;

-- Drop unused index
drop index if exists posts_unused_idx;

// BAD: Selecting all columns when only need few
const { data } = await supabase
  .from('posts')
  .select('*')  // Fetches ALL columns

// GOOD: Select only needed columns
const { data } = await supabase
  .from('posts')
  .select('id, title, created_at')

// BAD: Multiple separate queries (N+1 problem)
const { data: posts } = await supabase.from('posts').select('*')
for (const post of posts) {
  const { data: author } = await supabase
    .from('profiles')
    .select('*')
    .eq('id', post.user_id)
    .single()
  // N+1 queries!
}

// GOOD: Use joins to fetch related data in one query
const { data: posts } = await supabase
  .from('posts')
  .select(`
    id,
    title,
    content,
    author:profiles(name, avatar_url)
  `)

// BAD: Fetching all records without limit
const { data } = await supabase
  .from('posts')
  .select('*')  // Could return 10,000+ records!

// GOOD: Always use limits
const { data } = await supabase
  .from('posts')
  .select('*')
  .limit(20)

// Use EXPLAIN ANALYZE to profile queries
-- Run in SQL Editor
explain analyze
select * from posts where user_id = 'some-id';

-- Look for:
-- - Seq Scan (bad) vs Index Scan (good)
-- - Execution time
-- - Rows returned vs estimated

// Supabase provides two connection modes:

// 1. Session Mode (Direct Connection)
// - Default for most apps
// - Max ~60 connections
// - Use for: Small to medium apps
const supabase = createClient(
  'https://your-project.supabase.co',
  'your-anon-key'
)

// 2. Transaction Mode (Connection Pooler)
// - For high-traffic apps
// - Supports 1000+ concurrent connections
// - Use for: Production apps with many users

// Enable in Supabase Dashboard:
// Settings > Database > Connection Pooling
// Use the pooler connection string

// Connection string format:
// postgresql://postgres.xxxxx:[email protected]:6543/postgres

// In Next.js API routes or serverless functions
import { createClient } from '@supabase/supabase-js'

export default async function handler(req, res) {
  // Create client per request
  const supabase = createClient(
    process.env.SUPABASE_URL,
    process.env.SUPABASE_SERVICE_KEY
  )

  // Use client
  const { data } = await supabase.from('posts').select('*')

  res.json(data)
  // Connection automatically released
}

// For server-side applications with connection pools
import { Pool } from 'pg'

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 20,  // Maximum pool size
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
})

// Use pooled connection
const client = await pool.connect()
try {
  const result = await client.query('SELECT * FROM posts')
  return result.rows
} finally {
  client.release()
}

// Client-side caching with React Query
import { useQuery } from '@tanstack/react-query'
import { supabase } from './supabaseClient'

function usePosts() {
  return useQuery({
    queryKey: ['posts'],
    queryFn: async () => {
      const { data } = await supabase
        .from('posts')
        .select('*')
        .order('created_at', { ascending: false })
      return data
    },
    staleTime: 5 * 60 * 1000,  // Cache for 5 minutes
    cacheTime: 10 * 60 * 1000,  // Keep in cache for 10 minutes
  })
}

// Server-side caching with Redis
import Redis from 'ioredis'

const redis = new Redis(process.env.REDIS_URL)

async function getCachedPosts() {
  const cacheKey = 'posts:all'
  
  // Try cache first
  const cached = await redis.get(cacheKey)
  if (cached) {
    return JSON.parse(cached)
  }

  // Fetch from database
  const { data } = await supabase
    .from('posts')
    .select('*')

  // Cache for 5 minutes
  await redis.setex(cacheKey, 300, JSON.stringify(data))

  return data
}

// Invalidate cache on updates
async function createPost(post) {
  const { data } = await supabase
    .from('posts')
    .insert(post)
    .select()

  // Invalidate cache
  await redis.del('posts:all')

  return data
}

// CDN caching for public data
// Next.js example with revalidation
export async function getStaticProps() {
  const { data } = await supabase
    .from('posts')
    .select('*')
    .eq('published', true)

  return {
    props: { posts: data },
    revalidate: 60,  // Regenerate page every 60 seconds
  }
}

// BAD: Subscribe to all table changes
const subscription = supabase
  .channel('all-posts')
  .on('postgres_changes', {
    event: '*',
    schema: 'public',
    table: 'posts'
  }, (payload) => {
    // Receives ALL changes from ALL users
  })
  .subscribe()

// GOOD: Filter subscriptions
const userId = 'current-user-id'

const subscription = supabase
  .channel(`user-${userId}-posts`)
  .on('postgres_changes', {
    event: '*',
    schema: 'public',
    table: 'posts',
    filter: `user_id=eq.${userId}`  // Only current user's posts
  }, (payload) => {
    // Receives only relevant changes
  })
  .subscribe()

// GOOD: Subscribe to specific events
const subscription = supabase
  .channel('new-posts')
  .on('postgres_changes', {
    event: 'INSERT',  // Only new posts
    schema: 'public',
    table: 'posts'
  }, (payload) => {
    // Handle new post
  })
  .subscribe()

// Batch updates to reduce re-renders
import { useState, useEffect } from 'react'
import { useDebounce } from 'use-debounce'

function PostsList() {
  const [posts, setPosts] = useState([])
  const [debouncedPosts] = useDebounce(posts, 300)  // Batch updates

  useEffect(() => {
    const subscription = supabase
      .channel('posts-changes')
      .on('postgres_changes', {
        event: '*',
        schema: 'public',
        table: 'posts'
      }, (payload) => {
        setPosts(current => [...current, payload.new])
        // Debounced, so multiple rapid changes batched
      })
      .subscribe()

    return () => subscription.unsubscribe()
  }, [])

  return debouncedPosts.map(post => <PostCard key={post.id} {...post} />)
}

// Cleanup subscriptions properly
useEffect(() => {
  const subscription = supabase
    .channel('my-channel')
    .on('postgres_changes', {...}, handler)
    .subscribe()

  return () => {
    subscription.unsubscribe()
  }
}, [])

-- Query performance statistics
select
  query,
  calls,
  total_exec_time,
  mean_exec_time,
  max_exec_time
from pg_stat_statements
order by mean_exec_time desc
limit 20;

-- Slow queries (> 100ms average)
select
  query,
  calls,
  mean_exec_time as avg_ms,
  max_exec_time as max_ms
from pg_stat_statements
where mean_exec_time > 100
order by mean_exec_time desc;

-- Table sizes
select
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size,
  pg_total_relation_size(schemaname||'.'||tablename) as bytes
from pg_tables
where schemaname = 'public'
order by bytes desc;

-- Index usage statistics
select
  schemaname,
  tablename,
  indexname,
  idx_scan as scans,
  pg_size_pretty(pg_relation_size(indexrelid)) as size
from pg_stat_user_indexes
where schemaname = 'public'
order by idx_scan asc;  -- Unused indexes at top

-- Cache hit ratio (should be > 99%)
select
  sum(heap_blks_read) as heap_read,
  sum(heap_blks_hit) as heap_hit,
  sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as ratio
from pg_statio_user_tables;

-- Active connections
select
  count(*) as connections,
  state
from pg_stat_activity
group by state;