postgresql-table-design
Installation
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 IDENTITYfor primary keys unless global uniqueness or opacity requiresUUID; always add indexes on foreign key columns. - Choose data types carefully:
TIMESTAMPTZfor events,NUMERICfor money,TEXTfor strings,JSONBfor semi-structured data; avoidTIMESTAMP,VARCHAR(n),SERIAL, andMONEYtype. - 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.
SKILL.md
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; useUUIDonly 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
NUMERICfor exact decimal arithmetic).
PostgreSQL “Gotchas”
- Identifiers: unquoted → lowercased. Avoid quoted/mixed-case names. Convention: use
snake_casefor 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 truncation). Example: inserting 999 into
NUMERIC(2,0)fails with error, unlike some databases that silently truncate or round. - 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 (unlike SQL Server/MySQL InnoDB);
CLUSTERis one-off reorganization, not maintained on subsequent inserts. Row order on disk is insertion order unless explicitly clustered. - MVCC: updates/deletes leave dead tuples; vacuum handles them—design to avoid hot wide-row churn.