SQL vs NoSQL Databases

The SQL vs NoSQL question comes up in almost every interview where you pick a database. We start from access patterns and consistency needs — one engine rarely fits every workload, and polyglot persistence is normal at scale.

We start from access patterns and consistency needs — one database rarely fits every workload:

Database selection is an access-pattern decision, not a popularity contest. We frame it around three lenses before naming an engine:

Walk polyglot persistence as interview steps. Step 1 — List hot queries: write each read/write path before drawing schemas. Step 2 — Match workloads: route ACID transactions to PostgreSQL, sessions to Redis, write-heavy logs to Cassandra, search to Elasticsearch. Step 3 — Draw CDC fan-out: show how change streams keep search indexes eventually consistent without synchronous joins. Step 4 — State consistency: name which stores are strong vs eventual for each entity.

We compare engine families by schema model, transaction bounds, and scaling paradigm — one row per talking point:

• Storage engine families span relational, key-value, wide-column, document, and graph — pick by access pattern, not label:

ACID and horizontal write scale pull in opposite directions. We articulate both sides:

These failure modes appear when we pick the wrong engine for the access pattern — we name them early:

Real systems mix engines deliberately. These examples show the rationale interviewers want to hear:

Start from query shape and consistency needs — then pick SQL, NoSQL, or both:

Database choice connects to indexing, sharding, and hash-ring routing — follow these when pushed deeper:

• Database Indexing & Query Optimization: Optimizing point lookup speeds in SQL.
• Sharding & Database Partitioning: Spreading SQL databases horizontally.
• Consistent Hashing: Hash rings in Cassandra Wide-Column clusters.

ACID vs BASE Paradigms

Databases represent two opposing design philosophies on the CAP theorem spectrum:

1. ACID (SQL Baseline)

• Atomicity: Entire transaction succeeds or rolls back.
• Consistency: Database transition matches all schema constraints.
• Isolation: Concurrent transactions do not bleed into each other.
• Durability: Committed transactions survive power crashes (via WAL log flush).

2. BASE (NoSQL Baseline)

• Basically Available: The database prioritizes accepting write commands over immediate synchronization.
• Soft State: Node replica values can drift over time without active lock enforcement.
• Eventual Consistency: Replicas eventually sync to store identical states after update streams stop.

Select ACID when consistency is absolute; choose BASE when write availability is the scaling priority.

Access-Pattern-First Data Modeling

In SQL interviews, candidates often start with entity diagrams (User, Post, Comment) and hope JOINs will work at scale. In NoSQL and high-scale SQL designs, flip the process: **list every read and write query first**, then design tables around those access paths.

1. Start from Queries, Not Entities

Write down the hot paths before drawing schemas: "fetch user timeline by user_id sorted by created_at DESC", "lookup order by order_id", "list products in category X under $50". Each distinct query pattern may need its own physical storage layout — one normalized table cannot serve all of them efficiently at billion-row scale.

2. Denormalize for Read Paths

If a feed query joins Users + Posts + LikeCounts on every page load, precompute and embed like_count and author_name directly into the Post row (or a feed-specific table). You pay extra write amplification on each like, but eliminate a three-table JOIN on every read — the classic read-heavy trade-off.

3. Composite Keys for Wide-Column Stores

In Cassandra or DynamoDB, the partition key determines which node holds the data; the sort key orders rows within that partition. Design the composite key to match your query: (user_id, created_at) for a per-user timeline, (category_id, price) for category-sorted product browse. A query that does not include the partition key triggers a full cluster scan — a design failure, not a tuning problem.

# Timeline table — one query, one table
PRIMARY KEY ((user_id), created_at, post_id)
→ SELECT * FROM timeline WHERE user_id = ? ORDER BY created_at DESC LIMIT 20

# Separate table for the inverse query (posts by hashtag)
PRIMARY KEY ((hashtag), created_at, post_id)

4. When to Duplicate Data Across Tables

• Different partition keys for the same entity: Store a user's posts under user_id for their profile and under hashtag for search — two write paths, zero JOINs at read time.
• Read replicas of hot fields: Copy display_name into every comment row so rendering a thread never hits the Users table.
• Materialized counters: Maintain a denormalized follower_count column updated asynchronously rather than COUNT(*) on every profile view.

Rule of thumb: duplicate when the read QPS on a JOIN path exceeds what a single node can serve, and the duplicated fields change infrequently or can tolerate brief staleness.