What problem does it solve?
PostgreSQL table design tasks are error-prone and time-consuming when deciding data types, keys, constraints, and indexing strategies. This Skill provides structured guidance to design robust, scalable schemas that enforce data integrity and optimize performance.
Core Features & Use Cases
- Normalize to 3NF and decide when denormalization is appropriate for read-heavy workloads.
- Define appropriate primary keys, foreign keys with proper actions, and constraints (CHECK, UNIQUE) to enforce data quality.
- Choose data types and indexing strategies (including partial and expression indexes, BRIN for large time-series data, and partitioning) to support scalable queries.
- Use extensions and architectural patterns (e.g., TIMESTAMPTZ, JSONB, and timescaledb) to support real-world data scenarios.
- Use case: design a users table with id, email, created_at, and a related orders table with proper FK relationships and indexing to support fast lookups.
Quick Start
Start by drafting a minimal, well-typed schema for a common domain (e.g., users and orders), then iteratively apply normalization, indexing, and constraints per the guide. Example steps: create a users table with a BIGINT identity primary key, a unique text email, and a TIMESTAMPTZ created_at with a default now(); create an orders table with a foreign key referencing users and an index on (user_id, created_at).
