postgresql-table-design

Design PostgreSQL schemas with best practices for data types, indexing, and performance.

• Design PostgreSQL-specific table structures
• Follow normalization and denormalization rules
• Choose appropriate data types and constraints
• Optimize indexing for query performance

• Use PRIMARY KEY with GENERATED ALWAYS AS IDENTITY
• Normalize to 3NF, denormalize only for high-ROI reads
• Index FK columns manually; no auto-indexing
• Use TIMESTAMPTZ for events, NUMERIC for money
• Avoid silent coercions and truncation
• No silent gaps in sequence IDs
• Heap storage has no clustering by default
• MVCC leaves dead tuples; avoid hot row churn

1. 1.Define PK with GENERATED ALWAYS AS IDENTITY or UUID when needed
2. 2.Add indexes on FKs, frequent filters, and join keys
3. 3.Use appropriate data types (e.g., NUMERIC for money)
4. 4.Index text with GIN for full-text search
5. 5.Avoid char/varchar; use text with length checks
6. 6.Use GIN for array and range types