PostgreSQL Table Design

Core Rules

• Define a PRIMARY KEY for reference tables. Prefer BIGINT GENERATED ALWAYS AS IDENTITY; use UUID only when global uniqueness is needed.
• Normalize first (to 3NF) to eliminate data redundancy; denormalize only for measured performance needs.
• 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.
• Prefer TIMESTAMPTZ for event time; NUMERIC for money; TEXT for strings; BIGINT for integers.

PostgreSQL "Gotchas"

• Identifiers: unquoted names are lowercased. Use snake_case.
• Unique + NULLs: UNIQUE allows multiple NULLs. Use NULLS NOT DISTINCT (PG15+) to restrict.
• FK indexes: PostgreSQL does not auto-index FK columns. Add them manually.
• No silent coercions: length/precision overflows error out (no truncation).
• Sequences have gaps: Normal behavior, don't try to "fix" it.

Data Types