Database Types

Choosing a database is one of the first big system design decisions. It is also one of the hardest to undo later.

In this chapter, we will look at the main types of databases, how each one stores and queries data, and how to decide which one fits a particular part of a system.

Later chapters in this module go deeper into each of these database types.

Suppose we are building an e-commerce application.

It needs to store users, orders, and payments. It needs to keep shopping carts and login sessions. It needs a search bar where users can type "running shoes" and get relevant products. And it may need to track metrics like requests per second.

We could try to store all of this in a single database. But these workloads behave very differently:

A database that is designed to be good at one of these jobs is usually not the best choice for all of them. That is why many different types of databases exist.

The diagram below groups the main categories covered in this chapter and lists common systems in each one.

These categories overlap in practice. PostgreSQL can store JSON, run full-text search, and support vector indexes through extensions. DynamoDB can behave like a document store. Use the categories to reason about trade-offs, not as strict boxes.

The most widely used type is the relational database. Examples include PostgreSQL, MySQL, Oracle, and SQL Server.

A relational database stores data in tables made of rows and columns. Each table has a fixed schema that defines its columns and their types.

For example, a users table might have columns for id, name, and email. An orders table might have columns for id, user_id, amount, and status.

The user_id column in the orders table points back to a row in the users table. This is called a foreign key, and it lets us model relationships between tables.

We query relational databases using SQL. With SQL, we can filter rows, join tables together, group results, and compute aggregates.

Why Relational Databases Are the Default Choice

Relational databases have a few strengths that make them a good starting point for most applications.

1. Transactions. Most relational databases support ACID transactions. Suppose a user places an order. We need to create the order, reduce the product's inventory, and record the payment. A transaction guarantees that either all three changes happen or none of them do.

2. Flexible querying. Because the data is normalized into separate tables, we can combine it in many different ways using joins. We do not need to know every future query in advance.

3. Data integrity. The schema, constraints, and foreign keys prevent a lot of invalid data from ever being written.

So when the data is structured and correctness matters, a relational database is usually the right place to start.

Where Relational Databases Struggle

Relational databases do have limitations.

A traditional relational database is designed to run on a single primary server. We can add read replicas to scale reads. But scaling writes beyond one machine usually requires sharding, which adds a lot of complexity.

The fixed schema can also slow things down when the data shape changes often. Large migrations on big tables need careful planning.

And some access patterns simply do not map well to tables. Deeply connected data, full-text search, and high-volume timestamped data can all be stored in a relational database. But other database types are built specifically for those workloads.

This is where NoSQL databases come in. NoSQL is a broad term for databases that do not use the traditional relational table model. There are several kinds, and they are quite different from each other.

The simplest type is the key-value store. Examples include Redis, Memcached, and Amazon DynamoDB.

A key-value store works like a giant hash map. Every piece of data is stored under a unique key. To read the data, you provide the key and get back the value.

For example, a session store might use the key session:abc123 and store the logged-in user's details as the value.

Because lookups happen by key, reads and writes are very fast. In-memory stores like Redis can often serve requests in under a millisecond.

Key-value stores are also easy to scale horizontally. Each key is independent, so it can be placed on any node by hashing it.

The trade-off is limited querying. The database generally does not understand what is inside the value. So a question like "find all sessions belonging to users in India" is hard to answer efficiently.

Use when: you need caching, session storage, rate limiting counters, or feature flags. In all of these, the application already knows the key it wants.

A key-value store is often used next to a primary database, not instead of one. Redis may cache user profiles while PostgreSQL remains the durable source of truth.

Document databases take the key-value idea further. Examples include MongoDB and Couchbase.

Instead of an opaque value, each record is a document, usually stored in a JSON-like format. A document can contain nested objects and arrays.

For example, a product document might contain the name, price, a list of sizes, and a nested object with shipping details. So loading a product page might require a single read, instead of joining several tables.

Unlike a key-value store, the database understands the structure of the document. So we can query and index fields inside it, such as all products where the category is shoes and the price is below $50.

Document databases also have a flexible schema. Two documents in the same collection can have different fields. In the example above, only the socks have colors and only the shoes have sizes. That makes it easier to evolve the data model as the product changes.

The trade-off shows up with relationships. If data is heavily connected across many documents, we either duplicate data or perform joins in the application.

Use when: you are building product catalogs, content management systems, or user profiles, where most reads fetch one whole object and the shape evolves over time.

Next are wide-column databases. Examples include Apache Cassandra, HBase, and Google Bigtable.

In these databases, data is still organized into rows and columns. But rows are grouped by a partition key, and each partition lives on a specific set of nodes in the cluster. Within a partition, rows are sorted by a clustering key.

Suppose we store chat messages in Cassandra. We could use the conversation id as the partition key and the message timestamp as the clustering key.

Now all messages for a conversation live together, already sorted by time. Fetching the latest 50 messages becomes a fast, sequential read.

Wide-column databases are built for very high write throughput across many machines. Cassandra, for example, uses a log-structured storage engine that turns writes into fast sequential disk operations.

The trade-off is that you design tables around your queries. If you need to look up messages by sender instead of by conversation, you usually create another table for that query.

Use when: you are building messaging, activity feeds, IoT data, or other write-heavy workloads at large scale. Write down the exact queries before choosing one.

Some data is mostly about relationships. Think of a social network. Alice follows Bob. Bob follows Carol. Carol works at the same company as Alice.

A graph database stores this data as nodes and edges. Nodes represent entities like people or companies. Edges represent relationships like FOLLOWS or WORKS_AT. Examples of graph databases include Neo4j and Amazon Neptune.

Now suppose we want to find friends of friends who work at the same company.

In a relational database, that means joining a large table to itself several times. Each extra level of the relationship makes the query more expensive.

A graph database is built to follow edges directly from one node to the next. So multi-hop queries stay efficient even when the relationships are deep. In Cypher, the query reads almost like the question:

Use when: you are building social networks, recommendation engines, fraud detection, or knowledge graphs, where relationships are the main thing you query.

Another category is the time-series database. Examples include InfluxDB, TimescaleDB, and Prometheus.

Time-series data is a sequence of measurements, each tagged with a timestamp. For example, a server might report its CPU usage every 10 seconds. With thousands of servers, that becomes millions of data points per hour.

This data has a clear pattern:

Time-series databases are optimized for this pattern. They store data in time order, compress consecutive values very efficiently, and make time-window aggregations fast.

Many also support retention policies. For example, you might keep raw data for 7 days, then keep only hourly averages for a year.

Use when: you need monitoring, metrics, sensor data, or financial tick data, and queries are mostly time-range filters and summaries.

One thing to watch: tagging points with too many unique labels (for example, a user id on every metric) can blow up the index and slow the database down.

Now let's go back to the search bar in our e-commerce application. A user types "red running shoes".

A relational database could use a query like name LIKE '%shoes%'. But that cannot easily rank results by relevance, handle typos, or match "run" with "running".

Search engines like Elasticsearch and OpenSearch are built for this problem. They use a data structure called an inverted index. An inverted index maps each word to the list of documents that contain it.

So the word "shoes" points directly to every product that mentions shoes. The engine can then combine the matches for each word and score them by relevance. In the example above, Product 1 matches all three words, so it ranks first. Because words are reduced to a common root before indexing, "running" in the query matches "run" in the index.

Search engines also support fuzzy matching, autocomplete, and filters like category or price.

In most systems, the search engine is not the source of truth. The primary data lives in another database, and changes are copied into the search index. That means the index can be slightly behind the primary database.

Use when: users need ranked text search, typo tolerance, autocomplete, faceted filters, or log exploration.

The newest category is the vector database. Examples include Pinecone, Milvus, Weaviate, and the pgvector extension for PostgreSQL.

A machine learning model can convert text, images, or audio into a list of numbers called an embedding. Items with similar meaning end up with embeddings that are close to each other.

For example, the embeddings for "cheap flights" and "budget airfare" would be close together, even though the words are different.

A vector database stores these embeddings and answers one main question: given a new vector, which stored vectors are most similar to it?

Comparing against every vector would be too slow for millions of items. So vector databases use approximate nearest neighbor (ANN) indexes, such as HNSW, to find close matches quickly. The trade-off is that results are approximate, not exact.

Use when: you are building semantic search, recommendations, or retrieval-augmented generation (RAG) in AI applications, where similar meaning matters more than exact matching.

Like search indexes, vector databases usually do not replace the source data. They store searchable representations of it.

So how do you choose the right database? A good way is to ask a few questions about the data and how it will be used.

Decision Guide

The flowchart below turns these questions into a rough path, starting from the most common default.

Quick Reference

In practice, many large systems use more than one database. This is called polyglot persistence.

Let's return to our e-commerce application:

Each database handles the part of the workload it is best at.

But this comes at a cost. Every additional database is another system to deploy, monitor, back up, and upgrade. And when the same data is copied into several databases, you need a reliable way to keep those copies in sync.

Because of that cost, it usually makes sense to start simple.

For most new applications, a single relational database can handle much more than people expect. PostgreSQL, for example, supports JSON columns, full-text search, and vector search through extensions. So one well-tuned database can cover many early needs.

You add a specialized database when a specific workload clearly outgrows it. Maybe search relevance becomes a core feature, or the metrics data starts slowing down the main database. At that point, the specialized database solves a real problem instead of adding complexity in advance.

The right choice depends on how your data is queried, how much consistency you need, and how the workload needs to scale.