Individual skill
design-postgres-tables
Use this skill for general PostgreSQL table design. **Trigger when user asks to:** - Design PostgreSQL tables, schemas, or data models when creating new tables and when modifying existing ones. - Choose data types, constraints, or indexes for PostgreSQL - Create user tables, order tables, reference tables, or JSONB schemas - Understand PostgreSQL best practices for normalization, constraints, or indexing - Design update-heavy, upsert-heavy, or OLTP-style tables **Keywords:** PostgreSQL schema, table design, data types, PRIMARY KEY, FOREIGN KEY, indexes, B-tree, GIN, JSONB, constraints, normalization, identity columns, partitioning, row-level security Comprehensive reference covering data types, indexing strategies, constraints, JSONB patterns, partitioning, and PostgreSQL-specific best practices.
skills/design-postgres-tables/SKILL.md
Source commit c39008a2773f75d63037e8eecf937456204786ba
What was checked
- Matches the observed repository HEAD
- Yes
- Scanner rules are current
- Yes
- Repository scan complete
- No
- Skill scan complete
- Yes
- Observed behavior
- Instructions only
- Last checked
- Nov 9, 2026
These are observations at the recorded commit, not a guarantee of safety. Review the source before running it.
Install this skill
Choose an agent in your Stack or copy a command below. Commands fetch the pinned commit and copy only this skill directory. Existing destinations are refused. Copying a command does not install anything.
An install command is unavailable: current rules, complete repository and skill scans, an eligible behavior level and a permitted license are required.
Repository context
- Repository stars (not skill ratings)
- 1,885
- Repository last updated
- Nov 8, 2026
- License
- Apache-2.0
Observed agent integration: No specific integration evidence. Standard SKILL.md installation does not imply verified compatibility.
- ai Repository topic
- ai-agents Repository topic
- ai-coding Repository topic
- claude-marketplace Repository topic
- claude-plugin Repository topic
- claude-skills Repository topic
- docs Repository topic
- documentation Repository topic
- mcp Repository topic
- mcp-server Repository topic
- design Derived category
- data Derived category
- security Derived category
- data-databases Derived category
Files and excerpt
File metadata and permitted, redacted excerpts come from the recorded scan. Source links are pinned to that commit.
Browse files
SKILL.mdcore · 16,881 bytes
Read excerpt
# 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 **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 values, **DOUBLE PRECISION** for floats (or `NUMERIC` for exact decimal arithmetic).
## PostgreSQL “Gotchas”
- **Identifiers**: unquoted → lowercased. Avoid quoted/mixed-case names. Convention: use `snake_case` for table/column names.
- **Unique + NULLs**: UNIQUE allows multiple NULLs. Use `UNIQUE (...) NULLS NOT DISTINCT` (PG15+) to restrict to one NULL.