DEV Community

Cover image for How to Choose Between SQL, NoSQL, and Everything in Between
Sanu Khan
Sanu Khan

Posted on

How to Choose Between SQL, NoSQL, and Everything in Between

#systemdesign #architecture #backend #database

System Design from Developer to Architect — Part 2

In Part 1, we took a backend from:

User → Application → Database
Enter fullscreen mode Exit fullscreen mode

to something much larger:

Users
  ↓
CDN
  ↓
Load Balancer
  ↓
API Cluster
  ↓
Cache + Database
  ↓
Read Replicas
  ↓
Event Broker
Enter fullscreen mode Exit fullscreen mode

We scaled application servers.

We introduced caching.

We added read replicas.

We moved static content to the edge.

We pushed non-critical work into asynchronous processing.

Then someone asks:

Should we move to NoSQL?

It's one of the most common questions in system design.

But it's usually the wrong first question.

A better question is:

What problem are we trying to solve with our data?

Because database architecture shouldn't begin with:

SQL vs NoSQL
Enter fullscreen mode Exit fullscreen mode

It should begin with:

Data
  ↓
Access Pattern
  ↓
Consistency Requirement
  ↓
Scale
  ↓
Constraints
  ↓
Storage Decision
Enter fullscreen mode Exit fullscreen mode

Let's work through it.

Start With the Boring Choice

Suppose we're still building the service-booking platform from the previous articles.

Our core data looks something like this:

User
  │
  ▼
Booking
  │
  ├──── Professional
  │
  └──── Payment
Enter fullscreen mode Exit fullscreen mode

We need to answer questions such as:

Who created this booking?

Which professional owns the slot?

Has the booking been paid?

What is the booking status?
Enter fullscreen mode Exit fullscreen mode

These entities have clear relationships.

A relational database is a natural starting point.

              ┌───────────┐
              │   Users   │
              └─────┬─────┘
                    │
                    ▼
              ┌───────────┐
              │ Bookings  │
              └─────┬─────┘
                    │
             ┌──────┴──────┐
             ▼             ▼
      Professionals     Payments
Enter fullscreen mode Exit fullscreen mode

PostgreSQL, MySQL, or another relational database gives us useful capabilities immediately:

  • Transactions
  • Foreign keys
  • Unique constraints
  • Indexes
  • Joins
  • Mature query tooling
  • Strong data-integrity mechanisms

For many systems, that's an excellent default.

You don't need NoSQL simply because your application might become large.

Relationships Matter

Consider a booking.

Booking
  │
  ├── belongs to → User
  │
  ├── reserves → Professional
  │
  └── has → Payment
Enter fullscreen mode Exit fullscreen mode

Now imagine two customers attempt to reserve the same professional and time slot.

Our database may need to protect something like:

professional_id + booking_date + start_time
Enter fullscreen mode Exit fullscreen mode

from being booked twice.

A relational database can enforce important invariants close to the data.

For example:

UNIQUE (
  professional_id,
  booking_date,
  start_time
)
Enter fullscreen mode Exit fullscreen mode

Application code can contain bugs.

Two application servers can race.

Requests can arrive simultaneously.

The database can still protect the invariant.

This is one reason database choice isn't only about performance.

It's also about correctness.

Then the Data Stops Looking So Relational

Now our professional profiles become more complicated.

A cleaner may have:

{
  "equipment": ["vacuum", "steam cleaner"],
  "languages": ["English", "Arabic"],
  "serviceArea": ["Dubai Marina", "JLT"]
}
Enter fullscreen mode Exit fullscreen mode

A salon professional might have:

{
  "specialties": ["hair", "nails"],
  "certifications": ["CERT-123"],
  "products": ["Brand A", "Brand B"]
}
Enter fullscreen mode Exit fullscreen mode

A maintenance professional may need completely different attributes.

Now our data becomes more flexible.

One option is still relational storage.

Modern relational databases can support JSON columns and hybrid models very effectively.

Another option, depending on the workload, is a document database.

Professional
      │
      ▼
┌────────────────────────┐
│ Document               │
│                        │
│ name                    │
│ services[]              │
│ languages[]             │
│ certifications[]        │
│ metadata{}              │
└────────────────────────┘
Enter fullscreen mode Exit fullscreen mode

The question isn't:

"Is MongoDB better than PostgreSQL?"

The question is:

Does our access pattern benefit enough from a document model to justify another storage technology?

Think in Access Patterns

This is one of the most important ideas in database design.

Don't ask only:

What does my data look like?

Also ask:

How will the application access it?

Suppose we frequently perform:

session_id
    ↓
session
Enter fullscreen mode Exit fullscreen mode

or:

token
  ↓
metadata
Enter fullscreen mode Exit fullscreen mode

or:

user_id
   ↓
preferences
Enter fullscreen mode Exit fullscreen mode

These are simple key-based access patterns.

KEY
 │
 ▼
VALUE
Enter fullscreen mode Exit fullscreen mode

For workloads dominated by this kind of lookup, a key-value store can be extremely effective.

Conceptually:

session:8fa92
      │
      ▼
┌─────────────────────┐
│ userId: 123         │
│ expires: 10:30      │
│ permissions: [...]  │
└─────────────────────┘
Enter fullscreen mode Exit fullscreen mode

This is why systems often use Redis or distributed key-value databases for particular workloads.

Not because:

"NoSQL is faster."

But because the access pattern matches the storage model.

Search Is a Different Data Problem

Now users want to search for:

Female cleaner near Dubai Marina, available tomorrow morning, rated above 4.5.

Suddenly we're dealing with combinations of:

Text
Location
Availability
Rating
Filters
Sorting
Enter fullscreen mode Exit fullscreen mode

We could continue pushing increasingly complex search queries into the primary database.

But at some point, search itself becomes a specialized workload.

We may introduce a search index:

              Primary Database
                     │
                     │ index/update
                     ▼
               Search Engine
                     │
                     ▼
                 Search API
                     │
                     ▼
                   User
Enter fullscreen mode Exit fullscreen mode

The primary database remains the source of truth.

The search engine provides a representation optimized for discovery.

Now we can optimize for:

  • Full-text search
  • Relevance ranking
  • Faceted filters
  • Fuzzy matching
  • Geospatial queries

But we've created another problem.

What if:

Database
Professional rating = 4.8

Search Index
Professional rating = 4.6
Enter fullscreen mode Exit fullscreen mode

The index hasn't caught up yet.

Our search system may now be eventually consistent.

Once again:

Problem
   ↓
Specialized solution
   ↓
New trade-off
Enter fullscreen mode Exit fullscreen mode

Not Every Relationship Needs a Graph Database

Our platform keeps growing.

Now we have relationships such as:

User
 │
 ├── booked ──────→ Professional
 │
 ├── likes ───────→ Service
 │
 └── referred ────→ User
                       │
                       └── booked ──→ Professional
Enter fullscreen mode Exit fullscreen mode

Maybe we want to answer:

Which professionals are popular among users connected to this customer?

Or:

Which services are commonly booked together across several degrees of relationships?

Highly connected traversal can become an interesting graph problem.

A graph model represents information as:

Node ── Relationship ── Node
Enter fullscreen mode Exit fullscreen mode

For example:

        BOOKED
User ─────────────→ Professional
 │
 │ LIKES
 ▼
Service
 │
 │ RELATED_TO
 ▼
Service
Enter fullscreen mode Exit fullscreen mode

A graph database may make complex relationship traversal natural.

But this does not mean:

We have relationships
       ↓
Use graph database
Enter fullscreen mode Exit fullscreen mode

Relational databases already handle relationships extremely well.

Graph databases become interesting when relationship traversal itself becomes a dominant access pattern.

Files Don't Belong Everywhere Either

Our application also stores:

Profile photos
Invoices
Documents
Videos
Attachments
Exports
Enter fullscreen mode Exit fullscreen mode

Should these live directly inside our relational database?

Sometimes binary data can be stored there.

But for large files and media, object storage is often a better fit.

Application
    │
    ├──── Metadata ────→ Database
    │
    └──── File ────────→ Object Storage
Enter fullscreen mode Exit fullscreen mode

The database might store:

file_id
owner_id
object_key
content_type
created_at
Enter fullscreen mode Exit fullscreen mode

while object storage holds the actual binary content.

Again, different data characteristics create different storage requirements.

Now We Have Multiple Databases

Our simple architecture started as:

Application
     │
     ▼
Database
Enter fullscreen mode Exit fullscreen mode

Now it might look like this:

                       Application
                            │
          ┌─────────────────┼─────────────────┐
          │                 │                 │
          ▼                 ▼                 ▼
     Relational           Redis            Search
      Database             │                 │
          │             Sessions          Discovery
      Bookings            Cache             Text
      Payments                              Filters
          │
          └─────────────────┐
                            │
                            ▼
                       Object Storage
                       Files / Media
Enter fullscreen mode Exit fullscreen mode

Potentially, another specialized workload may justify a document or graph database.

This is called polyglot persistence.

But be careful.

Polyglot persistence does not mean:

"Use every database."

It means:

Allow different storage technologies when different data problems justify the operational complexity.

Every database you add creates costs:

Another database
      │
      ├── Deployment
      ├── Monitoring
      ├── Backups
      ├── Security
      ├── Access control
      ├── Data synchronization
      ├── Developer knowledge
      └── Failure modes
Enter fullscreen mode Exit fullscreen mode

Sometimes one PostgreSQL database is better than five specialized systems.

Consistency Changes the Decision

Database choice isn't only about data shape.

It's also about how correct the data must be at a particular moment.

Consider:

Booking confirmed?
Payment completed?
Slot available?
Enter fullscreen mode Exit fullscreen mode

These decisions may require strong guarantees.

Now compare them with:

Analytics dashboard
Search results
Recommendations
Activity feed
Enter fullscreen mode Exit fullscreen mode

If analytics is 10 seconds behind, the business may barely notice.

If booking availability is 10 seconds behind, two customers may try to buy the same slot.

So we can think about data differently:

Booking / Payment
       │
       ▼
Correctness critical
       │
       ▼
Stronger consistency
Enter fullscreen mode Exit fullscreen mode

versus:

Analytics / Search
       │
       ▼
Temporary staleness acceptable
       │
       ▼
Eventual consistency may work
Enter fullscreen mode Exit fullscreen mode

Consistency should follow business correctness requirements—not architecture fashion.

CAP Theorem Without the Interview Definition

You'll eventually hear:

CAP theorem.

The textbook definition matters.

But let's make the architectural problem concrete.

Imagine our database is distributed across two nodes.

        Network
          ✕
     ┌────┴────┐
     ▼         ▼
  Node A     Node B
Enter fullscreen mode Exit fullscreen mode

The nodes can no longer communicate.

But requests are still arriving.

Now the system has a decision to make.

Should both nodes continue accepting operations even though they may temporarily disagree?

Or should some operations be rejected until the nodes can communicate again?

Conceptually:

Network Partition
       │
       ├── Preserve stronger consistency
       │       ↓
       │   Some requests may fail
       │
       └── Preserve availability
               ↓
          Nodes may temporarily disagree
Enter fullscreen mode Exit fullscreen mode

That's the practical architectural tension.

The important lesson isn't memorizing:

C + A + P
Enter fullscreen mode Exit fullscreen mode

It's understanding:

What should our system do when parts of the distributed data layer cannot communicate?

Build the Decision From Requirements

Instead of beginning with database products, start with questions.

What does the data need?
          │
          ├── Transactions?
          │       └── Relational
          │
          ├── Simple key lookup?
          │       └── Key-Value
          │
          ├── Flexible documents?
          │       └── Document
          │
          ├── Full-text discovery?
          │       └── Search
          │
          ├── Relationship traversal?
          │       └── Graph
          │
          └── Large binary objects?
                  └── Object Storage
Enter fullscreen mode Exit fullscreen mode

This is not an automatic decision tree.

It's a way to start asking better questions.

Before choosing storage, ask:

  1. What does the data look like?
  2. How will it be accessed?
  3. What are the read/write ratios?
  4. Which operations require transactions?
  5. How much staleness is acceptable?
  6. How large can the dataset become?
  7. What are the expected query patterns?
  8. What happens during failure?
  9. What operational complexity can the team support?

Then evaluate technologies.

Not the other way around.

The Database Architect's Loop

The same mental model we've used throughout this series still works.

Data Requirement
       ↓
Access Pattern
       ↓
Consistency Requirement
       ↓
Scale Requirement
       ↓
Storage Options
       ↓
Trade-offs
       ↓
Decision
       │
       └──────────────↺
Enter fullscreen mode Exit fullscreen mode

We didn't choose a relational database because:

"SQL is better."

We didn't introduce Redis because:

"Redis is fast."

We didn't introduce a search engine because:

"Databases can't search."

Each technology entered the architecture because the workload developed a specific requirement.

So... SQL or NoSQL?

The answer is frustratingly simple:

It depends on the problem.

But now we can make "it depends" useful.

Requirement Storage Model to Evaluate
Transactions and relational integrity Relational
Flexible nested documents Document
Extremely simple key-based access Key-Value
Full-text search and ranking Search engine
Complex relationship traversal Graph
Large files and media Object storage

And sometimes the correct answer is:

PostgreSQL.
Enter fullscreen mode Exit fullscreen mode

Sometimes:

PostgreSQL + Redis.
Enter fullscreen mode Exit fullscreen mode

Sometimes:

PostgreSQL
   +
Redis
   +
Search Index
   +
Object Storage
Enter fullscreen mode Exit fullscreen mode

The goal isn't to collect databases.

The goal is to use the smallest set of storage technologies that correctly supports the workload.

Don't Choose the Database First

A common mistake looks like this:

"We want MongoDB."
       ↓
"What can we store in it?"
Enter fullscreen mode Exit fullscreen mode

or:

"We should use Redis."
       ↓
"What should we cache?"
Enter fullscreen mode Exit fullscreen mode

Reverse it:

Requirement
    ↓
Data Shape
    ↓
Access Pattern
    ↓
Consistency
    ↓
Scale
    ↓
Trade-offs
    ↓
Technology
Enter fullscreen mode Exit fullscreen mode

That's architecture.

The technology comes after the reasoning.

The Real Goal

The question isn't:

SQL or NoSQL?

The better questions are:

What does this data represent?

How will we access it?

How correct must it be?

How will it scale?

What happens when the system fails?

And is another database worth the operational complexity it introduces?

If one relational database solves those problems, keep it.

If the workload develops a specialized requirement, introduce the appropriate tool.

But make every database earn its place in the architecture.

Up Next

Our data layer is evolving.

Our application is scaling.

Now another boundary starts becoming critical:

Client
  │
  ▼
 API
  │
  ▼
System
Enter fullscreen mode Exit fullscreen mode

APIs that look perfectly reasonable at small scale can become difficult to evolve as clients, services, and integrations multiply.

So next we'll look at:

Part 3 — How to Design APIs That Don't Fall Apart as Your System Grows

We'll cover:

REST boundaries
Resource design
Pagination
Filtering
Versioning
Idempotency
Rate limiting
API gateways
Synchronous vs asynchronous communication
Service-to-service APIs
Enter fullscreen mode Exit fullscreen mode

This is **Part 2* of System Design from Developer to Architect — a practical series about scalability, databases, APIs, distributed systems, reliability, and the engineering decisions behind production architecture.*

Previous: Part 1 — How to Scale a Backend From 1 User to 1 Million Users

Series: System Design from Developer to Architect

Top comments (0)