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

Data Type Quick Reference

Common Patterns

Composite Index Order:

-- Equality columns first, then range columns

CREATE INDEX idx ON orders (status, created_at);

-- Works for: WHERE status = 'pending' AND created_at > '2024-01-01'

Covering Index:

CREATE INDEX idx ON users (email) INCLUDE (name, created_at);

-- Avoids table lookup for SELECT email, name, created_at

Partial Index:

CREATE INDEX idx ON users (email) WHERE deleted_at IS NULL;

-- Smaller index, only includes active users

RLS Policy (Optimized):

CREATE POLICY policy ON orders

  USING ((SELECT auth.uid()) = user_id);  -- Wrap in SELECT!

UPSERT:

INSERT INTO settings (user_id, key, value)

VALUES (123, 'theme', 'dark')

ON CONFLICT (user_id, key)

DO UPDATE SET value = EXCLUDED.value;

Cursor Pagination:

SELECT * FROM products WHERE id > $last_id ORDER BY id LIMIT 20;

-- O(1) vs OFFSET which is O(n)

Queue Processing:

UPDATE jobs SET status = 'processing'

WHERE id = (

  SELECT id FROM jobs WHERE status = 'pending'

  ORDER BY created_at LIMIT 1

  FOR UPDATE SKIP LOCKED

) RETURNING *;

Anti-Pattern Detection

-- Find unindexed foreign keys

SELECT conrelid::regclass, a.attname

FROM pg_constraint c

JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = ANY(c.conkey)

WHERE c.contype = 'f'

  AND NOT EXISTS (

    SELECT 1 FROM pg_index i

    WHERE i.indrelid = c.conrelid AND a.attnum = ANY(i.indkey)

  );

-- Find slow queries

SELECT query, mean_exec_time, calls

FROM pg_stat_statements

WHERE mean_exec_time > 100

ORDER BY mean_exec_time DESC;

-- Check table bloat

SELECT relname, n_dead_tup, last_vacuum

FROM pg_stat_user_tables

WHERE n_dead_tup > 1000

ORDER BY n_dead_tup DESC;

Configuration Template

-- Connection limits (adjust for RAM)

ALTER SYSTEM SET max_connections = 100;

ALTER SYSTEM SET work_mem = '8MB';

-- Timeouts

ALTER SYSTEM SET idle_in_transaction_session_timeout = '30s';

ALTER SYSTEM SET statement_timeout = '30s';

-- Monitoring

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Security defaults

REVOKE ALL ON SCHEMA public FROM public;

SELECT pg_reload_conf();

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)