# Data Storage and Replication Course: [High-Level Design](https://www.devtutorials.ai/learn/system-design/hld) · Section: HLD Foundations Source: https://www.devtutorials.ai/learn/system-design/hld/foundations/data-storage > Choose where data lives: SQL tables, joins and transactions, NoSQL document, key-value and graph stores, indexes and their cost, leader-follower replication, lag and failover. Think about how a library manages its books. Books are arranged on shelves in a specific order. A catalogue tells you exactly where to find a book, so you do not have to search every shelf. Large libraries often keep additional copies of popular books in other branches, ensuring that books remain available even if one copy is lost or unavailable. Databases work in a similar way. They decide **how data is organized**, use **indexes** to quickly locate information, and maintain **replicas** to improve availability and reliability. **The main idea:** choose the right database structure for your data, create indexes for common queries, and maintain multiple copies so a single machine failure does not cause data loss. ## What You Will Learn In this lesson, you will learn: 1. What a database provides beyond storing data in files. 2. How relational (SQL) databases organize data using tables. 3. What transactions are and why "all or nothing" execution matters. 4. The major types of NoSQL databases and their use cases. 5. How to choose between SQL and NoSQL. 6. What an index is and the trade-offs it introduces. 7. How replication creates copies of data. 8. What replication lag is and how read-your-writes consistency helps. 9. What happens when the primary database fails. From Lesson 2: QuickBite stores about **1 million orders a day**, about **2.2 TB a year** with three copies, and does about **20 reads for every write**. Keep these numbers in mind as you pick storage. ## What a Database Does It is possible to store information inside plain files, but this approach quickly becomes difficult at scale. Problems include: - Finding a specific record may require scanning the entire file. - Multiple users writing simultaneously can corrupt data. - System crashes can leave partially written data behind. - Hardware failures can permanently destroy data. A **database** solves these problems by providing: - Fast data retrieval. - Safe concurrent access. - Crash recovery. - Replication and backup capabilities. ## The Main Types of Databases ### Relational (SQL) Databases A **relational database** stores information in tables. Each table consists of: - **Columns** that define the structure. - **Rows** that hold individual records. Data is queried using **SQL (Structured Query Language)**. Popular examples include: - PostgreSQL - MySQL - Microsoft SQL Server One of the biggest strengths of relational databases is the ability to perform **joins**, which connect related data across tables. For example, an order may store only a restaurant ID, while restaurant details remain in a separate table. Step through the join, one order at a time: The result: Order 101 is Biryani Co (Bengaluru), ₹280. Order 102 is Dosa Point (Chennai), ₹150. Order 103 is Biryani Co (Bengaluru), ₹420. This design avoids storing the same information repeatedly. If a restaurant changes its name, only a single record must be updated. ### Transactions: All or Nothing Placing an order typically involves multiple related operations. For example: 1. Deduct money from the customer's account. 2. Create the order record. What happens if the system crashes between these two operations? #### The wrong way first: separate operations Step through an order placed with two separate writes: The customer's money was removed, but the order was never created. This leads to inconsistent and incorrect data. #### The right way: use a transaction A **transaction** ensures that all operations succeed together or fail together. If a failure occurs, the database automatically reverses any changes that were already applied. This process is called a **rollback**. Step through the same crash inside a transaction: The code behind the animation: ```js runnable title="The right way: a transaction undoes step 1 when step 2 never happens" const db = { wallet: 500, orders: [] }; function transaction(steps) { const saved = JSON.stringify(db); // remember how things were try { steps(); return "committed"; } catch (error) { Object.assign(db, JSON.parse(saved)); // roll back to how things were return `rolled back (${error.message})`; } } const result = transaction(() => { db.wallet -= 280; throw new Error("server crashed"); db.orders.push({ total: 280 }); }); console.log(result); console.log(`Wallet: ₹${db.wallet}, orders: ${db.orders.length}`); ``` Expected output: ```text rolled back (server crashed) Wallet: ₹500, orders: 0 ``` The customer keeps their money because the entire transaction failed. This is one of the primary reasons order processing and payment systems are typically stored in relational databases. ## NoSQL Databases The term **NoSQL** means "Not Only SQL." These databases often sacrifice strict structure, joins, or transactional guarantees in exchange for flexibility, scalability, or performance. The three most common categories are: ### Document Databases Document databases store complete objects, usually using JSON-like structures. Example: - Restaurant information and menu stored together in one document. Popular systems: - MongoDB - Couchbase ### Key-Value Databases Key-value databases store data as: `Key → Value`. You give the key, and you get the value back. They are optimized for extremely fast lookups. Popular systems: - Redis - Amazon DynamoDB Common use cases: - Login sessions - User preferences - Rider locations ### Graph Databases Graph databases focus on relationships between records. Popular systems: - Neo4j - Amazon Neptune Common use cases: - Recommendations - Social graphs - Route planning ### Choosing the Right Database | QuickBite Data | Recommended Choice | Reason | | --- | --- | --- | | Orders and payments | Relational (SQL) | Requires transactions and accuracy | | Restaurant menus | Document or SQL | Structure may vary | | Login sessions | Key-value | Fast lookup by session ID | | Rider locations | Key-value | Frequent updates and simple access | | Recommendations | Graph database | Relationship-heavy queries | When uncertain, start with a relational database. QuickBite's storage requirements are easily handled by modern SQL databases, and SQL solutions provide strong consistency and transactional guarantees. **In simple terms:** use SQL when correctness and relationships matter. Use NoSQL when speed, flexibility, or specialized access patterns matter more. ## Indexes: Finding Data Quickly ### The Problem Suppose QuickBite stores one million orders. A customer opens "My Orders." Without an index, the database may need to inspect every order to find the ones belonging to that customer. This is called a **full table scan**. ### What an Index Is An index works like the index at the back of a book. Instead of searching through every page, you jump directly to the correct location. Indexes maintain a sorted structure that allows databases to locate records efficiently. Step through both ways of finding one customer's orders among 1,000,000 rows: A full scan checks **1,000,000 rows**. A sorted index needs about **20 steps**, because each step halves what is left. Searching one million rows can often be reduced to only a few lookup steps. ### The Cost of Indexes Indexes improve read performance, but they are not free. Costs include: - Additional storage space. - Additional work during writes. Whenever a new row is added, the database must also update all relevant indexes. The chart counts the work at lunch, with 58 new orders a second and three indexes: For this reason: - Index columns used frequently in search conditions. - Avoid creating indexes for every column without a clear need. ### Real SQL: What the Database Does Picture a small QuickBite database in SQLite, with `orders` and `customers` tables. Here is what four common queries do inside it. #### Example: Join and Aggregation To count orders per city, the query joins each order to its customer, groups the rows by city and counts them: #### Example: Query Plans Before and After an Index `EXPLAIN QUERY PLAN` shows how the database will answer a query. Step through a search by date before and after `CREATE INDEX`: #### Example: Transaction and Rollback The same all-or-nothing idea works in SQL: 1. `BEGIN;` starts a transaction. 2. `DELETE FROM orders WHERE customer_id = 42;` removes customer 42's orders. Inside the transaction, a count for customer 42 gives 0. 3. `ROLLBACK;` cancels the transaction. 4. Count again: all of customer 42's orders are back, as if the delete never happened. Before creating a new index, use **EXPLAIN QUERY PLAN** to inspect how the database executes the query. If the query already uses an existing index, adding another one may provide little or no benefit. ## Replication: Creating Copies ### Why Replicate Data? Relying on a single database server creates a single point of failure. Replication provides two major benefits: - Improved fault tolerance. - Increased read capacity. ### Leader and Followers A common architecture uses: - One **Leader** (Primary). - Multiple **Followers** (Replicas). In this model: - All writes go to the leader. - Followers copy changes from the leader. - Reads can be distributed across replicas. This architecture works particularly well for QuickBite because reads greatly outnumber writes. ### Replication Lag Replication is not always instantaneous. Followers typically receive updates shortly after the leader. This delay is known as **replication lag**. Step through a price change, a read from a follower and the fix: At 300 ms the leader says ₹280, but the follower still says ₹250. For a brief period, different replicas may return different answers. Most customers will never notice. However, the user who just updated the information may become confused. ### Read-Your-Writes A common solution is **read-your-writes consistency**. After a user performs a change: - Their reads temporarily go to the leader. - Everyone else's reads can continue using followers. This guarantees the user immediately sees their own update. ### Try It: Watch Replicas Catch Up Experiment with replication timing and observe how replicas eventually synchronize. ## What Happens When the Leader Fails? If the leader crashes: - New writes can no longer be accepted. - The system must promote a follower to become the new leader. This process is called **failover**. ### Asynchronous Replication With asynchronous replication: - The leader confirms writes immediately. - Followers update afterward. Advantages: - Faster writes. Disadvantages: - Recent writes may be lost if the leader fails before replication completes. ### Synchronous Replication With synchronous replication: - The leader waits for one or more replicas to confirm the change before responding. Advantages: - Confirmed writes are protected against leader failures. Disadvantages: - Higher write latency. | | Asynchronous Replication | Synchronous Replication | | --- | --- | --- | | Write Performance | Faster | Slightly slower | | Data Loss Risk | Recent writes may be lost | Confirmed writes are protected | | Best For | Menus, ratings, recommendations | Orders, payments | Replication is not the same as backup. If someone accidentally deletes important data, that deletion is immediately replicated to every follower. Proper backups and tested recovery procedures are still required. **Easy way to remember:** use tables for structured business data, transactions for operations that must succeed together, indexes for common searches, replicas for availability, and the leader for users who just made changes. ## Common Mistakes | Mistake | Wrong | Correct | Why it happens | |---|---|---|---| | Choosing NoSQL only to "scale" | "We might be huge one day, so let's avoid SQL." | "Our orders need transactions, and 2.2 TB a year fits on one server. We'll use SQL, and move data elsewhere only when there is a clear reason." | You lose transactions and joins, and gain scale you may never need. | | Related steps without a transaction | Take the money in one write and create the order in another. | Put both in one transaction. | A crash in between leaves the customer charged with nothing to show for it. | | Too many indexes, or none | No index on `customer_id`, or an index on every column. | Index the columns your common questions search by. | Missing indexes make reads crawl. Extra indexes make every write slower. | | Forgetting replication lag | Save to the leader, then read straight back from a follower. | Read your own recent changes from the leader. | Otherwise people see their change "disappear", and report a bug that is not one. | | Treating copies as backups | "We have three copies, so we can't lose data." | Keep separate backups, and practise restoring them. | A mistake on the leader is copied everywhere in milliseconds. | ## Practice ## What to Remember - Databases provide fast access, concurrency control, crash recovery, and replication. - **SQL databases** use tables, joins, and transactions. - **NoSQL databases** include document, key-value, and graph models. - When uncertain, begin with SQL. - **Transactions** ensure all operations succeed together or fail together. - **Indexes** dramatically improve read performance but increase write overhead. - **Replication** maintains multiple copies of data. - **Replication lag** means followers may temporarily contain older data. - **Read-your-writes** ensures users immediately see their own updates. - **Failover** promotes a follower when the leader becomes unavailable. - Replicas improve availability, but only backups protect against accidental deletion. ## Further Reading These are the sources this lesson was checked against: - [PostgreSQL Documentation: Indexes](https://www.postgresql.org/docs/current/indexes-intro.html): what indexes do and what they cost on writes. - [PostgreSQL Documentation: High Availability and Replication](https://www.postgresql.org/docs/current/high-availability.html): leaders, standby copies, synchronous vs asynchronous replication. - [Designing Data-Intensive Applications (Martin Kleppmann)](https://dataintensive.net/): the standard book on storage, replication and lag. - [System Design Primer: Database](https://github.com/donnemartin/system-design-primer#database): SQL vs NoSQL and replication setups. Next: **Partitioning and Consistent Hashing**, where you will learn how QuickBite distributes data across multiple machines and minimizes data movement when servers are added or removed.