SQL vs NoSQL: choosing the store

SQL (relational) databases such as PostgreSQL and MySQL store data in tables with a fixed schema, support joins and secondary indexes, and give ACID transactions across many rows and tables. You describe what you want with SQL and the query planner decides how to get it.

'NoSQL' is not one thing. It is a family of stores that each give up some relational features to win somewhere else: key-value stores (Redis, DynamoDB) for very fast lookups by key; wide-column stores (Cassandra, ScyllaDB, HBase) for huge write volumes partitioned by key; document stores (MongoDB, Couchbase) for nested JSON-like records; graph databases (Neo4j) for many-hop relationship queries.

The line has blurred. PostgreSQL has JSONB columns with GIN indexes, MongoDB has multi-document transactions, DynamoDB has transactions on up to 100 items, and 'NewSQL' systems (Google Spanner, CockroachDB, TiDB, YugabyteDB) give SQL and ACID transactions while sharding automatically. So the real question is not 'SQL or NoSQL' but 'which access patterns, consistency and scale does this data have, and which store fits them best'.

1. 1List the entities and, for each, the exact queries: 'get order by id', 'list a user's orders newest first', 'count orders per restaurant per hour'. Access patterns come before the store.
2. 2Check the relationships and transactions: if a single business action must update several entities atomically (debit wallet, create payment, update order), a relational database with ACID transactions is the natural fit.
3. 3Check the numbers: a single well-tuned PostgreSQL primary on a large machine handles roughly 10,000 to 50,000 simple writes per second and several TB of data; read replicas add read capacity. If your estimates fit, you do not need NoSQL for scale.
4. 4If writes are far beyond one machine (hundreds of thousands per second) and every read uses a known key, a partitioned wide-column or key-value store (Cassandra, DynamoDB) fits: you design one table per query, keyed by the partition key.
5. 5If records are nested and their shape varies per item (product attributes differ for phones and sarees), a document store or a JSONB column fits; prefer JSONB inside Postgres when the rest of the data is relational.
6. 6If the core query is 'friends of friends who like X' with many hops, a graph store fits; for one or two hops, SQL joins or an adjacency table is usually enough.
7. 7Consider operations: who runs it, backups, managed offerings (RDS, Aurora, Cloud SQL, DynamoDB, Atlas) and team familiarity. An unfamiliar store is a real cost.
8. 8Use more than one store when patterns differ (polyglot persistence): Postgres for orders and payments, Redis for sessions and counters, Elasticsearch for search, S3 for media, a column store for analytics. Keep one system as the source of truth for each piece of data and sync the others from it.

-- Same access pattern, two models

-- PostgreSQL: normalised, any query possible, joins at read time
CREATE TABLE orders (
  id         BIGINT PRIMARY KEY,
  user_id    BIGINT NOT NULL REFERENCES users(id),
  created_at TIMESTAMPTZ NOT NULL,
  total_paise BIGINT NOT NULL
);
CREATE INDEX ON orders (user_id, created_at DESC);

-- Cassandra (CQL): one table per query, partitioned by user
CREATE TABLE orders_by_user (
  user_id    bigint,
  created_at timestamp,
  order_id   bigint,
  total_paise bigint,
  restaurant_name text,          -- denormalised copy, no joins
  PRIMARY KEY ((user_id), created_at, order_id)
) WITH CLUSTERING ORDER BY (created_at DESC, order_id ASC);