Databases

Database Engineering Mastery

Production-ready database patterns for PostgreSQL, MongoDB, and Redis. Focuses on design, optimization, and administration — language-agnostic fundamentals that work with any backend (Rust, Go, Python, Node.js, etc.).

Backend-Agnostic Design

This skill focuses on database fundamentals that work with ANY backend:

Implementation Examples:

• Rust: See rust-backend-advance
• Go: Use Gin/Echo + GORM or sqlx
• Python: Use FastAPI + SQLAlchemy or asyncpg
• Node.js: Use Express + Prisma or Knex

Database Selection

Quick Start

PostgreSQL

-- Create with constraints
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email TEXT UNIQUE NOT NULL,
    name TEXT NOT NULL,
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ DEFAULT now()
);
CREATE INDEX idx_users_email ON users(email);

-- Performance check
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM users WHERE email = 'test@example.com';

MongoDB

// Insert with validation
db.createCollection("users", {
  validator: { $jsonSchema: {
    bsonType: "object",
    required: ["email", "name"],
    properties: {
      email: { bsonType: "string", pattern: "^.+@.+$" },
      name: { bsonType: "string", minLength: 1 }
    }
  }}
});

// Aggregation pipeline
db.orders.aggregate([
  { $match: { status: "completed" } },
  { $group: { _id: "$userId", total: { $sum: "$amount" } } },
  { $sort: { total: -1 } },
  { $limit: 10 }
]);

Redis

# Caching pattern
SET user:123 '{"name":"Alice"}' EX 3600
GET user:123

# Pub/Sub
PUBLISH notifications '{"type":"order","id":456}'
SUBSCRIBE notifications

# Sorted set leaderboard
ZADD leaderboard 100 "player1" 200 "player2"
ZREVRANGE leaderboard 0 9 WITHSCORES

Reference Navigation

PostgreSQL Deep-Dive

• Schema Design — Normalization, partitioning, JSONB, constraints, migrations
• Advanced Queries — CTEs, Window Functions, lateral joins, recursive queries
• Optimization — EXPLAIN, indexing strategies, query planner, statistics
• Administration — Users, backups, VACUUM, monitoring, pgBouncer
• Replication & HA — Streaming replication, failover, pg_basebackup

MongoDB Deep-Dive

• Document Modeling — Embedding vs referencing, schema patterns, polymorphism
• Aggregation Mastery — Pipeline stages, $lookup, $graphLookup, optimization
• Indexing & Performance — Compound, multikey, text, 2dsphere, covered queries
• Atlas & Operations — Atlas setup, monitoring, sharding, backup, security

Redis Deep-Dive

• Patterns & Use Cases — Caching, sessions, rate limiting, pub/sub, streams, Lua scripts

Cross-Database

• Selection Guide — When to use what, hybrid architectures, migration strategies

Best Practices

PostgreSQL: Normalize to 3NF first, denormalize for read performance. Always use EXPLAIN ANALYZE. Index foreign keys. VACUUM regularly. Use pgBouncer for connection pooling.

MongoDB: Embed for 1-to-few, reference for 1-to-many. Index every query pattern. Use aggregation pipeline over map-reduce. Enable authentication. Use Atlas for production.

Redis: Set TTL on everything. Use pipelines for bulk ops. Don't store data you can't lose (unless persisted). Monitor memory with INFO memory.

Related Skills