---
name: design-postgres-tables
description: Designs or reviews plain PostgreSQL tables - column types, primary and foreign keys, NOT NULL, CHECK and UNIQUE constraints, the indexes your queries need, JSONB where it fits, partitioning and safe live changes. Use when the user is creating Postgres tables, choosing types or keys, reviewing a schema before launch, or adding a column to a busy table.
license: MIT
metadata:
  category: backend
  tags: "postgres,database,sql"
  author: johnny-bravo
  version: "1.0"
  updated: "2026-11-02"
  status: published
  aliases: "postgresql schema, table design, data types, foreign keys, database schema review, jsonb"
  source_url: "https://github.com/wshobson/agents/tree/7e2fb559cf4b824b27f1fec49fe88f7511066d26/plugins/database-design/skills/postgresql-table-design"
  source_license: MIT
  copyright: "Copyright (c) 2024 Seth Hobson"
  original_author: "cevian (contributed in wshobson/agents PR #113, postgresql-table-design); repo by Seth Hobson"
  modified: "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."
  imported_on: "2026-11-02"
---

# Design PostgreSQL tables

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` |

```sql
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.