PostgreSQL Patterns

# PostgreSQL Patterns

## Indexes
- B-tree (default): equality + range
- GIN: jsonb, full-text, array containment
- BRIN: huge time-series tables
- Partial: `CREATE INDEX ON orders(user_id) WHERE status='open';`
- Multi-column: leftmost prefix matters

## Common joins
```sql
SELECT u.id, COUNT(o.id) AS orders
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id;
```

## CTEs
```sql
WITH recent AS (
  SELECT * FROM events WHERE created_at > now() - interval '7 days'
)
SELECT user_id, count(*) FROM recent GROUP BY user_id;
```

## Window functions
```sql
SELECT
  user_id,
  amount,
  SUM(amount) OVER (PARTITION BY user_id ORDER BY created_at) AS running_total,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
FROM payments;
```

## JSONB
```sql
SELECT data->>'name' AS name FROM users WHERE data @> '{"active": true}';
CREATE INDEX users_data_gin ON users USING gin(data);
```

## UPSERT
```sql
INSERT INTO kv(k, v) VALUES('x', 1)
ON CONFLICT (k) DO UPDATE SET v = EXCLUDED.v;
```

## Performance
- `EXPLAIN (ANALYZE, BUFFERS) SELECT ...`
- Avoid `SELECT *` in hot paths.
- `VACUUM ANALYZE` after big writes; autovacuum usually handles it.
- Use `LIMIT` + keyset pagination (`WHERE id > last_id ORDER BY id LIMIT 50`).