Quick Supabase Performance Optimizer

Analyze and optimize Supabase database performance with intelligent index recommendations, query tuning, and RLS policy profiling.

When to Use This Command

Run this command when you need to:

• Identify slow queries and receive actionable index recommendations for your Supabase database
• Profile RLS policy execution time and optimize policies that add significant overhead
• Analyze storage patterns, data types, and table bloat to reduce database size and cost

Consider alternatives when:

• You need to restructure your entire schema or data model (plan a migration instead)
• Performance issues stem from application-level N+1 queries (profile your application code first)

Quick Start

Configuration

Copy
name: quick-supabase-performance-optimizer
type: command
category: database

Example Invocation

Copy
claude command:run quick-supabase-performance-optimizer --focus queries --project myapp-prod

Example Output

[Connect] Project: myapp-prod | Database size: 4.2 GB
[Analyze] Scanning query patterns over last 24 hours...

[Slow Queries] Found 5 queries exceeding 500ms:
  1. SELECT * FROM orders WHERE user_id = $1 (avg: 1.2s, calls: 4,521)
     Recommendation: CREATE INDEX idx_orders_user_id ON orders(user_id);
     Estimated improvement: ~95% faster

  2. SELECT count(*) FROM events WHERE created_at > $1 (avg: 890ms, calls: 312)
     Recommendation: CREATE INDEX idx_events_created_at ON events(created_at);
     Estimated improvement: ~80% faster

[RLS Policies] 2 policies with high overhead:
  - orders_select_policy: adds 45ms per query (subquery on profiles)
  - events_select_policy: adds 22ms per query (function call)

[Summary] Applying top 2 index recommendations would reduce avg response by 62%

Core Concepts

Performance Optimization Overview

Aspect	Details
Analysis Targets	Query plans, index usage, table statistics, RLS policy cost
Data Sources	pg_stat_statements, pg_stat_user_tables, EXPLAIN ANALYZE
Recommendations	Index creation/removal, query rewrites, RLS simplification
Monitoring	Before/after comparison with measurable improvement metrics

Optimization Workflow

[Collect Performance Metrics]
          |
[Identify Bottlenecks]
   /       |        \
[Queries] [Indexes] [RLS Policies]
   \       |        /
[Generate Recommendations]
          |
[Apply + Measure Impact]
          |
[Report Before/After Comparison]

Configuration

Parameter	Type	Default	Description
focus	string	all	Optimization focus: queries, indexes, storage, rls, or all
project	string	auto	Supabase project reference or connection string
threshold	number	500	Minimum query duration (ms) to flag as slow
apply	boolean	false	Automatically apply recommended indexes (requires confirmation)
report	string	terminal	Output destination: terminal, json, or markdown file

Best Practices

1. Profile Before Optimizing - Run the analyzer in read-only mode first to understand your baseline. Premature index creation can slow down writes without meaningfully improving reads.

2. Focus on High-Frequency Slow Queries - A query running 10,000 times at 800ms has far more impact than one running 5 times at 5 seconds. Prioritize optimizations by total time spent, not just per-query duration.

3. Test Index Impact on Writes - Every index speeds up reads but slows down INSERT and UPDATE operations. Measure write performance after adding indexes, especially on high-write tables.

4. Simplify RLS with Security Definer Functions - Complex RLS policies with subqueries execute per row. Wrap authorization logic in a SECURITY DEFINER function to reduce repeated computation.

5. Schedule Regular Performance Audits - Run this command weekly as query patterns change with feature development. An index that was optimal last month may be unused after a refactor.

Common Issues

1. pg_stat_statements Not Enabled - Query statistics require the pg_stat_statements extension. Enable it via the Supabase dashboard under Database > Extensions before running analysis.

2. Index Recommendations Conflict - Two recommended indexes may cover overlapping columns. Review composite index opportunities to consolidate multiple single-column indexes into one.

3. RLS Analysis Shows Zero Cost - If RLS policies show no measurable overhead, the tables may have too few rows for policies to matter. Re-run the analysis once the dataset grows to production size.