PostgreSQL database patterns for query optimization, schema design, indexing, and security. Based on Supabase best practices. Use when designing PostgreSQL schemas, indexes, or RLS policies, or when a query is too slow.

PostgreSQL Patterns Quick reference for PostgreSQL best practices. For detailed guidance, use the database reviewer agent. When to Activate Writing SQL queries or migrations Designing database schemas Troubleshooting slow queries Implementing Row Level Security Setting up connection pooling Quick Reference Index Cheat Sheet Query Pattern Index Type Example WHERE col = value B tree (default) CREATE INDEX idx ON t (col) WHERE col value B tree CREATE INDEX idx ON t (col) WHERE a = x AND b y Composite CREATE INDEX idx ON t (a, b) WHERE jsonb @ '{}' GIN CREATE INDEX idx ON t USING gin (col) WHERE tsv @@ query GIN CREATE INDEX idx ON t USING gin (col) Time series ranges BRIN CREATE INDEX idx ON t USING brin (col) Data Type Quick Reference Use Case Correct Type Avoid IDs bigint int , random UUID Strings text varchar(255) Timestamps timestamptz timestamp Money numeric(10,2) float Flags boolean varchar , int Common Patterns Composite Index Order: Covering Index: Partial Index: RLS Policy (Optimized): UPSERT: Cursor Pagination: Queue Processing: Anti Pattern Detection Configuration Template Related Agent: database reviewer Full database review workflow Skill: clickhouse io ClickHouse analytics patterns Skill: backend patterns API and backend patterns Based on Supabase Agent Skills (credit: Supabase team) (MIT License)

postgres-patterns