4.3 Storage Engine Layer

Level: 4 | Version: v1.1 | Author: Meptrasoft

Overview

The Storage Engine is the database layer responsible for managing access to stored data.

A simple way to understand the difference is:

Compute Engine decides how to process a query. Storage Engine handles how the required data is accessed or changed.

For example:

SQL QUERY
    ↓
COMPUTE ENGINE
    ↓
STORAGE ENGINE
    ↓
DATA

The Storage Engine works with database pages, indexes, memory buffers, transactions, and persistent storage to read or modify data.

Access Method

The Access Method determines how the database obtains or modifies the required records.

Two common read strategies are:

Index Scan

If a suitable index exists, the database may use it to locate matching rows more efficiently.

QUERY
  ↓
INDEX
  ↓
MATCHING ROWS

Full Table Scan

The database can read the table's data pages and examine rows to find matches.

QUERY
  ↓
READ TABLE PAGES
  ↓
CHECK ROWS
  ↓
MATCHING ROWS

For example:

SELECT *
FROM Employees
WHERE Employee_ID = 101;

If a suitable index exists on Employee_ID, the optimizer may choose an index-based access path.

Access Method = Decide how the required data is accessed.

The exact access methods available depend on the database system and execution plan.

Buffer Manager

The Buffer Manager manages database pages held in memory.

Database data is stored persistently, but reading every required page from storage repeatedly can be expensive.

The buffer manager helps by keeping frequently needed pages in RAM.

A simplified flow is:

QUERY NEEDS DATA
      ↓
CHECK BUFFER
      ↓
PAGE AVAILABLE?
   ↙         ↘
 YES          NO
  ↓            ↓
READ RAM    READ STORAGE
               ↓
          LOAD INTO BUFFER

This is one reason repeated access to frequently used data can be faster.

Simple Analogy

Think of a student studying from a book:

Disk / storage → the bookshelf
RAM / buffer → the pages already on the desk

Getting a page from the desk is quicker than walking to the bookshelf every time.

Buffer Manager = Manages database pages in memory to reduce unnecessary storage I/O.

Transaction Manager

The Transaction Manager coordinates operations that change database state, such as:

INSERT
UPDATE
DELETE

It works with transaction, locking/concurrency, logging, and recovery mechanisms to help maintain reliable changes.

For example:

BEGIN;

UPDATE Accounts
SET Balance = Balance - 1000
WHERE Account_ID = 1;

COMMIT;

The transaction manager is involved in ensuring the change is handled according to the database's transaction rules.

Transaction Manager = Coordinates database changes as part of reliable transactions.

Read and Write Paths

At a simplified level, database activity can be thought of as having different paths.

Read Example

For a SELECT statement:

SELECT
  ↓
EXECUTION PLAN
  ↓
ACCESS METHOD
  ↓
BUFFER / STORAGE
  ↓
RESULT

Write Example

For an UPDATE:

UPDATE
  ↓
EXECUTION PLAN
  ↓
ACCESS METHOD
  ↓
TRANSACTION MANAGEMENT
  ↓
DATA PAGES / LOGGING
  ↓
COMMIT

Important Note

These paths are conceptual, not strict rules.

A SELECT can still involve transaction-related behavior, and write operations also use buffers and storage pages. Database systems coordinate these components together rather than treating reads and writes as completely separate pipelines.

Storage Engine and Persistent Storage

Ultimately, database data must be stored somewhere persistently.

A simplified model is:

STORAGE ENGINE
      ↓
DATA / INDEX PAGES
      ↓
DATABASE FILES
      ↓
SSD / DISK

The Storage Engine abstracts many of these physical details from the application.

An application does not normally need to know which physical disk location contains a particular employee record.

Example: Finding an Employee

Consider:

SELECT Name, Department
FROM Employees
WHERE Employee_ID = 101;

A simplified flow might be:

SQL QUERY
    ↓
COMPUTE ENGINE
    ↓
EXECUTION PLAN
    ↓
ACCESS METHOD
    ↓
CHECK BUFFER
    ↓
INDEX / DATA PAGES
    ↓
EMPLOYEE RECORD
    ↓
RESULT

If the required page is already in memory, the database may avoid reading it again from persistent storage.

If it is not available, the required page may be loaded into the buffer.

Example: Updating an Account

Consider:

BEGIN;

UPDATE Accounts
SET Balance = Balance - 500
WHERE Account_ID = 1;

COMMIT;

The database must not only find the correct account record. It must also manage the change as part of a transaction.

Conceptually:

UPDATE REQUEST
      ↓
ACCESS ACCOUNT DATA
      ↓
TRANSACTION MANAGEMENT
      ↓
MODIFY DATA PAGE
      ↓
LOG / RECOVERY WORK
      ↓
COMMIT

The exact internal sequence varies by database system.

Storage Engine vs Compute Engine

These two layers are easy to confuse.

Compute Engine	Storage Engine
Processes SQL queries	Manages data access and storage
Parses and optimizes queries	Reads and modifies data
Produces execution plans	Works with indexes, pages, buffers, and persistent storage
Focuses on query execution logic	Focuses on data management

A simple memory trick:

COMPUTE ENGINE
→ “HOW SHOULD THE QUERY RUN?”

STORAGE ENGINE
→ “HOW DO WE ACCESS THE DATA?”

Why the Storage Engine Matters for Performance

Storage access can be expensive compared with working with data already in memory.

Performance is therefore affected by things such as:

Index usage
Data page access
Buffer/cache efficiency
Storage I/O
Query access patterns

For example, an appropriate index may reduce the amount of data the database needs to examine.

However, indexes are not free. They consume storage and can add work to data modifications.

Efficient data access is an important part of database performance.

Common Beginner Mistakes

✗ Mistake	✓ Correct Understanding
Storage Engine means only the hard disk	It includes the mechanisms used to access, cache, modify, and persist database data
Buffer Manager stores the permanent database	It manages database pages in memory; persistent copies remain in storage
Every `SELECT` is a full table scan	The optimizer may choose an index or another access method
`SELECT` never uses transaction mechanisms	Reads can participate in transactions and isolation depending on the database
`UPDATE` goes directly to disk	Database systems use buffers, transaction management, logging, and storage mechanisms
The Access Method always means an index	Access can use an index, table scan, or other supported access path
All database products have identical storage-engine architecture	Internal implementation differs significantly between products

Placement Quick Points

STORAGE ENGINE
→ MANAGES DATA ACCESS AND STORAGE

ACCESS METHOD
→ INDEX SCAN / TABLE SCAN / OTHER ACCESS PATH

BUFFER MANAGER
→ MANAGES DATABASE PAGES IN MEMORY

TRANSACTION MANAGER
→ COORDINATES DATA-MODIFYING TRANSACTIONS

PERSISTENT STORAGE
→ DATABASE FILES / DATA PAGES

The Storage Engine manages how data is accessed and changed.
Access methods determine how the required records are located.
An index scan and a full table scan are common access strategies.
The Buffer Manager manages database pages in memory.
The Transaction Manager coordinates reliable data modifications.
Database systems use persistent storage for durable data.
Read and write operations use these components in coordinated ways.
Exact Storage Engine architecture varies between database products.

Interview Questions

What is the Storage Engine?: The Storage Engine is the database layer responsible for managing access to stored data, including reading, modifying, caching, and persistence.
What is an Access Method?: An access method is the mechanism used to locate or access the required database data, such as an index scan or table scan.
What is the role of the Buffer Manager?: The Buffer Manager manages database pages in memory and loads required pages from persistent storage when they are not already available.
Why is buffering useful?: Keeping frequently needed database pages in RAM can reduce repeated storage I/O and improve performance.
What does the Transaction Manager do?: It coordinates data-modifying operations with transaction, concurrency, logging, and recovery mechanisms.
What is the difference between an index scan and a full table scan?: An index scan uses an index to locate relevant rows, while a full table scan examines the table's data pages to find matching rows.
How does the Storage Engine affect performance?: It affects how efficiently the database accesses data, including index usage, memory buffering, and storage I/O.

Practice & Hands-On Exercises

Beginner Practice

What is the purpose of the Storage Engine?
What does the Access Method do?
What does the Buffer Manager do?
What does the Transaction Manager do?
What is the difference between RAM and persistent database storage?

Think and Answer

Why can reading data from memory be faster than reading it from storage?
Why might a database choose an index scan instead of a full table scan?
Why do write operations need transaction management?
Why are the Storage Engine and Compute Engine separate concepts?

Query Flow Practice

For:

SELECT Name
FROM Employees
WHERE Employee_ID = 101;

Which layer processes the SQL query?
Which component may choose an index-based access path?
Where might the required database page already be stored?
What happens if the required page is not currently in the buffer?
What is finally returned to the client?

💡 Tip: Test your queries using the in-browser interactive runner above.

Key Takeaway

COMPUTE ENGINE
       ↓
STORAGE ENGINE
       ↓
ACCESS METHOD
   ↙        ↘
BUFFER     TRANSACTION
MANAGER      MANAGER
   ↓            ↓
DATA / INDEX PAGES
       ↓
PERSISTENT STORAGE

The Storage Engine manages how database data is accessed, cached, modified, and persisted, while the Compute Engine handles the processing of the SQL query itself.

End of Storage Engine Layer