What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
A database turns logical tables into physical structures: files or tablespaces divided into pages, with rows and index entries organized inside them. The engine may keep frequently used pages in memory and use transaction logs to recover changes after a failure. The exact layout depends on the database and its configuration.
Where does a database save your data?
A table is the logical view of a collection of records. On persistent storage, a database engine organizes that data in files or tablespaces, which are divided into pages (also called blocks in some systems). Pages contain structures and metadata—not simply a continuous text file of rows.
Think of a warehouse: a table is the inventory category, pages are labeled bins, and records are the items inside. Page metadata helps the engine interpret and locate those records. This is only a mental model; real layouts and recovery rules differ by engine.
Rows and page space
A row occupies space within a page, alongside bookkeeping information the engine needs to find and manage it. Pages can also contain free space for later changes. PostgreSQL’s page layout, for example, includes a header, item identifiers, free space, row items, and potentially index-specific special space. PostgreSQL rows have a fixed-size header followed by optional metadata and user data. These details describe PostgreSQL, not a universal SQL format.
#1 Best Overall
Indexes are separate lookup structures
An index is an additional organized structure that helps the engine narrow down where matching records are located. SQLite and InnoDB document B-tree structures for relevant table and index data. Indexes take storage and must be maintained as records change; the query planner may choose one when it helps, but not every query uses an index, and an index does not guarantee a faster query.
What happens when you insert or read a row?
During a write
- The engine checks the operation. It evaluates the statement and transaction according to the database’s rules.
- It changes relevant structures. The engine may update data and index pages in memory.
- It records recovery information. A transaction log or journal helps the engine preserve the required transaction and durability guarantees.
- It writes changes to persistent storage. Changed pages are eventually written to the database’s files or tablespaces. The order and timing depend on the engine and its configuration, so a successful commit does not universally mean every changed page has already been copied to the main data file.
If a process or machine stops unexpectedly, the engine uses its recovery mechanism to bring stored data back to a consistent state. Logs and main data files serve different roles: a log records changes or recovery information, while the main structures hold the database’s organized data.
During a read
The engine uses the query plan to identify the records it needs. It may find the required pages in memory or read them from persistent storage. An index can reduce the search work for some queries, while a query that needs many records may use a different access path.
The Tool Desk
Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →How do major database engines organize storage?
These examples illustrate why “a database file” is not a universal description. Each engine has its own physical layout and settings.
Rank #3
| Engine | Persistent organization | Page or index details | Logging and recovery |
|---|---|---|---|
| PostgreSQL 17 | Tables and indexes are arrays of fixed-size pages. | The usual page size is 8 kB, though it can differ if the server is compiled with another page size. A page header is 24 bytes. | See the PostgreSQL documentation for the implementation details; the figures here do not specify a universal recovery sequence. |
| SQLite | The complete database state is usually in one main database file, but journal mode can involve a rollback journal or a WAL file as well. | Database page sizes range from 512 bytes to 65,536 bytes. Table and index structures use B-trees. | In WAL mode, frames record revised pages; a commit marker identifies a committed transaction, and checkpointing transfers WAL content into the main file. |
| MySQL InnoDB | Data is stored in tablespaces made up of pages. | The default tablespace page size is 16 KB in the MySQL 26.7 Reference Manual; it is configurable when initializing the instance. InnoDB indexes are B-trees except spatial indexes, which use R-trees. | Redo supports crash recovery. Recovery applies redo and rolls back incomplete transactions after an unexpected shutdown. |
Sources: PostgreSQL 17, Database Page Layout; SQLite, Database File Format; SQLite, Architecture; Oracle, MySQL Reference Manual, InnoDB Architecture; Oracle, MySQL Reference Manual, InnoDB Recovery.
PostgreSQL: fixed-size pages
PostgreSQL’s documented usual page size is 8 kB, but the value can differ when the server is compiled with a different page size. Its 24-byte page header is followed by item identifiers, free space, and items; index pages may also have special space. Those figures are PostgreSQL-specific.
SQLite: a main file, with possible journal files
SQLite says, “The complete state of an SQLite database is usually contained in a single file on disk called the ‘main database file’.” The word “usually” matters: transaction state may also involve a rollback journal or a write-ahead log (WAL), depending on the journal mode. SQLite’s documented page-size range is 512 to 65,536 bytes; its file-format specification covers the format used since SQLite 3.0.0.
In WAL mode, revised pages are recorded in WAL frames. A commit marker identifies a committed transaction, and checkpointing later transfers those changes back to the main database file. Thus, the main file is not necessarily the only file involved while a database is operating.
MySQL InnoDB: tablespaces and redo
InnoDB organizes data in tablespaces composed of pages. The MySQL 26.7 Reference Manual gives 16 KB as the default page size; the size can be configured when initializing an instance. InnoDB uses B-trees for indexes other than spatial indexes, which use R-trees. Its redo log helps correct data files after an unexpected shutdown; recovery reapplies redo and rolls back incomplete transactions.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Why does a database keep pages in memory?
Reading from storage for every request would be inefficient. Engines can cache pages in memory so repeated work may avoid a disk read. SQLite’s architecture describes a page cache; InnoDB uses a buffer pool for data and index pages. The cache or pool mediates access to persistent storage, but it does not replace the database’s persistent files.
Quick Recap
What should you remember about database storage?
- Tables are logical structures; persistent data is organized into engine-specific files or tablespaces and pages.
- Pages hold records plus structures and metadata used to manage them.
- Indexes provide additional lookup structures, with storage and maintenance costs.
- Memory caching can reduce repeated storage reads, while logs or journals support transaction handling and recovery.
- Page sizes, file arrangements, index implementations, and recovery behavior vary by engine and configuration. PostgreSQL, SQLite, and InnoDB are examples, not a complete map of every database.
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
Recommended Free Tools
