Data Storage Formats on Disk: Record Formats and I/O Management

Introduction: How Records are Packed into Disk Blocks

In earlier units, we saw that a database relation consists of tuples (rows).

When these rows are written to disk, the database storage engine must decide:

• How to arrange fields within a record.
• How to handle variable-length text (like VARCHAR) and NULL values.
• How to store records inside a physical block so that records can be inserted, deleted, and updated without corrupting adjacent data.

1. Track Format and Sector Formatting

When a hard disk platter is manufactured, it undergoes Low-Level Formatting:

• Each concentric track is partitioned into sectors.
• Each sector contains:
  1. Preamble / Header: Contains synchronization bits and the physical sector address (Cylinder number, Head number, Sector number).
  2. Data Payload Area: Typically 512 bytes or 4,096 bytes of user data.
  3. ECC (Error-Correcting Code): Parity bits used by the disk controller to detect and automatically correct bit-flips caused by magnetic degradation.
  4. Inter-Sector Gap: Small unrecorded buffer separating adjacent sectors.

2. Record Formats: Fixed-Length vs. Variable-Length

A. Fixed-Length Records:

• Structure: Easy to implement. If each record is exactly 120 bytes, the 10th record in a block starts at byte offset 10 × 120 = 1200.
• Handling Deletions:
  1. Shift Records: Move all subsequent records up by one slot (Expensive!).
  2. Free List (Linked List of Deleted Slots): Overwrite the deleted record with a pointer to the next deleted slot. New insertions reuse free slots!

B. Variable-Length Records & Slotted-Page Architecture:

In modern relational databases (PostgreSQL, Oracle, MySQL InnoDB), records vary in length because of columns like VARCHAR(255).

To store variable-length records cleanly inside a block, databases use the Slotted-Page Architecture:

How Slotted Pages Work:

1. The Slot Directory sits at the beginning of the block and grows downward. Each slot contains the starting byte offset and size of a record.
2. The actual Record Data is stored starting from the very end of the block and grows upward.
3. Record Identifier (RID): An external pointer to a record is simply (Block_Number, Slot_Number).
4. If a record expands or shrinks during an update, the database engine can move the record data within the block without changing its RID!

3. Spanned vs. Unspanned Records

What happens when a single record is larger than the physical block size (or when a record does not fit in the remaining space of a block)?

Detailed Comparison:

• Unspanned: Simpler, faster. Every record is guaranteed to reside completely inside one block. However, it wastes unused bytes at the end of each block.
• Spanned: Essential when a record size exceeds the block size (e.g., storing images, large documents, or massive tables with 100+ columns). Reading a single spanned record requires multiple disk I/Os.

4. Input/Output Management: Double Buffering

When an application reads a large database file sequentially (like computing total payroll over 1,000,000 rows):

• Single Buffering: CPU waits while disk block 1 is read. CPU processes block 1 while disk sits idle. CPU waits while disk reads block 2. (Very slow!).
• Double Buffering: The DBMS allocates two memory buffer frames (Buffer A and Buffer B):
  • While the CPU is busily processing data in Buffer A, the disk controller is simultaneously streaming the NEXT block into Buffer B in the background!
  • Once CPU finishes Buffer A, it immediately switches to Buffer B without waiting, while the disk starts filling Buffer A!
  • CPU computation and disk I/O are completely overlapped, doubling data throughput!

💡 DAVV Exam Preparation Tips

5-Mark Question: “Differentiate between Spanned and Unspanned record organizations. What are the trade-offs?”

• Define Unspanned (records never cross block boundaries, causes internal fragmentation).
• Define Spanned (records split across multiple blocks via pointers).
• Explain when Spanned is mandatory (record size > block size).

10-Mark Question: “Explain Data Storage Formats on disk. Detail the Slotted-Page architecture for variable-length records with a neat diagram. What is Double Buffering in I/O management and how does it improve system throughput?”

• Explain fixed vs variable length records.
• Draw the Slotted-Page block diagram showing the Header, Slot Directory growing downward, and Record data growing upward.
• Explain how RID = (Block_No, Slot_No) maintains stable pointers.
• Illustrate Double Buffering showing overlapped CPU processing and disk reading.