If you're a developer, data engineer, or just building something that stores user data, understanding how databases store and fetch that data is crucial.
Most of us use databases daily, but rarely think about what happens under the hood when we insert, update, or query information.
This blog breaks down the internal mechanics of storage engines not with jargon, but in a way that's easy to follow, relatable, and practical.
You open your favorite app. Maybe it's Instagram, Twitter, or even your banking app. You type in your username and instantly, it finds your profile, pulls up your data, and renders your feed.
But have you ever paused to think: how does the app know exactly where your data is?
More importantly — how is it so fast, even with millions of users?
Behind that speed lies one of the most important systems in modern computing: the storage engine — the part of a database responsible for writing, storing, and retrieving your data efficiently.
Let’s explore how that works. We’ll start simple and build up. And by the end, you’ll understand the difference between things like B-Trees and LSM-Trees — and why that matters when building real-world apps.
Imagine you’re managing a notebook. Every time someone signs up for your app, you write their name and info at the next empty line.
At first, this is easy — you can just flip through the pages to find things. But as the notebook fills up, finding a name becomes slower and slower. You’re forced to scan line by line.This is exactly what happens in databases without indexes.
Instead of scanning everything, you need a shortcut — like a table of contents. This is what an index does. It maps keys (like user IDs) to their locations, so you can jump straight to the data you want. Different types of indexes exist, and each one is better suited for different tasks. Let’s meet the most important ones.
A hash index is like a locker system. You give it a key, and it uses a mathematical function (a hash) to find the exact slot.
For example,
hash("user42") = 137
the system knows instantly to go to slot 137.
Fast? Yes. But there’s a catch: you can only ask exact questions. You can’t ask “show me all users between user40 and user50” — because the data isn’t sorted.
To support more flexible queries — especially range lookups — we need our data to be sorted.
SSTables are exactly that: immutable files where keys are stored in sorted order. Because they’re sorted, the system can use binary search, which is much faster than scanning linearly. When your in-memory table fills up, it’s flushed to disk as a sorted file an SSTable. Over time, you get multiple SSTables on disk, each containing a piece of your data.
But having dozens of SSTables creates a new problem: duplicate keys and outdated data.
Here Enters the Log-Structured Merge Tree (LSM-Tree) — a smart data structure that combines fast writes with background merging.
Here’s how it works:
It’s like keeping a pile of sticky notes and later organizing them into a single clean notebook. LSM-Trees are used in systems like LevelDB, RocksDB, and Cassandra because they handle heavy write loads like a champ.
Before LSM-Trees took off, most databases used B-Trees — and many still do.
A B-Tree is like a branching catalog. Each node contains keys and pointers to other nodes or data pages. It’s sorted, balanced, and designed to minimize disk reads by keeping the tree shallow. When you insert data, the B-Tree may split pages and rebalance itself to stay efficient. This makes B-Trees ideal for read-heavy or mixed workloads where both reads and updates are common.
Good question.
In many systems, you don’t actually overwrite data. Instead, you append a new version. This is called append-only storage. Every update is like adding a new line to your journal. It’s fast and avoids many concurrency issues. But it also creates clutter.
To solve that, storage engines use compaction — a background process that merges segment files, discards outdated entries, and keeps only the latest versions. If something is deleted, a tombstone (a deletion marker) is written. During compaction, both the original data and its tombstone are removed.
Now you’re starting to see how all of this fits together.
A log-structured storage engine treats the database like a log: every change is appended. Systems like LevelDB and Cassandra use this model to achieve high write throughput. Of course, reads can be slower unless you use caches and Bloom filters — tools that speed things up by avoiding unnecessary disk access.
Let’s say one user is reading a product review, and at the same time, another user is editing it. Should the reader wait?
Not if the database supports Multi-Version Concurrency Control (MVCC). With MVCC, the system keeps multiple versions of data so readers always see a consistent snapshot — even if writers are making changes. It’s like time travel for data — and it’s essential for systems that need to stay highly available.
There’s no perfect answer — it depends on your workload.
| Feature | B-Trees | LSM-Trees |
|---|---|---|
| Write Speed | Medium (in-place) | High (append-only) |
| Read Performance | High | Medium (needs caching) |
| Compaction | Low | High (background merges required) |
| Use Case | Read-heavy / transactional | Write-heavy / log-style data |
Choose B-Trees for traditional applications like MySQL. Choose LSM-Trees when ingest speed is critical, like in event logs or time-series apps.
They do a lot more than read and write.
Databases manage multiple users with locks or MVCC to prevent data corruption.
They write changes to a Write-Ahead Log (WAL) first, so if the system crashes, it can replay the log and restore state.
Hot data (like user profiles) is cached in memory to avoid slow disk access.
Column-based systems often compress data for speed and space savings.
Even on SSDs, sequential access is faster than jumping around randomly.
That’s why storage engines optimize locality — storing related data close together.
If a user has 20 orders, it’s best to store them on the same disk page. It speeds up reads and improves performance for common queries.
| Feature | Row-Oriented(e.g. MySQL) | Column-Oriented (e.g. ClickHouse) |
|---|---|---|
| Data Layout | All fields of a row | One column at a time |
| Best For | OLTP (inserts, updates) | OLAP (analytics, aggregation) |
| Compression | Moderate | High |
| Query Example | Insert user | Average salary across 1M users |
Column stores shine in analytical workloads where you only need a few fields over many rows.
Row stores are better for traditional apps that read and write full rows often.
We’ve come a long way — from hash indexes and B-Trees to LSM-Trees and compaction.
The core idea? The way your database stores and retrieves data changes everything: speed, scalability, and even your app’s design choices.
If you’re building an app that needs high write throughput, LSM-Trees might be your best bet.
If you care about consistent reads and structured transactions, B-Trees will serve you well.
And if you're diving into data analytics, columnar storage will save both time and space.
The best part? You now understand why — and that’s a superpower.
Follow me on X (Twitter) for more deep dives on system design, databases, and the real engineering behind the apps we use every day.