Database Choice for a SaaS Product: SQL vs. NoSQL, and When It Actually Matters

Why Database Choice Deserves Real Scrutiny

This guide connects to our guide on choosing a tech stack, which covers the general decision framework for languages and frameworks; this guide goes one level deeper into a single, specific, technically consequential piece of that decision — the database — with the real formal results and real documented case studies behind it, rather than the informal “SQL is for structured data, NoSQL is for scale” heuristic that circulates widely without much technical grounding.

The Real Technical Distinction

It is worth being precise about a common point of confusion: “NoSQL” does not name one alternative to relational databases, it names a broad category of several distinct data models that share a rejection of the fixed relational-table structure as the only option. A document store (like MongoDB) stores self-contained, often JSON-like documents; a key-value store (like Redis in its persistent-store use cases) maps keys directly to opaque values; a wide-column store (like Apache Cassandra) organizes data in a sparse, distributed table structure optimized for very high write throughput; a graph database (like Neo4j) models data explicitly as nodes and relationships. These are genuinely different systems solving genuinely different problems — treating “NoSQL” as a single technology to compare directly against “SQL” obscures more than it clarifies.

ACID and BASE: Where These Terms Actually Come From

The ACID acronym is not folklore — it comes directly from Theo Härder and Andreas Reuter's paper “Principles of Transaction-Oriented Database Recovery,” published in ACM Computing Surveys, volume 15, issue 4, in December 1983, which formalized atomicity, consistency, isolation, and durability as the defining guarantees a database transaction should provide. This is the real academic foundation underneath every relational database's transaction guarantees, not a marketing term invented later by any specific vendor.

BASE has a similarly real, specific, dated origin, though a more recent and more practitioner-oriented one: Dan Pritchett's article “BASE: An ACID Alternative,” published in ACM Queue, volume 6, issue 3, in 2008, written from his own engineering experience at eBay, describing the design philosophy large, partitioned, high-availability systems adopt when strict ACID guarantees become impractical at scale: the system remains basically available even under partial failure, exists in a soft state that may not be immediately consistent across all replicas, and becomes eventually consistent once updates propagate. Eric Brewer is widely associated with popularizing the underlying conceptual tradeoff BASE describes, through his CAP-related work covered next, but the specific, citable, named print source that coined and fully articulated the BASE acronym itself is Pritchett's 2008 article.

The CAP Theorem, Formally

Brewer's original 2000 PODC keynote was exactly that — a keynote talk drawing on his own operational experience running Inktomi's large-scale web infrastructure, presenting CAP as an informal design conjecture rather than a mathematically proven result. The formal proof arrived in Seth Gilbert and Nancy Lynch's paper, “Brewer's Conjecture and the Feasibility of Consistent, Available, Partition-Tolerant Web Services,” published in ACM SIGACT News, volume 33, issue 2, in 2002. It is this 2002 paper that actually formalized the precise, technical definitions worth knowing exactly: Consistency in the CAP sense means atomic, linearizable consistency — there must exist a total ordering of all operations such that the system behaves as if every operation completed at a single, instantaneous point in time, equivalent to requiring every node to see the same data at the same moment. Availability means every request received by a non-failing node must eventually produce a response. Partition tolerance means the system continues operating correctly even when an arbitrary number of messages between nodes are dropped or arbitrarily delayed by the network. Gilbert and Lynch proved formally that no asynchronous distributed system can guarantee all three simultaneously — during an actual network partition, a system must choose between remaining fully consistent or remaining fully available.

Brewer's Own 2012 Correction

Brewer's 2012 retrospective, “CAP Twelve Years Later: How the ‘Rules’ Have Changed,” published in IEEE Computer, volume 45, issue 2, is worth reading directly rather than relying on the popular, static “CP or AP, choose one” framing that has become the conventional shorthand. Brewer's own correction is specific: the three-way tradeoff only actually forces a binary choice during the window an actual network partition is occurring, and real-world partitions are both rarer and shorter than the popular framing assumes; outside of an active partition, a well-designed system can genuinely provide both consistency and availability at once. He also emphasized that “consistency” in the formal CAP sense specifically means strict, linearizable consistency — a much stronger and rarer guarantee than the everyday, looser use of the word “consistent” in casual engineering conversation — and that mature system design involves explicit, nuanced strategies for detecting a partition, choosing a response during it, and recovering cleanly afterward, rather than a single static architectural label chosen once and never revisited.

This is precisely why this guide's own CAP diagram above deliberately does not label specific named databases as belonging permanently to one corner of the triangle: that kind of fixed categorization is exactly the oversimplification Brewer's own 2012 paper corrected. A specific database's actual behavior during a partition depends on its configuration, its consistency-level settings, and the specific failure scenario — not a single, permanent label a marketing page assigns it.

Schema Migrations: The Real Cost Difference

The traditional, real concern about relational schemas is worth stating precisely rather than vaguely: before this specific improvement, adding a column with a default value to an existing table in most relational databases required physically rewriting every existing row to include that new column's default value, which meant a genuine, full-table lock for the duration of that rewrite — a real operational risk on a large, actively-used production table, since it could block reads and writes for a meaningful period. PostgreSQL's own release notes for version 11, shipped in October 2018, document a direct fix for exactly this case: adding a column with a constant default no longer requires a table rewrite, storing the default as metadata instead and only materializing it lazily as existing rows are actually read or updated. This is real, specific, verifiable evidence that at least one major relational database has directly addressed one of the most commonly cited historical arguments for choosing a schema-flexible NoSQL store instead.

It would be inaccurate, though, to claim this eliminates every real difference in migration cost between a rigid and a flexible schema. A document store genuinely does let two records in the same collection have entirely different shapes without any migration step at all — useful for a product still actively iterating on exactly what fields a given entity needs. A relational schema still requires an explicit migration for a genuinely new column, even if that specific operation is now cheaper than it used to be on a database like Postgres 11 and later. The honest, practical framing: schema flexibility is a real, still-relevant tradeoff, but the specific cost of relational schema changes has gotten meaningfully cheaper on modern relational databases than the historical horror stories about multi-hour, table-locking migrations would suggest.

Isolation Levels: The Real Standard Behind “Consistency”

It is worth being precise about a real, common point of confusion this guide has already flagged once: the word “consistency” does real, distinct technical work in three different contexts covered in this guide, and conflating them produces genuinely confused reasoning about database behavior. The “C” in ACID refers to a database enforcing its own defined integrity constraints (foreign keys, unique constraints, check constraints) across a transaction. The “C” in CAP, per Gilbert and Lynch's formal 2002 definition, refers to linearizable consistency across distributed nodes. And separately, the ANSI SQL standard's isolation levels govern a real, related but distinct question: how much of one transaction's in-progress work is visible to another concurrently running transaction on the same database, before either commits. A relational database offering serializable isolation is making a real, strong guarantee about concurrent transaction behavior that has nothing directly to do with whether that same database can survive a network partition across multiple physical nodes — a single-node database can offer perfect isolation guarantees while having no meaningful CAP tradeoff to make at all, since CAP's partition-tolerance question only becomes relevant once a system is actually distributed across multiple nodes that can lose contact with each other.

When ACID Guarantees Actually Matter

The practical test worth applying to any specific piece of a SaaS product's data model: would a “lost update” — two concurrent operations each reading a value, then each writing back a result based on that stale read, silently overwriting one another's work — actually produce a real, visible, harmful outcome? For a billing ledger, a subscription seat count, or an inventory level, the answer is a direct, unambiguous yes: two concurrent decrements of an inventory count that each read the same starting value can leave the recorded count higher than what was actually sold, and two concurrent attempts to charge the same invoice can produce a genuine double-charge if the operations aren't properly serialized. This is precisely the kind of scenario ACID's atomicity and isolation guarantees exist to prevent, and it is a real, concrete, common pattern in ordinary SaaS products — not a niche financial-services-only concern.

By contrast, plenty of real, common SaaS data does not carry this same risk profile. A user's last-viewed timestamp on a dashboard, a cached count of how many times a feature was used for an analytics display, or a log of user activity events, generally tolerate a stale read or an occasional duplicate write without producing a genuinely harmful outcome — nobody is harmed if an activity feed briefly shows slightly out-of-date data. This is the real, practical distinction worth applying directly: strict ACID guarantees earn their real cost (in throughput, in distributed-systems complexity) specifically on the subset of a product's data where a lost update or a dirty read would produce an actually wrong, harmful result — not uniformly across every table in a schema.

Eventual Consistency: What It Actually Means for Users

This is worth making concrete rather than abstract, since “eventual consistency” as a phrase can sound like a purely technical footnote until it produces an actual, visible product bug report. A real, common example: a user updates their profile name, and a comment they post moments later still displays their old name in a different part of the application that reads from a replica that hasn't yet received the update — a real, user-visible inconsistency directly caused by the eventual- consistency model, not a bug in the traditional sense, but a real design tradeoff the team accepted when choosing that data store or replication strategy. The practical question worth asking directly for any specific piece of data: is a brief, bounded window of staleness actually acceptable for this particular user-facing behavior, or does this specific interaction need the stronger, more expensive guarantee that every read reflects the very latest write? Different parts of the same product can reasonably answer this question differently — a chat message list arguably tolerates brief staleness better than an account balance display does.

Uber's Real, Documented Postgres-to-MySQL Migration

It is worth stating directly, since this detail gets lost constantly in secondhand retellings: Uber's real, documented 2016 migration was from one relational database to another — PostgreSQL to MySQL — not a move from SQL to NoSQL. The four real, specific technical reasons Klitzke's post gives are worth knowing precisely rather than summarized away:

1. 1

   Secondary-index write amplification

   Postgres's MVCC design meant updating even a single field required writing an entirely new row version and updating every secondary index pointing to that row's physical location, not just indexes on the changed column. MySQL's InnoDB secondary indexes point to the primary key rather than a physical location, requiring only the affected indexes to update.

2. 2

   Replication format

   Postgres, at the time, replicated at the physical write-ahead-log (WAL) level, producing large, verbose replication streams that were a real bandwidth cost for cross-datacenter replication. MySQL's logical (row-based) replication was significantly more compact.

3. 3

   Replica-side MVCC limitations

   Because Postgres replicas apply physical WAL changes rather than logical ones, they could not implement true row-level MVCC — a long-running query on a replica could block replication application, forcing Postgres to kill queries after a timeout. MySQL's logical replication let replicas maintain genuine MVCC.

4. 4

   Upgrade downtime

   Postgres major-version upgrades at the time required full downtime — shutting down the primary, running pg_upgrade (potentially hours), then resyncing replicas. MySQL supported near-zero-downtime rolling upgrades.

Uber's subsequent, real, documented database engineering built directly on this MySQL foundation rather than reversing course: the company published further dated engineering posts describing “Schemaless,” a custom sharding datastore built on top of MySQL, and later presented a further custom distributed SQL system, DocStore, at industry conferences. This guide could not locate any dated, official Uber statement describing a later move back toward Postgres or a Postgres-compatible system — the verifiable, documented trajectory runs from Postgres, to MySQL in 2016, to increasingly sophisticated custom infrastructure built on that MySQL foundation, not a reversal.

Instagram: Postgres at Very Large Scale

Instagram's engineering blog post, “Sharding & IDs at Instagram,” describes a concrete, real sharding architecture: rather than sharding across many separate physical database servers from day one, Instagram created thousands of logical shards implemented as PostgreSQL schemas (namespaces within Postgres), initially co-locating many logical shards on a small number of physical machines. As the company grew, it redistributed those logical shards across a larger number of physical servers without needing to re-bucket or reassign the underlying data — the logical-to-physical mapping could change independently of the sharding scheme itself. This is a genuinely useful, technically specific counter-example to the assumption that relational databases inherently cannot scale horizontally: Instagram grew to serve hundreds of millions of users while remaining on a Postgres-based architecture, using a real, documented sharding design rather than abandoning the relational model.

JSONB and the Blurring of SQL vs. NoSQL

PostgreSQL's own official release documentation confirms the jsonb binary JSON data type shipped in PostgreSQL 9.4, released December 18, 2014, storing JSON in a decomposed binary format (rather than the exact-text storage of the earlier json type) specifically to enable efficient indexing — including GIN indexes — and query operators over nested JSON structures directly inside an otherwise fully relational table. This is real, official, dated, technical evidence for a genuine trend worth naming directly: a modern relational database is not limited to rigid, fully-normalized tables the way the SQL-vs-NoSQL framing sometimes implies, and a team does not have to choose a document-store database purely to get flexible, semi-structured data support within an otherwise relational schema.

A related industry term worth naming with an honest caveat: “NewSQL” is a real, widely used term describing systems (such as distributed SQL databases) that aim to combine ACID-style relational semantics with NoSQL-style horizontal scalability. This guide could not verify one precise, dated, primary-source citation for exactly who coined the term or when, despite its now-common industry usage, so it is named here as a real, current term in wide use rather than attributed to a specific person or publication this guide independently confirmed.

Real Popularity Data, and Why It Is a Moving Target

The DB-Engines Ranking, published at db-engines.com, is a real, ongoing, methodologically documented composite index combining multiple real signals of database popularity rather than a single vague claim. It is worth using this resource directly when actually making a decision, rather than relying on a specific number quoted in an article that may be stale by the time it is read — the ranking itself documents historical trend lines, including a real, specifically dated milestone: PostgreSQL overtook MongoDB in the DB-Engines Ranking in September 2016, a genuine, citable historical data point rather than a live figure that will drift.

Sharding Mechanics: Choosing a Partition Key

The mechanic itself is real and reasonably simple to describe precisely: a partition key (sometimes called a shard key) is chosen from the data itself — a user ID, an account ID, a geographic region — and a deterministic function of that key decides which physical shard a given piece of data lives on. The real, consequential engineering risk is a poorly chosen partition key producing a “hot shard”: if one specific key value (a very large customer account, a viral piece of content) receives disproportionate traffic, the single physical shard responsible for that key absorbs disproportionate load no matter how many other shards exist and sit comparatively idle. This is exactly the kind of problem Instagram's own documented logical-shard design was built to avoid — by decoupling the logical shard count from the physical machine count, the company could redistribute load across more physical servers later without having to change the sharding scheme or the partition key logic itself.

It is worth stating plainly that this specific engineering challenge — picking a partition key that distributes load evenly and keeps data that is frequently queried together on the same shard — is not a problem that disappears by choosing a NoSQL database instead of a relational one. Many NoSQL systems shard natively and expose partition-key selection as an explicit, first-class part of their data modeling, which can make the tradeoff more visible earlier in the design process, but the underlying hard problem is the same regardless of which category of database sits underneath it. A team choosing a database primarily to get “built-in sharding” should understand that the actual hard part — choosing a good partition key for its own specific access patterns — is still work only the team itself can do, informed by its own real query patterns, not something a database category solves automatically on a team's behalf.

What This Guide Could Not Verify

Consistent with the standing rule across this series, it's worth naming directly the specific claims this guide's research could not confirm to a standard it's comfortable presenting as settled fact:

1. 1

   Any claim that Uber later reversed course back toward PostgreSQL

   This guide could not locate a dated, official Uber engineering statement describing a move back to Postgres or a Postgres-compatible system after the 2016 migration — the documented trajectory continues forward onto MySQL-based custom infrastructure (Schemaless, DocStore).

2. 2

   Whether Postgres autovacuum/table-bloat behavior was the primary stated reason for Uber's migration

   Vacuum-related bloat is a very commonly discussed Postgres-at-scale pain point in general industry commentary, but this guide could not confirm it was the central, named reason in Evan Klitzke's original 2016 Uber post — the post's primary documented reasons are secondary-index write amplification, replication format, replica MVCC limits, and upgrade downtime.

3. 3

   A specific named author for Instagram's "Sharding & IDs at Instagram" engineering post

   The post is organizationally attributed to "Instagram Engineering" in this guide's research; no specific individual author was independently confirmed, so none is named here.

4. 4

   A precise, dated, primary-source origin for the term "NewSQL"

   The term is in real, wide industry use, generally associated with industry-analyst commentary from around 2011, but this guide could not confirm one specific, dated, primary citation for its coining.

5. 5

   A specific, current DB-Engines ranking figure at the time of publication

   The DB-Engines Ranking updates monthly; this guide cites its real, fixed September 2016 PostgreSQL/MongoDB milestone as a stable historical data point, but recommends checking db-engines.com directly for any current ranking rather than relying on a number that will go stale.

A Practical Framework

Bringing the research above together into an actual sequence for a team choosing a database for a new SaaS product, or evaluating whether an existing choice still fits:

It is worth being direct about the order these four steps belong in, since a team excited about a particular NoSQL product's marketing often wants to start with the “blend” or “weigh” steps before doing the harder, less exciting work of the first two. The default and identify steps are deliberately first: a relational database, chosen by default and only abandoned for a specific, named technical reason, is the position both Uber's and Instagram's real, documented histories actually support, even though the two companies reached different final conclusions. Uber found a specific, real, named pressure (secondary-index write amplification under their particular write volume) that justified moving off Postgres; Instagram never found an equivalent pressure and instead solved its scaling problem within the relational model. Both are real, documented, successful outcomes — the difference between them was not which category of database is objectively better, but which team found a specific, real technical reason to move, and which team didn't.

None of this requires a distributed-systems specialist on staff to apply correctly. What it requires is treating the SQL-vs-NoSQL decision the way this guide has approached the underlying research itself: naming the actual, specific technical pressure at hand, checking it against real documented cases like Uber's and Instagram's, and understanding the formal CAP tradeoff precisely enough to know it is not a permanent, static label — rather than defaulting to whichever database category currently has the most confident-sounding marketing behind it.

Frequently Asked Questions

It is also worth naming directly why this particular technical decision has attracted so much genuine folklore, more than most engineering choices covered elsewhere in this series: it sits at the intersection of a real, formally proven distributed-systems result (CAP), a real academic transaction model (ACID/BASE), and intense vendor marketing from companies selling specific database products, each incentivized to frame the tradeoffs in whichever light favors their own technology. The genuinely useful discipline this guide has tried to model throughout is separating those three layers cleanly: cite the formal computer-science result precisely, cite the real documented company experience precisely, and treat marketing framing as exactly that, rather than blending all three into a single, confidently asserted rule of thumb that sounds authoritative but doesn't actually trace back to anything real.

Every formal result, quote, and company case study in this guide traces to a real, named, dated source — a peer-reviewed or industry-refereed publication, or a company's own named engineer writing on its own engineering blog — and every place this guide's research hit a genuine limit, that limit is stated directly rather than papered over with an invented detail. Choosing a database is a real engineering decision with real formal results behind it, not a matter of picking whichever category currently sounds more modern.