AgentAlley

Skills · Coding

Postgresql Table Design

Unverified●24/40

Use this skill when designing or reviewing a PostgreSQL-specific schema. Covers best-practices, data types, indexing, constraints, performance patterns, and advanced features

Originally by wshobson · MIT

Claude Code◐PartialHas SKILL.md but declares no allowed-tools — Claude Code will ask for permission each time
Cursor◐PartialPlain prose you can paste in — but no Cursor rules file
Codex◐PartialPlain prose you can paste in — but no AGENTS.md
Gemini CLI◐PartialPlain prose you can paste in
Copilot◐PartialPlain prose you can paste in — but no Copilot instructions file
npx agentalley add postgresql-table-design

This command does not work yet — the CLI is still being built. Until then, use Raw in the reader below to take the file.

Who is stuck, and on what

Use this skill when designing or reviewing a PostgreSQL-specific schema. Covers best-practices, data types, indexing, constraints, performance patterns, and advanced features

The whole source

No sign-in, no blur, nothing truncated
postgresql-table-design/SKILL.md130 lines7.7 KBRawView on GitHub
Frontmatter — 2 properties
namepostgresql-table-design
descriptionUse this skill when designing or reviewing a PostgreSQL-specific schema. Covers best-practices, data types, indexing, constraints, performance patterns, and advanced features
1---
2name: postgresql-table-design
3description: Use this skill when designing or reviewing a PostgreSQL-specific schema. Covers best-practices, data types, indexing, constraints, performance patterns, and advanced features
4---A5 — No allowed-tools declared — no way to tell what this skill may touch
5 
6# PostgreSQL Table Design
7 
8## When to Use
9 
10- Designing a new PostgreSQL schema, or reviewing one before it ships.
11- Choosing column types, keys, constraints, or indexes for PostgreSQL specifically.
12- Deciding whether and how to partition a large table, or how to store semi-structured data.
13- Planning a schema change on a live database without downtime.
14 
15The rules and decision points for a PostgreSQL schema. The full data-type catalog, workload
16patterns (update-heavy, insert-heavy, upsert, schema evolution), extensions, JSONB indexing,
17and worked DDL examples are in `references/details.md`; open it when a section below points there.
18 
19## Core Rules
20 
21- 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.
22- **Normalize first (to 3NF)** to eliminate data redundancy and update anomalies; denormalize **only** for measured, high-ROI reads where join performance is proven problematic.
23- Add **NOT NULL** everywhere it is semantically required; use **DEFAULT**s for common values.
24- Create **indexes for access paths you actually query**: PK/unique (auto), **FK columns (manual!)**, frequent filters/sorts, and join keys.
25- Prefer **TIMESTAMPTZ** for event time; **NUMERIC** for money; **TEXT** for strings; **BIGINT** for integers; **DOUBLE PRECISION** for floats (or `NUMERIC` for exact decimal arithmetic).
26 
27## PostgreSQL Gotchas
28 
29- **Identifiers**: unquoted → lowercased. Avoid quoted/mixed-case names; use `snake_case`.
30- **Unique + NULLs**: UNIQUE allows multiple NULLs. Use `UNIQUE NULLS NOT DISTINCT (...)` (PG15+) to restrict to one NULL.
31- **FK indexes**: PostgreSQL **does not** auto-index FK columns. Add them.
32- **No silent coercions**: length/precision overflows error out (no truncation). Inserting 999 into `NUMERIC(2,0)` fails, unlike databases that silently truncate or round.A1 — Empties a table
33- **Sequences/identity have gaps** (normal; don't "fix"). Rollbacks, crashes, and concurrent transactions leave gaps (1, 2, 5, 6...).
34- **Heap storage**: no clustered PK by default; `CLUSTER` is a one-off reorganization, not maintained on later inserts.
35- **MVCC**: updates/deletes leave dead tuples; vacuum handles them—design to avoid hot wide-row churn.
36 
37## Data Types
38 
39- **IDs**: `BIGINT GENERATED ALWAYS AS IDENTITY`; `UUID` for distributed or opaque IDs, generated with `uuidv7()` (PG18+) or `gen_random_uuid()`.
40- **Numbers**: `BIGINT` unless storage is critical; `DOUBLE PRECISION` over `REAL`; `NUMERIC(p,s)` for money and exact decimals.
41- **Strings**: `TEXT`, with `CHECK (LENGTH(col) <= n)` when a limit is needed; `BYTEA` for binary. Case-insensitive lookups: expression index on `LOWER(col)`, or `CITEXT` when a constraint must be case-insensitive.
42- **Time**: `TIMESTAMPTZ`, `DATE`, `INTERVAL`. `now()` is transaction start; `clock_timestamp()` is wall clock.
43- **Booleans**: `BOOLEAN NOT NULL` unless tri-state is required.
44- **Enums**: `CREATE TYPE ... AS ENUM` only for small, stable sets; evolving business values get `TEXT` + `CHECK` or a lookup table.
45- **JSONB** over JSON, indexed with GIN, for optional/semi-structured attributes only.
46- Arrays, ranges, network, geometric, full-text, domain, composite, and vector types, plus TOAST storage and collation control: see `references/details.md`.
47 
48### Types to avoid
49 
50| Avoid | Use instead |
51|---|---|
52| `timestamp` (without time zone) | `timestamptz` |
53| `char(n)`, `varchar(n)` | `text` (+ `CHECK` on length if needed) |
54| `money` | `numeric` |
55| `timetz` | `timestamptz` |
56| `timestamptz(0)` or any precision | `timestamptz` |
57| `serial` | `generated always as identity` |
58 
59## Constraints
60 
61- **PK**: implicit UNIQUE + NOT NULL; creates a B-tree index.
62- **FK**: specify `ON DELETE/UPDATE` (`CASCADE`, `RESTRICT`, `SET NULL`, `SET DEFAULT`). Index the referencing column. Use `DEFERRABLE INITIALLY DEFERRED` for circular dependencies checked at commit.
63- **UNIQUE**: creates a B-tree index; allows multiple NULLs unless `NULLS NOT DISTINCT` (PG15+). Prefer `NULLS NOT DISTINCT` unless duplicate NULLs are wanted.
64- **CHECK**: row-local; NULL passes (three-valued logic). Combine with `NOT NULL`: `price NUMERIC NOT NULL CHECK (price > 0)`.
65- **EXCLUDE**: prevents overlaps with operators, e.g. `EXCLUDE USING gist (room_id WITH =, booking_period WITH &&)` stops double-booking. Needs a GiST-capable type.
66 
67## Indexing
68 
69- **B-tree**: default for equality/range (`=`, `<`, `>`, `BETWEEN`, `ORDER BY`).
70- **Composite**: leftmost-prefix rule (`WHERE a = ? AND b > ?` uses `(a,b)`; `WHERE b = ?` does not). Most selective columns first.
71- **Covering**: `CREATE INDEX ON tbl (id) INCLUDE (name, email)` for index-only scans.
72- **Partial**: hot subsets, `CREATE INDEX ON tbl (user_id) WHERE status = 'active'`.
73- **Expression**: `CREATE INDEX ON tbl (LOWER(email))`; the query must use the same expression.
74- **GIN**: JSONB containment/existence, arrays, full-text search. **GiST**: ranges, geometry, exclusion constraints.
75- **BRIN**: large, naturally ordered data (time-series) at minimal storage cost; effective when disk order correlates with the indexed column.
76 
77## Partitioning
78 
79- Use for large tables (>100M rows) whose queries consistently filter on the partition key, or where maintenance (pruning, bulk replacement) follows a key.
80- **RANGE** for time-series (`PARTITION BY RANGE (created_at)`; **TimescaleDB** automates it with retention and compression), **LIST** for discrete values, **HASH** for even distribution without a natural key.
81- **Constraint exclusion**: the planner prunes partitions through their `CHECK` constraints; declarative partitioning (PG10+) creates them for you.
82- Prefer declarative partitioning or hypertables. Do NOT use table inheritance.
83- **Limitations**: no global UNIQUE constraints—include the partition key in PK/UNIQUE. FKs from partitioned tables need PG11+, FKs referencing a partitioned table need PG12+; on older versions, use triggers.
84 
85## Examples
86 
87```sql
88CREATE TABLE users (
89 user_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
90 email TEXT NOT NULL UNIQUE,
91 name TEXT NOT NULL,
92 created_at TIMESTAMPTZ NOT NULL DEFAULT now()
93);
94CREATE UNIQUE INDEX ON users (LOWER(email));
95CREATE INDEX ON users (created_at);
96```
97 
98```sql
99CREATE TABLE orders (
100 order_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
101 user_id BIGINT NOT NULL REFERENCES users(user_id),
102 status TEXT NOT NULL DEFAULT 'PENDING' CHECK (status IN ('PENDING','PAID','CANCELED')),
103 total NUMERIC(10,2) NOT NULL CHECK (total > 0),
104 created_at TIMESTAMPTZ NOT NULL DEFAULT now()
105);
106CREATE INDEX ON orders (user_id);
107CREATE INDEX ON orders (created_at);
108```
109 
110```sql
111-- JSONB attributes with a generated, indexable scalar
112CREATE TABLE profiles (
113 user_id BIGINT PRIMARY KEY REFERENCES users(user_id),
114 attrs JSONB NOT NULL DEFAULT '{}',
115 theme TEXT GENERATED ALWAYS AS (attrs->>'theme') STORED
116);
117CREATE INDEX profiles_attrs_gin ON profiles USING GIN (attrs);
118```
119 
120## Going deeper
121 
122`references/details.md` holds the material this file only names:
123 
124- The full data-type catalog: TOAST storage, collations, arrays, ranges, network, geometric, text search, domains, composites, vectors.
125- Table types (`TEMPORARY`, `UNLOGGED`) and row-level security.
126- Constraint and index notes, and partitioning DDL for RANGE, LIST, and HASH.
127- Workload patterns: update-heavy, insert-heavy, upsert design, safe schema evolution.
128- Generated columns and extensions (`pg_trgm`, `citext`, `timescaledb`, `postgis`, `pgvector`, and more).
129- JSONB indexing strategies, including `jsonb_path_ops` and extracted B-tree columns.
130 

Reviews

Installed this one?Write the first review and take the Trailblazer badge.

Reviews only open after a real install, so this is empty — and we leave it empty rather than invent one.

Alternatives

Also in Coding
Subagent Driven DevelopmentUse when executing implementation plans with independent tasks in the current sessionCoding · MIT◐◐◐◐◐●36/40Python Code Style & DocumentationPython code style, linting, formatting, naming conventions, and documentation standards. Use when writing new code, reviewing style, configuring linters, writing docstrings, or establishing project standards.Coding · MIT◐····●35/40Competitor Price Analysis 💲Competitor pricing strategy analysis and market positioning. Price mapping, pricing gaps identification, elasticity signals evaluation, and strategic pricing optimization. Use when the user asks about competitor pricing, price analysis, pricing strategy, or coCoding · MIT◐····●34/40Competitor Price Tracker 📊Set up competitor price tracking and monitoring workflows. Track price changes, detect promotions, analyze pricing patterns, and get alerts for competitive price movements.Coding · MIT◐····●34/40