Agent skill · wshobson
postgresql-table-design
Use this skill when designing or reviewing a PostgreSQL-specific schema. Covers best-practices, data types, indexing, constraints, performance patterns, and advanced features
What it needs
About 4k tokens when loaded.
What this skill does
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. 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. Add NOT NULL everywhere it is 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 integers; DOUBLE PRECISION for floats (or NUMERIC for exact decimal arithmetic). PostgreSQL Gotchas Identifiers: unquoted → lowercased. Avoid quoted/mixed-case names; use snakecase. 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). Inserting 999 into NUMERIC(2,0) fails, unlike databases that silently truncate or round. Sequences/identity have gaps (normal; don't "fix"). Rollbacks, crashes, and concurrent transactions leave gaps (1, 2, 5, 6...). …
How to use it
Reference it in AdaL, Claude Code, Cursor or any coding agent — nothing to install:
@skills wshobson/postgresql-table-design