postgresqllisted

# PostgreSQL Table Design ## Use this skill when - Designing a schema for PostgreSQL - Selecting data types and constraints that enforce invariants - Planning indexes, partitions, or RLS policies - Reviewing tables for scale and maintainability ## Workflow 1. Capture entities, access patterns, and scale targets (rows, QPS, retention). 2. Choose data types and constraints that enforce invariants. 3. Add indexes for real query paths and validate with `EXPLAIN`. 4. Plan partitioning or RLS where required by scale or access control. 5. Review migration impact and apply changes safely (backups + staging first; no destructive DDL on prod without a rollback plan). ## 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 **DEFAULT**s 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 va