design-postgres-tables

PostgreSQL table design guide covering data types, indexing, constraints, and performance best practices.

• Comprehensive PostgreSQL table design reference
• Rules for data types, indexing, and constraints
• Performance patterns and advanced features
• Guidance on normalization, denormalization, and storage

• Define PK with GENERATED ALWAYS AS IDENTITY or UUID when needed
• Normalize to 3NF, denormalize only for high-ROI reads
• Index FK columns manually; no auto-FK indexing
• Use TIMESTAMPTZ for events, NUMERIC for money
• Avoid silent coercions and truncation in data types
• Heap storage has no clustered PK by default
• MVCC leaves dead tuples; avoid hot row churn
• Use GIN/GiST for array, range, and text search indexing

1. 1.Use BIGINT GENERATED ALWAYS AS IDENTITY for primary keys
2. 2.Add indexes on FK columns and query paths
3. 3.Choose appropriate data types (e.g., TEXT, NUMERIC)
4. 4.Avoid char/varchar; prefer text with length checks
5. 5.Use GIN for array and text search indexing
6. 6.Use UUID only for global opacity or distributed systems