DBMS
SQL, NoSQL, Redis, indexing, transactions, schema design, and the database concepts every backend developer gets asked.
Database Fundamentals
What Is a DBMS?
What a database management system is, why we need one, and the main types out there.
Types of Databases
A breakdown of every major database type and when to pick each one.
SQL vs NoSQL
Schema vs schema-less, vertical vs horizontal scaling, and when to pick which.
ACID Properties
Atomicity, Consistency, Isolation, and Durability — the four guarantees of reliable transactions.
BASE Properties
Basically Available, Soft state, Eventually consistent — the NoSQL alternative to ACID.
CAP Theorem
Why distributed databases can only guarantee two of three: Consistency, Availability, and Partition Tolerance.
SQL & Querying
DDL, DML & DCL
The four categories of SQL commands: DDL, DML, DCL, and TCL with examples of each.
Joins
INNER, LEFT, RIGHT, FULL OUTER, CROSS, and SELF joins explained with visual diagrams and examples.
Subqueries & CTEs
Scalar, row, and table subqueries, correlated vs non-correlated, EXISTS vs IN, and CTEs for cleaner SQL.
Aggregations & GROUP BY
COUNT, SUM, AVG, MIN, MAX, GROUP BY, and the crucial difference between HAVING and WHERE.
Window Functions
ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, running totals, and PARTITION BY vs GROUP BY.
Views & Materialized Views
Virtual tables, precomputed snapshots, when to use each, and refresh strategies.
Stored Procedures & Triggers
Server-side logic, BEFORE/AFTER triggers, and why modern apps often use ORMs instead.
Schema Design & Normalization
ER Diagrams
Entities, attributes, relationships, and cardinality — the blueprint before we write any SQL.
Normalization (1NF-BCNF)
Step-by-step normalization from a flat table to BCNF, eliminating redundancy at each level.
Denormalization
Why we sometimes intentionally add redundancy for performance, and common patterns for doing it.
Relationships & Keys
Primary keys, foreign keys, composite keys, junction tables, and ON DELETE behavior.
Schema Design Patterns
Star schema, snowflake, EAV, polymorphic associations, soft deletes, and when to use each.
Database Migrations
What migrations are, up/down patterns, tooling, zero-downtime strategies, and common pitfalls.
Indexing & Query Optimization
How Indexes Work
Understanding database indexes — what they are, how they speed up queries, and when to use them.
B-Tree and B+ Tree
How B-Trees and B+ Trees work — the data structures behind database indexes.
Types of Indexes
Hash, composite, covering, partial, unique, and full-text indexes — when to use each type.
EXPLAIN and Query Plans
How to read query execution plans to understand and optimize database performance.
Query Optimization
Practical techniques to make SQL queries faster — from avoiding SELECT * to cursor-based pagination.
Full-Text Search
How full-text search works in databases — LIKE vs tsvector/tsquery in PostgreSQL and FULLTEXT in MySQL.
Transactions & Concurrency
Transactions Deep Dive
Understanding database transactions — BEGIN, COMMIT, ROLLBACK, savepoints, and common pitfalls.
Isolation Levels
The four SQL isolation levels — dirty reads, non-repeatable reads, phantom reads, and how each level handles them.
Locking Mechanisms
Database locks — shared, exclusive, row-level, table-level, advisory locks, and how they affect performance.
Deadlocks
What deadlocks are, how databases detect them, and strategies to prevent them.
MVCC (Multi-Version Concurrency Control)
How MVCC lets readers and writers work simultaneously — the secret behind modern database performance.
Optimistic vs Pessimistic Locking
Two approaches to handling concurrent updates — lock first or check later.
NoSQL Databases
Document Stores
What document databases are, how MongoDB works, and when documents beat tables.
Key-Value Stores
The simplest NoSQL model — just a key and a value. Redis, DynamoDB, and when to use them.
Wide-Column Stores
How Cassandra and HBase work — column families, partition keys, and writing at massive scale.
Graph Databases
When relationships ARE the data — nodes, edges, Neo4j, and the Cypher query language.
NoSQL Data Modeling
How to design data models for NoSQL databases — think access patterns first, schema later.
Choosing the Right Database
A practical decision framework for picking the right database — PostgreSQL, MongoDB, Redis, Neo4j, and more.
Redis Essentials
Redis Data Types and Commands
Redis core data types — strings, hashes, lists, sets, sorted sets, streams, and more — with practical commands for each.
Redis Persistence
How Redis saves in-memory data to disk — RDB snapshots, AOF logging, and hybrid persistence.
Caching Patterns
Common caching strategies — cache-aside, write-through, write-behind — plus how to handle stampedes, penetration, and invalidation.
Redis Eviction and Expiry
What happens when Redis runs out of memory — eviction policies, TTL management, and how expiration actually works under the hood.
Redis Pub/Sub, Distributed Locks, and Cluster
Redis messaging with Pub/Sub, distributed locking with Redlock, and the basics of Redis Cluster for horizontal scaling.
Scaling & Real-World Patterns
Replication
Database replication — master-slave, master-master, sync vs async, and how failover works.
Sharding
Splitting data across multiple database servers — sharding strategies, consistent hashing, and the challenges of cross-shard queries.
Connection Pooling
Why database connections are expensive, how connection pooling works, and how to configure it properly.
CQRS and Event Sourcing
Separating reads from writes with CQRS, storing events instead of state with event sourcing, and when these patterns are actually worth the complexity.
Database per Service, Outbox Pattern, and CDC
Microservice data patterns — database per service, the outbox pattern for reliable events, change data capture with Debezium, and the saga pattern.