What problem does it solve?
This Skill provides structured guidance for PostgreSQL schema design, helping teams create correct, scalable tables by outlining best practices for keys, normalization, data types, indexing, constraints, and extension considerations.
Core Features & Use Cases
- Primary key and normalization guidance: enforce 3NF where appropriate, use BIGINT generated identities for surrogate keys, and apply UUIDs when global uniqueness is needed.
- Data types and constraints: recommendations for TIMESTAMPTZ, TEXT, NUMERIC, JSONB, and proper NOT NULL/DEFAULT patterns; discusses MVCC, vacuum, and commit behavior to minimize bloat.
- Indexing, constraints, and maintenance: PK/FK indexing, unique constraints with NULL handling, partial and expression indexes, and notes on partitioning and extension usage (e.g., jsonb, timescaledb).
- Use Case: design a users table with id as BIGINT GENERATED ALWAYS AS IDENTITY primary key, created_at TIMESTAMPTZ NOT NULL DEFAULT now(), and a separate normalized reference table structure to illustrate efficient foreign keys.
Quick Start
Apply these guidelines to blueprint a new PostgreSQL schema for a bounded domain. Start by defining a primary key, normalization to 3NF, appropriate data types for each column, NOT NULL constraints, and indexes on frequently queried fields. Draft a sample CREATE TABLE for a simple domain (e.g., users) that demonstrates IDENTITY-based primary keys, timestamp columns, and a foreign key relationship.
