# Nebo Database Mastery

> DATABASE-MASTERY SuperSkill

- Skill: `lifenewjob/nebo-database-mastery` (Agent Skill)
- Install (CLI): `npx skillmds@latest add lifenewjob/nebo-database-mastery`
- Raw SKILL.md: https://api.skillmd.com/api/skills/lifenewjob/nebo-database-mastery/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Coding & Dev Tools
- Author: lifenewjob (https://skillmd.com/u/lifenewjob)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/lifenewjob/nebo-database-mastery

---


# DATABASE-MASTERY SuperSkill
> Триггеры: "база данных", "SQL", "postgres", "redis", "индекс", "ETL", "запрос", "query optimization", "N+1"
> Атомов: 45

---

## WHEN TO USE
- Slow query → EXPLAIN ANALYZE → fix index or rewrite
- New table design → check index strategy + partition decision
- Caching needed → Redis cache-aside pattern
- N+1 detected → eager loading fix
- Bulk data processing → ETL/upsert patterns
- Table > 10M rows → partition by range

## KEY ACTIONS

### 1. Diagnose slow query
```sql
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) <your query>;
```
Read bottom-up:
- `Seq Scan` on big table → **missing index**
- `Nested Loop` with high rows → needs hash/merge join
- `shared read` >> `shared hit` → poor cache, needs more `shared_buffers`
- Estimated vs actual rows diverge → run `ANALYZE tablename;`

### 2. Choose index type
```
equality filter (WHERE status = 'x')     → B-tree
equality + range (WHERE status = 'x' AND created_at > y)
                                          → Composite: (status, created_at DESC)
                                            equality columns FIRST, range LAST
filtered subset (WHERE status = 'pending') → Partial index
need columns without table access         → Covering: INCLUDE (col1, col2)
JSONB / array / full-text search          → GIN
spatial / range types                     → GiST
production table (no downtime)            → CREATE INDEX CONCURRENTLY
```

### 3. Index templates
```sql
-- Composite (equality first, range last)
CREATE INDEX idx_orders_status_date ON orders (status, created_at DESC);

-- Partial (dramatically smaller)
CREATE INDEX idx_orders_pending ON orders (created_at) WHERE status = 'pending';

-- Covering (index-only scan, no table lookup)
CREATE INDEX idx_users_email ON users (email) INCLUDE (name, avatar_url);

-- GIN for JSONB
CREATE INDEX idx_products_meta ON products USING GIN (metadata);

-- Full-text search
CREATE INDEX idx_docs_search ON documents USING GIN (to_tsvector('english', content));
```

### 4. Partition (tables > 10M rows)
```sql
CREATE TABLE events (
    id BIGINT GENERATED ALWAYS AS IDENTITY,
    payload JSONB NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2024_q1 PARTITION OF events
    FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');
```
Partition key MUST appear in WHERE clauses, otherwise full scan across all partitions.

### 5. Fix N+1
```python
# BAD: N+1 (1 query + N queries)
for user in db.query(User).all():
    print(user.orders)  # separate query per user

# GOOD: eager load (1-2 queries total)
users = db.query(User).options(joinedload(User.orders)).all()
```

### 6. Redis cache-aside
```typescript
async function getUser(id: string): Promise<User> {
  const cached = await redis.get(`user:${id}`);
  if (cached) return JSON.parse(cached);
  const user = await db.user.findUnique({ where: { id } });
  if (user) await redis.set(`user:${id}`, JSON.stringify(user), "EX", 3600);
  return user;
}
```
Always set TTL. Invalidate on write. Use pipeline for batch ops.

### 7. Bulk upsert (ETL pattern)
```sql
INSERT INTO fact_orders (order_id, amount, fiscal_quarter)
VALUES ($1, $2, $3)
ON CONFLICT (order_id) DO UPDATE SET
  amount = EXCLUDED.amount,
  fiscal_quarter = EXCLUDED.fiscal_quarter;
```
Batch size: 1000-5000 rows per statement. Make runs idempotent.

## CHECKLIST
- [ ] EXPLAIN ANALYZE on all slow queries
- [ ] Indexes on FK columns and WHERE/ORDER BY columns
- [ ] No N+1 queries (check ORM logs)
- [ ] Connection pooling configured (PgBouncer or built-in)
- [ ] Redis TTL on all cache keys
- [ ] Bulk ops for mass inserts/updates (not row-by-row)
- [ ] Partitioning for tables > 10M rows
- [ ] Backups configured and tested

## ANTI-PATTERNS
1. Adding indexes without checking EXPLAIN -- you might index the wrong column
2. Composite index in wrong order -- equality columns must come before range columns
3. Caching without TTL -- stale data forever, memory leak
4. Row-by-row inserts in loops -- use batch/bulk operations
5. Partitioning by wrong key -- partition key must match WHERE clause filters