SQL vs NoSQL

The SQL vs NoSQL choice is not about which is 'better' — it's about which trade-offs fit your problem. SQL databases excel at structured data, complex queries, and ACID transactions. NoSQL databases excel at scale, flexible schemas, and specific access patterns.

The key insight: NoSQL is not one thing. It's an umbrella for several different database models — key-value, document, wide-column, graph — each with different trade-offs.

SQL strengths:

• ACID transactions: multi-operation, all-or-nothing. Critical for payments, inventory, bookings.
• Structured schema: columns and types are enforced. Prevents bad data.
• JOINs: query across related tables efficiently.
• Mature ecosystem: ORMs, tooling, decades of optimization.
• Consistency: strong (linearizable) by default.

SQL weaknesses:

• Vertical scaling: hard to shard across machines.
• Schema changes: migrations are painful at scale.
• Impedance mismatch: relational model doesn't map cleanly to objects.

NoSQL types:

• Key-value (Redis, DynamoDB): simplest. Key → value. Extremely fast. No query complexity.
• Document (MongoDB, CouchDB): key → document (JSON). Flexible schema. Good for content, profiles, catalogs.
• Wide-column (Cassandra, HBase): rows with dynamic columns. Good for time-series, high-write throughput.
• Graph (Neo4j): nodes and edges. Good for social networks, recommendation engines, fraud detection.

Each is optimized for a specific access pattern. Choosing the right NoSQL type requires knowing your query patterns.

Real example: Uber's polyglot persistence.

Uber's stack uses different databases for different access patterns (documented across years of engineering blog posts):

• Schemaless on MySQL for trips, users, drivers — sharded by UUID, high-write-throughput, eventually consistent across regions. Custom store they built because no off-the-shelf DB fit their write profile + global consistency needs.
• PostgreSQL for billing and financial data — ACID transactions, foreign keys, auditable. Worth the cost of a stricter scaling story because the cost of a double-charge or lost-charge is so high.
• Cassandra for location tracking (driver positions updated every few seconds per driver; hundreds of thousands of writes/sec; reads are 'where are drivers near this rider'). Massive write throughput, time-series friendly.
• Redis for caching (hot trip state, rate-limit counters, session data). Sub-millisecond reads, in-memory, accepts data loss on crash for the cache use case.
• Elasticsearch for full-text search (rider destination autocomplete, driver destination search). Inverted-index optimized for relevance-ranked text queries.
• Kafka for event streaming (every trip event flows through Kafka; downstream consumers derive analytics, billing, fraud signals).

Why so many? Because each access pattern has a different optimal storage primitive. Trying to do all of this in one database means either (a) the financial data loses ACID guarantees (dangerous) or (b) the location tracking can't keep up with writes (system failure). Polyglot persistence — using different databases for different access patterns — is the standard pattern at scale. The cost: more infrastructure to operate, more schemas to keep in sync, more failure modes to understand. The rule of thumb: don't introduce a new database until the cost of operating it is less than the cost of forcing your existing DB to do something it's bad at.

PostgreSQL jsonb — the bridge that often eliminates the need for NoSQL.

A common architectural mistake is choosing MongoDB for 'flexible schema' when PostgreSQL has had jsonb (binary JSON) since 9.4 (2015). jsonb stores arbitrary JSON in a binary format, supports indexing via GIN indexes, and lets you query nested fields with SQL operators. The result: many workloads that 'need NoSQL' actually just need flexible schema in a single table — and PostgreSQL can do that without giving up ACID, JOINs, or transactions.

Example schema that bridges the gap:

CREATE TABLE events (
  id          BIGSERIAL PRIMARY KEY,
  user_id     BIGINT NOT NULL,
  event_type  TEXT   NOT NULL,
  payload     JSONB  NOT NULL,  -- flexible per-type schema
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
-- Index the JSON for fast lookups
CREATE INDEX idx_events_payload ON events USING GIN (payload jsonb_path_ops);
-- Index user_id + created_at for time-range queries
CREATE INDEX idx_events_user_time ON events (user_id, created_at DESC);

Now you can query with both structured (SQL) and document (JSON) operations:

-- Find login events for user 42 in the last hour with browser=Chrome
SELECT * FROM events
  WHERE user_id = 42
    AND event_type = 'login'
    AND created_at > NOW() - INTERVAL '1 hour'
    AND payload->>'browser' = 'Chrome';

When does this NOT suffice, and you really do need NoSQL?

• Write throughput beyond ~50K writes/sec on a single primary — Cassandra or DynamoDB scale horizontally where PostgreSQL can't.
• NoSQL-style horizontal sharding is core — Cassandra, DynamoDB, and MongoDB shard transparently; PostgreSQL sharding (Citus) is good but adds operational complexity.
• Schema is truly schemaless and evolves per record — e.g., user-generated forms. Document stores handle this natively; SQL requires the jsonb escape hatch.

The decision rule: reach for PostgreSQL + jsonb first. Switch to a document store only when you've proven the jsonb path can't meet your write throughput or scaling needs. The cost of being wrong in this direction is small (a migration); the cost of starting with NoSQL and realizing you needed ACID is much larger.

Mental model. Think of SQL vs NoSQL as a deliberate boundary in a system. The boundary exists because something becomes harder to manage when everything is done in one place: latency, scale, failure isolation, consistency, cost, or team ownership. The useful question is not “what does SQL vs NoSQL mean?” but “what pressure makes this boundary worth introducing, and what new failure mode does it create?”

Numerical lens: write down traffic, payload size, read/write ratio, peak multiplier and durability target before choosing a component. The numbers should justify the architecture.

Answer this without notes: When would you choose SQL vs NoSQL, and when would you intentionally avoid it? Mention at least one bottleneck it addresses, one failure mode it introduces, and one alternative. Then quantify the workload you are designing for.

Numerical sanity check

A rough capacity check: required write throughput ≈ peak writes/s × average record size. At 5,000 writes/s and 2 KB average payloads, the raw incoming data stream is about 10 MB/s before indexes, replication and overhead.