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
When to Use
- Designing a new PostgreSQL schema, or reviewing one before it ships.
- Choosing column types, keys, constraints, or indexes for PostgreSQL specifically.
- Deciding whether and how to partition a large table, or how to store semi-structured data.
- Planning a schema change on a live database without downtime.
The rules and decision points for a PostgreSQL schema. The full data-type catalog, workload
patterns (update-heavy, insert-heavy, upsert, schema evolution), extensions, JSONB indexing,
and worked DDL examples are in references/details.md; open it when a section below points there.