Design Postgres Tables

A comprehensive reference for PostgreSQL table design that covers the practical stuff you actually need: when to use BIGINT GENERATED ALWAYS AS IDENTITY versus UUID, why you should manually index foreign key columns, and how JSONB indexing works with GIN. It pushes you toward normalization first and includes the gotchas that bite people, like how PostgreSQL doesn't auto-index FKs and why UNIQUE constraints allow multiple NULLs. The guide is opinionated in useful ways, like preferring TEXT over VARCHAR and being explicit about avoiding certain types entirely. Good for both greenfield schema design and when you're refactoring existing tables and need to remember which index type actually makes sense for your query patterns.

skills.sh installs: 649
GitHub stars: 1.9K
last updated: Aug 21, 2026

View on GitHub

Install

$ npx -y skills add timescale/pg-aiguide --skill design-postgres-tables --agent claude-code

Installs into .claude/skills of the current project.

Files

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; 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).