postgresql_skill

Overview

This skill designs PostgreSQL-specific schemas focused on robust, performant table layouts and realistic operational patterns. It codifies best-practices for keys, data types, indexing, partitioning, constraints, and workload-driven optimizations. The guidance balances normalization with pragmatic denormalization and covers advanced features like JSONB, range types, and extensions.

How this skill works

It inspects schema decisions and recommends concrete alternatives: primary key choices, proper data types, where to add indexes, and when to partition. It highlights PostgreSQL gotchas (MVCC, identifier rules, sequence gaps) and prescribes storage, VACUUM, and write-path optimizations. It also suggests specific index types and extension use for JSONB, geospatial, and vector workloads.

When to use it

• Designing a new PostgreSQL schema for OLTP or analytical workloads
• Optimizing read/write performance for large tables or time-series data
• Converting legacy schemas to PostgreSQL idioms and types
• Planning partitioning, retention, or maintenance strategies
• Evaluating use of JSONB, PostGIS, pgvector, or other extensions

Best practices

• Always define a primary key; prefer BIGINT GENERATED ALWAYS AS IDENTITY unless UUID is required
• Normalize to 3NF first; denormalize only for measured, high-ROI read patterns
• Use TIMESTAMPTZ for timestamps, NUMERIC for money, TEXT for strings; avoid timestamp without timezone and varchar/char
• Create indexes only for real query paths: PKs, UNIQUEs, FK columns, frequent filters/sorts; add FK indexes manually
• Partition very large tables (>100M rows) by time/hash when queries filter on the partition key; prefer declarative partitioning
• Design update-heavy tables to separate hot columns, set sensible fillfactor, and avoid updating indexed columns

Example use cases

• High-volume event logs: PARTITION BY RANGE(created_at), BRIN or GiST/BRIN for time-ordered reads
• E-commerce orders: BIGINT PK, FK indexes on customer_id, NUMERIC for prices, partial indexes for active orders
• User profile attributes: base columns normalized, optional attributes in JSONB with GIN index for flexible queries
• Scheduling/bookings: tstzrange columns with GiST index and EXCLUDE constraint to prevent overlaps
• Embedding search: pgvector column with appropriate index for nearest-neighbor similarity