PostgreSQL — Imagine a library where every book is a row in a table. The library’s catalog (the schema) tells you what each book contains: title, author, ISBN, etc. Shelves (the indexes) let…
Database normalization — Think of a library. If every book had its own copy of the author’s biography, the library would store the same text thousands of times. Instead, the library keeps one biography…
Joins — Think of a library where each book has a catalog record (title, author ID, genre ID). The author and genre tables hold the actual names. When you want to display a book, you look…
Indexes — Problem Databases store data in tables that are essentially unordered collections of rows. When you run a query like SELECT * FROM users WHERE email = 'alice@example.com', the…
Composite indexes — Motivation When a database receives a query, it must decide how to locate the requested rows. If the query filters on multiple columns (WHERE a = 5 AND b = 10) and the database…
Query Planning — When a backend receives a request that needs data from a database, the database must decide how to fetch that data. The query planner is the component that chooses the most…
Transactions — Motivation When a backend service performs multiple database operations that belong to a single logical unit of work, we want either all of those operations to succeed or none to…
ACID — When a backend service performs multiple database operations that belong to a single logical task—like transferring money between accounts or creating an order with inventory…
Isolation Levels — Isolation levels appear in every relational DB (PostgreSQL, MySQL, SQL Server, Oracle) and in any system that relies on ACID transactions, such as microservices that share a…
Deadlocks — Motivation When multiple concurrent processes (transactions, threads, or services) try to modify the same data, they must coordinate to keep the database consistent. If they lock…
Locks — When multiple processes or threads try to read or write the same data concurrently, they can interfere with each other. Imagine two people editing the same paragraph in a document…
MVCC — Without a mechanism to isolate concurrent operations, you either sacrifice performance (by serializing all access) or risk data corruption.
Constraints — Why it matters Without constraints, a backend could store contradictory data: two users with the same email, an order referencing a non‑existent user, or a product with a negative…
Foreign keys — When building relational data models you often have entities that belong to other entities. For example, a Post belongs to a User. In a database this relationship is represented…
Views — Views let you declare a reusable, named SELECT that the database engine can treat like a table. They provide a single source of truth for complex logic, enforce consistent…
Materialized views — Materialized views provide a database‑level, declarative way to store pre‑computed results that can be refreshed on demand or automatically, giving predictable performance and…
Stored procedures — Analogy Think of a stored procedure as a factory assembly line inside the database.
Replication — Motivation In a real‑world backend you rarely have a single database instance that can serve all traffic, guarantee uptime, and provide fast reads for users spread across the…
Read replicas — Think of a library that has one master copy of a book (the primary). Every time someone wants to read the book, they must go to the master copy. If many people come at once, the…
Sharding — Motivation When a single database instance grows beyond its hardware limits—whether in storage, I/O, or query throughput—it becomes a bottleneck. A monolithic database can no…
MongoDB — Motivation Modern web services need to store large amounts of semi‑structured data that evolve over time. Traditional relational databases force a rigid schema, which can slow…
DynamoDB concepts — In production systems, DynamoDB is often the backbone for session stores, analytics pipelines, leaderboards, and any component that requires fast, scalable key‑value access.
Cassandra concepts — Why it matters If you cannot scale writes or tolerate node failures, your service will become a bottleneck or crash under load. For example, a real‑time analytics platform that…
Document Modeling — Why the problem matters Backends that serve millions of requests per day must keep latency low and throughput high. A bad document model can become the single bottleneck, forcing…
Embedding vs Referencing — Embedding is like putting a photo inside a photo album: you can see the picture immediately, but if the photo changes you have to replace every copy. Referencing is like keeping a…
Partition keys — When a backend system grows, a single database instance can no longer hold all data or handle all traffic. Partition keys let us split the data across many machines (shards) while…
Consistency models — When a backend system is distributed across multiple nodes (databases, caches, micro‑services), a write performed on one node may not be immediately visible on another…
Why a query can be slow even when an index exists — Analogy Imagine a library with a perfect index (the card catalog). If you want a book on “Quantum Mechanics,” you look up the card, find the shelf, and grab the book. But if the…