Summary
- PostgreSQL schema design covering best practices, data types, indexing, constraints, and performance patterns.
- Prioritize normalization to 3NF; denormalize only when join performance is proven problematic and measured for ROI.
- Use BIGINT GENERATED ALWAYS AS IDENTITY for primary keys unless global uniqueness or opacity requires UUID ; always add indexes on foreign key columns.
- Choose data types carefully: TIMESTAMPTZ for events, NUMERIC for money, TEXT for strings, JSONB for semi-structured data; avoid TIMESTAMP , VARCHAR(n) , SERIAL , and MONEY type.
- Index strategically for actual query patterns: B-tree for equality/range, GIN for JSONB/arrays/full-text, GiST for ranges/geometry, BRIN for large time-series data.
- Partition tables >100M rows by range (time) or hash; use TimescaleDB for time-series automation; separate hot/cold columns and minimize indexes for insert-heavy workloads.