How a Database Really Works Underneath

One question keeps coming up when people start going deeper into databases:

How does a database actually work underneath?

Not the SQL part. Not the API. Not the dashboard.

The real part:

• Where is the data actually stored?
• Is it kept in memory, on disk, or both?
• How are rows and columns laid out?
• Why does a query return quickly instead of scanning everything forever?

This article is a general answer to that question.

Different databases make different tradeoffs, but most serious databases are built from the same small set of ideas.

The short answer

A database is usually not one magical structure.

It is several layers working together:

• A Storage Format For Data On Disk
• An In-Memory Layer For Hot Data
• One Or More Index Structures
• A Query Engine
• A Transaction / Recovery Layer

When you run a query, the database does not start from scratch.

It uses those layers together:

1. find the right pages or files
2. use indexes if they help
3. load needed data into memory
4. execute the query
5. return only the rows, columns, or records you asked for

That is the basic picture.

Where the data is really stored

Most databases store durable data on disk.

That can mean:

• regular files on SSD or HDD
• memory-mapped files
• append-only log files
• page files
• SSTables in LSM-based systems
• custom binary file formats

The exact layout changes from one database to another, but the key point is simple:

Memory is fast, but temporary. Disk is slower, but durable.

So the normal design is:

• disk is the long-term source of truth
• memory is the fast working area

That is why databases usually use both.

What lives in memory

A database does not usually load the whole dataset into RAM.

Instead, it keeps the parts it needs most often in memory.

This memory layer is often called a:

• buffer pool
• page cache
• block cache
• memory table

depending on the database design.

The job is the same:

• keep hot data close
• avoid repeated disk reads
• batch writes when possible
• make queries much faster than raw disk access

So when people ask, "Is the data stored in memory or in files?" the honest answer is usually:

Both.

The durable copy is on disk. The actively used working set is often in memory.

How the data is laid out on disk

This depends on the database model.

Row-oriented storage

Traditional relational databases usually think in rows.

If you have a table like:

users(id, name, email, created_at)

a row-oriented system tends to store one full row together:

[id, name, email, created_at]

then the next row:

[id, name, email, created_at]

and so on.

This is good for:

• OLTP workloads
• reading one record at a time
• inserts and updates on complete records
• transactional business data

That is why row storage is common in systems like PostgreSQL and MySQL.

Column-oriented storage

Analytical databases often store data by column instead:

• all id values together
• all name values together
• all email values together
• all created_at values together

That is useful when queries read a few columns across many rows, such as:

SELECT created_at, count(*) ...

instead of loading every field of every row.

This is good for:

• analytics
• scans
• aggregations
• compression

because values in the same column are often similar and compress well.

That is why columnar engines show up in analytics, time-series, and warehouse-style systems.

Document storage

Document databases usually store self-contained records, often in binary JSON-like formats.

One record may contain:

{
  "id": 1,
  "name": "Farhan",
  "tags": ["db", "rust"],
  "profile": {
    "city": "KL"
  }
}

Instead of fixed columns, the document keeps structure inside the record itself.

This is useful when:

• fields vary across records
• nested data matters
• schema flexibility matters

Key-value storage

A key-value database stores data as:

key -> value

This is the simplest mental model.

It is often very fast for direct lookup, but weaker when you want rich relational or analytical behavior unless more layers are added.

What a page is

Most databases do not read and write one row at a time directly from disk.

They usually organize storage into fixed-size blocks called pages.

A page might be:

• 4 KB
• 8 KB
• 16 KB
• or some other fixed size

Why pages?

Because disks and operating systems work better with chunked reads and writes than with tiny scattered operations.

So instead of asking the disk for "row 187" directly, the database often asks for:

"load the page that contains row 187."

That page is then decoded in memory, and the database finds the exact row inside it.

This is one of the most important ideas in database internals.

Rows, documents, or index entries are usually inside pages, not floating around individually on disk.

How indexes make lookups fast

Without an index, a database often has to scan everything.

If you ask:

SELECT * FROM users WHERE email = '[e-mail]'

and there is no index on email, the database may need to inspect row after row until it finds matches.

That is called a full scan.

Indexes exist to avoid that.

An index is another structure built beside the main data so the database can find locations quickly.

The two most common families are:

B-tree indexes

This is the classic index structure in many relational databases.

A B-tree keeps values in sorted order and makes lookup efficient.

That helps with:

• equality lookup
• range queries
• ordered scans

Example:

If an index exists on email, the database can search the index first, find the location of the matching row, then jump to the real data.

Instead of scanning 10 million rows, it may touch only a small number of pages.

Hash indexes

Hash indexes are optimized for direct equality lookup.

They are good for:

• id = 123
• exact key lookup

They are usually weaker for range queries like:

id > 1000

LSM-tree style indexes

Many modern write-heavy systems use log-structured merge trees.

Instead of updating one sorted tree in place all the time, they:

• write sequentially
• buffer changes in memory
• flush sorted files to disk
• merge those files later

This often improves write throughput, but it changes the read path and compaction behavior.

Systems like RocksDB and Cassandra use LSM-style ideas.

How a database finds a row quickly

Suppose a table has an index on email.

When you search for one email, the general path looks like this:

1. the query parser understands what you asked for
2. the planner checks available indexes
3. the engine chooses the email index
4. the index search finds the row location
5. the relevant data page is loaded into memory if needed
6. the row is read from that page
7. the result is returned

That is why search can feel fast.

The database is not "being smart" in a mystical way. It is using pre-built structures to avoid unnecessary work.

Why some queries are still slow

Indexes help, but they do not solve everything.

Queries can still be slow when:

• there is no useful index
• the query needs too many rows anyway
• the planner chooses badly
• the data is fragmented
• too much data must be read from disk
• joins, sorts, or aggregations become expensive

So performance is not only about "having an index."

It is about:

• storage layout
• memory pressure
• access pattern
• query plan
• workload shape

What happens on writes

Reads are only half the story.

When a database writes data, it usually does not just overwrite the file carelessly.

Serious databases care about crashes, partial writes, and recovery.

So many of them use a write-ahead log, often called a WAL.

The general idea is:

1. record the change in a durable log first
2. acknowledge the write
3. apply or flush the data pages after that
4. recover from the log if the system crashes mid-way

This is one reason databases can survive crashes better than a naive file format.

The log is not only about speed. It is also about correctness and recovery.

Why databases do not just use normal files directly

People sometimes ask:

Why not just save JSON files, CSV files, or objects directly?

You can, for simple systems.

But a real database gives you things normal files do not handle well on their own:

• indexing
• concurrency control
• transactions
• recovery
• query planning
• caching
• data integrity

A file can store data.

A database is the machinery that makes that data searchable, writable, recoverable, and safe under load.

The simple mental model

If you want the simplest mental model, think of a database like this:

• data is stored durably on disk
• hot parts are kept in memory
• storage is organized into pages or files
• indexes point to where data lives
• a planner decides the cheapest way to answer a query
• logs protect writes and recovery

That is not the whole story, but it is enough to start reading database internals without feeling lost.

Final thought

A database is not just "a place to keep data."

Underneath, it is a storage engine, memory system, indexing system, execution engine, and recovery system working together.

The exact implementation changes from NodeDB to PostgreSQL to SQLite to RocksDB to ClickHouse to MongoDB, but the deeper ideas repeat.

If you care about how databases really work, follow NodeDB. I will keep writing more about storage engines, query planning, indexing, execution, and why database internals matter more than most application developers realize.