paulserban.eu

Writing Edition

Paul Serban

Writing, snippets & book notes

Vol. 2026

SQL vs NoSQL: Choosing the Right Database for Your Project

A practical framework for evaluating relational and non-relational databases based on data shape, consistency needs, and how your system actually grows

Introduction

Few decisions in system design generate as much debate - and as much recycled folklore - as "should we use SQL or NoSQL." Part of the confusion is that the question is usually asked too early and too broadly, as if there were one universally correct answer waiting to be discovered. In practice, the choice is not between two competing technologies so much as between two different sets of assumptions about your data: how structured it is, how it changes over time, how it needs to be queried, and what kind of consistency guarantees your application actually requires.

This article works through that decision the way an experienced systems engineer would: starting from the underlying data model and consistency trade-offs, moving through concrete implementation examples in both a relational database and a document store, and ending with a practical framework for making the call on a real project. The goal is not to declare a winner - there isn't one - but to give you the vocabulary and reasoning to make a defensible, informed choice, and to recognize when the "obvious" choice for your team is actually the wrong one for your workload.

Context: Why This Decision Still Matters

For much of the 2000s, "database" meant a relational database - MySQL, PostgreSQL, Oracle, SQL Server - and the conversation was mostly about which relational engine to use, not whether to use one at all. That changed as internet-scale companies ran into real limits with strict relational schemas and single-node scaling, giving rise to a wave of non-relational systems collectively branded "NoSQL": document stores like MongoDB, wide-column stores like Apache Cassandra, key-value stores like Redis and Amazon DynamoDB, and graph databases like Neo4j. These systems relaxed one or more of the guarantees relational databases had historically provided, in exchange for horizontal scalability, schema flexibility, or both.

The reason this decision still matters, rather than having settled into a simple default, is that both categories have matured considerably since that first wave. Modern PostgreSQL supports JSON and JSONB columns with indexing, letting relational databases absorb some of NoSQL's schema flexibility. Modern NoSQL systems like MongoDB support multi-document ACID transactions since version 4.0, letting document stores absorb some of the consistency guarantees that used to be exclusively relational. The lines have blurred enough that picking a database based on old assumptions - "NoSQL scales, SQL doesn't" or "SQL is for structured data, NoSQL is for everything else" - will frequently lead you astray.

What has not blurred is the underlying trade-off each family of databases is built around. Relational databases are built around a fixed schema, strong consistency, and powerful ad hoc querying via joins. Most NoSQL databases are built around flexible or schema-less data, horizontal partitioning across many nodes, and a willingness to relax consistency guarantees under partition, formalized by the CAP theorem, which states that a distributed data store can provide at most two of Consistency, Availability, and Partition tolerance at the same time. Understanding which of these properties your application genuinely needs - not which ones sound good in an architecture review - is the actual work of this decision.

Deep Technical Explanation: Data Models and Consistency

The most consequential difference between SQL and NoSQL databases is not syntax, it is the data model each one assumes by default. Relational databases store data in normalized tables connected by foreign keys, and they enforce a schema at write time: every row in a table conforms to the same column structure, and referential integrity constraints prevent orphaned or inconsistent references. This makes relational databases excellent at representing data with fixed structure and complex relationships - orders that reference customers that reference addresses - because the join is a first-class, optimizer-driven operation rather than something the application has to implement itself.

Most NoSQL databases invert this default. Document stores like MongoDB store denormalized, often deeply nested JSON-like documents, where related data that would be split across multiple tables in a relational schema is instead embedded directly in a single document. This trades normalization for locality: reading a full order with its line items and shipping address can be a single document fetch instead of a multi-table join, at the cost of needing to think carefully about data duplication and update patterns whenever shared data changes. Key-value stores like Redis and DynamoDB simplify further, treating the value as an opaque blob addressed by a single key, optimized for extremely fast point lookups rather than complex queries.

Consistency is the second axis, and it is where the CAP theorem trade-off becomes concrete. Traditional relational databases default to strong consistency, generally through ACID transactions - Atomicity, Consistency, Isolation, Durability - meaning a transaction either fully commits or fully rolls back, and once committed, a read from any client sees the latest value. Many distributed NoSQL systems instead offer eventual consistency by default, a model often summarized as BASE (Basically Available, Soft state, Eventually consistent), where a write may take some time to propagate to all replicas, and a read shortly after a write might return a stale value from a different node. This is not a flaw; it is a deliberate trade for availability and partition tolerance in a system distributed across many nodes, and it is entirely appropriate for workloads - a social media like counter, a product view count - where slightly stale reads are harmless.

It is worth being precise here because "NoSQL means eventual consistency" is an oversimplification that has hardened into folklore. Many NoSQL systems offer tunable consistency: DynamoDB lets you choose between eventually consistent and strongly consistent reads per request, and MongoDB supports causally consistent sessions and, since version 4.0, multi-document ACID transactions within a replica set. Conversely, "SQL means it doesn't scale horizontally" is equally outdated - distributed SQL systems like Google Cloud Spanner and CockroachDB provide horizontal scalability while retaining strong consistency and SQL semantics, by paying a different cost, typically in write latency, to coordinate across nodes.

Implementation: Practical Examples

Concrete code makes the data-model difference easier to internalize than any amount of prose. Consider an e-commerce order system. In PostgreSQL, using Python's SQLAlchemy, the relational version normalizes orders, customers, and line items into separate tables connected by foreign keys, and a single query can join across all three to answer a question like "what did this customer order last month."

from sqlalchemy import select
from sqlalchemy.orm import Session, selectinload

with Session(engine) as session:
    stmt = (
        select(Order)
        .join(Customer)
        .where(Customer.email == "jane@example.com")
        .options(selectinload(Order.line_items))
        .order_by(Order.created_at.desc())
    )
    recent_orders = session.scalars(stmt).all()
    for order in recent_orders:
        print(order.id, order.total_cents, len(order.line_items))

The relational engine enforces, at the schema level, that every line_item.order_id refers to a real order, and every order.customer_id refers to a real customer - the database itself guarantees this, not application code.

The equivalent MongoDB document model embeds line items directly inside the order document, avoiding the join entirely at the cost of some duplication (the product name and price are typically copied into the line item at order time, rather than referenced live).

// order document shape
const order = {
  _id: ObjectId("..."),
  customerEmail: "jane@example.com",
  createdAt: new Date(),
  totalCents: 4599,
  lineItems: [
    { productId: "sku-123", name: "Wireless Mouse", priceCents: 2999, qty: 1 },
    { productId: "sku-456", name: "USB-C Cable", priceCents: 1600, qty: 1 },
  ],
  shippingAddress: { line1: "123 Main St", city: "Austin", zip: "78701" },
};

// fetching recent orders for a customer - no join required
const recentOrders = await db
  .collection("orders")
  .find({ customerEmail: "jane@example.com" })
  .sort({ createdAt: -1 })
  .limit(20)
  .toArray();

Notice what each model optimizes for. The relational version makes it trivial to answer questions that cut across entities - "which products are most frequently ordered together," "which customers haven't ordered in 90 days" - because the data is normalized and joinable. The document version makes it trivial and fast to fetch one order with everything needed to render it, in a single round trip, which matters when that read happens on every page load of an order confirmation page.

A third pattern worth showing is the key-value case, where the access pattern is a single, extremely hot lookup rather than a rich query. A session store or a feature flag cache is the canonical example, and Redis is the typical choice precisely because it drops relational features entirely in favor of raw lookup speed.

import { createClient } from "redis";

const redis = createClient({ url: process.env.REDIS_URL });
await redis.connect();

async function getSession(sessionId: string): Promise<SessionData | null> {
  const raw = await redis.get(`session:${sessionId}`);
  return raw ? JSON.parse(raw) : null;
}

async function setSession(sessionId: string, data: SessionData): Promise<void> {
  await redis.set(`session:${sessionId}`, JSON.stringify(data), { EX: 3600 });
}

None of these three examples is "the right way" to build an order system in the abstract; each is the right way to serve a specific access pattern, and real production systems frequently use more than one of these databases side by side, a pattern often called polyglot persistence.

Trade-offs and Pitfalls

The most expensive mistake teams make with this decision is choosing NoSQL for perceived scale they do not yet have, and then discovering that the flexible schema they wanted has quietly turned into an unenforced, undocumented one. Without foreign keys or schema constraints, it becomes the application's job to guarantee that every document has the fields it needs and that references between collections stay valid, and in practice, that responsibility gets distributed across dozens of code paths written by different engineers over time. The result is often a codebase where nobody is fully certain what shape a given document is actually in production, especially after several rounds of "add an optional field" changes that were never backfilled onto old documents.

The opposite mistake - forcing a genuinely document-shaped or graph-shaped problem into rigid relational normalization - is just as real, if less discussed. Deeply nested, variably structured data, like a content management system's arbitrary page layouts, or a social graph with highly connected, traversal-heavy queries, tends to fight a relational schema, producing either an explosion of sparse nullable columns or a maze of joins that a graph database or document store would have modeled naturally. The CAP theorem trade-off is also frequently misunderstood in practice: teams pick an eventually consistent NoSQL store for a workload - financial balances, inventory counts - that actually needs strong consistency, and then build ad hoc, error-prone reconciliation logic to paper over the staleness the database was explicitly designed to allow.

Best Practices for Making the Choice

The most reliable starting point is to model your actual access patterns before choosing a database, not after. Write out the five or ten queries your application needs to answer most often and under the tightest latency budget, and ask which data model makes those queries natural rather than which one is currently fashionable. If most of your hot paths are "fetch one aggregate root with everything needed to render it," a document model is doing you a favor. If most of your hot paths are "answer a question that spans multiple entities in ways you can't fully predict in advance," a relational schema with proper indexing will serve you far better, because ad hoc joins are exactly what the relational query optimizer is built for.

Second, take consistency requirements seriously and per-workload, not per-application. A single system can reasonably have strongly consistent financial data in PostgreSQL and eventually consistent view counters in DynamoDB; treating "our database" as a single monolithic consistency decision for the whole application is a common source of both over-engineering and under-engineering. Explicitly write down which pieces of your data require strong consistency and which can tolerate staleness, and let that drive per-component storage choice rather than a single company-wide database mandate.

Third, be honest about your team's operational maturity with whatever you choose. A distributed NoSQL cluster and a sharded relational deployment are both operationally demanding in different ways, and "NoSQL is easier to scale" often quietly means "someone still has to run and tune a distributed cluster," not that scaling is free. If your team has deep PostgreSQL operational experience and no distributed-systems background, a well-indexed PostgreSQL deployment with read replicas will likely get you further, faster, and with fewer 2 a.m. pages than an unfamiliar distributed NoSQL system adopted for scale you don't have yet.

Analogies and Mental Models

A useful way to frame the decision is filing cabinets versus labeled boxes. A relational database is a filing cabinet with strict folders: every document must go in the folder matching its type, cross-references between folders are tracked explicitly, and you can ask sophisticated questions across folders because the structure is enforced and known in advance. A document database is more like a set of labeled boxes: each box (document) can contain everything relevant to one thing - one order, one user profile - packed together for fast retrieval, but asking a question that spans many boxes means opening each one individually, because there's no cabinet-wide index tying them together automatically.

The CAP theorem trade-off is often clarified by thinking about a bank versus a group chat. A bank balance needs strong consistency - two ATMs must never both report you can withdraw the same $100 - so banking systems are built to sacrifice availability during a network partition rather than risk an inconsistent balance. A group chat's "message delivered" status can be eventually consistent - it's mildly annoying if one participant's client shows a message a second later than another's, but nothing breaks - so a chat system reasonably prioritizes availability and partition tolerance over instantaneous global consistency.

The 80/20 Insight

Most of the real-world value in this decision comes down to three questions, and teams that answer these honestly before writing any schema tend to make the right call far more often than teams that start from a technology preference. First: what are my actual hot-path queries, and does the data naturally normalize (many entities referencing each other) or naturally aggregate (one entity with everything needed bundled together)? Second: which parts of my data need strong consistency because being wrong is expensive or dangerous, and which parts can tolerate staleness because being wrong is merely cosmetic? Third: does my team have the operational experience to run what I'm about to choose reliably, independent of which one is theoretically more scalable?

Everything else - specific vendor feature comparisons, benchmark numbers, conference talk anecdotes about hyperscale migrations - matters far less than getting these three questions right for your actual workload. A well-modeled relational schema will comfortably serve the overwhelming majority of applications that will never operate at the scale that originally justified NoSQL's design trade-offs, and a well-chosen NoSQL store will save a genuinely document-shaped or massive-scale system from years of fighting its data model.

Key Takeaways

Conclusion

The SQL versus NoSQL question is best understood not as a binary technology choice but as a set of trade-offs around data shape, consistency, and operational reality that every system has to resolve somehow, whether explicitly or by accident. Relational databases remain the right default for data with genuine, evolving relationships and workloads that need strong consistency and flexible ad hoc querying; NoSQL databases earn their place when data is naturally aggregate-shaped, access patterns are simple and extremely high-volume, or the system's scale genuinely exceeds what a single well-tuned relational deployment can handle.

The engineers who navigate this decision well are rarely the ones who have memorized the sharpest talking points for their preferred camp. They are the ones who model their actual queries and consistency needs first, choose the storage engine that fits those needs, and remain willing to use more than one kind of database in the same system when the workload genuinely calls for it. That discipline - starting from the problem rather than the technology - is what actually separates a well-architected data layer from one that will need to be painfully migrated in two years.

References