Supabase Performance Optimization

-- Index on columns you frequently filter or sort by
CREATE INDEX idx_posts_user_id ON posts(user_id);
CREATE INDEX idx_posts_created_at ON posts(created_at DESC);

-- Composite index for queries filtering on multiple columns
CREATE INDEX idx_posts_user_status ON posts(user_id, status)
  WHERE status = 'published';  -- Partial index: only indexes published posts

-- Unique index
CREATE UNIQUE INDEX idx_profiles_username ON profiles(LOWER(username));

-- GIN index for JSONB columns (contains, exists operators)
CREATE INDEX idx_posts_metadata ON posts USING GIN (metadata);

-- GIN index for full-text search
ALTER TABLE posts ADD COLUMN fts tsvector
  GENERATED ALWAYS AS (to_tsvector('english', title || ' ' || content)) STORED;
CREATE INDEX idx_posts_fts ON posts USING GIN (fts);

-- Query full-text search
SELECT * FROM posts WHERE fts @@ to_tsquery('english', 'supabase & tutorial');

-- GiST index for geospatial (with PostGIS)
CREATE INDEX idx_locations_geo ON locations USING GIST (coordinates);

-- pg_trgm for fuzzy/LIKE queries
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_posts_title_trgm ON posts USING GIN (title gin_trgm_ops);

-- Analyze a query's execution plan
EXPLAIN ANALYZE
SELECT p.*, u.username
FROM posts p
JOIN profiles u ON u.id = p.user_id
WHERE p.status = 'published'
ORDER BY p.created_at DESC
LIMIT 20;

-- Look for:
-- ✅ Index Scan / Index Only Scan (good)
-- ❌ Seq Scan on large tables (needs an index)
-- ❌ Sort with high cost (consider an index on sort column)
-- ❌ Nested Loop with many rows (consider a different join strategy)

// Supabase provides Supavisor for connection pooling
// Use the pooler connection string for serverless/edge functions

// Transaction mode (port 6543) — for serverless
// Each query can use a different connection
const supabase = createClient(
  'https://your-project.supabase.co',
  'your-anon-key',
  {
    db: { schema: 'public' },
    // Connection is pooled automatically via the API
  }
)

// For direct database connections (e.g., migrations, long transactions):
// Use port 5432 (direct) or 6543 (pooled) in your connection string
// postgresql://postgres:[PASSWORD]@db.[REF].supabase.co:6543/postgres

-- Cache expensive aggregation queries
CREATE MATERIALIZED VIEW post_stats AS
SELECT
  user_id,
  COUNT(*) AS total_posts,
  COUNT(*) FILTER (WHERE status = 'published') AS published_posts,
  MAX(created_at) AS last_post_at
FROM posts
GROUP BY user_id;

-- Create an index on the materialized view
CREATE UNIQUE INDEX idx_post_stats_user ON post_stats(user_id);

-- Refresh periodically (can use pg_cron)
REFRESH MATERIALIZED VIEW CONCURRENTLY post_stats;

-- Query the view instead of the expensive aggregation
SELECT * FROM post_stats WHERE user_id = 'some-uuid';

-- Find your slowest queries
SELECT
  query,
  calls,
  mean_exec_time::numeric(10,2) AS avg_ms,
  total_exec_time::numeric(10,2) AS total_ms,
  rows
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 20;