Postgres Patterns

1---2name: postgres-patterns3description: PostgreSQL database patterns for query optimization, schema design, indexing, and security. Based on Supabase best practices.4origin: ECC5---6 7# PostgreSQL Patterns8 9Quick reference for PostgreSQL best practices. For detailed guidance, use the `database-reviewer` agent.10 11## When to Activate12 13- Writing SQL queries or migrations14- Designing database schemas15- Troubleshooting slow queries16- Implementing Row Level Security17- Setting up connection pooling18 19## Quick Reference20 21### Index Cheat Sheet22 23| Query Pattern | Index Type | Example |24|--------------|------------|---------|25| `WHERE col = value` | B-tree (default) | `CREATE INDEX idx ON t (col)` |26| `WHERE col > value` | B-tree | `CREATE INDEX idx ON t (col)` |27| `WHERE a = x AND b > y` | Composite | `CREATE INDEX idx ON t (a, b)` |28| `WHERE jsonb @> '{}'` | GIN | `CREATE INDEX idx ON t USING gin (col)` |29| `WHERE tsv @@ query` | GIN | `CREATE INDEX idx ON t USING gin (col)` |30| Time-series ranges | BRIN | `CREATE INDEX idx ON t USING brin (col)` |31 32### Data Type Quick Reference33 34| Use Case | Correct Type | Avoid |35|----------|-------------|-------|36| IDs | `bigint` | `int`, random UUID |37| Strings | `text` | `varchar(255)` |38| Timestamps | `timestamptz` | `timestamp` |39| Money | `numeric(10,2)` | `float` |40| Flags | `boolean` | `varchar`, `int` |41 42### Common Patterns43 44**Composite Index Order:**45```sql46-- Equality columns first, then range columns47CREATE INDEX idx ON orders (status, created_at);48-- Works for: WHERE status = 'pending' AND created_at > '2024-01-01'49```50 51**Covering Index:**52```sql53CREATE INDEX idx ON users (email) INCLUDE (name, created_at);54-- Avoids table lookup for SELECT email, name, created_at55```56 57**Partial Index:**58```sql59CREATE INDEX idx ON users (email) WHERE deleted_at IS NULL;60-- Smaller index, only includes active users61```62 63**RLS Policy (Optimized):**64```sql65CREATE POLICY policy ON orders66  USING ((SELECT auth.uid()) = user_id);  -- Wrap in SELECT!67```68 69**UPSERT:**70```sql71INSERT INTO settings (user_id, key, value)72VALUES (123, 'theme', 'dark')73ON CONFLICT (user_id, key)74DO UPDATE SET value = EXCLUDED.value;75```76 77**Cursor Pagination:**78```sql79SELECT * FROM products WHERE id > $last_id ORDER BY id LIMIT 20;80-- O(1) vs OFFSET which is O(n)81```82 83**Queue Processing:**84```sql85UPDATE jobs SET status = 'processing'86WHERE id = (87  SELECT id FROM jobs WHERE status = 'pending'88  ORDER BY created_at LIMIT 189  FOR UPDATE SKIP LOCKED90) RETURNING *;91```92 93### Anti-Pattern Detection94 95```sql96-- Find unindexed foreign keys97SELECT conrelid::regclass, a.attname98FROM pg_constraint c99JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = ANY(c.conkey)100WHERE c.contype = 'f'101  AND NOT EXISTS (102    SELECT 1 FROM pg_index i103    WHERE i.indrelid = c.conrelid AND a.attnum = ANY(i.indkey)104  );105 106-- Find slow queries107SELECT query, mean_exec_time, calls108FROM pg_stat_statements109WHERE mean_exec_time > 100110ORDER BY mean_exec_time DESC;111 112-- Check table bloat113SELECT relname, n_dead_tup, last_vacuum114FROM pg_stat_user_tables115WHERE n_dead_tup > 1000116ORDER BY n_dead_tup DESC;117```118 119### Configuration Template120 121```sql122-- Connection limits (adjust for RAM)123ALTER SYSTEM SET max_connections = 100;124ALTER SYSTEM SET work_mem = '8MB';125 126-- Timeouts127ALTER SYSTEM SET idle_in_transaction_session_timeout = '30s';128ALTER SYSTEM SET statement_timeout = '30s';129 130-- Monitoring131CREATE EXTENSION IF NOT EXISTS pg_stat_statements;132 133-- Security defaults134REVOKE ALL ON SCHEMA public FROM public;135 136SELECT pg_reload_conf();137```138 139## Related140 141- Agent: `database-reviewer` - Full database review workflow142- Skill: `clickhouse-io` - ClickHouse analytics patterns143- Skill: `backend-patterns` - API and backend patterns144 145---146 147*Based on Supabase Agent Skills (credit: Supabase team) (MIT License)*