Data Storage and Replication

By the Syncfusion DevTutorials team · Last updated October 20, 2026

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.

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:

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:

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

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.

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.

Common Mistakes

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: what indexes do and what they cost on writes.
• PostgreSQL Documentation: High Availability and Replication: leaders, standby copies, synchronous vs asynchronous replication.
• Designing Data-Intensive Applications (Martin Kleppmann): the standard book on storage, replication and lag.
• 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.