Choosing between SQL and NoSQL databases

Start from the data's shape, the query patterns and the consistency needs.

Model answer. The right choice depends on the data model, access patterns, consistency requirements and scale — not on which is "newer".

Relational databases (PostgreSQL, MySQL)

• Data in tables with a fixed schema; relationships through foreign keys and joins.
• ACID transactions across multiple rows and tables.
• Powerful ad-hoc querying with SQL.
• Scale vertically well and horizontally with read replicas; scaling writes across many machines (sharding) needs more effort.
• Best for: payments, orders, inventory, student records — anything with strong integrity rules and relationships.

NoSQL categories

• Key-value (Redis, DynamoDB): get/put by key, extremely fast. Use for sessions, caches, feature flags.
• Document (MongoDB, Couchbase): JSON-like documents with flexible fields. Use for product catalogues where each category has different attributes, or user profiles.
• Wide-column (Cassandra, HBase): rows partitioned across many nodes, optimised for very high write throughput and queries by partition key. Use for time-series data, event logs, IoT readings.
• Graph (Neo4j): nodes and edges with fast traversal. Use for social connections, recommendations, fraud rings.

Decision checklist

• Need multi-entity transactions and complex joins → SQL.
• Schema changes often or varies per record → document store.
• Massive write volume with simple lookups by key → wide-column or key-value.
• Relationship-heavy queries ("friends of friends") → graph.
• Team familiarity and operational maturity matter too.

Nuances that impress interviewers

• The line is blurrier today: PostgreSQL supports JSONB documents, and MongoDB supports multi-document ACID transactions (since version 4.0).
• Many NoSQL systems trade some consistency for availability and partition tolerance, often offering eventual consistency or tunable consistency.
• Real systems are often polyglot: e.g. PostgreSQL for orders, Redis for caching, Elasticsearch for search.

Common mistake: saying "NoSQL because it scales". A well-indexed relational database handles very large workloads; choose NoSQL for a specific access pattern or scaling need, and design your keys around the queries you will run.