Loading…
Hello! Welcome to this lesson on high-level database architecture. So far, we've mostly treated the database as a single, reliable box. But what happens when that box gets overwhelmed with traffic, or worse, what happens when it fails?
This lesson covers the essential concepts for scaling (handling more traffic) and ensuring high availability (surviving failures). These are critical topics for any professional backend or full-stack role, and we'll use simple analogies to make them clear and understandable.
Replication is the process of creating and maintaining live, read-only copies of your main database. The main database is called the primary (or master), and the copies are called replicas (or slaves).
Analogy: The Magic Notebook
Imagine you have a Master Notebook (the primary). Everything you write in it (`INSERT`, `UPDATE`) is instantly and magically copied, word-for-word, into several Replica Notebooks.
This is incredibly useful for two main reasons:
The types of replication describe how patient the master is when writing:
Clustering is a more advanced concept where multiple database servers (nodes) work together to act as one single database.
It's a "shared-nothing" architecture where data is automatically sharded (split) across all the nodes, but to your application, it just looks like one big database.
Analogy: Replication vs. Clustering
Here's a quick comparison:
| Feature | Replication (Primary-Replica) | Clustering (e.g., Group Replication) |
|---|---|---|
| Write To | Only the Primary node. | You can write to any node (multi-master). |
| Data Consistency | Eventually consistent (replicas can lag). | Strongly consistent (all nodes must agree). |
| Failover | Manual or needs an external tool. | Automatic and built-in. |
| Complexity | Simpler to set up and manage. | Much more complex (networking, quorum). |
Failover is the automatic process of detecting that the primary database has crashed and promoting a replica to become the new primary.
This is the "high availability" (HA) payoff of having a replica. The goal is to minimize downtime.
Analogy: The Understudy
The primary is the "star actor" and the replica is the "understudy." Failover is the system where, the moment the star gets sick (crashes), the understudy (replica) is automatically pushed onto the stage to take over the role.
The process has two main challenges:
Read-write splitting is the entire purpose of having replicas for scaling.
It is the practice of configuring your application (or a proxy) to send all "write" queries (`INSERT`, `UPDATE`, `DELETE`) to the primary database, while sending all "read" queries (`SELECT`) to the replicas.
Analogy: The Busy Office
This is the perfect analogy.
This keeps the boss free to handle the important write operations, while the assistants handle the high volume of read requests.
-- Your application (or proxy) sees this query:
SELECT * FROM Users WHERE id = 123;
-- It says: "This is a SELECT, it's a read."
-- >> Routes to: replica_server_1.example.com
-- Your application (or proxy) sees this query:
UPDATE Users SET name = 'Alice' WHERE id = 123;
-- It says: "This is an UPDATE, it's a write."
-- >> Routes to: primary_server.example.com
Key Caveat: Replication Lag!
You must always remember "replication lag." If you use asynchronous replication, the assistant (replica) might not have the memo that the boss just wrote 100 milliseconds ago. Your application must be able to handle this "eventual consistency."
This question ties everything together.
ProxySQL is a high-performance, database-aware proxy that sits between your application and your database cluster.
Analogy: The Smart Receptionist
If your database cluster is a busy office with one boss (primary) and five assistants (replicas), ProxySQL is the smart receptionist that sits in the lobby.
Your application (the employee) doesn't need to know who is who. The employee just hands all their work to the receptionist. The receptionist (ProxySQL) looks at each request and decides where to send it.
In short, ProxySQL provides a single, stable endpoint for your application and handles all the complex logic of failover, scaling, and routing behind the scenes.
3 questions · no sign-up, nothing stored
Progress is stored in this browser only - no sign-up, nothing sent anywhere.
The same topic at architecture level, in the system design curriculum.