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

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

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

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

# 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

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)

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