RDBMS vs NoSQL Trade-offs
Relational databases (PostgreSQL, MySQL) use structured schemas, SQL, and enforce ACID transactions. NoSQL databases sacrifice some of those guarantees for horizontal scalability, flexible schemas, or specialized data models.
RDBMS = a filing cabinet with labeled folders and strict rules about where things go. NoSQL = a giant warehouse of bins -- throw anything in fast, but good luck finding it without a label.
Choose RDBMS when: data is relational (foreign keys, joins), you need ACID transactions across multiple entities, or schema is stable. Choose NoSQL when: you need horizontal write scalability beyond a single node (Cassandra, DynamoDB), your access patterns are simple key-value or document lookups (no complex joins), or your schema evolves rapidly. NoSQL sub-types: document (MongoDB), key-value (DynamoDB, Redis), wide-column (Cassandra, HBase), graph (Neo4j), time-series (InfluxDB).
The NoSQL vs RDBMS framing is often false. Modern PostgreSQL handles JSON documents, array types, and full-text search. CockroachDB and Spanner provide distributed SQL with ACID transactions. The real question is: what is your primary access pattern? If you always query by primary key (user profile by user_id), a key-value or document store is simpler and faster. If you need ad-hoc querying, aggregations, and complex joins, RDBMS wins. For write-heavy time-series data, LSM-tree-based stores (Cassandra, RocksDB) vastly outperform B-tree stores due to sequential I/O. Denormalization is often required in NoSQL to support access patterns — you model data around queries, not around entities.
My default is PostgreSQL — it handles most use cases and ACID transactions prevent entire classes of bugs. I'd move to NoSQL when I need: (1) write throughput beyond what a single Postgres node can handle after vertical scaling and sharding are exhausted, (2) a flexible schema for rapidly evolving data, or (3) a specialized access pattern like wide-column time-series data. I'd be explicit about which ACID properties I'm giving up and design compensating mechanisms.
Assuming NoSQL is automatically more scalable. PostgreSQL with proper indexing, connection pooling, and read replicas can handle hundreds of thousands of QPS. NoSQL adds operational complexity and gives up ACID — only make that trade when you have a concrete need.