This Claude skill provides expert-level guidance for designing optimized PostgreSQL schemas. It automates the application of industry best practices, including precise data type selection (such as TIMESTAMPTZ and JSONB), advanced indexing strategies (B-tree, GIN, BRIN), and robust constraint management to ensure data integrity and peak query performance.
Use Cases
Architecting new relational database schemas from scratch using PostgreSQL-specific features like identity columns and proper normalization to 3NF.
Optimizing existing database performance by identifying and implementing the correct index types (B-tree, GIN, GiST) for specific query patterns.
Implementing advanced security and data integrity measures, including Row-Level Security (RLS) and complex CHECK constraints.
Refactoring legacy database structures to modern PostgreSQL standards, such as replacing SERIAL with IDENTITY and utilizing JSONB for semi-structured data.
name
postgresql-table-design
description
Design a PostgreSQL-specific schema. Covers best-practices, data types, indexing, constraints, performance patterns, and advanced features
PostgreSQL Table Design
Core Rules
Define a PRIMARY KEY for reference tables. Prefer BIGINT GENERATED ALWAYS AS IDENTITY; use UUID only when global uniqueness is needed.
Normalize first (to 3NF) to eliminate data redundancy; denormalize only for measured performance needs.
Add NOT NULL everywhere it's semantically required; use DEFAULTs for common values.
Create indexes for access paths you actually query: PK/unique (auto), FK columns (manual!), frequent filters/sorts.
Prefer TIMESTAMPTZ for event time; NUMERIC for money; TEXT for strings; BIGINT for integers.
PostgreSQL "Gotchas"
Identifiers: unquoted names are lowercased. Use snake_case.
Unique + NULLs: UNIQUE allows multiple NULLs. Use NULLS NOT DISTINCT (PG15+) to restrict.
FK indexes: PostgreSQL does not auto-index FK columns. Add them manually.
No silent coercions: length/precision overflows error out (no truncation).
Sequences have gaps: Normal behavior, don't try to "fix" it.
Data Types
Use Case
Recommended Type
IDs
BIGINT GENERATED ALWAYS AS IDENTITY or UUID
Integers
BIGINT (prefer) or INTEGER
Strings
TEXT (not VARCHAR(n) or CHAR(n))
Money
NUMERIC(p,s) (never float)
Timestamps
TIMESTAMPTZ (not TIMESTAMP)
Booleans
BOOLEAN NOT NULL
JSON data
JSONB (not JSON)
Do Not Use
timestamp without time zone - use timestamptz
char(n) or varchar(n) - use text
money type - use numeric
serial - use generated always as identity
Constraints
PK: implicit UNIQUE + NOT NULL; creates B-tree index
FK: specify ON DELETE/UPDATE action; add explicit index on referencing column
-- Composite index (order matters)
CREATE INDEX ON orders (user_id, created_at);
-- Partial index
CREATE INDEX ON orders (user_id) WHERE status = 'active';
-- Expression index
CREATE INDEX ON users (LOWER(email));
-- Covering index
CREATE INDEX ON orders (id) INCLUDE (status, total);
Row-Level Security
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users can read own posts"
ON posts FOR SELECT
USING (user_id = auth.uid());
Examples
Users Table
CREATE TABLE users (
user_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email TEXT NOT NULL UNIQUE,
name TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX ON users (LOWER(email));
Orders Table
CREATE TABLE orders (
order_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
user_id BIGINT NOT NULL REFERENCES users(user_id),
status TEXT NOT NULL DEFAULT 'PENDING' CHECK (status IN ('PENDING','PAID','CANCELED')),
total NUMERIC(10,2) NOT NULL CHECK (total > 0),
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX ON orders (user_id);
CREATE INDEX ON orders (created_at);
JSONB Usage
CREATE TABLE profiles (
user_id BIGINT PRIMARY KEY REFERENCES users(user_id),
attrs JSONB NOT NULL DEFAULT '{}'
);
CREATE INDEX ON profiles USING GIN (attrs);
-- Query JSONB
SELECT * FROM profiles WHERE attrs @> '{"theme": "dark"}';
