When to Choose Relational vs NoSQL? A Practical Guide to Database Architecture Evolution

Introduction

Many developers face CPU bottlenecks when a system receives a traffic surge, and they are often unsure which database technology best fits their data characteristics. This guide reviews structured, semi‑structured and unstructured data, the evolution of relational database architectures, and the strengths and weaknesses of various NoSQL solutions.

Data Types

Structured data follows a two‑dimensional table model with fixed schemas (e.g., rows in MySQL). Unstructured data lacks a predefined model and includes documents, images, videos, etc. Semi‑structured data, such as XML or JSON, carries self‑describing tags that allow hierarchical organization.

<person>
  <name>张三</name>
  <age>18</age>
  <phone>12345</phone>
</person>

Relational Database Architecture Evolution

Four typical stages are described:

Single application server with one relational database.

Multiple application servers behind an Nginx load balancer.

Read‑write separation using a master‑slave setup with binlog replication.

Sharding (horizontal) and vertical splitting of tables and databases, often managed by middleware (e.g., Shard).

These stages can support up to ~10k QPS, but relational databases still suffer from high I/O under concurrency, costly index maintenance, strict ACID overhead, and difficulty scaling horizontally.

Pros of Relational Databases

Easy to understand due to row‑column model.

Powerful SQL language with joins and complex queries.

Strong ACID guarantees ensure data consistency.

Stable persistence on disk, suitable for massive data.

Highly reliable products like MySQL and Oracle.

Cons of Relational Databases

Row‑based storage leads to high I/O when only a few columns are needed.

Maintaining multiple secondary indexes degrades write performance.

Transaction isolation levels increase locking overhead.

Horizontal scaling introduces data migration, cross‑shard joins, and distributed transaction challenges.

Schema changes require DDL operations that can lock tables.

Limited full‑text search capabilities.

NoSQL as a Complement

NoSQL (Not Only SQL) fills the gaps left by relational databases. It is not a replacement but a supplement, chosen according to workload characteristics.

KV‑type NoSQL

Example: Redis. Stores data as key‑value pairs in memory, offering O(1) lookups and high throughput (≈100k TPS). Drawbacks include lack of secondary lookups, limited query capabilities, memory constraints, and potential data loss.

Search‑type NoSQL

Example: Elasticsearch. Provides inverted‑index based full‑text search, powerful aggregations, and horizontal scalability. Cons include heavy memory usage, costly indexing, and limited schema evolution.

Column‑oriented NoSQL

Example: HBase. Stores data by column families, enabling selective column reads, high compression, and efficient scans. Advantages are massive storage capacity, good read/write performance, and easy horizontal scaling. Drawbacks are operational complexity, weak conditional queries, and lack of native pagination.

Document‑type NoSQL

Example: MongoDB. Stores semi‑structured JSON/BSON documents without a fixed schema, allowing flexible field addition and high read/write speed. Limitations include limited transaction support, no native joins, and higher storage consumption.

Comparative Summary

The article concludes with two key decisions:

When to use relational databases (strong consistency, complex joins) versus NoSQL (high write volume, flexible schema).

Which NoSQL family fits a given scenario based on read/write patterns, query requirements, and operational constraints.

Choosing the right storage engine requires balancing performance, cost, and team expertise rather than blindly following textbook architectures.