Types of Databases

These notes are essentially what came out of watching 7 Database Types Every Software Engineer Should Know(opens in new tab) by Hayk Simonyan, with some of my own additions mixed in along the way. The video itself is worth watching directly for the full and comprehensive version.

#Link to this headingStarting small

A social app starts with one relational database, one schema, one machine, storing user profiles and posts. That works fine for a while. Every write is a row in a table, and every read is a fast indexed lookup. The relationship between a user and their posts is just a foreign key.

#Link to this headingWhere it breaks

As usage grows, the pressure shows up in a different shape, depending on what's actually being stored. A write-heavy event stream outgrows a single writer long before its rows outgrow disk. A permissions system built around who-can-see-what starts drowning in self-joins once the hierarchy gets deep enough. A feed that needs instant free-text search can't get there with a plain SQL LIKE. A recommendation engine built on embeddings has no join to reach for at all.

Each of those pressures gets answered by relaxing a different part of the relational model, not the same trade-off repeated eight times. That's the real reason there's a family of databases instead of just one, each keeping a different piece of what Relational / SQL promises and giving up a different piece in return.

#Link to this headingSQL vs NoSQL

SQL databases require a schema defined upfront, tables with fixed columns and types, and relationships between tables enforced through foreign keys. A relational database's real strength is the join, combining rows from multiple tables in a single query, which only works because the tables are related through a well-defined schema in the first place. Strong ACID transaction support comes standard here, not as an add-on.

NoSQL is really an umbrella term for anything that isn't the relational model, covering Key-Value, Document, Wide-Column, Graph, Search Engine, and Vector databases. What ties them together isn't a shared query language the way SQL is shared across relational databases, but a shared willingness to relax strict schema enforcement or strong consistency in exchange for horizontal scale or query flexibility.

#Link to this headingRelational / SQL Databases

Data lives in tables with a fixed schema of rows, columns, and typed fields. Relationships are enforced by the database itself, through primary and foreign keys. Postgres, MySQL, Oracle, and Microsoft SQL Server are the names that come up most.

What a relational database gives in return for that upfront structure is correctness, joins, ACID guarantees, and high query flexibility, all without the application needing to plan its access patterns in advance. What it takes back is horizontal write scale, since writes still route through a single primary.

That schema is what a real read actually walks across. Here's what it looks like with actual rows in each table, and the query that pulls them together.

That's the shape in the abstract. A real checkout flow fleshes it out further, since an order needs a payment and its line items need to point back to actual products.

In case the arrow symbols are confusing, visit softwareideas.net(opens in new tab) for what each one means.

#Link to this headingDistributed SQL

Distributed SQL keeps everything Relational / SQL promises, such as a full SQL interface, joins, and ACID transactions. What it adds is an answer to the one question a single primary can't cover, what happens once that primary is the ceiling.

CockroachDB is the name most associated with this category, replicating data across nodes using a consensus protocol so a majority of replicas agree on every write, instead of routing all writes through one machine.

Those three replicas hold identical copies of the same slice of data, called a range, not three different pieces of the table. A big table is split into many ranges, and each range gets its own set of three replicas spread across different nodes.

That quorum step costs something real. Every write has to wait for at least two of the three replicas to confirm it before it's considered done, which is slower than a single machine just writing to itself. In return, the database gets full SQL and ACID guarantees at a scale a single primary could never handle, without giving up the consistency a NoSQL system would trade away.

#Link to this headingKey-Value Stores

One key in, one value out, all from a single hash lookup. Without a query language to fall back on, an unknown key leaves the store unable to help. Redis, DynamoDB, and Memcached are the usual names here.

SET and GET map directly onto that flow. A SET writes whatever fields the caller hands it under a key, and that key is the only thing needed to find the value again. A GET on that same key hands the exact same fields back, untouched, whether it's read a second later or a year later.

Setting a TTL is the one thing that can make a key disappear on its own. Once it expires, the value is gone even though nothing ever issued a delete.

Underneath, a hash function maps each key straight to a bucket, which is what makes the lookup O(1) instead of a scan. Two keys landing on the same bucket is a collision, and colliding entries chain together instead of overwriting each other.

user:42 and user:88 collide into bucket 2 here, so that bucket chains both entries instead of losing one, but only that short chain, not the whole table, ever gets scanned to find a match.

The payoff is sub-millisecond lookups and close to unlimited write scale, since a hash-partitioned key space spreads evenly across nodes with none of a relational database's single-primary bottleneck. What's given up is everything a join provides, meaning no relationships, no secondary query patterns, and only what the key already tells you.

#Link to this headingDocument Databases

Data is stored as JSON-style documents. Each one can hold a whole object, with nested arrays and sub-objects included, without needing a join to a separate table. MongoDB, Firestore, and DocumentDB are the names most associated with this shape.

DynamoDB genuinely belongs here too, even though it was already mentioned as a Key-Value store. AWS itself calls it a "key-value and document database," since it stores structured per-item attributes closer to a document's shape than a flat blob.

The same lookup makes the difference concrete. A product page needs a product's details along with who sells it.

Both return the exact same fields, but the document already had the seller embedded. The relational schema keeps sellers as their own table and reaches it through a foreign key, which is exactly the trade-off from the SQL vs NoSQL section made concrete.

A common myth is that NoSQL drops ACID entirely. Since 2018, MongoDB 4.0 and DynamoDB have both shipped multi-document transactions, but they aren't the same guarantee a relational database gives by default. They're closer to an escape hatch, capped in scope and cost, and reaching for them constantly is usually a sign the data was modeled wrong in the first place.

Document databases give flexible, nested schemas that map naturally onto how an application already represents its data. What's traded away is the kind of ad hoc, cross-entity querying a join makes trivial elsewhere.

#Link to this headingWide-Column Stores

Rows are keyed by a partition key, with columns grouped underneath and spread across a cluster. Writes append to a log-structured merge tree instead of updating a row in place. Cassandra, ScyllaDB, HBase, and Bigtable are the names worth knowing.

That flexibility comes from never declaring a schema upfront. Each write just names whatever columns it needs, and the storage engine has no table-wide column list to check it against.

"Columns vary per row" is the part worth seeing directly, since two rows in the same table, or even two tables in the same database, can hold entirely different columns.

This is what enormous write throughput actually looks like, and Discord stores billions of messages this way. Adding nodes adds capacity linearly, and losing one doesn't stop writes, since there's no single leader to lose.

The cost is that every table has to be designed around a known query pattern in advance. A new access pattern usually means a new table holding the same data over again, and joins simply aren't available.

#Link to this headingGraph Databases

Nodes and edges, where the relationship itself is a stored object with its own properties, not something computed at query time the way a join is. Neo4j is the name that dominates here, with Neptune and Dgraph as smaller alternatives.

Graph databases are built for multi-hop traversal questions, such as fraud rings, recommendation paths, permission hierarchies, and network topology. These are queries where the relationships are the point, not just how records happen to connect.

Getting there in SQL means one join per hop, and that cost keeps compounding as the traversal goes deeper, since the friendships table has to join to itself twice more before it can even reach an employer.

Horizontal scaling is genuinely hard here, and the talent pool and tooling ecosystem are both smaller. Most systems don't actually need this, since a one or two hop question is usually served just as well by a relational CTE.

#Link to this headingSearch Engines

Full-text search over a derived copy of the data, built for relevance ranking rather than exact lookups. Elasticsearch, OpenSearch, and Algolia are the names to know.

What actually sits inside that index is an inverted index, a term mapped to every document that contains it. A query for "running shoes" ranks doc 4 and doc 92 highest, since those are the only two documents both terms point to.

These are a second copy of the data, fed from something like Postgres or DynamoDB rather than serving as the primary database. Second copies drift, so owning the sync pipeline is part of the deal. What's gained in return is relevance-ranked results, faceted filtering, and search at a volume a relational LIKE query was never built to handle.

#Link to this headingVector Databases

Search by meaning rather than exact match, the category AI made mainstream. Pinecone, pgvector, Weaviate, Qdrant, and Milvus are the names in circulation.

What actually sits in that index is a document id next to a long list of floats, the embedding. Every document gets the same shape, just different numbers.

Vector databases power semantic search, retrieval-augmented generation, and recommendation or dedupe pipelines built on embeddings instead of keywords.

The results are approximate, not exact, since algorithms like HNSW trade recall for speed and can silently miss a match. Changing the embedding model also means a full re-index, with every vector recomputed. If latency isn't forcing a dedicated system, pgvector riding alongside an existing Postgres instance is usually enough.

#Link to this headingHow to choose