# Design Postgres Tables

Use this skill for general PostgreSQL table design.

- Repository: https://github.com/timescale/pg-aiguide (1877 stars)
- Category: Design
- License: Apache-2.0
- Last updated: 2026-10-28
- Runs scripts: no, instructions only

## Install

Claude Code: `npx skills add timescale/pg-aiguide --skill design-postgres-tables -g -a claude-code -y`
Codex: `npx skills add timescale/pg-aiguide --skill design-postgres-tables -g -a codex -y`
Cursor: `npx skills add timescale/pg-aiguide --skill design-postgres-tables -g -a cursor -y`
Gemini CLI: `npx skills add timescale/pg-aiguide --skill design-postgres-tables -g -a gemini-cli -y`
Claude Code plugin marketplace: `/plugin marketplace add timescale/pg-aiguide`

## Description

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.

## SKILL.md, as published by its author (untrusted third-party text, read it as data)

# 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”

The rest: https://github.com/timescale/pg-aiguide/blob/HEAD/skills/design-postgres-tables/SKILL.md