SQL vs NoSQL: Data Models and How to Choose the Right Database

"Should we use SQL or NoSQL?" is one of the most common questions in design interviews, and one of the most commonly mis-answered. "NoSQL, because it scales" is a slogan, not a reason. "SQL, because it's what I know" is honest, but still not a reason. The real question is: given these access patterns, this data shape, these consistency needs and this scale, which store makes the important operations simple, fast and correct?

This article gives you a working map of the database families, the trade-offs each one makes, and a process for choosing that you can say out loud in an interview.

• What "NoSQL" actually covers, and the main database families.
• What each family is good at, bad at, and typically used for.
• The common myths, and what is true behind them.
• Query-first modelling for key-value, document and wide-column stores.
• Polyglot persistence: using several stores, and its costs.
• A decision process, applied to worked examples.

"NoSQL" is an umbrella term for databases that do not use the relational table model as their primary interface. It covers very different systems with different data models, guarantees and query abilities. Treating them as one category is the first mistake. A more useful map:

The simplest model: put(key, value) and get(key). Because every operation names its key, the store can partition data by key and route each request to one node, which scales very well. Redis adds rich data structures in memory (article 09); DynamoDB adds sort keys, secondary indexes and durability as a managed service.

Use when access is almost always by a known key: sessions, user preferences, shopping carts, feature flags, idempotency records, rate-limit counters.

Each record is a document, often JSON, which can nest arrays and sub-objects. An order and its items can live in one document, read and written as a unit. MongoDB's data-modelling guidance discusses the central choice: embed related data in one document when it is read together, or reference it in separate documents when it is large, shared or grows without bound (MongoDB: Data Modeling).

"Flexible schema" means the database does not force every document to have the same fields. The schema still exists; it lives in your application code and must be versioned there ("schema on read").

Use for: content and catalogue data with varied attributes, user profiles with nested settings, data naturally shaped as self-contained documents.

Data lives in partitions identified by a partition key; within a partition, rows are sorted by a clustering key. Reads are efficient when they name the partition and a range of the clustering key ("the latest 50 messages of conversation X"). These systems descend from Google's Bigtable, described as a distributed storage system for structured data designed to scale to petabytes across thousands of machines (Bigtable), and from Amazon's Dynamo. Apache Cassandra's documentation explains how it adopts Dynamo-style partitioning with consistent hashing, replication and tunable consistency (Cassandra: Dynamo).

Use for: very high write volumes with predictable queries: messages, activity feeds, event logs, IoT readings, time-ordered data per entity.

When the important questions are about connections ("how is A connected to B within 3 hops?", "accounts sharing devices with this flagged account"), relational joins become deep and slow. Graph databases store relationships as first-class data, so traversals follow pointers instead of joining tables. Neo4j's documentation introduces this model of nodes, relationships and properties (What is a graph database?).

Use for: social graphs with multi-hop queries, fraud detection, recommendation paths, knowledge graphs. Not for simple "who do I follow" lookups, which a relational or key-value table handles perfectly well.

A search engine builds an inverted index: a map from each term to the documents containing it, with information for ranking. Elastic's documentation describes how documents are stored in indices and made searchable (Index basics). Search engines answer "find products matching 'red running shoes', ranked by relevance, filtered by size and price" far better than a relational LIKE query.

They are usually derived stores: the source of truth lives in a database, and an indexing pipeline copies changes into the search engine, so search results can lag by seconds. Articles 36 and 44 build on this.

Time-series stores optimise for appending measurements and querying windows of time, with automatic rollups and retention. Columnar analytical stores store each column separately and compress it, so "sum revenue by city for last year" reads only two columns from billions of rows. Both are typically fed from operational systems; they do not replace them.

This is the OLTP versus OLAP split: online transaction processing (many small reads and writes for users, which relational and NoSQL operational stores do) versus online analytical processing (large scans and aggregations for reports, which warehouses and columnar stores do).

An honest summary: a distributed database such as Google Spanner shows that strong consistency and horizontal scale can be combined, using carefully engineered clock infrastructure (Google documents how its TrueTime system supports external consistency (Spanner: TrueTime and external consistency)). The costs move into latency, complexity or price; they do not disappear.

Relational modelling starts from entities and normalises. Key-value, document and especially wide-column modelling start from queries: for each important query, design a structure that answers it with one lookup or one range read, even if that duplicates data.

Access pattern: "show the latest 50 messages of a conversation, then older ones as the user scrolls."

-- Cassandra Query Language (CQL), which looks like SQL but follows wide-column rulesCREATE TABLE messages_by_conversation (    conversation_id  uuid,    month_bucket     text,        -- for example '2026-10'; keeps partitions from growing forever    message_seq      bigint,      -- per-conversation sequence number    sender_id        bigint,    body             text,    sent_at          timestamp,    PRIMARY KEY ((conversation_id, month_bucket), message_seq)) WITH CLUSTERING ORDER BY (message_seq DESC);

• The partition key (conversation_id, month_bucket) puts a conversation's messages for a month together on one set of replicas.
• The clustering key message_seq DESC stores them newest first, so "latest 50" is one sequential read.
• The month bucket prevents one very long-lived conversation from becoming one enormous partition.
• A second query such as "all messages sent by user X" is not served by this table; if it were needed, you would write the same message to a second table keyed by sender. Duplication per query is normal here.

{  "_id": "ord_77",  "customer_id": 42,  "status": "shipped",  "items": [    {"product_id": 9, "name": "Running shoes", "qty": 1, "unit_price_paise": 349900},    {"product_id": 15, "name": "Socks", "qty": 2, "unit_price_paise": 19900}  ],  "shipping_address": {"city": "Chennai", "pin": "600001"},  "created_at": "2026-10-04T18:22:07Z"}

Items and the address are embedded because they are always read with the order and belong to it. The product catalogue is referenced by product_id because products are shared and change independently.

Large systems often use several stores, each for what it does best: a relational database as the source of truth, Redis for caching and counters, object storage for files, a search engine for search, a warehouse for analytics. This is called polyglot persistence.

The costs are real:

• Synchronisation. Derived stores must be updated when the source changes, usually through events or change data capture, and they lag. A product renamed in the database may show the old name in search for a few seconds (articles 21 and 27).
• Consistency across stores. There is no transaction spanning PostgreSQL and Elasticsearch.
• Operations. Each store needs monitoring, backups, upgrades, capacity planning, security and people who understand it.

Keep the number of stores small, and know which one is the source of truth for each piece of data.

Work through these questions in order. Each narrows the choice.

1. What is the data's shape? Structured with many relationships, self-contained documents, simple key-values, time-ordered events, a graph, or large blobs?
2. What are the hot access patterns? By key? By key and time range? Ad hoc filtering and joins? Full-text? Multi-hop traversals? Aggregations?
3. What consistency and transactions are required? Must several records change atomically? Must reads always see the latest write?
4. What is the scale? Data size, write rate, read rate (from your estimates). Can one well-provisioned relational primary handle it, with replicas and caching?
5. What can the team operate? A managed service the team understands beats an exotic store nobody can run at 3 a.m.

When in doubt, start with a relational database, model it well, and move specific access patterns to specialised stores when estimates or measurements show the need. That is a mature, defensible default, not a lack of imagination.

Notice that Pixly does not start with Cassandra "for scale". The estimates show one relational primary is enough today; the design names exactly what would trigger a change.

Estimates from articles 06 and 07: about 92,000 messages per second at peak, about 1.2 PB per two years before replication; access is almost always "messages of one conversation, newest first".

• Relational, sharded by conversation: workable, but you would be building and operating sharding, rebalancing and routing yourself.
• Wide-column (Cassandra-like) with partition key (conversation, time bucket): matches the access pattern exactly, scales writes by adding nodes, and replicates across nodes with tunable consistency. Users, contacts and group membership still live in a relational database, where relationships and constraints matter.

Choice: wide-column for messages, relational for accounts and groups, Redis for presence and connection routing (article 34). Each store matches one access pattern.

• Choosing NoSQL "because it scales" without estimates or access patterns.
• Choosing a document store for heavily relational data with many-to-many relationships and strong integrity needs.
• Using a search engine or a cache as the system of record.
• Modelling a wide-column store like a relational one and then needing joins.
• Adding a new database for every feature.
• Ignoring who will operate the store.

• Do not open with a product name. Open with the access pattern: "messages are written at 90,000 per second and read by conversation, newest first."
• Then name the family and one example: "a wide-column store partitioned by conversation and time bucket, such as Cassandra; or sharded PostgreSQL if the team knows it better."
• Say what you give up (ad hoc queries, joins) and why that is acceptable.
• Name the source of truth when several stores hold related data, and how they stay in sync.

CREATE TABLE trips_by_rider (    rider_id     bigint,    year_month   text,    requested_at timestamp,    trip_id      uuid,    driver_id    bigint,    fare_paise   bigint,    PRIMARY KEY ((rider_id, year_month), requested_at, trip_id)) WITH CLUSTERING ORDER BY (requested_at DESC, trip_id ASC);CREATE TABLE trips_by_driver (    driver_id    bigint,    year_month   text,    requested_at timestamp,    trip_id      uuid,    rider_id     bigint,    fare_paise   bigint,    PRIMARY KEY ((driver_id, year_month), requested_at, trip_id)) WITH CLUSTERING ORDER BY (requested_at DESC, trip_id ASC);

• Next: Why Distributed Systems Are Hard: Partial Failure, Timeouts and Clocks starts Module 6: what changes once data and work are spread over many machines.
• Later: consistency levels and quorums (article 19), keeping derived stores in sync (articles 21 and 27), and search systems (articles 36 and 44).