Design PostgreSQL tables

curl -fsSL --create-dirs 'https://theskillvault.ai/api/skills/design-postgres-tables/raw' -o ~/'.claude/skills/design-postgres-tables/SKILL.md' && curl -fsSL --create-dirs 'https://theskillvault.ai/api/skills/design-postgres-tables/files/LICENSE.txt' -o ~/'.claude/skills/design-postgres-tables/LICENSE.txt'

curl -fsSL --create-dirs 'https://theskillvault.ai/api/skills/design-postgres-tables/raw' -o '.cursor/skills/design-postgres-tables/SKILL.md' && curl -fsSL --create-dirs 'https://theskillvault.ai/api/skills/design-postgres-tables/files/LICENSE.txt' -o '.cursor/skills/design-postgres-tables/LICENSE.txt'

curl -fsSL --create-dirs 'https://theskillvault.ai/api/skills/design-postgres-tables/raw' -o '.agents/skills/design-postgres-tables/SKILL.md' && curl -fsSL --create-dirs 'https://theskillvault.ai/api/skills/design-postgres-tables/files/LICENSE.txt' -o '.agents/skills/design-postgres-tables/LICENSE.txt'

{
  "mcpServers": {
    "theskillvault": {
      "url": "https://theskillvault.ai/mcp"
    }
  }
}

Good tables make bugs hard to write. Let the database reject bad data with types and constraints, then index the queries you actually run.

When to use

• You are creating the first tables for an app on any Postgres host.
• You are unsure which column type, key or constraint to use.
• You want a schema reviewed before real users arrive.
• You need to add or change a column on a table that is already busy.

When not to use

• Your database is Supabase and you need row level security. Use design-supabase-postgres-schema.
• A query is already slow. Use optimise-slow-sql-queries to read the plan first.

Steps

1. List the things you store and how they relate: a user has many orders, an order has many items.
2. Give each table a key: id bigint generated always as identity primary key. Use uuid only when ids must be unguessable or made outside the database.
3. Normalise first: one fact in one place. Copy data for speed only after you measure a slow join.
4. Pick types from the table below, and add not null wherever a value is required.
5. Add foreign keys with an explicit on delete rule, then index each foreign key column yourself.
6. Add check and unique constraints for business rules, such as a positive price or one email per user.
7. Index for real queries: filters, sorts and joins you run often. Skip guesses.
8. Use jsonb only for optional or changing attributes, and pull hot fields into real columns.
9. Partition only very large tables (around 100 million rows or more) that every query filters by one key, such as a date.
10. Ship schema changes as small migrations, and test them on a copy of production data first.

| Store | Use | Avoid |
| --- | --- | --- |
| Text | `text`, plus `check (length(x) <= n)` if needed | `varchar(n)`, `char(n)` |
| Whole numbers | `bigint` (or `integer` for small counts) | `serial` |
| Money, exact decimals | `numeric(12,2)` | `money`, `float` |
| Times | `timestamptz` | `timestamp` without zone |
| Yes or no | `boolean not null` | text flags |
| Fixed short list | `text` + `check (x in (...))` | enums for lists that will grow |
| Flexible extras | `jsonb` + a GIN index | `json` |

create table customers (
  id bigint generated always as identity primary key,
  email text not null,
  created_at timestamptz not null default now()
);
create unique index customers_email_lower on customers (lower(email));

create table invoices (
  id bigint generated always as identity primary key,
  customer_id bigint not null references customers(id) on delete restrict,
  status text not null default 'draft' check (status in ('draft','sent','paid','void')),
  total numeric(12,2) not null check (total >= 0),
  created_at timestamptz not null default now()
);
create index invoices_customer_id on invoices (customer_id);
create index invoices_open on invoices (created_at) where status <> 'paid';

Live changes without downtime

1. Add new columns as nullable or with a constant default, then backfill in batches.
2. Build indexes with create index concurrently so writes keep flowing.
3. Add constraints as not valid, then run validate constraint in a later step.
4. Rename or drop only after every running version of the app has stopped using the old column.

Gotchas

• Postgres does not index foreign keys for you. Missing ones make deletes and joins slow.
• unique allows many nulls. Use unique nulls not distinct (Postgres 15+) when you want one.
• Unquoted names are lowercased. Stick to snake_case and never quote identifiers.
• Identity and sequence values have gaps after rollbacks. That is normal, so do not "fix" it.
• Partitioned tables cannot have a unique key that leaves out the partition key.
• Take a backup before any destructive migration, and keep connection strings and passwords out of migration files.

Portability

Works with any Postgres host and any tool that runs SQL migrations: Prisma, Drizzle, Rails, Django, Flyway or plain psql. Most rules also apply to other SQL databases if you swap the type names for their equivalent.

Example prompt

"Design Postgres tables for a small booking app with customers, rooms and bookings. Stop double bookings and tell me which indexes I need."

Done when

• Every table has a primary key, required columns are not null and every foreign key is indexed.
• Business rules are enforced by constraints, not only by app code.
• Each index maps to a query you run, and migrations ran cleanly on a copy of real data.

---

Adapted from "postgresql-table-design" in wshobson/agents (MIT, Copyright (c) 2024 Seth Hobson). See LICENSE.txt.

Bundled files

• LICENSE.txt

Licence

Adapted from cevian (contributed in wshobson/agents PR #113, postgresql-table-design); repo by Seth Hobson (Copyright (c) 2024 Seth Hobson), MIT License. Adapted by TheSkillVault on 2026-11-02: rewritten as a design order with an original example schema; vendor-specific extension and hosting mentions (TimescaleDB and hypertables) removed; the long type catalogue was cut to one table; added safe live-change steps, gotchas and done-when. Not the original file.

Licence: MIT License. Attribution when you reuse it: Based on work by cevian (contributed in wshobson/agents PR #113, postgresql-table-design); repo by Seth Hobson (Copyright (c) 2024 Seth Hobson, MIT License), https://github.com/wshobson/agents/tree/7e2fb559cf4b824b27f1fec49fe88f7511066d26/plugins/database-design/skills/postgresql-table-design; adapted by TheSkillVault, https://theskillvault.ai