postgres-schema-design

Topics in the source

• PostgreSQL Table Design
• Core Rules
• PostgreSQL “Gotchas”
• Data Types
• Do not use the following data types
• Table Types
• Row-Level Security
• Constraints

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 snakecase for table/column names. Unique + NULLs: UNIQUE allows multiple NULLs. Use UNIQUE (...) NULLS NOT DISTINCT (PG15+) to restrict to one NULL. FK indexes: PostgreSQL does not auto-index FK columns. Add them. No silent coercions: length/precision overflows error out (no truncati

This is an excerpt. Refer to the complete source and its current requirements.