Why the shape matters
Ask three questions about the students:- Show MINH’s profile page.
- Find every student in branch CS.
- Find friends of MINH’s friends.
Before relational: files, trees and networks
The earliest “databases” were plain flat files: one record per line, with fields split by a character such as a colon. Unix still keeps its user list in/etc/passwd this way, and a CSV file is the same idea. Flat files need no special software, and you can read them by eye. But they have no structure to search with, so every question reads the whole file.
In the 1960s, hierarchical databases such as IBM’s IMS arranged records in a tree. Each record has exactly one parent: the school has branches, and each branch has its students. Walking down the tree is fast. But a question the tree was not built for means visiting everything. Your computer’s folders, DNS names and LDAP directories still follow this shape.
Network databases (the CODASYL standard, late 1960s, products such as IDMS) let a record have more than one owner. MINH could belong to both the CS set and the HANOI set, so both questions had a fast path. But the program still had to walk those paths step by step, and someone had to build each path in advance.
All three older models share one problem. The program decides how to reach the data. If the paths change, every program that walks them has to change too.
Relational: say what, not how
Edgar F. Codd proposed the relational model in 1970. Data lives in tables, and tables link through shared keys such asBRANCH_CODE. In SQL you describe the rows you want, and the database picks the path. The SQL at the bottom of Figure 1 never says how to walk anything.
- Good at: checking rules on every write, and joining any tables, even in ways nobody planned.
- Cost: data must fit the schema. Changing the shape later needs a migration.
- Examples: PostgreSQL, MySQL, SQLite, Oracle, SQL Server.
NoSQL: one shape per job
In the mid-2000s, big web companies needed to spread data across many machines and change its shape often. Joins across machines are slow, and fixed schemas slowed teams down. New databases each dropped some relational features to make one job fast. Together they are called NoSQL (“not only SQL”), a very mixed family.Key-value and document stores
A key-value store is a giant dictionary:SET a value under a key, GET it back. The store never looks inside the value, so key lookups are very fast, but you cannot ask “which values contain CS?”. Used for sessions, caches and config. Examples: Redis, Memcached, DynamoDB.
A document store also keeps one record per key, but the record is JSON the database can read, index and query. Documents nest, so MINH’s branch can live inside MINH’s record. Adding a field needs no migration. Examples: MongoDB, Couchbase, Firestore.
Nesting is the big trade-off. It makes reads fast and writes harder:
The document store wins the profile page, and pays when shared data changes. Keeping the copies in step becomes the application’s job, not the database’s. A useful rule: embed data that belongs to one record and changes with it, such as a student’s phone numbers. Reference data that many records share, such as a branch.
Graph databases
A graph database stores nodes (things, such as students), edges (named links between them, such asFRIEND) and properties on both. The links are stored as data, so following one is a direct step from one node to the next. Queries describe a pattern of nodes and links, and the database finds every match.
To be fair to SQL, a recursive query (WITH RECURSIVE) handles any depth, and for two or three hops on indexed tables a relational database is often fast enough. Graph databases are worth it when the questions are mostly about paths: social networks, fraud rings, recommendations, network maps. Examples: Neo4j, Amazon Neptune, JanusGraph, Dgraph.
Wide-column and column-oriented: not the same thing
These two names sound alike and mean different things. A column-oriented (columnar) database stores each column together on disk. All the ages sit in one place, all the names in another. A query that adds up one column reads only that column. Rebuilding one whole row means visiting every column. This is the engine behind analytics warehouses. Examples: ClickHouse, Amazon Redshift, Google BigQuery, Snowflake, DuckDB. A wide-column (or column-family) database, such as Apache Cassandra, HBase or ScyllaDB, is a different idea. Despite the name, it keeps each row together, under a row key. Each row can have its own set of columns, and a row can have thousands of them. Rows are spread across machines by their key, so these systems scale to huge write loads: You design the tables around the questions you will ask, because joins are not supported and queries should name a row key. In Cassandra, a sum over all rows means scanning the whole cluster. That is the opposite of what a columnar warehouse is built for.Time series databases
A time series is a list of timestamped values: CPU load every ten seconds, temperatures from a sensor, logins per minute. This data has a clear pattern. New points arrive all the time, always at the end. Old points are almost never changed. Recent data is read often and old data rarely. Time series databases are built for exactly this: They compress long runs of similar numbers, aggregate by time window, downsample old data and drop it after a retention period. Examples: Prometheus, InfluxDB, TimescaleDB (a PostgreSQL extension), OpenTSDB.NewSQL and the CAP theorem
NoSQL systems scaled out by giving things up: joins, schemas, and often strong guarantees about what a read returns. NewSQL, now usually called distributed SQL, tries to get the scale back without giving those up. These databases keep tables, SQL and transactions, and spread the data across many machines. They split the data into pieces (shards), keep copies of each piece, and use a consensus protocol so the copies agree on each write. Examples: Google Spanner, CockroachDB, YugabyteDB, TiDB, and in-memory engines such as VoltDB and SingleStore (formerly MemSQL). Copies on many machines raise a hard question. What happens when the network between them breaks? The CAP theorem answers it. When a network partition splits the machines, a system cannot be both fully consistent (every read sees the latest write) and fully available (every request gets a non-error answer). It must pick one until the network heals. CAP is often misquoted as “pick two of three”. The choice only applies during a partition. When the network is healthy, a well-built system gives you both. Distributed SQL databases choose consistency (CP): the cut-off side refuses requests rather than answer wrongly. Many NoSQL stores, such as Cassandra and DynamoDB, lean towards availability (AP) by default, though both let you ask for stronger consistency per request. Even with no partition, there is a second trade-off. Waiting for copies to agree adds delay to every write. The PACELC model gives that trade-off a name. The CAP Theorem covers both in depth.Multi-model, and using more than one
A multi-model database supports several of these shapes in one system: for example documents, graphs and key-value data, queried with one language. You run one system instead of three. The catch is that each model may be less complete than in a database built only for it, so check the parts you need. Examples: ArangoDB, OrientDB, Couchbase. The lines are blurring anyway. PostgreSQL stores and indexes JSON documents, and TimescaleDB adds time series features to it.Start with one, add with a reason
Say you are building a small shop. You need users, orders, login sessions and a dashboard of response times. That looks like four jobs and maybe four databases. Usually it is one. PostgreSQL stores the tables, keeps sessions in a plain table, and holds varied product fields in ajsonb column. A recursive query walks a short chain of links, and the TimescaleDB extension handles time series. One relational database covers most young products.
Add a second database only when you can name the job the first one does badly, and you have measured it. Good reasons look like this:
- Session lookups run thousands of times per second and add load to the main database. Add a key-value store such as Redis.
- Reports over years of orders slow down checkout. Copy the orders to a column store such as ClickHouse.
- Writes arrive faster than one machine can take them. Add a wide-column store, or a distributed SQL database if you still need transactions.
A typical web app might keep users and orders in PostgreSQL, sessions in Redis, and metrics in Prometheus.
Other types you will meet
- RDF triple stores (semantic graphs) store facts as subject–predicate–object triples, such as
MINH – studiesIn – CS, using the W3C’s RDF standard and the SPARQL query language. They suit linked open data and shared vocabularies. Property graphs like Neo4j are usually easier for application data, because their nodes and edges can carry properties. - Object-oriented databases store objects exactly as a program defines them, classes and all. They never went mainstream: there was no common standard, and the data was tied to one application’s code. Their main idea lives on in ORMs, which map objects onto relational tables.
Check yourself
Glossary
For AI agents
Adapted from Prisma’s Data Guide, “Comparing database types”, with corrections. This page explains CAP as a choice made only during a partition. It notes that MemSQL is now SingleStore and that only some NewSQL engines are in-memory. It drops FaunaDB, whose service shut down in 2025, and Calvin, a research protocol rather than a product. It adds the recursive-SQL caveat for graph queries. Product promotion and the unsourced claim that relational databases are the majority have been left out. Figures are simplified teaching models; the time series numbers are illustrative.