SQL vs NoSQL & Distributed Systems

1. What is the difference between SQL and NoSQL?

SQL (relational) databases store data in structured tables with fixed schemas and enforce relationships via keys.

NoSQL (non-relational) databases drop the rigid table model in favour of flexible schemas — documents (MongoDB), key-value (Redis), wide-column (Cassandra), or graphs (Neo4j).

	SQL	NoSQL
Schema	Fixed	Flexible
Model	Tables & relations	Documents, key-value, graphs
ACID	Full support	Varies, often relaxed
Scaling	Primarily vertical	Horizontal (easy sharding)
Best for	Complex queries, transactions	High-volume, rapidly changing data

The choice isn’t “which is better” — it’s “which fits the workload.” SQL for strong consistency and complex joins; NoSQL for scale and schema flexibility.

2. What is Database Sharding?

Sharding splits one big database into smaller pieces and spreads them across multiple servers. It’s a form of horizontal partitioning — rows are distributed, not columns.

Example: A users table with 10 million rows is split by user ID: users 1–5M go to server A, 5M–10M go to server B. Each server carries only its share of the load.

Why shard: when a single server can’t handle the reads/writes, sharding spreads the load and allows near-limitless growth.

The catch: queries that span shards get complicated. If your query needs data from two shards, the app has to merge results itself, and JOINs across shards are painful.

3. What is the CAP Theorem?

CAP says a distributed data system can guarantee at most two of these three simultaneously:

• Consistency — every node returns the same data at the same time.
• Availability — every request gets a response (success or failure), even if data is stale.
• Partition Tolerance — the system keeps working even if network links between nodes fail.

The nuance: you can’t choose not to have partition tolerance in a distributed system — network failures happen. So the real choice during a partition is between Consistency and Availability:

• CP system (e.g., MongoDB with majority reads): prefer consistency — may reject requests during a partition.
• AP system (e.g., Cassandra): prefer availability — keeps serving possibly-stale data.

The theorem doesn’t say “pick 2.” It says when the network splits, you must pick which of C and A to sacrifice.

4. What are OLTP and OLAP systems?

• OLTP (Online Transaction Processing) — handles high volumes of small, fast transactions: placing orders, booking tickets, updating balances. Row-level reads and writes, minimal delay.
• OLAP (Online Analytical Processing) — handles large, complex analytical queries over big datasets: monthly sales reports, trend analysis. Read-heavy, aggregates many rows.

	OLTP	OLAP
Workload	Many small transactions	Few large analytics queries
Operation type	Insert/update/delete	Aggregations, joins
Response time	Milliseconds	Seconds to minutes
Data size	Current data	Historical, massive

They’re optimised for opposite goals — that’s why many companies keep them in separate databases.

5. What is the difference between Synchronous and Asynchronous Database Replication?

The difference is when the primary node gets confirmation.

• Synchronous replication — the primary waits for the replica to write and confirm before telling the client the transaction succeeded. Data is identical on both nodes, always.
• Asynchronous replication — the primary confirms the transaction immediately and sends the update to the replica in the background. Faster, but the replica can briefly lag behind.

The trade-off: Synchronous gives you stronger consistency — if the primary dies, the replica has the data. But it costs latency, because every commit waits for the network round-trip. Asynchronous is fast and scales better, but risks losing the most recent writes if the primary fails before the replica catches up.

6. What is Database Partitioning? Explain its types.

Partitioning splits a large table into smaller, independently manageable pieces, based on a key. Queries that touch only one partition get faster because they scan far fewer rows.

The common types:

• RANGE partitioning — split by ranges of a value (e.g., orders by year: 2023, 2024, 2025).
• LIST partitioning — split by a discrete list of values (e.g., by region: North, South, East, West).
• HASH partitioning — split by a hash of the partition key, spreading rows evenly when no natural range exists.
• COMPOSITE partitioning — combine two strategies (e.g., range by year, then sub-partition by hash).

All partitions live in the same database instance — that’s what separates partitioning from sharding.

7. What is the difference between Sharding and Partitioning?

Both split data, but at different scales.

• Partitioning — splits a table within a single database instance. All partitions share the same server.
• Sharding — spreads data across multiple physical servers. Each server holds its own slice, and the pieces work together as one logical database.

Analogy: Partitioning is dividing a book into chapters — still one book. Sharding is splitting the book across multiple libraries — you may need to visit several to read it all.

Sharding is essentially partitioning taken across machines for scale, at the cost of cross-node queries and joins.

8. What are the four core categories of NoSQL database management systems?

NoSQL databases are grouped by their data model:

1. Document — JSON-like documents with flexible structure (MongoDB, CouchDB).
2. Key-Value — simple key → value pairs (Redis, DynamoDB).
3. Wide-Column (Column-family) — tables with dynamic columns, stored by column family (Cassandra, HBase).
4. Graph — nodes and edges for connected data (Neo4j, ArangoDB).

The common thread: they drop the rigid relational schema to gain flexibility and horizontal scalability.