SQL vs NoSQL: A Decision Guide From Real Project Scenarios

Start from access patterns, not fashion

I ask: What are the writes? What are the reads? Do we need multi-row transactions? How often will the shape change? Who will run migrations at 2am? Marketing tweets about “web scale” do not answer those questions.

Default bias: Postgres (or another mature relational DB) unless a documented access pattern clearly favors a document or key-value store. That bias has saved me more often than it has slowed me.

Write the top ten queries on a whiteboard before picking a store. If you cannot name them, you are not ready to choose a database—you are ready to prototype on Postgres.

Prototype speed matters, but so does the cost of being wrong. Postgres lets me change my mind with migrations. A document store that encouraged schemaless chaos made reverse-engineering the real schema a forensic exercise.

When SQL is the obvious win

Billing, inventory, permissions, anything with invariants across tables: relational + transactions. Ad-hoc reporting and joins (“orders with items for users in region X last week”) stay sane in SQL. Strong constraints (foreign keys, unique indexes) catch bugs before they become support tickets.

On a marketplace, we briefly considered Mongo for “flexible listings.” Listing fields were flexible; money movement was not. Split brain between two stores without a clear boundary became the actual problem. One Postgres database with JSONB for the flexible attributes would have been enough.

Team familiarity counts. A competent Postgres setup beats an exotic store nobody can tune when replication lag shows up on a Friday.

Foreign keys are not bureaucracy. They are executable documentation. When I drop them “for speed” without measuring, I usually regret the orphaned rows more than I celebrate the microbenchmark.

• Multi-entity invariants → SQL transactions
• Heavy relational reporting → SQL
• Mature ORMs/query tooling and hiring pool → SQL often cheaper operationally
• JSONB in Postgres covers many “document” needs without a second system

When NoSQL earns its keep

High-volume event or session data with simple key lookup, large semi-structured payloads, or workloads where you intentionally denormalize for read speed. Content CMS blobs, feature-flag dumps, IoT ingest buffers, and certain feed fan-outs can fit document or wide-column stores well.

I used Redis as a primary store for ephemeral multiplayer room state—correct call. I used Mongo as the primary for an app with lots of joins-in-application-code—wrong call. We spent months reinventing relational integrity poorly.

If your “documents” are mostly relational rows with a JSON column, stay on SQL. Document databases shine when the document is the unit of consistency and you rarely need cross-document joins.

Key-value and document stores excel when you can name the primary key for every read you care about. If you keep inventing secondary indexes that look relational, listen to that signal.

Scenario walkthroughs

SaaS billing and seats: Postgres. Social activity stream with millions of tiny writes and read-by-user-key: often a specialized store or carefully denormalized SQL plus cache—not “Mongo because social.” Product catalog with rare schema change and complex filtering: Postgres + indexes, maybe search engine alongside.

Analytics events: append-only store or warehouse (BigQuery/Snowflake/ClickHouse), not your OLTP Mongo cluster. Mixing analytics and transactional traffic is a classic slow-motion outage.

Multi-tenant SaaS with row-level isolation: Postgres plus careful indexing (and sometimes schema-per-tenant for extreme cases). Do not assume a document store gives you tenancy for free.

Search is its own problem. Elasticsearch/OpenSearch or a hosted search product next to Postgres is a normal architecture. Using a document DB as a pseudo-search engine produces mediocre relevance and operational pain.

-- Postgres: flexible attributes without leaving SQL
CREATE TABLE listings (
  id           bigserial PRIMARY KEY,
  seller_id    bigint NOT NULL REFERENCES users(id),
  title        text NOT NULL,
  price_cents  integer NOT NULL CHECK (price_cents >= 0),
  attrs        jsonb NOT NULL DEFAULT '{}',
  created_at   timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX listings_attrs_gin ON listings USING gin (attrs);

Operational questions people skip

Backups, point-in-time recovery, migrations, local dev story, observability. A shiny DB with weak backup tooling is a future incident report. Also ask: can the team query it under pressure? If only one person understands the data model, you do not have a database strategy—you have a bus factor.

Multi-model polyglot persistence is fine when boundaries are sharp (Postgres for core, Redis for cache/ephemeral, S3 for blobs). It is painful when every feature picks a new store because someone watched a talk.

Cost models differ. Managed Mongo and managed Postgres both bill for ignorance—oversized clusters, no indexes, bad data models. Pick the model that matches the queries, then right-size.

Compliance and audit trails lean relational. Immutable event tables with clear joins to actors and resources map cleanly to SQL. Bolting audit onto an unstructured blob store usually means incomplete history.

My decision cheat sheet

Unknown domain early on → Postgres. Need transactions and relations → Postgres. Need crazy write throughput with simple keys and TTL → Redis or similar. Truly document-shaped, rarely joined data with a clear key → document DB can be justified. Need search relevance → use a search engine, do not pretend LIKE or regex is search.

Regret I still remember: choosing Mongo to “move faster” on a relational problem. We moved slower after month two.

Revisit the choice when access patterns change. Migrating is expensive, but living forever on the wrong store is more expensive. Schedule an honest review when QPS or join complexity jumps an order of magnitude.

If leadership asks for NoSQL to attract hires, push back with a workload brief. Hiring fashion is not a query planner.

Write the decision down in the repo ADR folder. Future debates should start from recorded constraints, not from whoever argues loudest in Slack.

Hybrid designs that worked for me

Postgres for core entities, Redis for hot keys and rate limits, object storage for binaries, and a warehouse for analytics. That quartet covers most product companies I work with without introducing a document DB.

When we did use Mongo, it sat behind a bounded context: notification templates and provider payloads with wildly different shapes per channel. Core accounts and billing stayed in Postgres. The boundary was enforced in code review.

• Prefer clear boundaries over one mega-store
• Put binaries in object storage, not in documents or rows
• Revisit the map when a context outgrows its store

Key takeaways

• Choose from access patterns and invariants, not trends
• Default to Postgres unless NoSQL clearly fits the workload
• JSONB often replaces a second document database
• Keep analytics out of your OLTP primary when possible
• Polyglot persistence needs hard boundaries
• Team operability matters as much as theoretical scale