Supabase Database Indexes: Performance Optimization

-- Enable pg_stat_statements for query tracking
create extension if not exists pg_stat_statements;

-- View slowest queries
select
  substring(query, 1, 100) as short_query,
  calls,
  round(total_exec_time::numeric, 2) as total_time_ms,
  round(mean_exec_time::numeric, 2) as avg_time_ms,
  round(max_exec_time::numeric, 2) as max_time_ms
from pg_stat_statements
where mean_exec_time > 100  -- Queries over 100ms
order by mean_exec_time desc
limit 20;

-- Identify tables with high sequential scans
select
  schemaname,
  tablename,
  seq_scan,  -- Sequential scans (potentially needs index)
  seq_tup_read,
  idx_scan,  -- Index scans (good)
  idx_tup_fetch,
  n_live_tup as row_count,
  case
    when seq_scan > 0 then round((100.0 * idx_scan / (seq_scan + idx_scan))::numeric, 2)
    else 0
  end as index_scan_pct
from pg_stat_user_tables
where seq_scan > 0
  and n_live_tup > 10000  -- Focus on large tables
order by seq_scan desc, seq_tup_read desc
limit 20;

-- Analyze query execution plan
explain (analyze, buffers) 
select * from users 
where email = '[email protected]';

/* Example output:
Seq Scan on users  (cost=0.00..1234.56 rows=1 width=100) (actual time=45.123..45.125 rows=1 loops=1)
  Filter: (email = '[email protected]'::text)
  Rows Removed by Filter: 123455
  Buffers: shared hit=1000
Planning Time: 0.123 ms
Execution Time: 45.234 ms

"Seq Scan" indicates missing index!
*/

-- After creating index
create index idx_users_email on users(email);

explain (analyze, buffers)
select * from users 
where email = '[email protected]';

/* Example output:
Index Scan using idx_users_email on users  (cost=0.42..8.44 rows=1 width=100) (actual time=0.023..0.024 rows=1 loops=1)
  Index Cond: (email = '[email protected]'::text)
  Buffers: shared hit=4
Planning Time: 0.056 ms
Execution Time: 0.035 ms

Much faster with index!
*/

-- Check existing indexes
select
  schemaname,
  tablename,
  indexname,
  indexdef
from pg_indexes
where schemaname = 'public'
order by tablename, indexname;

-- View index sizes
select
  schemaname,
  tablename,
  indexname,
  pg_size_pretty(pg_relation_size(indexrelid)) as index_size,
  idx_scan as times_used
from pg_stat_user_indexes
order by pg_relation_size(indexrelid) desc;

-- B-tree is the default index type
-- Good for equality and range queries

-- Single column indexes
create index idx_users_email on users(email);
create index idx_posts_created_at on posts(created_at);
create index idx_orders_status on orders(status);

-- Composite (multi-column) indexes
-- Order matters! Most selective column first
create index idx_posts_user_created on posts(user_id, created_at);

-- This index helps queries like:
select * from posts where user_id = 123;  -- Uses index
select * from posts where user_id = 123 and created_at > '2024-01-01';  -- Uses index

-- But NOT:
select * from posts where created_at > '2024-01-01';  -- Doesn't use index (created_at is second column)

-- For both query patterns, create two indexes:
create index idx_posts_user_created on posts(user_id, created_at);
create index idx_posts_created on posts(created_at);  -- For date-only queries

-- Unique indexes (enforces uniqueness)
create unique index idx_users_email_unique on users(email);
create unique index idx_users_username_unique on users(username);

-- Case-insensitive indexes
create index idx_users_email_lower on users(lower(email));

-- Query must use same expression:
select * from users where lower(email) = '[email protected]';

-- Text pattern matching indexes
create index idx_users_name_pattern on users(name text_pattern_ops);

-- Helps with LIKE queries:
select * from users where name like 'John%';  -- Uses index

-- Descending indexes for ORDER BY DESC
create index idx_posts_created_desc on posts(created_at desc);

-- Expression indexes
create index idx_products_price_with_tax on products((price * 1.1));

-- Query:
select * from products where (price * 1.1) < 100;

-- Conditional/Partial indexes (covered in next section)
create index idx_orders_pending on orders(created_at)
where status = 'pending';

-- Index creation with CONCURRENTLY (doesn't lock table)
create index concurrently idx_users_last_login on users(last_login_at);

-- View index usage statistics
select
  schemaname,
  tablename,
  indexname,
  idx_scan,
  idx_tup_read,
  idx_tup_fetch
from pg_stat_user_indexes
where schemaname = 'public'
order by idx_scan desc;

-- GIN (Generalized Inverted Index) for JSONB, arrays, full-text search

-- JSONB indexing
create table products (
  id uuid primary key,
  name text,
  metadata jsonb
);

-- Default GIN index on entire JSONB column
create index idx_products_metadata on products using gin(metadata);

-- Supports containment queries:
select * from products where metadata @> '{"color": "blue"}';
select * from products where metadata ? 'warranty';
select * from products where metadata ?& array['color', 'size'];

-- jsonb_path_ops for faster containment queries (smaller index)
create index idx_products_metadata_path on products using gin(metadata jsonb_path_ops);

-- More efficient but only supports @> operator
select * from products where metadata @> '{"brand": "Apple"}';

-- Index specific JSONB path
create index idx_products_specs on products using gin((metadata->'specs'));

-- Array indexing
create table posts (
  id uuid primary key,
  title text,
  tags text[]
);

-- GIN index on array column
create index idx_posts_tags on posts using gin(tags);

-- Supports array operations:
select * from posts where tags @> array['postgresql'];  -- Contains
select * from posts where tags && array['database', 'tutorial'];  -- Overlaps

-- Full-text search with GIN
create table articles (
  id uuid primary key,
  title text,
  content text,
  search_vector tsvector generated always as (
    to_tsvector('english', coalesce(title, '') || ' ' || coalesce(content, ''))
  ) stored
);

-- GIN index on tsvector column
create index idx_articles_search on articles using gin(search_vector);

-- Full-text search query:
select * from articles 
where search_vector @@ to_tsquery('english', 'postgresql & performance');

-- Multi-column GIN index
create index idx_posts_title_tags on posts using gin(to_tsvector('english', title), tags);

-- Compare index sizes
select
  indexname,
  pg_size_pretty(pg_relation_size(indexrelid)) as index_size
from pg_stat_user_indexes
where tablename = 'products'
order by pg_relation_size(indexrelid) desc;

-- GIN index maintenance
-- Analyze table to update statistics
analyze products;

-- Reindex if index becomes bloated
reindex index concurrently idx_products_metadata;

-- Configure GIN index build parameters
set maintenance_work_mem = '256MB';  -- More memory for faster index creation

-- Partial indexes (filtered indexes) - smaller and faster

-- Index only active users
create index idx_users_active on users(last_login_at)
where status = 'active';

-- Much smaller than indexing all users
-- Query must include the WHERE condition:
select * from users 
where status = 'active' 
  and last_login_at > now() - interval '30 days';

-- Index only published posts
create index idx_posts_published on posts(created_at desc)
where published = true;

-- Index only pending orders
create index idx_orders_pending on orders(created_at, user_id)
where status = 'pending';

-- Index non-null values only
create index idx_users_phone on users(phone)
where phone is not null;

-- Index specific date ranges
create index idx_orders_recent on orders(created_at)
where created_at >= '2024-01-01';

-- Composite indexes (multiple columns)
-- Column order is crucial!

-- Good: Most selective column first
create index idx_posts_user_status_created on posts(user_id, status, created_at);

-- Helps these queries:
select * from posts where user_id = 123;  -- Uses index
select * from posts where user_id = 123 and status = 'published';  -- Uses index
select * from posts where user_id = 123 and status = 'published' and created_at > '2024-01-01';  -- Uses index

-- But NOT these:
select * from posts where status = 'published';  -- Doesn't use index (status is 2nd column)
select * from posts where created_at > '2024-01-01';  -- Doesn't use index

-- Create separate indexes for different query patterns
create index idx_posts_user_status_created on posts(user_id, status, created_at);
create index idx_posts_status on posts(status) where status = 'published';
create index idx_posts_created on posts(created_at desc);

-- Include columns in index (PostgreSQL 11+)
create index idx_posts_user_include on posts(user_id) include (title, status);

-- Index stores user_id (searchable) and title, status (non-searchable but returned)
select user_id, title, status from posts where user_id = 123;
-- Can retrieve all data from index without accessing table (index-only scan)

-- Covering indexes for specific queries
create index idx_orders_user_covering on orders(user_id, created_at) 
include (total_amount, status);

-- This query can be answered entirely from the index:
select user_id, created_at, total_amount, status 
from orders 
where user_id = 123 
order by created_at desc;

-- Verify index is being used
explain (analyze, buffers)
select user_id, created_at, total_amount, status 
from orders 
where user_id = 123 
order by created_at desc;

-- Look for "Index Only Scan" in output

-- Monitor index usage
select
  schemaname,
  tablename,
  indexname,
  idx_scan as times_used,
  idx_tup_read as rows_read,
  idx_tup_fetch as rows_fetched,
  pg_size_pretty(pg_relation_size(indexrelid)) as index_size
from pg_stat_user_indexes
where schemaname = 'public'
order by idx_scan desc;

-- Find unused indexes (candidates for removal)
select
  schemaname,
  tablename,
  indexname,
  pg_size_pretty(pg_relation_size(indexrelid)) as index_size,
  idx_scan as times_used
from pg_stat_user_indexes
where schemaname = 'public'
  and idx_scan = 0
  and indexrelname not like 'pg_toast%'
  and indexrelname not like '%_pkey'  -- Don't include primary keys
order by pg_relation_size(indexrelid) desc;

-- Drop unused index
drop index if exists idx_unused_index;

-- Index bloat detection
select
  schemaname,
  tablename,
  indexname,
  pg_size_pretty(pg_relation_size(indexrelid)) as index_size,
  idx_scan,
  case
    when idx_scan = 0 then 'Unused'
    when idx_scan < 10 then 'Rarely used'
    else 'Used'
  end as usage_status
from pg_stat_user_indexes
where schemaname = 'public'
order by pg_relation_size(indexrelid) desc;

-- Reindex bloated indexes
reindex index concurrently idx_bloated_index;

-- Reindex entire table
reindex table concurrently users;

-- Analyze table to update statistics
analyze users;

-- Vacuum to reclaim space
vacuum analyze users;

-- Auto-vacuum settings (in postgresql.conf)
-- These are defaults, adjust based on workload:
autovacuum = on
autovacuum_max_workers = 3
autovacuum_naptime = 1min
autovacuum_vacuum_threshold = 50
autovacuum_analyze_threshold = 50
autovacuum_vacuum_scale_factor = 0.2
autovacuum_analyze_scale_factor = 0.1

-- Check last vacuum/analyze
select
  schemaname,
  relname as table_name,
  last_vacuum,
  last_autovacuum,
  last_analyze,
  last_autoanalyze,
  vacuum_count,
  autovacuum_count,
  analyze_count,
  autoanalyze_count
from pg_stat_user_tables
where schemaname = 'public'
order by relname;

-- Index health check function
create or replace function check_index_health()
returns table(
  index_name text,
  table_name text,
  index_size text,
  times_used bigint,
  health_status text
) as $$
begin
  return query
  select
    i.indexrelname::text,
    i.relname::text,
    pg_size_pretty(pg_relation_size(i.indexrelid)),
    i.idx_scan,
    case
      when i.idx_scan = 0 and pg_relation_size(i.indexrelid) > 1048576 then 'Remove (unused, >1MB)'
      when i.idx_scan < 10 and pg_relation_size(i.indexrelid) > 1048576 then 'Consider removing'
      when i.idx_scan > 1000 then 'Healthy'
      else 'Monitor'
    end
  from pg_stat_user_indexes i
  where i.schemaname = 'public'
    and i.indexrelname not like '%_pkey'
  order by pg_relation_size(i.indexrelid) desc;
end;
$$ language plpgsql;

-- Run health check
select * from check_index_health();

-- Schedule regular maintenance (using pg_cron)
select cron.schedule(
  'analyze-tables',
  '0 2 * * *',  -- 2 AM daily
  'analyze;'
);

select cron.schedule(
  'reindex-heavy-tables',
  '0 3 * * 0',  -- 3 AM on Sundays
  'reindex table concurrently posts; reindex table concurrently users;'
);