System Design Interview: How to Choose the Right Data Store

Stop guessing which database to use. Learn the differences between SQL & NoSQL, master the CAP Theorem, & analyze why Discord migrated from MongoDB to Cassandra/Scylla to handle billions of messages.

This blog covers:

• Relational, document, and wide-column databases

• Database selection approach

• Discord case study

The most dangerous moment for a software application is often its greatest success.

When user traffic spikes unexpectedly, hidden architectural flaws instantly become critical failures.

The database is usually the first component to buckle under this pressure.

A slow query that went unnoticed with one hundred users can crash an entire system when one million users try to execute it simultaneously.

Selecting the correct data storage engine is one of the few decisions in software engineering that is difficult to reverse.

Migrating data from one architecture to another is risky, time-consuming, and expensive. It requires rewriting code, moving terabytes of information, and risking downtime.

Therefore, understanding the underlying mechanics of different databases is a mandatory skill for any developer hoping to build scalable systems.

This guide explores the fundamental differences between database types, the theoretical frameworks used to evaluate them, and a detailed analysis of why a major tech company had to completely rewrite their storage layer to survive.

Key Takeaways

Here are the key takeaways for selecting a database:

• Analyze the Data Shape: If the data is rigid and relational, use SQL. If it is unstructured or changes often, consider NoSQL.

• Define the CAP Requirements: Decide if the system needs to be 100% available (AP) or 100% consistent (CP) during a failure. You cannot have both.