Supabase Cost Optimization: Reduce Database Expenses

-- Monitor database size
select
  pg_size_pretty(pg_database_size(current_database())) as total_size;

-- View table sizes with indexes
select
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as total_size,
  pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) as table_size,
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename) - pg_relation_size(schemaname||'.'||tablename)) as index_size,
  pg_total_relation_size(schemaname||'.'||tablename) as size_bytes
from pg_tables
where schemaname = 'public'
order by size_bytes desc;

-- Identify large text columns
select
  table_name,
  column_name,
  data_type,
  character_maximum_length
from information_schema.columns
where table_schema = 'public'
  and data_type in ('text', 'character varying')
  and character_maximum_length is null  -- Unbounded text
order by table_name, column_name;

-- Implement data retention policy
create table data_retention_policies (
  table_name text primary key,
  retention_days int not null,
  enabled boolean default true,
  last_cleanup timestamp with time zone
);

insert into data_retention_policies (table_name, retention_days) values
  ('logs', 30),
  ('analytics_events', 90),
  ('audit_logs', 365),
  ('sessions', 7);

-- Cleanup function
create or replace function cleanup_old_data()
returns void as $$
declare
  policy record;
  deleted_count int;
begin
  for policy in
    select * from data_retention_policies where enabled = true
  loop
    execute format(
      'delete from %I where created_at < now() - interval ''%s days''',
      policy.table_name,
      policy.retention_days
    );
    
    get diagnostics deleted_count = row_count;
    
    raise notice 'Deleted % rows from %', deleted_count, policy.table_name;
    
    update data_retention_policies
    set last_cleanup = now()
    where table_name = policy.table_name;
  end loop;
end;
$$ language plpgsql;

-- Schedule cleanup (requires pg_cron)
select cron.schedule(
  'cleanup-old-data',
  '0 2 * * *',  -- Run at 2 AM daily
  'select cleanup_old_data();'
);

-- Archive old data instead of deleting
create table archived_logs (
  like logs including all
) partition by range (created_at);

-- Archive function
create or replace function archive_old_logs(days_old int default 90)
returns void as $$
begin
  -- Move old logs to archive
  insert into archived_logs
  select * from logs
  where created_at < now() - (days_old || ' days')::interval;
  
  -- Delete archived logs from main table
  delete from logs
  where created_at < now() - (days_old || ' days')::interval;
end;
$$ language plpgsql;

-- Compress text data using PostgreSQL compression
alter table logs alter column message set storage external;  -- Don't compress
alter table logs alter column message set storage extended;  -- Compress (TOAST)
alter table logs alter column message set storage main;      -- Inline storage

-- Use appropriate data types
-- BEFORE: text column for status (wastes space)
alter table orders alter column status type text;

-- AFTER: enum or varchar with constraint (saves space)
create type order_status as enum ('pending', 'processing', 'shipped', 'delivered', 'cancelled');
alter table orders alter column status type order_status using status::order_status;

-- Or use varchar with constraint
alter table orders alter column status type varchar(20);
alter table orders add constraint check_status 
  check (status in ('pending', 'processing', 'shipped', 'delivered', 'cancelled'));

-- Identify expensive queries
select
  substring(query, 1, 100) as query,
  calls,
  total_exec_time,
  mean_exec_time,
  (total_exec_time / sum(total_exec_time) over ()) * 100 as pct_total_time
from pg_stat_statements
order by total_exec_time desc
limit 20;

-- Optimize with proper indexes (review existing)
select
  schemaname,
  tablename,
  indexname,
  idx_scan,
  pg_size_pretty(pg_relation_size(indexrelid)) as index_size
from pg_stat_user_indexes
order by pg_relation_size(indexrelid) desc;

-- Find missing indexes (high seq_scan, low idx_scan)
select
  schemaname,
  tablename,
  seq_scan,
  seq_tup_read,
  idx_scan,
  seq_tup_read / seq_scan as avg_seq_read
from pg_stat_user_tables
where seq_scan > 0
order by seq_tup_read desc
limit 20;

-- Optimize query patterns
-- BEFORE: N+1 query problem
const { data: posts } = await supabase.from('posts').select('*')
for (const post of posts) {
  const { data: author } = await supabase
    .from('users')
    .select('*')
    .eq('id', post.author_id)
    .single()
}

-- AFTER: Join query (1 query instead of N+1)
const { data: posts } = await supabase
  .from('posts')
  .select(`
    *,
    author:users(
      id,
      name,
      avatar_url
    )
  `)

-- Use selective column queries
-- BEFORE: Fetches all columns (wastes bandwidth)
const { data } = await supabase.from('users').select('*')

-- AFTER: Only fetch needed columns
const { data } = await supabase
  .from('users')
  .select('id, name, email')

-- Implement pagination (reduce data transfer)
const PAGE_SIZE = 20
const { data, count } = await supabase
  .from('posts')
  .select('*', { count: 'exact' })
  .range(page * PAGE_SIZE, (page + 1) * PAGE_SIZE - 1)

-- Use COUNT optimization
-- BEFORE: Full table scan
select count(*) from large_table;

-- AFTER: Estimated count (faster)
select reltuples::bigint as estimate
from pg_class
where relname = 'large_table';

-- Batch operations
-- BEFORE: Multiple inserts
for (const user of users) {
  await supabase.from('users').insert(user)
}

-- AFTER: Single batch insert
await supabase.from('users').insert(users)

-- Use materialized views for expensive aggregations
create materialized view user_stats as
select
  user_id,
  count(*) as post_count,
  sum(views) as total_views,
  avg(rating) as avg_rating
from posts
group by user_id;

create unique index idx_user_stats_user on user_stats(user_id);

-- Refresh materialized view periodically
refresh materialized view concurrently user_stats;

-- Schedule refresh
select cron.schedule(
  'refresh-user-stats',
  '0 * * * *',  -- Every hour
  'refresh materialized view concurrently user_stats;'
);

-- Monitor storage usage
select
  bucket_id,
  count(*) as file_count,
  pg_size_pretty(sum((metadata->>'size')::bigint)) as total_size
from storage.objects
group by bucket_id
order by sum((metadata->>'size')::bigint) desc;

-- Identify large files
select
  name,
  bucket_id,
  pg_size_pretty((metadata->>'size')::bigint) as file_size,
  created_at
from storage.objects
order by (metadata->>'size')::bigint desc
limit 20;

-- Implement file size limits
create or replace function check_file_size()
returns trigger as $$
begin
  if (new.metadata->>'size')::bigint > 5242880 then  -- 5MB
    raise exception 'File size exceeds 5MB limit';
  end if;
  return new;
end;
$$ language plpgsql;

create trigger enforce_file_size
  before insert on storage.objects
  for each row
  execute function check_file_size();

// Image optimization before upload
import sharp from 'sharp'

export async function optimizeImage(file: File): Promise<Blob> {
  const buffer = await file.arrayBuffer()
  
  const optimized = await sharp(Buffer.from(buffer))
    .resize(1920, 1080, {
      fit: 'inside',
      withoutEnlargement: true,
    })
    .webp({ quality: 80 })  // WebP format saves space
    .toBuffer()
  
  return new Blob([optimized], { type: 'image/webp' })
}

// Usage
const optimized = await optimizeImage(file)
await supabase.storage
  .from('images')
  .upload(`public/${fileName}.webp`, optimized)

// Implement CDN caching
const imageUrl = supabase.storage
  .from('images')
  .getPublicUrl('avatar.jpg', {
    transform: {
      width: 400,
      height: 400,
      quality: 80,
    }
  })

-- Clean up unused storage files
create or replace function cleanup_orphaned_files()
returns void as $$
declare
  file_record record;
  deleted_count int := 0;
begin
  -- Find storage references that no longer have database records
  for file_record in
    select o.name, o.bucket_id
    from storage.objects o
    left join users u on u.avatar_url = o.name
    left join posts p on p.image_url = o.name
    where u.id is null and p.id is null
      and o.created_at < now() - interval '30 days'
  loop
    -- Delete orphaned file
    delete from storage.objects
    where name = file_record.name
      and bucket_id = file_record.bucket_id;
    
    deleted_count := deleted_count + 1;
  end loop;
  
  raise notice 'Deleted % orphaned files', deleted_count;
end;
$$ language plpgsql;

-- Storage retention policy
create table storage_retention_policies (
  bucket_id text primary key,
  retention_days int not null,
  enabled boolean default true
);

insert into storage_retention_policies (bucket_id, retention_days) values
  ('temp', 1),
  ('uploads', 30),
  ('backups', 90);

-- Cleanup based on retention policy
create or replace function cleanup_old_storage()
returns void as $$
declare
  policy record;
begin
  for policy in
    select * from storage_retention_policies where enabled = true
  loop
    delete from storage.objects
    where bucket_id = policy.bucket_id
      and created_at < now() - (policy.retention_days || ' days')::interval;
  end loop;
end;
$$ language plpgsql;

// Implement connection pooling
// lib/supabase/pool.ts
import { createClient } from '@supabase/supabase-js'

let supabaseInstance: ReturnType<typeof createClient> | null = null

export function getSupabaseClient() {
  // Reuse single instance (connection pooling)
  if (!supabaseInstance) {
    supabaseInstance = createClient(
      process.env.NEXT_PUBLIC_SUPABASE_URL!,
      process.env.SUPABASE_SERVICE_ROLE_KEY!,
      {
        db: {
          schema: 'public',
        },
        auth: {
          persistSession: false,  // Don't persist in server
          autoRefreshToken: false,
        },
        global: {
          headers: {
            'x-connection-pool': 'enabled',
          },
        },
      }
    )
  }
  return supabaseInstance
}

// Monitor connection usage
select
  count(*) as total_connections,
  count(*) filter (where state = 'active') as active_connections,
  count(*) filter (where state = 'idle') as idle_connections,
  max_connections.setting::int as max_connections,
  round(100.0 * count(*) / max_connections.setting::int, 2) as pct_used
from pg_stat_activity,
  (select setting from pg_settings where name = 'max_connections') max_connections
group by max_connections.setting;

-- Identify connection leaks
select
  datname,
  usename,
  application_name,
  state,
  count(*),
  max(state_change) as last_state_change
from pg_stat_activity
where state != 'active'
group by datname, usename, application_name, state
having count(*) > 5
order by count(*) desc;

// Use Supabase connection pooler
// Connection string with pooler
const DATABASE_URL = 'postgresql://postgres:[password]@aws-0-us-east-1.pooler.supabase.com:6543/postgres'

// Configure pool size
import { Pool } from 'pg'

const pool = new Pool({
  connectionString: DATABASE_URL,
  max: 20,  // Max connections in pool
  min: 2,   // Min connections maintained
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 10000,
})

// Close idle connections
pool.on('remove', () => {
  console.log('Connection removed from pool')
})

// Implement connection timeout
const client = await pool.connect()
try {
  await client.query('SET statement_timeout = 30000')  // 30s timeout
  const result = await client.query('SELECT * FROM users')
  return result.rows
} finally {
  client.release()  // Always release connection
}

-- Create cost tracking table
create table cost_metrics (
  id uuid default gen_random_uuid() primary key,
  metric_date date default current_date,
  database_size_bytes bigint,
  storage_size_bytes bigint,
  api_requests bigint,
  bandwidth_bytes bigint,
  connection_count int,
  estimated_cost numeric(10,2),
  created_at timestamp with time zone default now()
);

create unique index idx_cost_metrics_date on cost_metrics(metric_date);

-- Function to calculate daily costs
create or replace function calculate_daily_cost()
returns void as $$
declare
  db_size bigint;
  storage_size bigint;
  api_count bigint;
  bandwidth bigint;
  connections int;
  estimated_cost numeric(10,2);
begin
  -- Get database size
  select pg_database_size(current_database()) into db_size;
  
  -- Get storage size
  select sum((metadata->>'size')::bigint)
  into storage_size
  from storage.objects;
  
  -- Get API request count (from metrics table)
  select count(*)
  into api_count
  from api_metrics
  where timestamp >= current_date;
  
  -- Calculate estimated cost
  -- Database: $0.125 per GB over 8GB (Pro plan)
  estimated_cost := greatest(0, (db_size / 1073741824.0 - 8) * 0.125);
  
  -- Storage: Add storage costs if applicable
  estimated_cost := estimated_cost + (storage_size / 1073741824.0 * 0.021);
  
  -- Get current connections
  select count(*) into connections from pg_stat_activity;
  
  -- Store metrics
  insert into cost_metrics (
    metric_date,
    database_size_bytes,
    storage_size_bytes,
    api_requests,
    bandwidth_bytes,
    connection_count,
    estimated_cost
  ) values (
    current_date,
    db_size,
    storage_size,
    api_count,
    0,  -- Bandwidth tracking would need additional setup
    connections,
    estimated_cost
  )
  on conflict (metric_date)
  do update set
    database_size_bytes = excluded.database_size_bytes,
    storage_size_bytes = excluded.storage_size_bytes,
    api_requests = excluded.api_requests,
    connection_count = excluded.connection_count,
    estimated_cost = excluded.estimated_cost;
end;
$$ language plpgsql;

-- Schedule daily cost calculation
select cron.schedule(
  'calculate-daily-cost',
  '0 0 * * *',  -- Run at midnight
  'select calculate_daily_cost();'
);

-- Cost trend analysis
select
  metric_date,
  pg_size_pretty(database_size_bytes) as db_size,
  pg_size_pretty(storage_size_bytes) as storage_size,
  api_requests,
  estimated_cost,
  estimated_cost - lag(estimated_cost) over (order by metric_date) as cost_change
from cost_metrics
order by metric_date desc
limit 30;

-- Alert function for cost spikes
create or replace function check_cost_alert()
returns void as $$
declare
  current_cost numeric;
  avg_cost numeric;
  threshold numeric := 1.5;  -- 50% increase threshold
begin
  select estimated_cost into current_cost
  from cost_metrics
  where metric_date = current_date;
  
  select avg(estimated_cost) into avg_cost
  from cost_metrics
  where metric_date > current_date - interval '7 days'
    and metric_date < current_date;
  
  if current_cost > (avg_cost * threshold) then
    -- Send alert (webhook or notification)
    perform net.http_post(
      url := current_setting('app.webhook_url'),
      body := jsonb_build_object(
        'alert', 'cost_spike',
        'current_cost', current_cost,
        'average_cost', avg_cost,
        'increase_pct', round((current_cost / avg_cost - 1) * 100, 2)
      )
    );
  end if;
end;
$$ language plpgsql;

// Dashboard component
import { useEffect, useState } from 'react'
import { createClient } from '@/lib/supabase/client'

export function CostDashboard() {
  const [metrics, setMetrics] = useState([])
  const supabase = createClient()

  useEffect(() => {
    loadCostMetrics()
  }, [])

  async function loadCostMetrics() {
    const { data } = await supabase
      .from('cost_metrics')
      .select('*')
      .order('metric_date', { ascending: false })
      .limit(30)

    if (data) setMetrics(data)
  }

  return (
    <div className="space-y-6">
      <h2 className="text-2xl font-bold">Cost Overview</h2>
      
      <div className="grid grid-cols-3 gap-4">
        {metrics[0] && (
          <>
            <div className="bg-white p-6 rounded-lg shadow">
              <h3 className="text-sm text-gray-500">Database Size</h3>
              <p className="text-2xl font-bold">
                {(metrics[0].database_size_bytes / 1073741824).toFixed(2)} GB
              </p>
            </div>
            
            <div className="bg-white p-6 rounded-lg shadow">
              <h3 className="text-sm text-gray-500">Storage Size</h3>
              <p className="text-2xl font-bold">
                {(metrics[0].storage_size_bytes / 1073741824).toFixed(2)} GB
              </p>
            </div>
            
            <div className="bg-white p-6 rounded-lg shadow">
              <h3 className="text-sm text-gray-500">Estimated Cost</h3>
              <p className="text-2xl font-bold">
                ${metrics[0].estimated_cost}
              </p>
            </div>
          </>
        )}
      </div>
    </div>
  )
}