Postgres Best Practices

Postgres performance optimization guidelines from Supabase, prioritized by impact.

Categories (Priority Order)

1. Query Performance (Critical)

• Use EXPLAIN ANALYZE to understand query plans

• Add indexes on frequently queried columns (WHERE, JOIN, ORDER BY)

• **Avoid SELECT *** - specify only needed columns

• Use LIMIT for large result sets

• Optimize JOINs - ensure foreign keys are indexed

• Use prepared statements (Supabase client does this automatically)

• Batch operations when possible

2. Connection Management (Critical)

• Use connection pooling (Supabase provides this)

• Close connections properly

• Avoid connection leaks - use connection limits

• Monitor connection usage in Supabase dashboard

• Use server-side clients for server components

• Use client-side clients only in client components

3. Schema Design (High)

• Choose appropriate data types (UUID vs INTEGER, VARCHAR vs TEXT)

• Use NOT NULL constraints where appropriate

• Add foreign key constraints for data integrity

• Use ENUMs for fixed value sets

• Normalize appropriately - balance with query performance

• Use JSONB for flexible schema (products, metadata)

4. Concurrency & Locking (Medium-High)

• Use transactions for atomic operations

• Keep transactions short - avoid long-running transactions

• Use appropriate isolation levels

• Avoid deadlocks - acquire locks in consistent order

• Use SELECT FOR UPDATE carefully (can cause blocking)

5. Security & RLS (Medium-High)

• Enable RLS on all tables

• Create policies for tenant isolation in multitenant systems

• Test RLS policies thoroughly

• Use service role only when necessary (bypasses RLS)

• Validate inputs before database operations

• Use parameterized queries (Supabase client does this)

6. Data Access Patterns (Medium)

• Use pagination for large datasets (.range() in Supabase)

• Implement caching for frequently accessed data

• Use materialized views for complex aggregations

• Consider read replicas for read-heavy workloads

• Optimize for common query patterns

7. Monitoring & Diagnostics (Low-Medium)

• Monitor slow queries in Supabase dashboard

• Use pg_stat_statements for query analysis

• Set up alerts for performance degradation

• Review query logs regularly

• Track connection pool usage

8. Advanced Features (Low)

• Use full-text search (PostgreSQL tsvector)

• Consider partitioning for very large tables

• Use triggers judiciously

• Leverage Postgres extensions when needed

Common Patterns

Index Creation

-- Single column index CREATE INDEX idx_products_tenant_id ON products(tenant_id);

-- Composite index (order matters!) CREATE INDEX idx_products_tenant_category ON products(tenant_id, category_id);

-- Partial index (for filtered queries) CREATE INDEX idx_products_active ON products(tenant_id) WHERE active = true;

-- Unique index CREATE UNIQUE INDEX idx_products_sku ON products(tenant_id, sku);

Query Optimization

// ❌ Bad: SELECT * and no limit const { data } = await supabase .from('products') .select('*');

// ✅ Good: Specific columns with limit const { data } = await supabase .from('products') .select('id, name, price, image') .eq('tenant_id', tenant.id) .eq('active', true) .order('created_at', { ascending: false }) .limit(20);

Pagination

// Use range for pagination const pageSize = 20; const page = 1;

const { data, error } = await supabase .from('products') .select('id, name, price') .eq('tenant_id', tenant.id) .range((page - 1) * pageSize, page * pageSize - 1);

RLS Policy Best Practices

-- Enable RLS ALTER TABLE products ENABLE ROW LEVEL SECURITY;

-- Tenant isolation policy CREATE POLICY "tenant_isolation" ON products FOR ALL USING ( tenant_id = ( SELECT id FROM tenants WHERE slug = current_setting('app.tenant_slug', true) ) );

-- Public read policy (if needed) CREATE POLICY "public_read_active" ON products FOR SELECT USING ( active = true AND tenant_id = ( SELECT id FROM tenants WHERE slug = current_setting('app.tenant_slug', true) ) );

Connection Usage

// ✅ Server component - use server client import { createClient } from '@/lib/supabase/server';

export async function ServerComponent() { const supabase = createClient(); // Use supabase }

// ✅ Client component - use client 'use client'; import { createClient } from '@/lib/supabase/client';

export function ClientComponent() { const supabase = createClient(); // Use supabase }

Performance Checklist

• Queries use indexes on WHERE/JOIN columns

• SELECT statements specify columns (not *)

• Large queries use LIMIT or pagination

• RLS policies are optimized (not too complex)

• Foreign keys have indexes

• Transactions are kept short

• Connection pooling is configured

• Slow queries are identified and optimized

Key Files

• supabase/migrations/

• Schema and indexes

• src/lib/supabase/

• Client configuration

• Supabase Dashboard - Query performance monitoring