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.