postgres-patterns

When to Use

Trigger this skill when writing PostgreSQL queries, designing table schemas, or troubleshooting slow queries.

Core Capabilities

Query Optimization

• Use EXPLAIN ANALYZE to inspect execution plans, watch for Seq Scan vs Index Scan
• Avoid SELECT *, query only needed columns
• Replace large IN lists with JOIN or EXISTS
• Use keyset pagination (WHERE id > last_id) instead of OFFSET

Schema Design

• Use BIGSERIAL or UUID v7 for primary keys
• Use TEXT for large strings, not VARCHAR(n)
• Use JSONB for JSON storage, supports GIN indexing
• Use TIMESTAMPTZ for timestamps, never store timezone-naive times

Indexing Strategy

• B-tree for high-cardinality columns, partial index for low-cardinality
• Composite index column order: equality → range → sort
• GIN index for JSONB fields
• Minimize indexes on frequently updated columns

Security

• Always use parameterized queries, never string concatenation
• Row-Level Security (RLS) for multi-tenant data isolation