PostgreSQL Best Practices

> Goal: Design scalable, secure, and performant database schemas and queries, specifically optimized for Supabase/PostgreSQL environments.

1. Schema Design

• Normalization: Aim for 3NF (Third Normal Form) to reduce redundancy.
• Primary Keys: Use UUID (v7 or v4) or BIGINT (Identity) for primary keys. Avoid simplistic auto-increment INT if scaling horizontally.
• Naming: Use snake_case for table and column names. Plural for tables (users, orders).
• Foreign Keys: Always define foreign key constraints to ensure data integrity. Index all FK columns.

2. Performance & Indexing

• Indexes:
• B-Tree: Default for equality and range queries.
• GIN: For JSONB and Full-Text Search (tsvector).
• GiST: For Geo-spatial data (PostGIS).
• Composite Indexes: Create composite indexes for columns frequently queried together (order matters: equality first, then range).
• Explain Analyze: Always check EXPLAIN ANALYZE on complex queries to verify index usage.

3. Security (Row Level Security - RLS)

• Enable RLS: ALTER TABLE "table_name" ENABLE ROW LEVEL SECURITY;
• Policies: Define strict policies for SELECT, INSERT, UPDATE, DELETE.
• Example: auth.uid() = user_id for user-private data.
• Service Role: Use service role keys only for backend admin tasks; never expose to client.

4. JSONB vs Relational

• When to use JSONB:
• Dynamic payload (e.g., webhook responses, configuration settings).
• Data structure varies significantly per row.
• When NOT to use JSONB:
• Data that needs frequent joining or aggregating.
• Data that requires foreign key constraints.

5. Application Usage (Prisma/Supabase JS)

• Connection Pooling: Use Supabase Transaction Pooler (port 6543) for serverless functions to avoid connection exhaustion.
• N+1 Problem: Be wary of N+1 queries in ORMs (Prisma). Use .include() or explicit joins carefully.

Supabase Specific:

• Realtime: Only enable Realtime for tables that specifically need it (to save resources).
• Storage: Use RLS policies for Storage Buckets file access as well.