design-postgres-tables_skill

Overview

This skill is a comprehensive PostgreSQL table design reference focused on practical, production-ready guidance. It distills data types, indexing, constraints, partitioning, and performance patterns into actionable rules and anti-patterns. The goal is to help engineers design schemas that are maintainable, performant, and evolution-friendly.

How this skill works

The skill inspects common schema decisions and recommends best-fit types, index strategies, and constraints based on workload patterns (read-heavy, write-heavy, update-heavy, or time-series). It highlights PostgreSQL-specific gotchas (MVCC, identity behavior, identifier casing) and prescribes concrete patterns: normalization first, targeted denormalization, appropriate index types, and partitioning strategies. It also covers extensions and advanced features like JSONB, ranges, GiST/GIN indexes, and pgvector.

When to use it

• Designing new tables for OLTP, analytics, or time-series workloads
• Evaluating schema changes for performance or storage impact
• Choosing data types and indexes for predictable query patterns
• Planning partitioning or retention for very large tables
• Hardening schemas for safe evolution and migration

Best practices

• Normalize to 3NF first; denormalize only when join cost is proven high
• Use BIGINT IDENTITY for surrogate keys; use UUID only when global/opaque IDs are required
• Prefer TIMESTAMPTZ, TEXT, NUMERIC for money, and JSONB for semi-structured data
• Index actual query paths: PK/unique, FK columns, frequent filters/sorts, and include columns for index-only scans
• Partition large, time-filtered tables with RANGE or use TimescaleDB for automation
• Separate hot/cold columns, set fillfactor for update-heavy tables, and minimize indexes for high insert throughput

Example use cases

• Designing an orders table with TIMESTAMPTZ, NUMERIC price, FK to users (with manual index), and partial indexes for active orders
• Building a logging/time-series pipeline using partitioned RANGE tables or TimescaleDB hypertables for retention and compression
• Modeling semi-structured product attributes with JSONB + GIN for containment queries and occasional schema evolution
• Preventing double-booking with EXCLUDE USING GiST on (room_id WITH =, booking_period WITH &&)
• Preparing an import pipeline: UNLOGGED staging table, COPY bulk load, then CREATE INDEX CONCURRENTLY