When would you choose SQL versus NoSQL?

The useful question is not which database is better, but which data model and guarantees the workload needs. Relational databases excel at joins, constraints and multi-row transactions. Document, key-value and wide-column stores optimize for specific access paths and horizontal distribution.

CREATE TABLE orders (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL REFERENCES users(id),
  total_cents INTEGER NOT NULL CHECK (total_cents >= 0),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

SELECT o.id, o.total_cents
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE u.tenant_id = 7
ORDER BY o.created_at DESC
LIMIT 20;

The trade-offs

• SQL: strong schema, joins, ACID transactions and mature tooling
• SQL: vertical scaling first, with read replicas and partitioning later
• NoSQL: flexible or denormalized documents, high write throughput and horizontal partitioning
• NoSQL: eventual consistency, limited joins and application-side integrity are common
• NoSQL: query patterns often drive the key design, so changing access patterns is expensive
• Many systems use both, choosing each store for a specific job

Postgres is a strong default for most product backends because it handles relational data, JSON documents, full-text search and transactional workloads. A specialized store makes sense when a concrete requirement, such as a massive time-series or a low-latency key-value cache, justifies the operational cost.