postgres-schema-design_skill

Overview

This skill is a concise PostgreSQL table design reference focused on practical rules for data types, indexing, constraints, partitioning, and performance patterns. It distills real-world guidance for schema decisions that scale and remain maintainable. Use it to choose types, indexes, and layout patterns that match workload characteristics and operational constraints.

How this skill works

It inspects common schema choices and recommends concrete PostgreSQL primitives: identity keys, timestamptz, numeric for money, TEXT for strings, JSONB for semi-structured data, and appropriate index types (B-tree, GIN, GiST, BRIN). It also highlights gotchas—MVCC, sequence gaps, identifier casing, FK indexing, and DDL behavior—and prescribes patterns for partitioning, upserts, and safe schema evolution. Advanced topics include generated columns, range types, vector search, and extensions like PostGIS, pgvector, and TimescaleDB.

When to use it

• Designing new OLTP schemas where correctness and upgradeability matter
• Optimizing read or write performance for high-volume tables (hot vs cold columns)
• Choosing indexes for specific query patterns (filters, sorts, joins, JSONB/search)
• Preparing large-table maintenance: partitioning, BRIN, CLUSTER, or TimescaleDB
• Modeling semi-structured data, vectors, or geospatial workloads with JSONB/pgvector/PostGIS

Best practices

• Always define a PK; prefer BIGINT GENERATED ALWAYS AS IDENTITY unless global opaque IDs need UUIDs
• Normalize to 3NF first; denormalize only when measured read performance justifies it
• Add NOT NULL where semantic, use DEFAULTs for common values, and avoid volatile defaults that rewrite tables
• Index only queried paths: add explicit indexes on FK columns and create partial or covering indexes for hot subsets
• Prefer TIMESTAMPTZ for times, NUMERIC for money, TEXT over VARCHAR(n), JSONB with GIN for semi-structured data

Example use cases

• Event logs/time-series: PARTITION BY RANGE on timestamp with BRIN for large, append-only workloads
• Multi-tenant app: row-level security policies and partitioning by tenant_id for pruning and maintenance
• Product catalog: JSONB for optional attributes indexed with GIN and expression indexes for searchable fields
• Booking system: tstzrange columns with GiST and EXCLUDE constraints to prevent overlapping bookings
• Embedding store: pgvector vectors with appropriate index type for nearest-neighbor search