SQL vs NoSQL

SQL (relational): Schema-enforced tables with ACID transactions, complex joins, and strong consistency. The schema is a contract — any data that violates it is rejected at write time.

Choose SQL when:

  • Data has complex relationships (users, orders, products — multiple JOIN dimensions)
  • ACID transactions across multiple entities are required (financial systems, inventory)
  • Ad-hoc queries are needed (analytics, reporting)
  • Schema is relatively stable

NoSQL: Flexible schema (or schemaless), often horizontally scalable writes, optimized for specific access patterns.

Choose NoSQL when:

  • Access pattern is known and simple (lookup by key, no joins)
  • Horizontal write scaling is required beyond what one SQL node provides
  • Schema evolves rapidly (early product development, user-generated content)
  • Specific data model fits perfectly (documents, time-series, graphs, vectors)
graph TD q1{"Complex relationships<br/>across entities?"} -->|"yes"| sql["SQL"] q1 -->|"no"| q2{"ACID across<br/>multiple entities needed?"} q2 -->|"yes"| sql q2 -->|"no"| q3{"Access pattern is<br/>simple, known in advance?"} q3 -->|"yes"| nosql["NoSQL"] q3 -->|"no, ad-hoc queries needed"| sql

Chat app — what should you use? Messages are written once, read many times, in time order per conversation. Heavy write volume at scale. No complex joins needed (messages have metadata, but you read them by conversation_id + timestamp). Cassandra is ideal: partition by conversation_id, cluster by sent_at DESC. Fast writes, range reads by time, horizontal scale. Redis Pub/Sub or Streams for real-time delivery to connected clients.

Log system — what should you use? Logs are append-only, high write volume, queried by time range and filters. Elasticsearch: inverted index for full-text search on log content, excellent for grep-style queries over billions of log lines. Or ClickHouse (columnar OLAP) for aggregation queries (error rate over time). Not PostgreSQL — OLTP databases are not designed for write-heavy append-only workloads at log scale.

Common pitfall

Picking NoSQL specifically because "it's more scalable" without a concrete access pattern in mind is the most common way NoSQL adoption goes wrong — scalability is a property of matching the data model to the access pattern, not an inherent trait of the NoSQL category. A document store queried in ways that need joins and ad-hoc filters ends up re-implementing relational features (application-side joins, manual referential integrity) worse and slower than PostgreSQL would have done natively. The chat-app and log-system examples above work specifically because the access pattern was nailed down first — the technology choice followed from that, not the other way around.