Supabase Production Deployment: AI-Optimized Knowledge Base

Critical Configuration

Connection Pooling (MANDATORY)

Configuration:

postgresql://postgres:[PASSWORD]@db.[REF].supabase.co:6543/postgres?pgbouncer=true

Connection Limits:

• Free tier: 200 connections
• Pro tier: 500 connections
• Connection exhaustion starts at 100-150 concurrent requests

Failure Mode: "remaining connection slots are reserved" errors
Impact: Complete application failure with as few as 20 concurrent users
Solution: Use transaction pooling mode - releases connections after each query

Row Level Security Production Pitfalls

Critical Vulnerability: JWT expiration bypasses security

• Development JWTs: 24 hours
• Production JWTs: 1 hour
• auth.uid() returns null on expired tokens

Production-Safe Policy Pattern:

CREATE POLICY "users_own_data" ON profiles
  FOR ALL USING (
    auth.uid() IS NOT NULL AND 
    auth.uid()::text = user_id::text
  );

Testing Context Requirement:

SELECT set_config('request.jwt.claims', '{"sub":"[REAL_USER_ID]","role":"authenticated"}', true);

Error Pattern: "insufficient_privilege" with no actionable information

Resource Requirements & Scaling Thresholds

Database Performance Breaking Points

• UI becomes unusable: 1000 spans in distributed transaction tracing
• Query timeout threshold: 10 seconds
• Connection monitoring alert: >400 connections (80% of Pro limit)
• Slow query investigation: >10 seconds execution time

Storage Cost Structure

• Bandwidth: $0.09/GB (primary cost driver)
• CDN optimization: Reduces to $0.03/GB
• Image transformation: ?width=800&quality=80 converts 5MB → 200KB
• Real cost example: $800 bandwidth bill on $25 Pro plan from video uploads

Memory Constraints

• Edge Functions: 2MB file processing limit
• Failure mode: OOM crashes with larger files
• Workaround: Stream processing instead of loading entire files into memory

Critical Warnings & Failure Scenarios

Real-time Connection Stability

Mobile failure rate: High due to network transitions
Scaling limit: 200 concurrent users before random disconnections
Reconnection pattern:

const reconnectWithBackoff = (attempt = 1) => {
  const delay = Math.min(1000 * Math.pow(2, attempt), 30000);
  setTimeout(() => channel.subscribe(), delay);
};

Multi-tenancy Data Breach Risk

Critical requirement: JWT must include tenant_id
Failure consequence: Cross-tenant data exposure
Frequency: Common mistake in JWT claim configuration

Read Replica Consistency Issues

Lag time: 100-500ms behind writes
User impact: "Lost" data perception immediately after updates
Mitigation: Route to primary for 2 seconds after writes

Production Infrastructure Requirements

Mandatory Indexes

CREATE INDEX CONCURRENTLY idx_profiles_user_id ON profiles(user_id);
CREATE INDEX CONCURRENTLY idx_posts_created_at ON posts(created_at DESC);
CREATE INDEX CONCURRENTLY idx_posts_search ON posts USING gin(to_tsvector('english', title || ' ' || content));

Background Job Processing

Do NOT use: Edge Functions for background jobs
Recommended alternatives:

• Upstash Redis: Simple job queues
• Inngest: Event-driven workflows
• AWS SQS: Complex workflows
• Temporal: Multi-step orchestration

Monitoring Alert Thresholds

• Database connections > 400
• Query execution > 10 seconds
• Error rate > 5% for 5+ minutes
• Real-time connections dropping 50+ per minute

Decision Criteria Matrix

Tier Selection Based on Scale

Requirement	Free	Pro ($25/mo)	Enterprise ($500+/mo)
Database size	<500MB	<8GB	>8GB
Monthly users	<50K	<100K	>100K
Bandwidth	<5GB	<250GB	>250GB
Support SLA	None	99.9%	99.99%

When to Upgrade Triggers

• Pro → Enterprise: Database hits 8GB (happens faster than expected)
• Enterprise necessity: Compliance requirements for enterprise sales
• Cost crossover: Bandwidth bill exceeds Pro plan cost

Implementation Reality Checks

Load Testing Truth

Synthetic vs Real: Real users break apps in unpredictable ways
Connection test: 300 concurrent requests reveal pool exhaustion
Performance baseline: Most apps fail at 100-150 concurrent requests

Migration Complexity

"Zero-downtime" reality: Marketing term, every migration has risk
Safe pattern: 3am deployments during low traffic
Rollback requirement: Always have immediate rollback plan

Edge Function Limitations

Memory constraints: Tighter than development suggests
Timeout limits: 10 minutes maximum
Use case restriction: API endpoints only, not data processing

Operational Intelligence

Common Production Failures

1. Connection pool exhaustion: 90% of initial scaling issues
2. Missing indexes: Primary cause of slow queries
3. JWT expiration: Security bypass vulnerability
4. Real-time disconnections: Mobile network instability

Hidden Costs

• Bandwidth overages: Primary unexpected expense
• Support quality: Community often faster than official channels
• Migration complexity: Time investment higher than documentation suggests

Community Wisdom

• pgbouncer parameter: Single most important configuration
• RLS testing: Must use real JWTs, not dashboard simulation
• Edge Functions: Limited utility beyond simple API endpoints
• Read replicas: Most abandon due to consistency complexity

Success Patterns

Configuration Hierarchy

1. Enable connection pooling (?pgbouncer=true)
2. Create essential indexes
3. Implement proper RLS policies
4. Set up external job processing
5. Configure realistic monitoring

Proven Architecture

• API routes: Direct Supabase connection with pooling
• Background jobs: External service (Inngest/Upstash)
• File processing: Stream-based, not memory-loaded
• Real-time: Polling for non-interactive features
• Caching: Redis for API responses, not complex multi-layer

Resource Allocation Priority

1. Critical: Connection pooling and indexes
2. Important: Monitoring and alerting
3. Nice-to-have: Read replicas and advanced features
4. Enterprise: Multi-region and disaster recovery