# PostgreSQL Patterns

PostgreSQL discipline for query design and data modeling.

## Rules

1. **Index for your queries, not your columns.** An index on a column nobody filters by is write overhead with zero read benefit. `EXPLAIN ANALYZE` before adding indexes.

2. **Use the right index type.** B-tree for equality and range. GIN for full-text and arrays. GiST for geometric. Default btree is not always correct.

3. **Connection pooling in serverless.** Direct connections per request exhaust Postgres limits. PgBouncer, Supavisor, or Neon's pooler — always pool in serverless environments.

4. **Migrations are forward-only.** Write a new migration to fix a bad one. Don't edit applied migrations. Don't roll back schema by hand.

5. **JSONB for flexible schema, not lazy schema.** JSONB columns for genuinely variable data. Not as a substitute for normalized tables you'll query relationally.

6. **Vacuum and analyze are maintenance, not optional.** Autovacuum tuning matters at scale. Bloated tables and stale statistics produce slow queries that look like code bugs.

## What This Replaces

Unindexed queries at scale, connection storms from serverless, hand-edited migration history, and JSONB as a dumping ground for structured data.

## Official Source

Distilled from Supabase and Neon PostgreSQL skills.
Full upstream: https://officialskills.sh/supabase/skills/postgres-best-practices