Core Cloud Architecture · Part 6 of 13

Databases: Relational and NoSQL

Relational and NoSQL databases, and how to choose between them.

A database is where data goes once it needs to be queried, filtered, joined, and updated transactionally. Relational and NoSQL are the two main families, built on different assumptions about how data will be read. A few specialized categories cover access patterns neither fits 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 the data you want.

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 added work on every write, since the database keeps each index in sync with the table. A read replica is a copy of the database kept up to date from the primary, usually asynchronously so reads can lag slightly, 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.
• Graph: stores data as nodes and the relationships between them, built for traversing those relationships (Neo4j, Amazon Neptune, Spanner Graph). "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.

Specialized categories

• Distributed SQL: 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. These are relational in interface and distributed in design.
• Data warehouse: a SQL database that stores data by column, built for analytics and reporting queries that scan large datasets (Amazon Redshift, Google BigQuery, Azure Synapse Analytics). Application transactions go to a relational database, and a copy of that data is loaded into the warehouse for analysis.
• 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

Three questions separate the two families.

• How varied is the data's shape? A relational table fixes its columns, and every row has the same ones. Document databases let each record carry different fields, which suits data such as product types with different attributes. If every record has the same fields, a fixed schema costs little and validates the data for you.
• Does the workload need to scale out across servers? NoSQL systems were built to spread simple reads and writes over many servers by partitioning the data. Joins and transactions that span partitions are expensive in any database, so these systems limit them: Bigtable supports transactions only within a single row, and Cassandra has no joins. That limit is why the DynamoDB, Cassandra, and Bigtable documentation each tell you to design the data around the queries you will run. AWS describes the trade for DynamoDB: a relational database can be queried flexibly but at higher cost under heavy traffic, while DynamoDB queries efficiently in a limited number of ways.
• How much must one operation change at once? An operation that spans several related records and must succeed or fail as a unit is what relational databases are built for. Some NoSQL databases offer transactions as well (DynamoDB across up to 100 items, Cosmos DB, MongoDB), within limits that vary by product.