PostgreSQL Schema Design Engine

A Claude Code skill for designing robust, performant PostgreSQL database schemas. Covers table design, data types, primary keys, indexing strategies, constraints, relationships, migrations, and optimization — following PostgreSQL best practices for production workloads.

When to Use This Skill

Choose PostgreSQL Schema Design when:

• You're designing a new database schema for a project
• You need to optimize existing table structures for performance
• You want to implement proper indexing strategies
• You need to design relationships, constraints, and migrations
• You want to follow PostgreSQL-specific best practices

Consider alternatives when:

• You need instant database provisioning (use Neon Instagres skill)
• You want Supabase-specific patterns (use a Supabase skill)
• You need NoSQL database design (use a relevant NoSQL skill)

Quick Start

Copy
# Install the skill
claude install postgres-schema-design-engine

# Design a schema
claude "Design a PostgreSQL schema for a multi-tenant SaaS application with users, teams, projects, and tasks"

# Optimize an existing schema
claude "Review this schema for performance issues: [paste CREATE TABLE statements]"

# Add proper indexing
claude "Suggest indexes for these queries on my users and orders tables: [paste queries]"

Core Concepts

Primary Key Selection

Strategy	When to Use	Example
BIGINT GENERATED ALWAYS	Reference tables (users, orders)	id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY
UUID	Distributed systems, public-facing IDs	id UUID DEFAULT gen_random_uuid() PRIMARY KEY
Natural Key	Unique business identifiers	email TEXT PRIMARY KEY (rare, use carefully)
Composite	Junction/relationship tables	PRIMARY KEY (user_id, project_id)
No PK	Time-series, event logs, append-only	Partition by timestamp instead

Data Type Guidelines

Text:
  TEXT for variable strings (no VARCHAR needed in Postgres)
  CHAR(n) only for fixed-length codes (ISO country codes)

Numbers:
  BIGINT for IDs and foreign keys (INT runs out faster than you think)
  NUMERIC(precision, scale) for money (never FLOAT)
  INTEGER for bounded counts

Timestamps:
  TIMESTAMPTZ for all timestamps (never TIMESTAMP without TZ)
  TSTZRANGE for date ranges (start/end pairs)

JSON:
  JSONB for semi-structured data (not JSON — JSONB is indexed)
  Prefer typed columns when schema is known

Boolean:
  BOOLEAN with NOT NULL DEFAULT false
  Avoid nullable booleans (three-state logic is confusing)

Indexing Strategy

Index Type	Use Case	Example
B-Tree (default)	Equality and range queries	CREATE INDEX ON users (email)
GIN	JSONB, full-text search, arrays	CREATE INDEX ON docs USING GIN (metadata)
Partial	Subset of rows	CREATE INDEX ON orders (status) WHERE status = 'pending'
Composite	Multi-column queries	CREATE INDEX ON events (user_id, created_at DESC)
Unique	Enforce uniqueness	CREATE UNIQUE INDEX ON users (email)
Covering	Index-only scans	CREATE INDEX ON users (email) INCLUDE (name)

Configuration

Parameter	Type	Default	Description
style	string	"production"	Style: prototype (minimal), production (full), enterprise (strict)
id_type	string	"bigint"	ID strategy: bigint, uuid, ulid
timestamps	boolean	true	Include created_at/updated_at on all tables
soft_delete	boolean	false	Include deleted_at column for soft deletes
multi_tenant	boolean	false	Include tenant_id and RLS policies
migration_tool	string	"prisma"	Tool: prisma, drizzle, knex, raw_sql

Best Practices

1. Use BIGINT for primary keys — INT maxes out at ~2.1 billion. Sounds like a lot until you consider that auto-incrementing IDs include deleted records, test data, and gaps. BIGINT gives you 9.2 quintillion values and costs only 4 extra bytes.

2. Always use TIMESTAMPTZ — Plain TIMESTAMP stores without timezone info, causing bugs when servers or users are in different timezones. TIMESTAMPTZ stores in UTC and converts for display. There is no reason to use TIMESTAMP without timezone in modern applications.

3. Index your foreign keys — PostgreSQL does not automatically index foreign key columns. Without an index, JOIN operations and CASCADE deletes perform full table scans. Always create an index on every foreign key column.

4. Use CHECK constraints generously — Constraints are your last line of defense. CHECK (status IN ('active', 'inactive', 'suspended')) prevents invalid data even when application code has bugs. The database should enforce its own integrity.

5. Design for your queries — Look at the queries your application will run most frequently and design your schema to serve them efficiently. Denormalization is acceptable when it eliminates expensive JOINs on hot paths.

Common Issues

Slow queries despite proper indexes — The query planner might not use your index. Check with EXPLAIN ANALYZE. Common causes: outdated statistics (run ANALYZE), type mismatches in WHERE clauses, or functions applied to indexed columns (use expression indexes).

Migration conflicts in teams — Multiple developers creating migrations simultaneously causes ordering conflicts. Use a migration tool that generates sequential filenames with timestamps, and merge migration branches carefully.

Schema changes on large tables lock the database — Adding a column with a default value locks the table in older Postgres versions. In Postgres 11+, ADD COLUMN ... DEFAULT is instant. For other changes, use tools like pg_repack or perform migrations during low-traffic windows.