Supabase Postgres Smart

A Claude Code skill providing comprehensive performance optimization guidance for PostgreSQL within the Supabase ecosystem. Covers query optimization, indexing strategies, Row Level Security (RLS) performance, connection pooling, and Supabase-specific best practices.

When to Use This Skill

Choose Supabase Postgres Smart when:

• You're using Supabase and want to optimize database performance
• You need to implement Row Level Security without killing performance
• You want to optimize Supabase queries and reduce latency
• You need connection pooling guidance for serverless functions
• You want to set up proper indexes for your Supabase tables

Consider alternatives when:

• You need general PostgreSQL schema design (use a Postgres schema skill)
• You want instant database provisioning (use a Neon Instagres skill)
• You need a different database entirely (use the relevant database skill)

Quick Start

Copy
# Install the skill
claude install supabase-postgres-smart

# Optimize a slow query
claude "This Supabase query takes 2 seconds: supabase.from('orders').select('*, user:users(name)').eq('status', 'active'). How do I optimize it?"

# Set up RLS efficiently
claude "Write performant RLS policies for a multi-tenant app where users can only see their team's data"

# Optimize connection pooling
claude "My Supabase project gets connection errors from Vercel Edge Functions. How do I fix this?"

Core Concepts

Supabase Performance Priorities

Priority	Category	Impact
1	Query Optimization	Reduces response time by 10-100x
2	Proper Indexing	Prevents full table scans
3	RLS Policy Design	Prevents policy evaluation overhead
4	Connection Pooling	Prevents connection exhaustion
5	Edge Function Optimization	Reduces cold start + query time
6	Realtime Optimization	Reduces subscription overhead

RLS Performance Patterns

SLOW (evaluates per row):
  CREATE POLICY "users_own_data" ON documents
    USING (user_id = auth.uid());
  -- Scans every row, checks auth.uid() per row

FAST (uses index + auth context):
  CREATE POLICY "users_own_data" ON documents
    USING (user_id = (SELECT auth.uid()));
  -- Subselect evaluates auth.uid() ONCE, enables index usage

FASTEST (with proper index):
  CREATE INDEX ON documents (user_id);
  CREATE POLICY "users_own_data" ON documents
    USING (user_id = (SELECT auth.uid()));
  -- Index + single evaluation = millisecond performance

Query Optimization Checklist

Check	Action	Tool
Missing indexes	Add indexes for WHERE and JOIN columns	EXPLAIN ANALYZE
N+1 queries	Use `.select(', relation()')	Supabase logs
Over-fetching	Select only needed columns	.select('id, name')
Unnecessary RLS	Bypass for server-side operations	supabase.rpc() with SECURITY DEFINER
Connection leaks	Use connection pooling	Supavisor (built-in)

Configuration

Parameter	Type	Default	Description
pool_mode	string	"transaction"	Pooling: transaction, session, statement
pool_size	number	15	Maximum connections
rls_enabled	boolean	true	Include RLS optimization guidance
edge_functions	boolean	true	Include Edge Function patterns
realtime	boolean	false	Include Realtime optimization

Best Practices

1. Use (SELECT auth.uid()) in RLS policies — Wrapping auth.uid() in a subselect forces PostgreSQL to evaluate it once per query instead of once per row. This single change can make RLS policies 10-100x faster on large tables.

2. Always index your RLS filter columns — If your RLS policy checks user_id or team_id, those columns must have indexes. Without an index, every query does a full table scan even for a single user's data.

3. Use the Supabase connection pooler for serverless — Serverless functions create many short-lived connections. Connect through Supabase's built-in Supavisor pooler (port 6543) instead of the direct connection (port 5432) to prevent connection exhaustion.

4. Select only the columns you need — .select('*') fetches all columns including large TEXT and JSONB fields. Use .select('id, name, email') to reduce data transfer and query time. This is especially important for list views.

5. Use database functions for complex operations — Create PostgreSQL functions (RPC) for operations that involve multiple queries or complex logic. One supabase.rpc('process_order') call is faster than 5 separate client-side queries.

Common Issues

RLS makes queries slow — Check your RLS policies with EXPLAIN ANALYZE (enable in Supabase dashboard). The most common fix is wrapping auth.uid() in a subselect and adding an index on the filtered column.

Connection pool exhausted — Serverless functions create too many connections. Switch to the Supavisor connection string (port 6543), reduce connection timeout, and ensure clients release connections after each request.

Edge Function cold start is slow — The first invocation initializes the Supabase client. Reuse the client across invocations by declaring it outside the handler function. Also minimize imports to reduce cold start time.