Core Cloud Architecture · Part 6 of 13

Databases: Relational and NoSQL

The decision comes down to what your queries look like.

A database is where data goes once it needs to be queried, filtered, joined, and updated transactionally. Relational and NoSQL are the two families that cover most of that territory, and they organize data around different assumptions about how it will be read. A handful of more specialized categories exist because even NoSQL's sub-types don't fit every access pattern well.

Relational databases

A relational database organizes data into tables with a fixed schema (every row in a table has the same columns, of a declared type) and lets you combine data across tables at query time with a join. Queries are written in SQL, a declarative language for describing what data you want rather than how to fetch it.

Relational databases guarantee ACID transactions: a group of operations either all succeed or all roll back together (atomicity), the database moves between valid states only (consistency), concurrent transactions don't see each other's half-finished work (isolation), and a committed transaction survives a crash (durability). This matters where correctness can't be approximate: moving money between two accounts, decrementing inventory when an order is placed, anywhere "half of this happened" is a bug.

An index lets the database find matching rows without scanning the whole table, at the cost of extra storage and slightly slower writes; choosing what to index is one of the most consequential performance decisions in a relational schema. A read replica is a copy of the database kept in sync with the primary, used to serve read queries so the primary isn't the only thing handling load. Partitioning (or sharding) splits a large table across multiple underlying stores, which becomes necessary once a single database instance can't hold or serve the data volume. The cost is complexity: queries spanning partitions, and transactions across partitions in particular, get considerably harder.

NoSQL databases

NoSQL is an umbrella term covering several different data models that all relax the fixed-schema, join-everything-at-query-time relational approach in exchange for something else: one access pattern served far faster, or scale characteristics a single relational instance struggles with.

• Key-value: the simplest model, looking up a value by its key and nothing more structured than that. Fast, and limited in query flexibility.
• Document: stores semi-structured documents (typically JSON-like), each of which can have a different shape, queryable by fields inside the document. A natural fit when the data's shape varies record to record, or when an application's own objects map cleanly onto a document without being split across many joined tables.
• Wide-column: rows can have a huge, sparse, varying set of columns, optimized for high write throughput and horizontal scale across huge datasets.
• Distributed / globally distributed: some newer databases (Google Cloud Spanner, Amazon Aurora DSQL, and relational databases increasingly borrowing distributed techniques) offer horizontal scale and multi-region distribution while keeping SQL and strong transactional guarantees. They sit outside both buckets above.
• Graph: stores data as nodes and the relationships between them, built for traversing those relationships (Neo4j, Amazon Neptune, GCP's graph-oriented offerings). "Find everyone within three connections of this person who also works at a company this person's employer has partnered with" is the kind of query a graph database answers directly, and one a relational database answers only through a chain of joins that gets slower and harder to write as the relationship depth grows.
• Time-series: optimized for data points indexed by time (Amazon Timestream for InfluxDB, Bigtable used in a time-series access pattern, Azure Data Explorer), with storage, compression, and query features built around that shape: metrics, sensor readings, financial ticks, anything where "give me this value over the last 24 hours, downsampled to one point per minute" is the dominant query.
• In-memory, as a primary datastore: Caching covers Redis and Memcached sitting in front of a database, but the same technology also serves as an application's system of record for data that's naturally transient, or where in-memory speed matters more than disk-backed durability: session state, live leaderboards, rate-limiting counters. That's a separate role from caching, with different consequences when a node is lost.
• Vector: stores high-dimensional embeddings and finds the nearest ones to a query vector, the retrieval mechanism behind semantic search and retrieval-augmented generation. Vector Databases, in the Generative AI Architecture series, covers it in full.

SQL vs. NoSQL: start from the access patterns

The common shorthand, "NoSQL is for when you need to scale," is misleading. Relational databases scale to enormous size in production, and a badly modeled NoSQL database can fall over at modest scale just as easily as a badly modeled relational one. What holds up is a decision made on how the data will be queried.

• How varied is the data's shape? If every record has the same fields, a fixed schema costs nothing and gives you validation. If records vary widely (different product types with different attributes, for instance), a document model avoids a schema full of mostly-null columns or an awkward key-value side table.
• Do you need to query across relationships, arbitrarily, after the fact? Relational joins let you ask a question you didn't anticipate at design time, by combining tables you didn't originally design together. A document or key-value model performs best when the access pattern is largely known in advance and the data is shaped to match it directly.
• How strong does consistency need to be, and across how much data at once? If an operation spans multiple related records and must be all-or-nothing (the transaction example above), that's relational territory. If each operation naturally touches one record or one partition key at a time, a NoSQL model's simpler consistency guarantees are rarely a limitation.
• What's the write and read shape? Very high write throughput on data accessed almost entirely by a known key (event logs, sensor data, session state) plays to NoSQL's strengths. Reporting, ad hoc analysis, and anything where "we'll need to slice this a new way next quarter" is likely favors relational.

Most production systems past a certain size end up using more than one database, each serving the access pattern it fits best: a relational database as the system of record, alongside a key-value store for session data or a document store for one subsystem. That's a deliberate architecture as long as every choice traces back to an access pattern someone can name.