How Databases Work Internally: Storage, Indexes, Queries and Transactions Explained

Every modern application depends on a database. Whether you are building a simple web app, a mobile backend, or a large distributed system, data must be stored reliably, retrieved quickly, and kept consistent under concurrent access. Yet many developers treat the database as a black box: they write SQL or use an ORM and hope for the best.

Understanding how databases work internally changes that. It helps you write better queries, design schemas that scale, diagnose performance problems, and choose the right database for a given workload. This article explains the core internal architecture of database systems in clear, beginner-friendly language while remaining technically accurate.

What Is a Database Management System?

A database is the collection of data. A Database Management System (DBMS) is the software that manages that data. Popular examples include PostgreSQL, MySQL, SQLite, MongoDB, and Redis. The DBMS sits between your application and the physical storage (disk or memory) and provides:

• A way to define the structure of data (schema)
• Languages or APIs to insert, update, delete, and query data
• Concurrency control so multiple users can work safely
• Durability guarantees so data survives crashes
• Security, backups, and recovery mechanisms

Internally, a DBMS is not a single component. It is a layered system of cooperating parts.

High-Level Architecture of a Database

Most relational and many non-relational databases share a similar architectural skeleton:

Client Application
        |
        v
Transport / Connection Layer
        |
        v
Query Processor (Parser + Optimizer + Planner)
        |
        v
Execution Engine
        |
        v
Storage Engine (Buffer Manager + Indexes + Data Files)
        |
        v
Disk / Persistent Storage

Let’s walk through each layer.

1. Transport and Connection Layer

Applications connect to the database over a network (or locally via sockets). The transport layer accepts connections, authenticates users, manages sessions, and receives SQL (or API) requests. It also returns result sets. This layer is responsible for connection pooling in many deployments so that creating a new connection for every query does not become a bottleneck.

2. Query Processor

When a query arrives, three main steps occur:

• Parsing: The SQL text is turned into a structured representation (parse tree or abstract syntax tree). Syntax errors are caught here.
• Semantic analysis and binding: Table and column names are resolved against the catalog (the database’s metadata about its own objects). Permissions are checked.
• Optimization and planning: The optimizer explores different ways to execute the query and chooses a plan that is expected to be cheapest. Cost is estimated using statistics about table sizes, index selectivity, and disk I/O cost.

The output of this stage is a query plan — a tree of operators such as sequential scan, index scan, nested-loop join, hash join, sort, aggregate, and so on.

3. Execution Engine

The execution engine takes the chosen plan and runs it. It pulls data from the storage engine, applies filters, joins, aggregations, and projections, and produces the final result rows. Modern engines often use vectorized or pipelined execution so that data flows through operators without materializing large intermediate results when possible.

4. Storage Engine

This is the heart of the database for durability and performance. The storage engine is responsible for:

• Organizing data on disk into pages or blocks
• Managing an in-memory buffer (cache) of frequently used pages
• Maintaining indexes
• Writing a transaction log for recovery
• Providing the low-level API that the execution engine uses to read and write records

Popular storage engines include InnoDB (MySQL/MariaDB), the PostgreSQL heap + indexes, RocksDB, and WiredTiger (MongoDB).

How Data Is Stored on Disk

Disks (HDDs and SSDs) are much slower than memory, especially for random access. Databases therefore organize data carefully.

Pages and Blocks

Data is stored in fixed-size units called pages (commonly 4 KB, 8 KB, or 16 KB). A page is the unit of I/O: the database reads and writes whole pages. Rows live inside pages. When a page fills up, the storage engine may split it or allocate a new page and link them.

Tables are usually collections of pages. In many systems the pages of a table form a heap (unordered) or are organized by a clustered index (ordered by the primary key).

The Buffer Manager (Buffer Pool)

Because disk is slow, the database keeps a large cache of recently used pages in memory — the buffer pool or buffer cache. When the execution engine needs a page, it first asks the buffer manager. If the page is already in memory (a cache hit), access is fast. If not (a cache miss), the page is read from disk into the buffer pool, possibly evicting a less useful page according to a replacement policy such as LRU (Least Recently Used) or a variant.

Dirty pages (pages that have been modified) are eventually written back to disk. The timing of these writes is carefully controlled so that durability and performance goals are met.

Indexes: Finding Data Without Scanning Everything

Without indexes, answering a query such as SELECT * FROM users WHERE email = 'alice@example.com' would require reading every row (a full table scan). Indexes solve this problem.

An index is a separate data structure that maps values of one or more columns to the physical location of the corresponding rows (page numbers and offsets, or primary-key values).

B-Trees and B+ Trees

The dominant index structure in relational databases is the B-Tree (more precisely, most systems use a B+ Tree variant). A B-Tree is a balanced multi-way tree:

• Internal nodes contain keys and pointers to child nodes.
• Leaf nodes contain keys and pointers to the actual data (or the full row in a clustered index).
• All leaves are at the same depth, so lookup cost is logarithmic in the number of keys.
• Leaves are often linked, enabling efficient range scans.

Because nodes are sized to match disk pages, a single I/O brings many keys into memory. This makes B-Trees excellent for both point lookups and range queries on disk-based storage.

Hash Indexes

Hash indexes map a key to a bucket using a hash function. They are excellent for exact equality lookups but do not support range queries. They are common in in-memory databases and as secondary indexes in some systems.

Other Index Types

Specialized indexes exist for full-text search, geospatial data (R-Trees), and write-heavy workloads (LSM Trees). LSM Trees (Log-Structured Merge Trees) write data sequentially into memory and later merge sorted runs on disk. They favor high write throughput at the cost of more complex reads and background compaction.

How a Query Actually Executes

Consider a simple query:

SELECT name, email
FROM users
WHERE city = 'Berlin'
ORDER BY name;

Rough steps inside the database:

1. The query is parsed and validated.
2. The optimizer examines available indexes on city and name, table statistics, and estimated selectivity.
3. It may choose an index scan on city, followed by a sort, or a sequential scan plus sort, depending on which plan has lower estimated cost.
4. The execution engine requests the necessary pages from the buffer manager.
5. Rows that match the filter are projected (only name and email are kept) and sorted.
6. The result is returned to the client.

The same logical query can be executed in many different physical ways. The optimizer’s job is to pick a good one using cost models and statistics.

Transactions and the ACID Properties

A transaction is a logical unit of work that groups multiple reads and writes. Databases guarantee the ACID properties:

• Atomicity: Either all changes in the transaction succeed, or none of them do. Partial updates are never visible.
• Consistency: The database moves from one valid state to another. Constraints (primary keys, foreign keys, checks) are enforced.
• Isolation: Concurrent transactions do not interfere with each other in unexpected ways. Different isolation levels (Read Committed, Repeatable Read, Serializable) trade consistency for performance.
• Durability: Once a transaction is committed, its effects survive system crashes.

Write-Ahead Logging (WAL)

Durability is usually achieved with a write-ahead log. Before any change is written to the data pages on disk, a description of the change is appended to a sequential log file. In the event of a crash, the database can replay the log (redo) to bring the data files up to date, and undo any incomplete transactions.

This design allows the buffer manager to write dirty pages lazily while still guaranteeing that committed work is never lost.

Concurrency Control

Multiple transactions run at the same time. To keep isolation, databases use locking, multi-version concurrency control (MVCC), or a combination. In MVCC (used by PostgreSQL, Oracle, and others), readers see a consistent snapshot of the data as of a particular point in time and do not block writers. Writers create new versions of rows. Old versions are cleaned up later by a vacuum or purge process.

Real-World Examples

Web application user lookup: A login form looks up a user by email. An index on the email column turns what would be a full table scan into a fast B-Tree lookup followed by a single page read.

E-commerce order placement: Creating an order involves inserting into several tables (orders, order_items, inventory updates) inside one transaction. Atomicity ensures that either the entire order is recorded or none of it is. Isolation prevents two concurrent checkouts from overselling the same item.

Analytics dashboard: A report that aggregates millions of rows may use sequential scans, hash aggregates, and possibly materialized views or columnar storage for speed. Understanding the execution plan (via EXPLAIN) lets developers add indexes or rewrite queries when performance is poor.

Caching layer: Many systems place Redis or Memcached in front of the primary database. The cache stores hot key-value pairs in memory. Understanding that the primary database still owns durability and consistency helps design correct cache invalidation strategies.

Code Example: Observing a Query Plan

In PostgreSQL you can inspect the plan the optimizer chose:

EXPLAIN ANALYZE
SELECT name, email
FROM users
WHERE city = 'Berlin'
ORDER BY name;

The output shows whether an index was used, how many rows were estimated versus actually returned, and the time spent in each operator. Learning to read these plans is one of the highest-leverage skills for backend developers.

Common Misconceptions

• “Indexes make everything faster.” Indexes speed up reads that match the indexed columns, but they slow down writes because every insert, update, or delete must also maintain the index. Too many indexes can hurt overall performance.
• “The database stores rows the way I see them in SELECT *.” Physically, data lives in pages; columns may be stored separately in columnar engines; and the on-disk order may be determined by a clustered index rather than insertion order.
• “NoSQL means no transactions.” Many modern document and key-value stores offer transactions or at least atomic single-document operations. The real differences are data model, consistency model, and scaling approach.
• “Memory is just a cache.” In pure in-memory databases the primary copy of the data lives in RAM and durability is provided by logging or replication. In traditional disk-based systems memory is mainly a cache, but a large buffer pool is still critical for performance.

Best Practices and Key Takeaways

• Design schemas with the dominant access patterns in mind. Primary keys and indexes should support the most common filters and joins.
• Use EXPLAIN (or the equivalent) regularly. Guessing is expensive; measuring is cheap.
• Keep transactions short. Long-running transactions hold locks or versions longer and increase the chance of contention.
• Monitor buffer-pool hit rates, lock waits, and slow-query logs. These metrics reveal internal pressure points.
• Understand the storage engine of the database you use. InnoDB, PostgreSQL’s heap, and LSM-based engines behave differently under write-heavy versus read-heavy loads.
• Prefer sequential I/O and good locality when possible. Random page reads are the classic performance killer on disk.

Mastering these ideas does not require becoming a database kernel developer. It does require treating the database as a sophisticated system with its own architecture rather than a simple key-value store with SQL on top.

FAQ

What is the difference between a database and a DBMS?
A database is the data itself. A DBMS is the software that stores, retrieves, and manages that data while providing concurrency, durability, and query capabilities.

Why do databases use pages instead of storing individual rows?
Disk I/O is expensive. Reading or writing a whole page amortizes the cost and matches the way storage devices transfer data.

What is a clustered index?
A clustered index determines the physical order of rows on disk. In InnoDB, the primary key is the clustered index; the table data is stored in the leaf pages of the primary-key B-Tree.

How does a database recover from a crash?
Using the write-ahead log. On restart the system analyzes the log, redos committed changes that may not have reached the data files, and undoes changes from transactions that did not commit.

Are B-Trees only used in relational databases?
No. Many key-value stores, file systems, and even some NoSQL systems use B-Trees or B+ Trees because of their balanced performance for both point and range lookups on disk.

When should I use an LSM Tree instead of a B-Tree?
LSM Trees excel at high write throughput because they turn random writes into sequential ones. They are common in write-heavy workloads (certain time-series, logging, and some NoSQL systems). Reads can be more expensive because multiple runs may need to be checked.

What is MVCC?
Multi-Version Concurrency Control. Instead of locking rows for readers, the database keeps multiple versions of a row. Readers see a consistent snapshot; writers create new versions. This reduces contention between readers and writers.

How can I see what my database is actually doing for a query?
Use the EXPLAIN or EXPLAIN ANALYZE command (PostgreSQL, MySQL, etc.). It shows the chosen plan, estimated costs, and (with ANALYZE) actual row counts and timings.

Related Articles

• How DNS Works
• How HTTP Works
• What Happens When You Type a URL in Your Browser
• How CPUs Execute Instructions
• How APIs Work
• Caching Explained

Understanding the internals of databases turns them from mysterious black boxes into predictable, tunable systems. The concepts of pages, buffer pools, B-Trees, query optimization, write-ahead logging, and transactions appear again and again across almost every serious data store. Once you internalize them, you will write better applications and diagnose problems with far greater confidence.