supabase-load-scale

What this skill does

# Supabase Load & Scale

## Overview

Supabase scaling operates at six layers: **read replicas** (offload analytics and reporting queries), **connection pooling** (Supavisor pgBouncer replacement with transaction/session modes), **compute upgrades** (vCPU/RAM tiers), **CDN for Storage** (cache public bucket assets at the edge), **Edge Function regions** (deploy functions closer to users), and **table partitioning** (split billion-row tables for query performance). This skill covers each layer with real `createClient` configuration, SQL, and CLI commands.

## Prerequisites

- Supabase project on a Pro plan or higher (read replicas require Pro+)
- `@supabase/supabase-js` v2+ installed
- `supabase` CLI installed and linked to your project
- Database access via `psql` or Supabase SQL Editor
- TypeScript project with generated database types

## Step 1 — Read Replicas and Connection Pooling

Read replicas let you route read-heavy queries (dashboards, reports, search) to replica databases while keeping writes on the primary. Supabase uses **Supavisor** (their pgBouncer replacement) for connection pooling with two modes: **transaction** (default, shares connections between requests) and **session** (holds a connection per client session, needed for prepared statements).

### Configure the Read Replica Client

```typescript
// lib/supabase.ts
import { createClient } from '@supabase/supabase-js'
import type { Database } from './database.types'

// Primary client — handles all writes and real-time subscriptions
export const supabase = createClient<Database>(
  process.env.SUPABASE_URL!,
  process.env.SUPABASE_ANON_KEY!
)

// Read replica client — use for analytics, dashboards, search
// The read replica URL is available in Dashboard > Settings > Database
export const supabaseReadOnly = createClient<Database>(
  process.env.SUPABASE_READ_REPLICA_URL!,  // e.g., https://<project-ref>-ro.supabase.co
  process.env.SUPABASE_ANON_KEY!,          // same anon key works for replicas
  {
    db: { schema: 'public' },
    // Replicas may have slight lag (typically <100ms)
    // Do NOT use for reads-after-writes in the same request
  }
)

// Server-side admin client with connection pooling via Supavisor
// Use the pooled connection string (port 6543) instead of direct (port 5432)
export const supabaseAdmin = createClient<Database>(
  process.env.SUPABASE_URL!,
  process.env.SUPABASE_SERVICE_ROLE_KEY!,
  {
    auth: { autoRefreshToken: false, persistSession: false },
    db: { schema: 'public' },
  }
)
```

### Direct Postgres Connections via Supavisor Pooling

```bash
# Transaction mode (default, port 6543) — best for serverless/short-lived connections
# Shares connections across clients. Cannot use prepared statements.
psql "postgresql://postgres.[project-ref]:[password]@aws-0-us-east-1.pooler.supabase.com:6543/postgres"

# Session mode (port 5432 via pooler) — needed for prepared statements, LISTEN/NOTIFY
# One-to-one connection mapping per client.
psql "postgresql://postgres.[project-ref]:[password]@aws-0-us-east-1.pooler.supabase.com:5432/postgres"

# Direct connection (no pooling) — for migrations and admin tasks only
psql "postgresql://postgres:[password]@db.[project-ref].supabase.co:5432/postgres"
```

### Route Queries to the Right Target

```typescript
// services/analytics.ts
import { supabaseReadOnly } from '../lib/supabase'

// Heavy analytics queries go to the read replica
export async function getDashboardMetrics(orgId: string) {
  const { data, error } = await supabaseReadOnly
    .from('events')
    .select('event_type, count:id.count()')
    .eq('org_id', orgId)
    .gte('created_at', new Date(Date.now() - 24 * 60 * 60 * 1000).toISOString())

  if (error) throw new Error(`Dashboard query failed: ${error.message}`)
  return data
}

// services/orders.ts
import { supabase } from '../lib/supabase'

// Writes always go to the primary
export async function createOrder(order: OrderInsert) {
  const { data, error } = await supabase
    .from('orders')
    .insert(order)
    .select('id, status, total, created_at')
    .single()

  if (error) throw new Error(`Order creation failed: ${error.message}`)
  return data
}
```

### Monitor Connection Pool Usage

```sql
-- Check active connections by source (run in SQL Editor)
SELECT
  usename,
  application_name,
  client_addr,
  state,
  count(*) AS connections
FROM pg_stat_activity
WHERE datname = 'postgres'
GROUP BY usename, application_name, client_addr, state
ORDER BY connections DESC;

-- Check connection limits for your compute tier
SHOW max_connections;
-- Micro: 60, Small: 90, Medium: 120, Large: 160, XL: 240, 2XL: 380, 4XL: 480
```

## Step 2 — Compute Upgrades, CDN for Storage, and Edge Function Regions

### Compute Size Selection Guide

| Tier | vCPU | RAM | Max Connections | Best For |
|------|------|-----|----------------|----------|
| Micro (Free) | 2 shared | 1 GB | 60 | Development, prototypes |
| Small (Pro) | 2 dedicated | 2 GB | 90 | Low-traffic production |
| Medium | 2 dedicated | 4 GB | 120 | Growing apps, moderate traffic |
| Large | 4 dedicated | 8 GB | 160 | High-traffic, complex queries |
| XL | 8 dedicated | 16 GB | 240 | Large datasets, concurrent users |
| 2XL | 16 dedicated | 32 GB | 380 | Enterprise, heavy analytics |
| 4XL | 32 dedicated | 64 GB | 480 | Mission-critical, max throughput |

```bash
# Upgrade compute via CLI (requires Pro plan)
supabase projects update --experimental --compute-size small  # or medium, large, xl, 2xl, 4xl

# Check current compute size
supabase projects list
```

### CDN Caching for Storage Buckets

Public buckets are automatically served through Supabase's CDN. Optimize cache behavior with proper headers and transforms.

```typescript
import { createClient } from '@supabase/supabase-js'

const supabase = createClient(
  process.env.SUPABASE_URL!,
  process.env.SUPABASE_ANON_KEY!
)

// Upload with cache-control headers for CDN optimization
async function uploadPublicAsset(
  bucket: string,
  path: string,
  file: File
) {
  const { data, error } = await supabase.storage
    .from(bucket)
    .upload(path, file, {
      cacheControl: '31536000',  // 1 year cache for immutable assets
      upsert: false,             // prevent accidental overwrites
      contentType: file.type,
    })

  if (error) throw new Error(`Upload failed: ${error.message}`)

  // Get the CDN-cached public URL
  const { data: { publicUrl } } = supabase.storage
    .from(bucket)
    .getPublicUrl(path, {
      transform: {
        width: 800,        // Image transforms are cached at the CDN edge
        quality: 80,
        format: 'webp',
      },
    })

  return { path: data.path, publicUrl }
}

// Bust cache by uploading to a new path (content-addressed)
import { createHash } from 'crypto'

async function uploadVersionedAsset(bucket: string, file: Buffer, ext: string) {
  const hash = createHash('sha256').update(file).digest('hex').slice(0, 12)
  const path = `assets/${hash}.${ext}`

  const { error } = await supabase.storage
    .from(bucket)
    .upload(path, file, {
      cacheControl: '31536000',
      upsert: false,
    })

  if (error && error.message !== 'The resource already exists') {
    throw new Error(`Versioned upload failed: ${error.message}`)
  }

  return supabase.storage.from(bucket).getPublicUrl(path).data.publicUrl
}
```

### Edge Function Regional Deployment

```bash
# Deploy to a specific region (closer to your users)
supabase functions deploy my-function --region us-east-1
supabase functions deploy my-function --region eu-west-1
supabase functions deploy my-function --region ap-southeast-1

# List available regions
supabase functions list

# Deploy all functions to a region
supabase functions deploy --region us-east-1
```

```typescript
// supabase/functions/geo-router/index.ts
// Edge Function that runs in the region closest to the user
import { createClient } from 'https://esm.sh/@supabase/supabase-js@2'

Deno.serve(async (req) => {
  const supabase = creat