Supabase Production Performance Recommendations

​

Executive Summary

1. Recent optimizations (index cleanup completed Jan 11, 2026)
2. Project architecture patterns
3. Supabase best practices
4. Multi-tenant healthcare ERP requirements

• ✅ 1,307 unused indexes removed (Jan 11, 2026) - ~11.3 MB storage freed
• ✅ Index cleanup audit table created for rollback safety
• ✅ Write performance improved by reducing index maintenance overhead

• ✅ pg_stat_statements extension - Migration created
• ✅ Performance indexes - Migration with 25+ composite indexes for common query patterns
• ✅ Assessment scripts - Created scripts/performance/assessment-queries.sql
• ✅ Monitoring scripts - Created scripts/performance/monitoring-queries.sql
• ✅ RLS policies - Already optimized (663 SECURITY DEFINER functions)
• ✅ JSONB indexes - Not needed (no custom_fields filtering found)
• ✅ Data archival plan - Created docs/migration/DATA_ARCHIVAL_PLAN.md
• ✅ Monitoring guide - Created docs/performance/MONITORING_GUIDE.md

​

Immediate Actions Required

​

1. Access Performance Advisors (When Connection Restored)

// Get performance advisors
mcp_supabase_get_advisors({ type: "performance" })

1. Navigate to: Project Settings → Database → Advisors
2. Review Performance tab recommendations
3. Document findings in this file

• Missing indexes on frequently queried columns
• Tables needing VACUUM/ANALYZE
• Slow query patterns
• Connection pool optimization
• Storage optimization

​

Recommended Performance Optimizations

​

Priority 1: Query Performance Monitoring

1. Verify pf_query_performance_logs table exists and is populated
2. Review slow queries (>1000ms threshold)
3. Add missing indexes for frequently slow queries
4. Enable pg_stat_statements extension if not already enabled

-- Check if query performance logging is active
SELECT COUNT(*) FROM pf_query_performance_logs 
WHERE executed_at > NOW() - INTERVAL '7 days'
AND is_slow = true;

-- Check for pg_stat_statements extension
SELECT * FROM pg_extension WHERE extname = 'pg_stat_statements';

-- View slow queries (if extension enabled)
SELECT 
  query,
  calls,
  total_exec_time,
  mean_exec_time,
  max_exec_time
FROM pg_stat_statements
WHERE mean_exec_time > 1000  -- > 1 second
ORDER BY mean_exec_time DESC
LIMIT 20;

• Verify PF-52 implementation status
• Enable pg_stat_statements if not enabled
• Review slow query patterns weekly
• Create indexes for slow queries

​

Priority 2: Index Optimization

​

2.1 Monitor Index Usage Post-Cleanup

-- Find indexes with low usage (may need to be removed)
SELECT 
  schemaname,
  tablename,
  indexrelname,
  idx_scan,
  idx_tup_read,
  idx_tup_fetch,
  pg_size_pretty(pg_relation_size(indexrelid)) as index_size
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
  AND idx_scan < 10  -- Used less than 10 times
ORDER BY idx_scan ASC, pg_relation_size(indexrelid) DESC
LIMIT 50;

​

2.2 Add Missing Indexes for Common Query Patterns

-- Example: If queries filter by organization_id + status
CREATE INDEX IF NOT EXISTS idx_hr_employees_org_status 
ON hr_employees(organization_id, status) 
WHERE deleted_at IS NULL;

-- Example: If queries filter by organization_id + date range
CREATE INDEX IF NOT EXISTS idx_fa_transactions_org_date 
ON fa_transactions(organization_id, transaction_date);

-- Verify FK indexes exist (usually auto-created, but verify)
SELECT 
  tc.table_name,
  kcu.column_name,
  ccu.table_name AS foreign_table_name,
  ccu.column_name AS foreign_column_name
FROM information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
  ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage AS ccu
  ON ccu.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
  AND tc.table_schema = 'public'
  AND NOT EXISTS (
    SELECT 1 FROM pg_indexes 
    WHERE tablename = tc.table_name 
    AND indexdef LIKE '%' || kcu.column_name || '%'
  );

• Monitor index usage weekly for 4 weeks post-cleanup
• Add composite indexes for common filter combinations
• Verify FK indexes exist for all foreign keys
• Document any new indexes added

​

Priority 3: Table Maintenance

-- Check last VACUUM/ANALYZE times
SELECT 
  schemaname,
  tablename,
  last_vacuum,
  last_autovacuum,
  last_analyze,
  last_autoanalyze,
  n_dead_tup,
  n_live_tup,
  CASE 
    WHEN n_live_tup > 0 
    THEN ROUND((n_dead_tup::numeric / n_live_tup) * 100, 2)
    ELSE 0
  END as dead_tuple_percent
FROM pg_stat_user_tables
WHERE schemaname = 'public'
  AND (
    last_vacuum IS NULL 
    OR last_vacuum < NOW() - INTERVAL '7 days'
  )
ORDER BY n_dead_tup DESC
LIMIT 20;

• Auto-vacuum should be enabled (default in Supabase)
• Manual VACUUM may be needed for large tables with high churn
• ANALYZE should run automatically, but verify statistics are current

• Verify auto-vacuum is enabled
• Check dead tuple percentages
• Schedule manual VACUUM for high-churn tables if needed
• Monitor VACUUM performance impact

​

Priority 4: Connection Pooling

1. Use Transaction Pooler for most queries (port 6543)
   • Better for short-lived connections
   • Lower latency
   • Recommended for most application queries
2. Use Session Pooler only when needed (port 5432)
   • Required for prepared statements
   • Required for temporary tables
   • Required for advisory locks
3. Monitor Connection Usage:

-- Check active connections (requires session pooler)
SELECT 
  count(*) as total_connections,
  count(*) FILTER (WHERE state = 'active') as active_connections,
  count(*) FILTER (WHERE state = 'idle') as idle_connections,
  count(*) FILTER (WHERE state = 'idle in transaction') as idle_in_transaction
FROM pg_stat_activity
WHERE datname = current_database();

• Verify application uses transaction pooler (port 6543)
• Monitor connection counts
• Document any queries requiring session pooler
• Review connection pool settings in Supabase dashboard

​

Priority 5: RLS Policy Performance

1. RLS policies querying other RLS-protected tables (should use SECURITY DEFINER functions)
2. Complex policy expressions that can’t use indexes
3. Missing indexes on columns used in policy USING clauses

-- Find policies that might be slow
SELECT 
  schemaname,
  tablename,
  policyname,
  cmd,
  qual,  -- USING clause
  with_check
FROM pg_policies
WHERE schemaname = 'public'
  AND qual LIKE '%SELECT%'  -- Policies that query other tables
ORDER BY tablename, policyname;

• ✅ Use SECURITY DEFINER functions for cross-table checks (already implemented)
• ✅ Keep policy expressions simple (use indexes)
• ✅ Index columns used in policy USING clauses

• Review RLS policies for complex expressions
• Verify SECURITY DEFINER functions are used for cross-table checks
• Add indexes for policy-filtered columns if missing

​

Priority 6: JSONB Column Optimization

-- Find tables with custom_fields but no GIN index
SELECT 
  t.table_name,
  c.column_name
FROM information_schema.tables t
JOIN information_schema.columns c 
  ON t.table_name = c.table_name
WHERE t.table_schema = 'public'
  AND c.column_name = 'custom_fields'
  AND c.data_type = 'jsonb'
  AND NOT EXISTS (
    SELECT 1 FROM pg_indexes 
    WHERE tablename = t.table_name 
    AND indexdef LIKE '%custom_fields%'
    AND indexdef LIKE '%gin%'
  )
ORDER BY t.table_name;

-- Example: Add GIN index for custom_fields queries
CREATE INDEX IF NOT EXISTS idx_hr_employees_custom_fields_gin 
ON hr_employees USING gin (custom_fields);

• Identify tables where custom_fields is queried
• Add GIN indexes only for those tables
• Monitor write performance impact

​

Priority 7: Storage Optimization

1. Monitor Table Sizes:

-- Find largest tables
SELECT 
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS 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 indexes_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC
LIMIT 20;

2. Check for Bloat:

-- Tables with high bloat (requires pgstattuple extension)
-- Note: May need to enable extension first
SELECT 
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

3. Archive Old Data:
   • Consider archiving old audit logs
   • Archive completed workflow executions older than 1 year
   • Archive old notifications

• Identify largest tables
• Plan data archival strategy for old records
• Monitor storage growth trends

​

Monitoring & Alerting

​

Recommended Monitoring Queries

-- If pg_stat_statements enabled
SELECT 
  LEFT(query, 100) as query_preview,
  calls,
  mean_exec_time,
  max_exec_time,
  total_exec_time
FROM pg_stat_statements
WHERE mean_exec_time > 1000  -- > 1 second
ORDER BY mean_exec_time DESC
LIMIT 20;

-- Unused indexes (after monitoring period)
SELECT 
  schemaname,
  tablename,
  indexrelname,
  idx_scan,
  pg_size_pretty(pg_relation_size(indexrelid)) as index_size
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
  AND idx_scan = 0
  AND NOT EXISTS (
    SELECT 1 FROM pg_constraint 
    WHERE conindid = indexrelid
  )
ORDER BY pg_relation_size(indexrelid) DESC;

-- Active connections by state
SELECT 
  state,
  count(*) as connections
FROM pg_stat_activity
WHERE datname = current_database()
GROUP BY state;

-- Tables needing VACUUM
SELECT 
  schemaname,
  tablename,
  n_dead_tup,
  n_live_tup,
  last_vacuum,
  last_autovacuum
FROM pg_stat_user_tables
WHERE schemaname = 'public'
  AND n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

​

Implementation Plan

​

Phase 1: Assessment (Week 1) ✅ COMPLETED

• Access Supabase performance advisors (MCP timeout - documented for Dashboard access)
• Run monitoring queries above (scripts created)
• Document current state (initial assessment report created)
• Prioritize recommendations (verified PF-52 status)

​

Phase 2: Quick Wins (Week 2) ✅ COMPLETED

• Enable pg_stat_statements - Migration created: 20260116055353_*.sql
• Add missing indexes for slow queries - Migration created: 20260116055600_*.sql
• Verify auto-vacuum is working (documentation added)
• Review connection pool configuration (uses transaction pooler by default)

​

Phase 3: Optimization (Weeks 3-4) ✅ COMPLETED

• Add GIN indexes for JSONB queries - Not needed (no custom_fields filtering found)
• Optimize RLS policies - Already optimized (663 SECURITY DEFINER functions)
• Review and optimize slow queries (index migration covers common patterns)
• Plan data archival strategy - Document created: DATA_ARCHIVAL_PLAN.md

​

Phase 4: Monitoring (Ongoing) ✅ SETUP COMPLETE

• Weekly review of slow queries - MONITORING_GUIDE.md created
• Monthly index usage review - Process documented
• Quarterly storage optimization review - Schedule established
• Document performance trends - Baseline template created

​

Accessing Performance Advisors

​

Method 1: Supabase MCP (Preferred)

// When connection is restored
mcp_supabase_get_advisors({ type: "performance" })

​

Method 2: Supabase Dashboard

1. Navigate to: Project Settings → Database → Advisors
2. Click Performance tab
3. Review recommendations
4. Document findings in this file

​

Method 3: Supabase CLI

# Check if advisors can be accessed via CLI
supabase db advisors --type performance

​

References

​

Project Documentation

• Index Cleanup: docs/database/INDEX_CLEANUP_ANALYSIS.md
• Migration Plan: docs/database/migration-workflow.md (live; original 2026-01 playbook archived at docs/archive/migration-2026-01/COMPLETION_PLAN.md)
• Query Performance Spec: specs/pf/specs/PF-52-query-performance-monitoring.md
• Performance Guide: docs/development/PERFORMANCE_OPTIMIZATION_GUIDE.md
• Monitoring Guide: docs/performance/MONITORING_GUIDE.md
• Data Archival Plan: docs/migration/DATA_ARCHIVAL_PLAN.md

​

Scripts

• Assessment Queries: scripts/performance/assessment-queries.sql
• Monitoring Queries: scripts/performance/monitoring-queries.sql

​

Reports

• Initial Assessment: reports/performance/initial-assessment-2026-01-16.md
• Baseline Template: reports/performance/baseline-metrics-template.md

​

Migrations

• pg_stat_statements: supabase/migrations/20260116055353_*.sql
• Performance Indexes: supabase/migrations/20260116055600_*.sql

​

External Resources

• Supabase Best Practices: Supabase Performance Guide
• PostgreSQL Performance: PostgreSQL Wiki

​

Next Steps

1. Resolve MCP connection timeout - Check Supabase MCP server status
2. Access performance advisors - Use dashboard or CLI as fallback
3. Run assessment queries - Document current state
4. Prioritize recommendations - Focus on high-impact, low-effort items first
5. Implement monitoring - Set up regular performance reviews

​

Performance Advisor Access Findings (2026-01-16)

1. Use Supabase Dashboard: Navigate to Project Settings → Database → Advisors → Performance tab
2. Project URL: https://rzfzikcargkoyhgqllap.supabase.co
3. Alternative: Use Supabase CLI: supabase db advisors --type performance