Storage Manager

Definition

The Storage Manager is the DBMS subsystem responsible for how data is physically organized on disk (or SSD), and for translating between the logical view of data (tables and rows, as the Query Processor understands them) and the physical byte-level layout on the storage device.

How It Works — Example

The Storage Manager organizes data into fixed-size pages or blocks — commonly 8 KB pages in PostgreSQL, or 16 KB pages in MySQL's InnoDB. It tracks free space within data files so it knows where a new row can fit, and it maintains indexes (typically B-tree or hash structures) as separate physical structures alongside the table's own data, enabling fast lookups without scanning every row. It also manages file- and directory-level allocation, deciding which physical file(s) a given table and its indexes live in.

Example: when a new student row is INSERTed, the Storage Manager decides which existing page has enough free space for that row — or allocates a brand-new page if none does — writes the row's bytes into that page, updates any indexes defined on the table (such as one on student_id), and marks the modified page as "dirty" so the Buffer Manager knows it eventually needs to be flushed back to disk.

Edge Cases and Pitfalls

• Fragmentation and bloat. Over time, deletes and updates leave "holes" in pages that aren't immediately reclaimed, causing a table to physically occupy far more disk space than its current row count would suggest. Periodic maintenance — VACUUM in PostgreSQL, OPTIMIZE TABLE in MySQL — reclaims this wasted space; skipping it lets tables silently bloat.
• Poor physical layout choices hurt I/O even with a good logical design. A very wide row that spans multiple physical pages, or a poorly chosen page fill-factor (how full pages are allowed to get before splitting), can degrade I/O efficiency even when the table's logical schema (columns, keys, constraints) is well designed.
• Indexes are physical structures with real maintenance cost. Every index the Storage Manager maintains must be updated on every INSERT/UPDATE/DELETE to the underlying table, so indexes speed up reads but add overhead to writes — a trade-off, not a free performance win.

Key Takeaways / Interview Q&A

Q: What is the core job of the Storage Manager? A: Translating between the logical view of data (tables/rows) and its physical byte-level layout on disk, organizing data into pages and maintaining indexes and free-space tracking.

Q: Why can a table occupy much more disk space than its row count suggests? A: Because deletes and updates leave fragmentation/bloat in pages that isn't automatically reclaimed until maintenance operations like VACUUM or OPTIMIZE TABLE run.

Q: Why aren't indexes a "free" performance improvement? A: Because the Storage Manager must update every index on the table on every write operation, so while indexes speed up reads, they add overhead to inserts, updates, and deletes.