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:

-- 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;