How to Scale Supabase to 100K+ Users: Complete Production Guide

Essential Indexes

-- Always index foreign keys
CREATE INDEX idx_posts_user_id ON posts(user_id);
CREATE INDEX idx_comments_post_id ON comments(post_id);
CREATE INDEX idx_orders_customer_id ON orders(customer_id);

-- Why: Dramatically speeds up JOIN queries

-- Index columns you filter on frequently
CREATE INDEX idx_posts_status ON posts(status);
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_created_at ON orders(created_at);

-- Why: WHERE clauses use these indexes for fast lookups

-- Combine multiple columns you query together
CREATE INDEX idx_posts_user_status
  ON posts(user_id, status);

CREATE INDEX idx_orders_customer_status_date
  ON orders(customer_id, status, created_at DESC);

-- Why: Optimizes queries filtering on multiple columns

-- For search functionality
CREATE INDEX idx_posts_search
  ON posts USING GIN (to_tsvector('english', title || ' ' || content));

-- Usage:
SELECT * FROM posts
WHERE to_tsvector('english', title || ' ' || content)
  @@ plainto_tsquery('english', 'search term');

-- Why: Enables fast full-text search without third-party services

Use EXPLAIN ANALYZE

This is your best friend for finding slow queries. Run it in the Supabase SQL Editor:

EXPLAIN ANALYZE
SELECT p.*, u.name, u.avatar
FROM posts p
JOIN users u ON p.user_id = u.id
WHERE p.status = 'published'
  AND p.created_at > NOW() - INTERVAL '7 days'
ORDER BY p.created_at DESC
LIMIT 20;

Look for "Seq Scan" (table scan) - this means it's reading every row. Add indexes to convert to "Index Scan".

Avoid SELECT *

-- ❌ Bad: Returns all columns (slower, more data transfer)
const { data } = await supabase
  .from('posts')
  .select('*')

// ✅ Good: Only select what you need
const { data } = await supabase
  .from('posts')
  .select('id, title, created_at, user:users(name, avatar)')
  .eq('status', 'published')

Use Pagination

// ❌ Bad: Loading thousands of records at once
const { data } = await supabase
  .from('posts')
  .select('*')

// ✅ Good: Paginate with range
const { data } = await supabase
  .from('posts')
  .select('*')
  .range(0, 19) // First 20 items
  .order('created_at', { ascending: false })

Enable Connection Pooling in Supabase

1. Go to Project Settings → Database
2. Copy the "Connection pooling" connection string (port 6543)
3. Use this for serverless functions and high-traffic apps

// Use pooler connection string
const supabase = createClient(
  'https://xxxxx.supabase.co',
  'your-anon-key',
  {
    db: {
      schema: 'public',
    },
    global: {
      headers: { 'x-connection-encrypted': 'true' },
    },
  }
)

Result: Can handle 10,000+ concurrent connections instead of just 100

For expensive aggregate queries (dashboards, reports), use materialized views:

-- Create materialized view
CREATE MATERIALIZED VIEW daily_stats AS
SELECT
  DATE(created_at) as date,
  COUNT(*) as total_orders,
  SUM(amount) as total_revenue,
  COUNT(DISTINCT customer_id) as unique_customers
FROM orders
GROUP BY DATE(created_at);

-- Create index on materialized view
CREATE INDEX idx_daily_stats_date ON daily_stats(date DESC);

-- Refresh periodically (via cron or manually)
REFRESH MATERIALIZED VIEW daily_stats;

Why: Complex aggregations computed once, not on every query. Reduced dashboard load time from 12s to 200ms on one project.

Configure JWT Expiry

Default JWT expires in 1 hour. For better UX at scale, increase it:

1. Go to Authentication → Settings
2. Set JWT expiry to 7 days (604800 seconds)
3. Enable automatic token refresh in your client

Why: Reduces auth server load and improves user experience (fewer re-logins)

Optimize RLS Policies

Row Level Security is powerful but can slow down queries if poorly written:

-- ❌ Bad: Subquery in RLS policy (slow)
CREATE POLICY "Users can read their posts"
  ON posts FOR SELECT
  USING (
    user_id IN (SELECT id FROM users WHERE auth.uid() = id)
  );

-- ✅ Good: Direct comparison (fast)
CREATE POLICY "Users can read their posts"
  ON posts FOR SELECT
  USING (user_id = auth.uid());

-- ✅ Even Better: Add index on user_id
CREATE INDEX idx_posts_user_id ON posts(user_id);

Service Role for Background Jobs

For cron jobs and background tasks, use the service role key to bypass RLS:

// Use service role for admin operations
const supabaseAdmin = createClient(
  process.env.SUPABASE_URL!,
  process.env.SUPABASE_SERVICE_ROLE_KEY!, // Bypasses RLS
  {
    auth: {
      autoRefreshToken: false,
      persistSession: false
    }
  }
)

// Clean up old sessions
await supabaseAdmin
  .from('sessions')
  .delete()
  .lt('expires_at', new Date().toISOString())

Warning: Never expose service role key to client! Only use server-side.

1. Subscribe to Specific Rows Only

// ❌ Bad: Subscribes to ALL posts (sends tons of unnecessary data)
const channel = supabase
  .channel('posts')
  .on('postgres_changes',
    { event: '*', schema: 'public', table: 'posts' },
    (payload) => console.log(payload)
  )
  .subscribe()

// ✅ Good: Subscribe only to relevant posts
const channel = supabase
  .channel('user-posts')
  .on('postgres_changes',
    {
      event: '*',
      schema: 'public',
      table: 'posts',
      filter: `user_id=eq.${userId}` // Only user's posts
    },
    (payload) => console.log(payload)
  )
  .subscribe()

2. Unsubscribe When Component Unmounts

// React example
useEffect(() => {
  const channel = supabase
    .channel('my-channel')
    .on('postgres_changes', { /* ... */ }, handleChange)
    .subscribe()

  // Clean up subscription
  return () => {
    supabase.removeChannel(channel)
  }
}, [])

// Why: Prevents memory leaks and reduces server load

3. Use Presence for User Status

For "who's online" features, use Presence instead of database polling:

const channel = supabase.channel('room-1')

// Track user presence
channel
  .on('presence', { event: 'sync' }, () => {
    const state = channel.presenceState()
    console.log('Online users:', state)
  })
  .subscribe(async (status) => {
    if (status === 'SUBSCRIBED') {
      await channel.track({
        user_id: userId,
        online_at: new Date().toISOString()
      })
    }
  })

Result: Real-time user presence without database queries

1. Image Optimization

Supabase Storage supports image transformations. Use them to reduce bandwidth:

// ❌ Bad: Loading full-size image
const { data } = supabase.storage
  .from('avatars')
  .getPublicUrl('user-123.jpg')

// ✅ Good: Resize on-the-fly
const { data } = supabase.storage
  .from('avatars')
  .getPublicUrl('user-123.jpg', {
    transform: {
      width: 200,
      height: 200,
      quality: 80,
      format: 'webp' // Modern format, smaller size
    }
  })

2. CDN Integration

Supabase Storage uses Cloudflare CDN by default, but you can further optimize:

• Enable "Public" buckets for static assets (faster, cacheable)
• Use signed URLs for private files (secure but still cached)
• Set appropriate cache headers on upload

3. Upload Size Limits

// Set file size limits in bucket policies
{
  "Version": "1.0",
  "Statement": [
    {
      "Effect": "Allow",
      "Principal": { "id": "*" },
      "Action": ["storage.objects.create"],
      "Resource": ["storage.objects/*"],
      "Condition": {
        "NumericLessThanEquals": {
          "storage:object-size": 10485760  // 10MB limit
        }
      }
    }
  ]
}

Essential RLS Policies

-- Enable RLS on all tables
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;
ALTER TABLE comments ENABLE ROW LEVEL SECURITY;

-- Users can read all published posts
CREATE POLICY "Public posts are viewable by everyone"
  ON posts FOR SELECT
  USING (status = 'published');

-- Users can only update their own posts
CREATE POLICY "Users can update own posts"
  ON posts FOR UPDATE
  USING (auth.uid() = user_id);

-- Users can delete their own posts
CREATE POLICY "Users can delete own posts"
  ON posts FOR DELETE
  USING (auth.uid() = user_id);

-- Users can insert posts for themselves
CREATE POLICY "Users can create posts"
  ON posts FOR INSERT
  WITH CHECK (auth.uid() = user_id);

Multi-Tenant RLS Pattern

For SaaS apps, ensure users only see their organization's data:

-- Create helper function
CREATE OR REPLACE FUNCTION auth.user_org_id()
RETURNS UUID AS $$
  SELECT org_id FROM users WHERE id = auth.uid()
$$ LANGUAGE SQL STABLE;

-- Apply to all tables
CREATE POLICY "Users see only their org data"
  ON customers FOR SELECT
  USING (org_id = auth.user_org_id());

CREATE POLICY "Users see only their org data"
  ON orders FOR SELECT
  USING (org_id = auth.user_org_id());

Key Metrics to Monitor

Using pg_stat_statements

Enable this extension to find your slowest queries:

-- Enable extension
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Find slowest queries
SELECT
  query,
  calls,
  total_exec_time,
  mean_exec_time,
  max_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

-- Reset stats
SELECT pg_stat_statements_reset();

Set Up Alerts

I use these alert thresholds in production:

• Database CPU usage > 80% for 5 minutes
• Active connections > 90% of limit
• Average query time > 500ms
• Error rate > 1% of total requests
• Disk usage > 85%