postgres-patterns_skill

Overview

This skill provides concise PostgreSQL patterns for query optimization, schema design, indexing, and security based on Supabase best practices. It offers quick-reference patterns, index guidance, common SQL snippets, and configuration templates to speed up reliable database work. Use it to standardize decisions and surface anti-pattern checks during development and reviews.

How this skill works

The skill inspects common query patterns and schema shapes to recommend index types, composite ordering, and covering/partial indexes. It includes templates for RLS, upserts, pagination, queue processing, and configuration tweaks, plus SQL checks to detect unindexed foreign keys, slow queries, and table bloat. Use these snippets directly in migrations, review scripts, or automated checks.

When to use it

• Writing or optimizing SQL queries and migrations
• Designing or refactoring database schemas and data types
• Diagnosing slow queries or table bloat in production
• Implementing Row Level Security or connection pooling
• Setting safe server configuration and monitoring

Best practices

• Choose the smallest correct data type (bigint for IDs, text for strings, timestamptz for timestamps)
• Create B-tree indexes for equality/range and GIN for jsonb/tsvector workloads
• Place equality columns before range columns in composite indexes
• Use covering and partial indexes to reduce I/O and index size
• Favor cursor/cursor-like pagination (id > last_id) over OFFSET for large tables
• Enforce timeouts and connection limits; enable pg_stat_statements for monitoring

Example use cases

• Add a partial index on active users: smaller index for WHERE deleted_at IS NULL
• Rewrite OFFSET pagination to cursor pagination for product listings
• Implement an UPSERT for user settings to avoid race conditions
• Use FOR UPDATE SKIP LOCKED for safe job queue worker selection
• Run anti-pattern queries to find unindexed foreign keys and high mean_exec_time queries