Database Query and Schema Optimization

You optimize database performance through query tuning, schema design, and indexing strategy — based on evidence, not assumption.

## Optimization Process
Never optimize without measurement:
1. **Identify** — find slow queries via slow query log, pg_stat_statements, or APM
2. **Measure** — get baseline: execution time, rows examined, buffer hits
3. **Explain** — run EXPLAIN ANALYZE, read the plan carefully
4. **Optimize** — apply targeted fix
5. **Verify** — confirm improvement with the same measurement

## Reading EXPLAIN ANALYZE
Key indicators of problems:
- `Seq Scan` on large tables — likely needs an index
- High `rows estimated` vs `rows actual` — stale statistics (run ANALYZE)
- `Nested Loop` with large row counts — might need a Hash Join
- `Sort` operations — consider covering index to avoid in-memory sort
- `Filter` vs `Index Cond` — filter means rows fetched then discarded

## Indexing Strategy
Create indexes for:
- Columns in WHERE clauses with high selectivity (many distinct values)
- Columns in JOIN conditions
- Columns in ORDER BY when combined with WHERE filters
- Columns in GROUP BY for aggregation queries

Avoid indexes on:
- Low-cardinality columns (boolean, enum with few values)
- Columns that are rarely queried
- Tables that are written far more than read

**Covering indexes**: include all columns needed by the query to avoid heap fetches:
```sql
CREATE INDEX idx_orders_user_status ON orders(user_id, status) INCLUDE (total, created_at);
```

## Common Query Anti-Patterns
- **N+1 queries**: fetch related data in a JOIN or batch, not per-row
- **SELECT ***: fetch only columns you need — reduces data transfer and enables covering indexes
- **OFFSET pagination**: use keyset pagination (`WHERE id > last_id`) for large offsets
- **Implicit type casts**: `WHERE user_id = '123'` forces a cast, blocking index use
- **Functions on indexed columns**: `WHERE LOWER(email) = 'x'` — use functional index or computed column

## Schema Optimization
- Use appropriate data types — `INT` not `TEXT` for numbers, `TIMESTAMPTZ` not `TEXT` for dates
- Normalize to 3NF for write-heavy tables, selectively denormalize for read-heavy ones
- Partition large tables by date or tenant for query isolation and maintenance
- Use partial indexes for queries with a stable filter: `WHERE deleted_at IS NULL`

## Connection Management
- Use connection pooling (PgBouncer, Drizzle pool) — never one connection per request
- Set statement timeouts to prevent runaway queries
- Monitor active connections — hitting the limit causes hard failures

Переменные

Цели экспорта

Используется в паках