01. The Architectural Split: SQL vs. NoSQL
Choosing a database is not a matter of picking what is trendy. It is an engineering calculation based on your system’s data relationships, consistency requirements, and access patterns.
Relational Databases (SQL — PostgreSQL, MySQL)
Data lives in rigid, pre-defined tables with rows and columns. Different entities are linked through foreign keys and queried via joins.
ACID Compliance — The Mathematical Guarantee:
| Property | Meaning | Why It Matters |
|---|---|---|
| Atomicity | All operations succeed or all fail — no partial writes | A transfer debits AND credits, never just one |
| Consistency | Every transaction leaves the database in a valid state | No orphaned records, no broken foreign keys |
| Isolation | Concurrent transactions don’t interfere with each other | Two users buying the last item don’t both succeed |
| Durability | Committed data survives power crashes and restarts | Once the server says “saved,” it’s on disk permanently |
Document Databases (NoSQL — MongoDB, DynamoDB)
Data lives as independent, flexible JSON-like documents. No rigid schema — each document can have different fields.
Query: db.users.findOne({ _id: "abc123" }) → Returns entire document in one read, no JOIN needed.
The Mindset: Data that is read together should live together. If a user and their orders are always fetched together, embed the orders inside the user document.
The Decision Matrix
| Factor | SQL (PostgreSQL) | NoSQL (MongoDB) |
|---|---|---|
| Schema | Rigid, defined upfront | Flexible, evolves per document |
| Relationships | Natural via JOINs | Complex — requires embedding or manual references |
| Consistency | Strong (ACID) | Eventual (BASE) — may read stale data briefly |
| Query Language | SQL — standardized for 40+ years | MongoDB Query API — vendor-specific |
| Scaling | Vertical (bigger server) | Horizontal (more servers) |
| Best For | Financial, healthcare, SaaS with complex relations | Content management, real-time logs, rapid prototyping |
When to Use What
Is your data highly relational with strict consistency requirements?
├── YES → PostgreSQL
│ (e-commerce, banking, user management, inventory)
│
├── NO → Does your data structure vary between documents?
│ ├── YES → MongoDB
│ │ (product catalogs, user profiles with optional fields, CMS)
│ │
│ └── NO → Do you need sub-millisecond reads for hot data?
│ ├── YES → Redis
│ │ (sessions, caching, real-time leaderboards)
│ │
│ └── NO → PostgreSQL (default choice for most apps)
02. The Core Engines: PostgreSQL, MongoDB, Redis
A production architecture often uses multiple database types together — each specialized for a different access pattern.
| Database | Type | Latency | Best For | Watch Out For |
|---|---|---|---|---|
| PostgreSQL | Relational | 1-50ms | Transactional data, complex queries, analytics | Vertical scaling limits, connection pool exhaustion |
| MongoDB | Document | 1-20ms | Flexible schemas, nested reads, high write throughput | No JOINs, eventual consistency, data duplication |
| Redis | In-Memory | 0.1-2ms | Caching, sessions, rate limiting, pub/sub | Volatile (data lost on crash without persistence config) |
| SQLite | Embedded | <1ms | Local dev, mobile apps, single-user tools | No concurrent writes, no network access |
The Multi-Database Architecture
Connection Pooling
Every database connection has overhead — TCP handshake, authentication, memory allocation. Opening a new connection per request is catastrophically slow. Connection pooling maintains a warm pool of reusable connections.
Tell your AI tool: “Set up a PostgreSQL connection pool with these settings: maximum 20 connections, idle timeout 30 seconds, connection timeout 5 seconds. Log any queries that take longer than 100ms as warnings.”
Why this matters: Without connection pooling, each user request opens a new database connection. At 100 concurrent users, you’d need 100 simultaneous connections — exhausting your database’s limits. Pooling reuses connections, keeping performance stable.
Senior Moves for Database Selection
- Start with PostgreSQL. It handles 90% of use cases. Only reach for MongoDB or Redis when you have a specific reason.
- Use Redis for caching, not as primary storage. Redis is fast but volatile. Use it to accelerate reads from PostgreSQL, not to replace it.
- MongoDB for write-heavy, read-heavy, or schema-unstable workloads. If your data structure changes weekly (A/B testing features, evolving product attributes), MongoDB avoids migration headaches.
- SQLite for local dev and single-user tools. Don’t spin up PostgreSQL for a CLI tool or a local prototype.
03. Schema Design: Normalization vs. Denormalization
How you structure your tables determines query performance, data integrity, and maintenance burden.
Normalization (SQL Default)
Split data into separate tables to eliminate duplication. Each fact lives in exactly one place.
-- Normalized: 3NF (Third Normal Form)
-- User data lives in ONE place
CREATE TABLE users (
id UUID PRIMARY KEY,
name TEXT NOT NULL,
email TEXT UNIQUE NOT NULL
);
-- Order data references user, doesn't duplicate user info
CREATE TABLE orders (
id UUID PRIMARY KEY,
user_id UUID REFERENCES users(id),
amount INTEGER NOT NULL,
created_at TIMESTAMPTZ DEFAULT now()
);
-- Product data lives in ONE place
CREATE TABLE products (
id UUID PRIMARY KEY,
name TEXT NOT NULL,
price INTEGER NOT NULL
);
-- Order items reference both order and product
CREATE TABLE order_items (
id UUID PRIMARY KEY,
order_id UUID REFERENCES orders(id),
product_id UUID REFERENCES products(id),
quantity INTEGER NOT NULL
);
Pros: No data duplication, update one place and it’s consistent everywhere, enforced relationships. Cons: Complex JOINs for reads, more tables to manage, slower queries for deeply nested data.
Denormalization (Performance Optimization)
Duplicate data strategically to avoid expensive JOINs. Trade storage for read speed.
Tell your AI tool: “Create an orders table that includes user_name and user_email directly (duplicated from the users table). This avoids JOINs for read-heavy queries.”
Pros: Fast reads, simple queries, no JOINs. Cons: Data duplication, must update multiple places when user changes name, risk of inconsistency.
The Hybrid Pattern
Normalize for write-heavy tables (orders, transactions). Denormalize for read-heavy tables (dashboards, analytics, search results).
Tell your AI tool: “Create a materialized view called order_summary that JOINs orders and users tables. Refresh it periodically for dashboard reads.” — The orders table stays normalized for writes — A materialized view handles read-heavy dashboards CREATE MATERIALIZED VIEW order_summary AS SELECT o.id, o.user_id, u.name AS user_name, u.email AS user_email, o.amount, o.created_at FROM orders o JOIN users u ON o.user_id = u.id;
— Refresh periodically (not real-time — that’s what the normalized table is for) REFRESH MATERIALIZED VIEW CONCURRENTLY order_summary;
---
## 04. High-Performance Caching with Redis
When PostgreSQL handles thousands of complex queries per second, CPU spikes and page loads slow down. Redis sits in front as an ultra-fast in-memory cache.
### The Cache-Aside Pattern
<div class="flow">
<div class="flow-step">
<div class="flow-box">
<span class="flow-num">1</span>
<span class="flow-label">Check Redis</span>
<span class="flow-sub">GET user:cydercoder</span>
</div>
</div>
<div class="flow-step">
<div class="flow-arrow"><svg viewBox="0 0 24 24" fill="none" stroke="currentColor" stroke-width="2"><line x1="5" y1="12" x2="19" y2="12"/><polyline points="12 5 19 12 12 19"/></svg></div>
</div>
<div class="flow-step">
<div class="flow-box">
<span class="flow-num">HIT</span>
<span class="flow-label">Return cached</span>
<span class="flow-sub">~2ms</span>
</div>
</div>
<div class="flow-step">
<div class="flow-arrow"><svg viewBox="0 0 24 24" fill="none" stroke="currentColor" stroke-width="2"><line x1="5" y1="12" x2="19" y2="12"/><polyline points="12 5 19 12 12 19"/></svg></div>
</div>
<div class="flow-step">
<div class="flow-box">
<span class="flow-num">MISS</span>
<span class="flow-label">Query PostgreSQL</span>
<span class="flow-sub">~50ms</span>
</div>
</div>
<div class="flow-step">
<div class="flow-arrow"><svg viewBox="0 0 24 24" fill="none" stroke="currentColor" stroke-width="2"><line x1="5" y1="12" x2="19" y2="12"/><polyline points="12 5 19 12 12 19"/></svg></div>
</div>
<div class="flow-step">
<div class="flow-box">
<span class="flow-num">2</span>
<span class="flow-label">Save to Redis</span>
<span class="flow-sub">SET with TTL 3600</span>
</div>
</div>
</div>
### Redis Implementation
**Tell your AI tool:**
"Implement the cache-aside pattern with Redis:
1. When reading data, check Redis first
2. If found (cache hit), return it immediately
3. If not found (cache miss), query PostgreSQL, then save to Redis with 1 hour TTL
4. When writing data, update PostgreSQL AND delete the Redis cache entry immediately"
**What this does:** The first request hits the database (slow). Every subsequent request for the same data hits Redis (100x faster). When data changes, the cache is invalidated so the next request gets fresh data.
### Cache Invalidation Strategies
| Strategy | When to Use | Implementation |
|----------|-------------|----------------|
| **TTL Expiration** | Data changes infrequently | `SET key value EX 3600` (expires in 1 hour) |
| **Active Invalidation** | Data changes on write | `DEL key` immediately after UPDATE |
| **Write-Through** | Write-heavy with consistent reads | Write to cache AND database simultaneously |
| **Write-Behind** | Write-heavy with eventual consistency | Write to cache first, sync to DB asynchronously |
### Eviction Policies
When Redis runs out of memory, it evicts keys based on the configured policy:
| Policy | Behavior | Best For |
|--------|----------|----------|
| `noeviction` | Reject writes when full | Critical data that must not be lost |
| `allkeys-lru` | Evict least recently used key | General caching (most common) |
| `volatile-lru` | Evict LRU key with TTL set | When some keys must persist |
| `allkeys-lfu` | Evict least frequently used | When access patterns are stable |
| `volatile-ttl` | Evict key with shortest TTL | When TTL reflects importance |
```bash
# Configure eviction policy
redis-cli CONFIG SET maxmemory 256mb
redis-cli CONFIG SET maxmemory-policy allkeys-lru
Cache Stampede Prevention
When a popular cache key expires, hundreds of requests simultaneously hit the database. This is a cache stampede (or thundering herd).
The Singleflight Pattern:
When a cache miss occurs, only ONE request fetches from the database. All other requests for the same key wait for that single fetch to complete. This prevents 100 simultaneous database queries for the same data.
Tell your AI tool: “Implement singleflight pattern for cache misses. When a cache key is missing, only one request should query the database. Other requests for the same key should wait for the first request to complete and return the cached result.”
05. Database Indexing: The Query Accelerator
Without an index, a query on 5 million rows performs a full table scan — reading every single row until it finds a match. This is the #1 cause of slow database queries.
B-Tree Index (Default)
The most common index type. Creates a balanced tree structure for fast lookups.
-- Without index: Full table scan (reads all 5,000,000 rows)
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'alice@example.com';
-- Seq Scan on users (cost=0.00..173210.00 rows=1 width=96)
-- Filter: (email = 'alice@example.com')
-- Planning Time: 0.1ms
-- Execution Time: 45.2ms ← SLOW
-- Create index
CREATE INDEX idx_users_email ON users(email);
-- With index: Index scan (reads ~4 levels of the tree)
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'alice@example.com';
-- Index Scan using idx_users_email on users (cost=0.42..8.44 rows=1 width=96)
-- Index Cond: (email = 'alice@example.com')
-- Planning Time: 0.1ms
-- Execution Time: 0.05ms ← 900x FASTER
Index Types
| Type | Best For | Example |
|---|---|---|
| B-Tree | Equality and range queries | WHERE email = 'x', WHERE created_at > '2026-01-01' |
| Hash | Equality only (faster than B-Tree for exact match) | WHERE id = 'abc123' |
| GIN | Full-text search, JSONB containment, array contains | WHERE tags @> ARRAY['javascript'] |
| GiST | Geospatial queries, range types | WHERE location @> POINT(40.7, -74.0) |
| Partial | Index only rows matching a condition | CREATE INDEX ... WHERE status = 'active' |
| Composite | Multi-column queries | CREATE INDEX ... ON orders(user_id, created_at) |
Composite Indexes
When you frequently query by multiple columns together, create a composite index.
Tell your AI tool: “Create a composite index on the orders table for (user_id, created_at DESC). This optimizes queries that filter by user_id and sort by date.”
Why column order matters: An index on (user_id, created_at) helps queries that filter by user_id first. It does NOT help queries that only filter by created_at — the database can’t skip the first column.
Partial Indexes
Index only the rows you actually query — saves space and speeds up writes.
Tell your AI tool: “Create a partial index on orders where status = ‘active’. Only index active orders, not completed or cancelled ones.”
The Index Trade-off
| Factor | Impact |
|---|---|
| Read Performance | Indexes make SELECT queries 100-1000x faster |
| Write Performance | Indexes slow down INSERT/UPDATE/DELETE by 5-20% |
| Storage | Each index consumes additional disk space |
The Rule: Index columns that appear in WHERE, JOIN ON, and ORDER BY clauses. Don’t index columns with low cardinality (like gender with only 3 values) — the index won’t help much.
EXPLAIN ANALYZE: Read Your Query Plans
Tell your AI tool: “Run EXPLAIN ANALYZE on this query and tell me if an index is being used or if it’s doing a full table scan.”
What to look for:
- “Seq Scan” → Missing index (reads entire table) — BAD
- “Index Scan” → Index is being used — GOOD
- “Sort” → Missing index on ORDER BY column
06. Query Optimization: The Performance Playbook
The N+1 Problem
The most common performance killer in databases:
The Problem: You fetch 100 users, then for each user, you fetch their orders. That’s 101 database queries (1 for users + 100 for orders).
The Solution: Use a JOIN to fetch everything in a single query. Instead of 101 queries, you run 1 query that returns users with their orders attached.
Tell your AI tool: “Optimize this query to avoid the N+1 problem. Use a JOIN to fetch users with their orders in a single query instead of looping through users individually.”
Pagination Patterns
Offset pagination (simple but slow for large pages): The database skips 1000 rows before returning 20. This gets slower as you go deeper.
Cursor pagination (consistent performance): Use a cursor (like the last item’s ID or timestamp) to jump directly to the right position. Performance stays constant regardless of page number.
Tell your AI tool: “Implement cursor-based pagination for the posts table. Use the created_at timestamp as the cursor. Return 20 posts per page.”
Batch Operations
The Problem: Inserting 1000 items one at a time = 1000 round trips to the database = ~5 seconds.
The Solution: Insert all 1000 items in a single query = 1 round trip = ~50ms.
Tell your AI tool: “Batch insert multiple records in a single query instead of inserting one at a time. This reduces database round trips from N to 1.”
Senior Moves for Database Performance
- Always run EXPLAIN ANALYZE before optimizing. You can’t fix what you can’t see. The query plan tells you exactly where time is spent.
- Add indexes for queries that run >100ms. Below that, the optimization isn’t worth the write overhead.
- Use connection pooling. Opening a new connection per request is 10x slower than reusing pooled connections.
- Batch your writes. 100 individual inserts are 100x slower than 1 batch insert.
- Cache aggressively, invalidate carefully. A stale cache is worse than no cache — users see wrong data and lose trust.
Key Takeaways
| Concept | Mental Model | Senior Insight |
|---|---|---|
| SQL vs NoSQL | Rigid tables vs flexible documents | Default to PostgreSQL; reach for MongoDB only when schema flexibility is required |
| ACID | Mathematical guarantees for transactions | Atomicity prevents partial writes — critical for financial operations |
| Redis Caching | Ultra-fast memory layer in front of PostgreSQL | Cache-aside pattern + active invalidation = fast AND consistent |
| Cache Stampede | Thundering herd when a popular key expires | Singleflight pattern — only one request fetches, others wait |
| Normalization | One fact, one place | Trade write complexity for read simplicity in transactional systems |
| Denormalization | Duplicate data for read speed | Use for dashboards and analytics — not for source-of-truth tables |
| B-Tree Index | Balanced tree for fast lookups | 100-1000x faster reads, 5-20% slower writes — index WHERE/JOIN/ORDER BY columns |
| Composite Index | Multi-column index | Column order matters — put equality columns first, range columns second |
| EXPLAIN ANALYZE | Read the query plan before optimizing | Seq Scan = missing index; Index Scan = good |
| N+1 Problem | One query becomes 100+ | Always JOIN or batch — never loop individual queries |