Core Databases

Whatever you are building, its data will almost always land in one of four places. Structured records go into a relational or NoSQL database, big files such as photos go into blob storage, and anything people type into a search box is served by a search engine. If you know what each store is built for and how they hand work to each other, you can sketch the storage layer of most systems with confidence.

Four stores at a glance#

Each store answers a different kind of question about your data. The table below is a map for the rest of the lesson.

A real design often uses several of these at once. A recipe app might keep users and recipes in PostgreSQL, recipe photos in S3, and a search index of recipe text in Elasticsearch.

Picking your main database#

You usually choose a single primary database for a design and then show how its features fit the problem. The kind of problem gives you a sensible starting point.

Argue from features, not labels#

Blanket "SQL vs NoSQL" arguments tend to fall apart, because the two families overlap more than people expect. The table shows two common weak claims and why they don't hold up.

A stronger answer names the exact capability you rely on. For example: "A transaction in PostgreSQL lets me take the payment and reserve the seat together, so a seat is never sold twice." That says what you need and why this database provides it.

Relational databases#

A relational database (RDBMS) organizes data into tables. A row is one record and a column is one attribute, so a customers table might hold id, name and email. You read and write it with SQL, which is declarative: you state the result you want and the query planner decides how to fetch it.

This model is the usual home for transactional business data such as customers, orders and payments.

The three features you will lean on#

Three capabilities come up again and again when you justify a relational choice.

Joins let you answer new questions without restructuring your data. Keep the heaviest ones off your busiest requests where you can.

Indexes are usually B-trees, sometimes hash indexes. A relational database lets you build many of them, including indexes over several columns and special kinds for geospatial and full-text queries. See Database Indexing for how they work.

Transactions protect you from half-finished work. In the example below, an order is never recorded without its stock being reduced. See ACID for the guarantees behind this.

BEGIN;
INSERT INTO orders (id, customer_id, product_id) VALUES (901, 7, 55);
UPDATE inventory SET stock = stock - 1 WHERE product_id = 55;
COMMIT;  -- the order and the stock change are saved together, or not at all

Which one? PostgreSQL and MySQL are both safe picks. With no other preference, go with PostgreSQL.

NoSQL databases#

"NoSQL" is an umbrella for databases that don't follow the classic table-and-SQL model. Many of them don't force a fixed schema, and most are designed from the start to run across many machines. They come in a few families.

Where NoSQL earns its place#

A NoSQL store is a good fit when your situation looks like one of these:

• The shape of the data keeps changing, or different records carry different fields.
• One machine isn't enough, because the dataset or the traffic has to be spread over many servers.
• Access is simple but massive: huge numbers of reads and writes by key, sometimes processed in real time.

None of these is out of reach for a relational database. PostgreSQL offers JSON columns for loose fields, and relational databases can be sharded. So, as before, make your case from what the specific store does.

Four things to understand before you choose#

Each of these points shapes how you'd actually use a NoSQL store in a design.

1. Model the data around your queries. In key-value and wide-column stores you start from the reads you need and design tables to serve them, rather than designing tables first.
2. Consistency is often a setting. Many stores let you choose per request between strong reads (always see the newest write) and eventual reads (replicas may lag briefly, but reads are faster and cheaper).
3. They have indexes too. DynamoDB, for instance, hashes the partition key to place items and keeps them ordered by a sort key. You can also add secondary indexes.
4. Scale comes from sharding. Data is split into partitions across nodes, often with consistent hashing, so adding a node only moves a small slice of the data.

Which one? DynamoDB is managed, popular and rich in features. Cassandra excels at write-heavy loads because its storage is append-only and log-structured, though it offers fewer ways to query. MongoDB is the common choice when you want a document model.

Blob storage#

Large files such as photos, videos and PDFs are a poor fit for a database: they bloat it, slow it down and cost a lot to store there. They belong in a blob store like Amazon S3 or Google Cloud Storage.

Using one is simple. You upload a file under a key, then fetch it later by that key or by a URL.

Metadata in the database, bytes in the blob store#

The usual pattern splits each file in two. Your database keeps a small row of metadata (owner, name, size, status) plus a pointer to the file, such as its object key. The blob store keeps the bytes themselves.

This keeps your database small and quick to query, while the blob store handles bulk storage cheaply. The reverse doesn't work: a blob store can't filter or query records, so never use it as your main database.

Moving files with presigned URLs#

Routing large uploads and downloads through your own servers wastes their bandwidth and memory. Instead, the client transfers the file directly with a presigned URL, which is a link that expires after a short time and allows one operation on one object.

Uploading a file takes four steps:

1. The client tells your server it wants to upload a file.
2. Your server inserts a row marked pending and replies with a presigned upload URL.
3. The client sends the file straight to the blob store with that URL.
4. The blob store emits an event (S3 event notifications, for example), and your server flips the row to uploaded.

Downloading is shorter:

1. The client asks your server for the file.
2. Your server checks that the user is allowed to see it and replies with a presigned download URL.
3. The client fetches the file through a CDN placed in front of the blob store, so the bytes come from a server close to the user.

What blob stores give you#

These properties are why you can usually treat a managed blob store as a solved problem in a design.

Versioning and lifecycle rules (such as moving old files to a cheaper storage tier) are also worth knowing. Many other providers expose an S3-compatible API, so S3 is a safe name to use when you describe blob storage.

Search engines#

Some features need full-text search: "find recipes that mention garlic and lemon", or "find listings near the beach". A plain SQL query handles this badly:

SELECT * FROM recipes WHERE body LIKE '%garlic%';

Because the pattern starts with a % wildcard, a B-tree index can't help. The database has to read every row, a full table scan, and the query gets slower as the table grows.

How an inverted index fixes it#

A search engine turns the lookup around. Rather than scanning each document for a word, it builds an inverted index ahead of time that lists, for each word, the documents that contain it:

{
  "garlic": ["recipe2", "recipe5", "recipe8"],
  "lemon":  ["recipe5", "recipe6"],
  "pasta":  ["recipe2", "recipe5"]
}

To answer "garlic lemon", the engine fetches both lists and keeps the documents they share, here recipe5. No scan is needed.

What makes results feel smart#

Search engines layer several text-processing techniques on top of the index.

Which one? Elasticsearch is the best-known choice: a distributed search and analytics engine built on Apache Lucene.

Or use what you already run#

You don't always need a separate search cluster. PostgreSQL has built-in full-text search backed by GIN indexes, and Redis offers a search module, though it is less mature than a dedicated engine.

Reusing your existing database keeps the design smaller and avoids syncing data into another system. A dedicated engine is worth it when search is central to the product and you need richer ranking and matching.

Key takeaways#

• Choose one primary database and defend it with concrete features, not "SQL vs NoSQL" slogans.
• Relational databases bring joins, flexible indexing and transactions, which makes them a strong default for product data.
• NoSQL stores make horizontal scale and loose schemas easy, but you design tables around your access patterns.
• Store big files in blob storage, keep metadata and a pointer in the database, and move bytes with presigned URLs and a CDN.
• For text search, use an inverted index (a search engine or your database's full-text features) instead of LIKE '%term%'.