PostgreSQL Table Design

Core Rules

Define a PRIMARY KEY for reference tables (users, orders, etc.). Not always needed for time-series/event/log data. When used, prefer BIGINT GENERATED ALWAYS AS IDENTITY; use UUID only when global uniqueness/opacity is needed.
Normalize first (to 3NF) to eliminate data redundancy and update anomalies; denormalize only for measured, high-ROI reads where join performance is proven problematic. Premature denormalization creates maintenance burden.
Add NOT NULL everywhere it’s semantically required; use DEFAULTs for common values.
Create indexes for access paths you actually query: PK/unique (auto), FK columns (manual!), frequent filters/sorts, and join keys.
Prefer TIMESTAMPTZ for event time; NUMERIC for money; TEXT for strings; BIGINT for integer values, DOUBLE PRECISION for floats (or NUMERIC for exact decimal arithmetic).

Identifiers: unquoted → lowercased. Avoid quoted/mixed-case names. Convention: use snake_case for table/column names.
Unique + NULLs: UNIQUE allows multiple NULLs. Use UNIQUE NULLS NOT DISTINCT (...) (PG15+) when NULLs should compare equal for uniqueness.
FK indexes: PostgreSQL does not auto-index FK columns. Add them.
Coercions: numeric scale can round before precision is checked. Excess precision then errors; explicit casts to bounded character types can truncate. Validate the intended range and precision.
Sequences/identity have gaps (normal; don't "fix"). Rollbacks, crashes, and concurrent transactions create gaps in ID sequences (1, 2, 5, 6...). This is expected behavior—don't try to make IDs consecutive.
Heap storage: no clustered PK by default; CLUSTER is a one-off reorganization. Do not rely on physical ordering; use ORDER BY for defined result order.
MVCC: updates/deletes leave dead tuples; vacuum handles them—design to avoid hot wide-row churn.