Jeremylongshore Claude Code Plugins Plus Skills Supabase Cost Tuning

Supabase Cost Tuning

Overview

Reduce Supabase spend by auditing usage against plan limits, eliminating database and storage waste, and right-sizing compute resources. The three biggest levers: database optimization (vacuum, index cleanup, archival), storage lifecycle management (compress before upload, orphan cleanup), and connection pooling to reduce compute add-on requirements.

Prerequisites

• Supabase project with Dashboard access (Settings > Billing)
• @supabase/supabase-js installed: npm install @supabase/supabase-js
• Service role key for admin operations (storage audit, cleanup scripts)
• SQL editor access (Dashboard > SQL Editor or psql connection)

Pricing Reference

Compute add-ons (Pro and above):

Decision framework: Read replicas ($25/mo each) beat scaling up when reads dominate and you need geographic distribution. Connection pooling (Supavisor, free) reduces compute pressure from idle connections.

Instructions

Step 1: Audit Current Usage and Identify Cost Drivers

Run these queries in the SQL Editor to understand where your database budget is going:

-- Total database size
select pg_size_pretty(pg_database_size(current_database())) as total_db_size;

-- Database size by table (find the biggest offenders)
select
  relname as table_name,
  pg_size_pretty(pg_total_relation_size(relid)) as total_size,
  pg_size_pretty(pg_relation_size(relid)) as table_size,
  pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) as index_size,
  n_live_tup as row_count
from pg_stat_user_tables
order by pg_total_relation_size(relid) desc
limit 20;

-- Find unused indexes consuming space (zero scans since last stats reset)
select
  schemaname || '.' || indexrelname as index_name,
  pg_size_pretty(pg_relation_size(indexrelid)) as size,
  idx_scan as scans_since_reset
from pg_stat_user_indexes
where idx_scan = 0
  and schemaname = 'public'
order by pg_relation_size(indexrelid) desc
limit 10;

-- Check dead tuple bloat (high ratio means VACUUM is needed)
select
  relname,
  n_dead_tup,
  n_live_tup,
  round(n_dead_tup::numeric / greatest(n_live_tup, 1) * 100, 1) as dead_pct
from pg_stat_user_tables
where n_dead_tup > 1000
order by n_dead_tup desc;

-- Connection count (high count may indicate pooling issues)
select count(*) as active_connections,
  max_conn as max_allowed
from pg_stat_activity,
  (select setting::int as max_conn from pg_settings where name = 'max_connections') mc
group by max_conn;

Audit storage usage programmatically:

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

const supabaseAdmin = createClient(
  process.env.SUPABASE_URL!,
  process.env.SUPABASE_SERVICE_ROLE_KEY!
)

// List storage usage per bucket
const { data: buckets } = await supabaseAdmin.storage.listBuckets()

for (const bucket of buckets ?? []) {
  const { data: files } = await supabaseAdmin.storage
    .from(bucket.name)
    .list('', { limit: 1000 })

  const totalSize = files?.reduce((sum, f) => sum + (f.metadata?.size || 0), 0) ?? 0
  console.log(`${bucket.name}: ${(totalSize / 1024 / 1024).toFixed(1)} MB`)
}

Check your current spend: Dashboard > Settings > Billing shows usage against plan limits with a breakdown by resource category.

Step 2: Optimize Database, Storage, and Bandwidth

Database optimization — reclaim space and reduce bloat:

-- Archive old data before deleting (preserve for compliance/analytics)
create table if not exists public.events_archive (like public.events including all);

insert into public.events_archive
select * from public.events
where created_at < now() - interval '6 months';

delete from public.events
where created_at < now() - interval '6 months';

-- Run VACUUM ANALYZE to reclaim space and update query planner stats
vacuum (verbose, analyze) public.events;

-- Drop confirmed-unused indexes (verify idx_scan = 0 from Step 1)
-- WARNING: always confirm the index is unused before dropping
drop index if exists idx_events_legacy_status;

-- Remove soft-deleted records past retention period
delete from public.orders
where deleted_at is not null
  and deleted_at < now() - interval '90 days';

vacuum (analyze) public.orders;

Storage optimization — compress before upload, clean orphans:

// Compress images before upload (reduces storage + bandwidth)
async function uploadCompressed(
  bucket: string,
  path: string,
  file: File
): Promise<string> {
  // Use client-side compression before uploading
  const compressed = await compressImage(file, { maxWidth: 1920, quality: 0.8 })

  const { data, error } = await supabaseAdmin.storage
    .from(bucket)
    .upload(path, compressed, {
      contentType: file.type,
      upsert: true,
    })

  if (error) throw error
  return data.path
}

// Clean orphaned files older than 30 days
async function cleanOrphanedUploads() {
  const cutoff = new Date(Date.now() - 30 * 24 * 60 * 60 * 1000).toISOString()

  const { data: orphans } = await supabaseAdmin
    .from('storage.objects')
    .select('name, created_at')
    .eq('bucket_id', 'uploads')
    .lt('created_at', cutoff)

  if (orphans?.length) {
    const paths = orphans.map(o => o.name)
    // Delete in batches of 100
    for (let i = 0; i < paths.length; i += 100) {
      await supabaseAdmin.storage
        .from('uploads')
        .remove(paths.slice(i, i + 100))
    }
    console.log(`Cleaned ${orphans.length} orphaned files`)
  }
}

Bandwidth reduction — select only what you need:

// BAD: transfers entire row (wastes bandwidth)
const { data } = await supabase.from('products').select('*')

// GOOD: request only needed columns
const { data } = await supabase.from('products').select('id, name, price')

// Use count queries for totals (head: true = zero data transferred)
const { count } = await supabase
  .from('orders')
  .select('*', { count: 'exact', head: true })

// Paginate large result sets
const { data } = await supabase
  .from('logs')
  .select('id, message, created_at')
  .order('created_at', { ascending: false })
  .range(0, 49)  // 50 rows per page

Step 3: Right-Size Compute and Reduce Edge Function Costs

Connection pooling with Supavisor (reduces need for compute upgrades):

// Use the pooler connection string instead of direct connection
// Dashboard > Settings > Database > Connection string > Mode: Transaction

// In your app, use the pooled connection URL (port 6543)
// Direct:   postgresql://postgres:pw@db.xxx.supabase.co:5432/postgres
// Pooled:   postgresql://postgres:pw@db.xxx.supabase.co:6543/postgres

// For @supabase/supabase-js, connection pooling is handled automatically
// For direct pg connections (migrations, ORMs), use pooled URL:
const pool = new Pool({
  connectionString: process.env.DATABASE_URL,  // Use pooler URL
  max: 10,  // Limit client-side pool size too
})

Edge Function cold start reduction:

// Minimize cold starts — keep imports lightweight
// BAD: importing heavy libraries unconditionally
import { parse } from 'some-huge-csv-library'

// GOOD: dynamic import only when needed
Deno.serve(async (req) => {
  const { action } = await req.json()

  if (action === 'parse-csv') {
    const { parse } = await import('some-huge-csv-library')
    return new Response(JSON.stringify(parse(data)))
  }

  // Fast path: no heavy import needed
  return new Response(JSON.stringify({ status: 'ok' }))
})

// Cache expensive computations across invocations
// Deno Deploy isolates persist for ~60 seconds between requests
const _cache = new Map<string, { data: unknown; ts: number }>()

function cached<T>(key: string, ttlMs: number, fn: () => T): T {
  const entry = _cache.get(key)
  if (entry && Date.now() - entry.ts < ttlMs) return entry.data as T
  const data = fn()
  _cache.set(key, { data, ts: Date.now() })
  return data
}

Usage monitoring — track spend with a lightweight counter:

-- Create usage tracking table
create table public.api_usage (
  id bigint generated always as identity primary key,
  endpoint text not null,
  method text not null,
  user_id uuid references auth.users(id),
  response_bytes int default 0,
  created_at timestamptz default now()
);

-- Create partitioned index for efficient time-range queries
create index idx_api_usage_created on public.api_usage (created_at desc);

-- Materialized view for daily cost estimation
create materialized view public.daily_usage_summary as
select
  date_trunc('day', created_at) as day,
  endpoint,
  count(*) as requests,
  sum(response_bytes) as total_bytes
from public.api_usage
group by 1, 2;

-- Auto-refresh via pg_cron (enable extension first)
select cron.schedule(
  'refresh-usage-summary',
  '0 1 * * *',
  'refresh materialized view concurrently public.daily_usage_summary;'
);

Output

After completing all three steps, you will have:

• Database size audit with table-level breakdown and dead tuple analysis
• Unused indexes identified and dropped to reclaim storage
• Old data archived and vacuumed to free database space
• Storage orphans cleaned and upload compression implemented
• Bandwidth reduced through column selection and pagination
• Connection pooling configured to avoid unnecessary compute upgrades
• Edge Function cold starts minimized with dynamic imports and caching
• Usage monitoring table and daily summary view for spend visibility

Error Handling

Examples

Quick cost check for a growing project:

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

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

// Check database size against plan limit
const { data: dbSize } = await supabase.rpc('get_db_size')
// CREATE FUNCTION get_db_size() RETURNS text AS $$
//   SELECT pg_size_pretty(pg_database_size(current_database()));
// $$ LANGUAGE sql;

console.log(`Database size: ${dbSize}`)

// Check if you're approaching storage limits
const { data: buckets } = await supabase.storage.listBuckets()
console.log(`Storage buckets: ${buckets?.length ?? 0}`)

Monthly cost estimation script:

function estimateMonthlyCost(usage: {
  dbSizeGb: number
  storageGb: number
  bandwidthGb: number
  edgeFnInvocations: number
  mau: number
}) {
  const pro = {
    base: 25,
    dbOverage: Math.max(0, usage.dbSizeGb - 8) * 0.125,
    storageOverage: Math.max(0, usage.storageGb - 100) * 0.021,
    bandwidthOverage: Math.max(0, usage.bandwidthGb - 250) * 0.09,
    edgeFnOverage: Math.max(0, usage.edgeFnInvocations - 2_000_000) / 1_000_000 * 2,
  }

  const total = pro.base + pro.dbOverage + pro.storageOverage
    + pro.bandwidthOverage + pro.edgeFnOverage

  console.log('Estimated monthly cost breakdown:')
  console.log(`  Base Pro plan:     $${pro.base}`)
  console.log(`  DB overage:        $${pro.dbOverage.toFixed(2)}`)
  console.log(`  Storage overage:   $${pro.storageOverage.toFixed(2)}`)
  console.log(`  Bandwidth overage: $${pro.bandwidthOverage.toFixed(2)}`)
  console.log(`  Edge Fn overage:   $${pro.edgeFnOverage.toFixed(2)}`)
  console.log(`  TOTAL:             $${total.toFixed(2)}/mo`)

  return total
}

// Example: project with 12GB DB, 150GB storage, 300GB bandwidth
estimateMonthlyCost({
  dbSizeGb: 12,
  storageGb: 150,
  bandwidthGb: 300,
  edgeFnInvocations: 1_500_000,
  mau: 80_000,
})
// Base Pro plan:     $25
// DB overage:        $0.50
// Storage overage:   $1.05
// Bandwidth overage: $4.50
// Edge Fn overage:   $0.00
// TOTAL:             $31.05/mo

Resources

• Supabase Pricing — plan comparison and calculator
• Compute Add-ons — instance sizing guide
• Spend Cap — prevent unexpected overage charges
• Database Disk Usage — monitoring and management
• Connection Pooling (Supavisor) — reduce connection overhead
• Edge Functions Best Practices — cold start and performance tips
• supabase-js Reference — createClient and SDK patterns

Next Steps

For architecture patterns, see supabase-reference-architecture. For performance tuning beyond cost, see supabase-performance-tuning.

Converted and distributed by TomeVault — claim your Tome and manage your conversions.