4.4 Physical Storage (Pages, Extents, Data Files, Database Files)

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

Overview

When a database stores data permanently, the data eventually has to be written to persistent storage such as an SSD or hard disk.

Database systems organize this stored data into logical and physical units so that data can be accessed and managed efficiently.

A simplified view is:

DATABASE
   ↓
DATA FILES
   ↓
PAGES
   ↓
ROWS / INDEX ENTRIES
   ↓
PHYSICAL STORAGE

Different database products use different storage architectures, terminology, and sizes. The concepts below provide a simplified model that is useful for understanding database internals.

Pages

A page is a fixed-size unit of data storage used internally by a database system.

Database systems commonly read and write data in pages rather than processing individual bytes or rows independently.

A page may contain:

Table rows
Parts of rows
Index entries
Other database metadata

For example:

PAGE
┌─────────────────────────┐
│ Row 1                   │
│ Row 2                   │
│ Row 3                   │
│ Index / page information │
└─────────────────────────┘

The exact page size depends on the database product.

For example, SQL Server uses 8 KB data pages, while other database systems may use different page sizes.

Page = A basic unit of database data storage and I/O.

Extents

An extent is a group of pages allocated together in database systems that use extent-based allocation.

For example, in SQL Server, one extent contains 8 pages, so one extent is 64 KB.

EXTENT
┌────┬────┬────┬────┬────┬────┬────┬────┐
│ P1 │ P2 │ P3 │ P4 │ P5 │ P6 │ P7 │ P8 │
└────┴────┴────┴────┴────┴────┴────┴────┘

The important concept is:

Extent = A group of database pages allocated together.

Important: Extent size and allocation behavior are database-product specific. Do not present 8 pages = 64 KB as a universal rule for every RDBMS.

Figure 2: An extent groups multiple pages; the 8-page, 64 KB example shown here applies to SQL Server.

Data Files

A data file is a file used by the database system to store database data.

Depending on the database product, data files can contain:

Table data
Index data
Database pages
Other internally managed structures

Conceptually:

DATA FILE
    ↓
PAGES
    ↓
TABLE / INDEX DATA

A database can use one or more data files depending on its architecture and configuration.

Database Files

A database may consist of multiple physical files, depending on the database system.

For example, SQL Server databases can use:

Data files
Transaction log files

A simplified representation is:

DATABASE
   ├── Data File
   ├── Data File
   └── Log File

The exact number and types of files depend on the database product.

A database is a logical collection of data managed by the DBMS, while physical files are storage structures used to persist that data.

Transaction Log

The transaction log records changes needed for transaction processing and recovery.

For example, suppose an account balance changes:

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

The database records appropriate information in its transaction log as part of its transaction and recovery mechanisms.

A simplified idea is:

CHANGE REQUEST
      ↓
TRANSACTION LOG
      ↓
DATA PAGES
      ↓
COMMIT

The transaction log is important for:

Recovery after failures
Maintaining transaction durability
Replaying or recovering committed changes
Supporting rollback/recovery mechanisms

Transaction Log = A durable record used by the database system to support transaction processing and recovery.

Important Correction

The transaction log should not be described simply as “all data modifications are written to the log before being written to disk.”

The exact write ordering and recovery process depend on the database system. The important concept is that transaction logging is a key part of reliable recovery and durability.

How Data Moves Through Physical Storage

Consider this simplified flow:

SQL QUERY
    ↓
COMPUTE ENGINE
    ↓
STORAGE ENGINE
    ↓
DATA ACCESS
    ↓
PAGE IN MEMORY
    ↓
DATA FILE
    ↓
SSD / DISK

The database does not usually think of a table as one giant continuous object.

Instead, table and index data are managed internally using pages and other storage structures.

Example: Finding a Row

Suppose we run:

SELECT Name
FROM Employees
WHERE Employee_ID = 101;

A simplified physical-storage path might be:

SQL QUERY
    ↓
EXECUTION PLAN
    ↓
INDEX / TABLE ACCESS
    ↓
REQUIRED PAGE
    ↓
BUFFER MEMORY
    ↓
EMPLOYEE ROW
    ↓
RESULT

If the required page is already in memory, the database may not need to read it again from persistent storage.

This connects the physical storage layer with the Buffer Manager discussed in the previous topic.

Physical Storage Hierarchy

The concepts can be viewed together as:

DATABASE
   ↓
DATA / LOG FILES
   ↓
PAGES
   ↓
ROWS / INDEX ENTRIES
   ↓
SSD / DISK

An extent, where supported by the database system, is a grouping of pages:

EXTENT
   ↓
PAGE
PAGE
PAGE
PAGE
...

Important Note

There is no universal hierarchy such as:

8 pages = 1 extent
8 extents = 1 data file
8 data files = 1 database

The first relationship is a valid SQL Server example, but data-file sizes, file counts, and database sizes are configuration-dependent and vary across database products.

Physical Storage and Query Performance

Physical storage matters because database operations involve reading and writing data.

For example:

Reading fewer pages can reduce I/O.
A useful index can reduce the number of pages that must be examined.
Keeping frequently used pages in memory can reduce storage reads.
Large amounts of random I/O can affect query performance.

This is why concepts such as pages, indexes, buffers, and I/O are important when studying database performance.

FEWER PAGES READ
      ↓
LESS I/O
      ↓
POTENTIALLY BETTER PERFORMANCE

Common Beginner Mistakes

✗ Mistake	✓ Correct Understanding
A page is always 8 KB in every database	Page size depends on the database product
Every database uses 8-page extents	Extent allocation is database-specific
A data file always has a fixed number of extents	Data-file size and allocation depend on configuration and product
A database always has exactly 8 data files	File count is configurable and database-specific
The transaction log is the same as a data file	The transaction log serves transaction and recovery purposes
A database table is stored as one giant block	Database systems manage table and index data using storage units such as pages
The application directly reads database pages from disk	The DBMS/storage subsystem manages physical data access

Placement Quick Points

PAGE
→ BASIC DATABASE STORAGE / I/O UNIT

EXTENT
→ GROUP OF PAGES IN SYSTEMS THAT USE EXTENTS

DATA FILE
→ PHYSICAL FILE USED TO STORE DATABASE DATA

LOG FILE
→ SUPPORTS TRANSACTION PROCESSING AND RECOVERY

PHYSICAL STORAGE
→ SSD / DISK

A page is a basic unit used to store and access database data.
Page size is database-product specific.
An extent is a group of pages in database systems that use extent-based allocation.
A data file stores database data on persistent storage.
A transaction log supports transaction processing and recovery.
Database file organization varies between products.
SQL Server provides a common example of 8 KB pages and 8-page extents.
Physical storage and I/O can significantly affect database performance.

Interview Questions

What is a database page?: A page is a fixed-size internal unit used by a database system to store and access data.
What is an extent?: An extent is a group of pages allocated together in database systems that use extent-based allocation.
Is the page size always 8 KB?: No. Page size depends on the database system. SQL Server uses 8 KB data pages, but other systems may use different sizes.
What is a data file?: A data file is a physical file used by a database system to persist database data such as table and index pages.
What is a transaction log?: A transaction log records information about database changes needed for transaction processing and recovery.
What is the difference between a data file and a log file?: A data file stores database data and structures such as table and index pages. A log file records transaction-related information used for recovery and transaction management.
Why are pages important for database performance?: Database systems perform many read and write operations at the page level. The number of pages that must be accessed affects I/O and therefore can affect query performance.
Are physical-storage structures identical across all RDBMS products?: No. Storage architecture, page sizes, extents, file structures, and allocation methods vary between database products.
How are pages related to the Buffer Manager?: The Buffer Manager loads database pages into memory so they can be accessed without repeatedly reading them from persistent storage.

Practice & Hands-On Exercises

Beginner Practice

What is a database page?
What is an extent?
What is a data file?
What is the purpose of a transaction log?
Why does a database use pages instead of treating the entire table as one large block?

Think and Answer

Why can reducing the number of pages read improve query performance?
Why is frequently accessed data often kept in memory?
Why can't we assume that all RDBMS products use the same page and extent sizes?
What is the difference between a data file and a transaction log?

SQL / Database Thinking

For this query:

SELECT Name
FROM Employees
WHERE Employee_ID = 101;

Explain the simplified path from the query to the physical data.

Why might an index reduce the amount of data the database needs to read?
How does the Buffer Manager help reduce repeated storage I/O?

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

Key Takeaway

DATABASE
   ↓
FILES
   ↓
PAGES
   ↓
ROWS / INDEX ENTRIES
   ↓
PHYSICAL STORAGE

EXTENT
→ GROUP OF PAGES

Physical storage explains how a database organizes persistent data into internal units such as pages and files, while mechanisms such as buffers and transaction logs help make data access and recovery efficient and reliable.

End of Physical Storage (Pages, Extents, Data Files, Database Files)