PostgreSQL Best Practices
Goal: Design scalable, secure, and performant database schemas and queries, specifically optimized for Supabase/PostgreSQL environments.
- 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.
- 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.
- 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.
- 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.
- 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.