database

🗄️ Databases live

PostgreSQL internals, HA CloudNativePG + PgBouncer, Redis/Valkey, ClickHouse OLAP, Vector DBs, and DBA Runbooks (Map 10)

▸ Stage 1. Relational Internals, Indexing & Query Optimization live

  • ACID isolation levels, MVCC tuple visibility, Write-Ahead Logging (WAL), and checkpoint tuning in PostgreSQL and MySQL
  • Index internals: B-Tree, GIN (JSONB/full-text), GiST, BRIN, covering indexes, and reading EXPLAIN (ANALYZE, BUFFERS)
  • Embedded SQLite in WAL mode with busy_timeout and type-safe schema migrations with Prisma/Alembic

▸ Stage 2. High-Availability PostgreSQL in Kubernetes (CloudNativePG & PgBouncer) live

  • 3-node CloudNativePG clusters across availability zones with synchronous quorum replication (RPO = 0, RTO < 10s)
  • Connection pooling at 10k+ RPS with PgBouncer in transaction mode to prevent max_connections exhaustion
  • Continuous WAL archiving and base backups to S3 for Point-in-Time Recovery (PITR) with automated restore drills

▸ Stage 3. On-Call DBA Troubleshooting Runbooks (Map 10) live

  • Diagnosing active query bottlenecks and lock trees via pg_stat_activity and pg_blocking_pids()
  • Zero-downtime DDL migrations: setting lock_timeout = '2s' and statement_timeout so ALTER TABLE never freezes reads
  • Tuning aggressive per-table autovacuum on hot tables to prevent Dead Tuple table bloat and Transaction ID Wraparound

▸ Stage 4. In-Memory Caching (Redis/Valkey) & Document Stores (MongoDB) live

  • Redis and Valkey production patterns: Cache-Aside, distributed locks, rate-limiting Lua scripts, and Redis Streams
  • Memory eviction policies (allkeys-lfu vs volatile-lru), bigkeys scanning, and avoiding O(N) KEYS * stalls
  • MongoDB document schema design, compound indexing, aggregation pipelines, replica sets, and sharding keys

▸ Stage 5. Columnar OLAP (ClickHouse) & Vector Databases for AI/RAG live

  • ClickHouse OLAP architecture: MergeTree primary sparse indexes, LowCardinality types, Partitioning, and Materialized Views
  • Distributed ClickHouse clusters on Kubernetes coordinated by lightweight C++ ClickHouse Keeper with S3 cold storage
  • Vector search with pgvector and Qdrant: HNSW index tuning (m, ef_construction), metadata filtering, and hybrid BM25 + dense search

Source: roadmaps/database.json · ETA: 2026-11