Optimizing File Storage and Organisation in Databases

Every database, whether it holds bank account details, library catalogue entries, or student records, eventually has to store its data somewhere physical: on a disk. The way records are arranged and packed onto that disk decides whether a query returns in milliseconds or drags on for seconds. This is the world of file storage concepts, where logical data meets physical reality. Understanding how files are organised, how records sit inside blocks, and how block size shapes performance gives you a clear view of why some databases feel fast and others feel sluggish.

What file organisation means 🔗

File organisation is the logical arrangement of records on a storage device. It defines how file records are mapped onto disk blocks and how those blocks are placed on the storage medium. The method chosen has a direct effect on how quickly the system can retrieve, insert, and update data.

It helps to separate two related ideas. File organisation is about how data is arranged and stored. The access method is the technique used to fetch that stored data. As one explanation puts it, file organisation sets the groundwork that influences how the access method will fetch the data. The two often share the same names, sequential, indexed, and direct, but they describe different parts of the same process.

Types of file organisation 🔗

There are several established ways to organise files, each with its own balance of speed, storage efficiency, and ease of updating. The three most important methods to understand are sequential, indexed sequential, and direct access.

Sequential file organisation 🔗

In a sequential file, records are stored one after another based on a key field, usually a unique identifier such as a student ID or account number. The records are kept in order, and new records are added in a way that maintains the sequence. This is the most straightforward method of organisation.

Reading a sequential file works much like reading a book from the first page to the last. To find a particular record, the system may have to traverse from the first record until it finds the desired one. This makes sequential organisation excellent for batch operations where you process every record anyway, such as generating monthly statements or payroll runs. The weakness is obvious: looking up a single record among millions can be slow because there is no shortcut to jump straight to it.

Indexed sequential file organisation 🔗

Indexed sequential organisation tries to keep the benefits of sequential storage while removing its biggest drawback. The data file is still stored sequentially on a primary key, but the system also builds an index, similar to the index at the back of a textbook, that lets it jump close to the record it wants instead of scanning from the start.

The most widely known implementation is the Indexed Sequential Access Method (ISAM). ISAM was developed by IBM in the 1960s and combines sequential and direct access using indexes. A typical indexed sequential file has three parts: a main file where records are stored sequentially, an index that points to the location of records, and an overflow area for storing newly inserted records. The overflow area matters because inserting a record into the middle of a strictly ordered file would otherwise force everything after it to shift.

This structure makes indexed sequential files a strong fit for large databases in banking and administrative systems, where the same data must sometimes be read in bulk and sometimes looked up one record at a time. The trade-off is the extra space the index occupies and the maintenance effort needed to keep it current. Over time, ISAM files need periodic reorganisation because, as a static system, it demands regular organisational refinement to maintain top performance.

Direct access file organisation 🔗

Direct access, also called hashed or random access organisation, lets the system reach a specific record without reading through everything before it. A hashing function takes the record key and computes the physical address where the record should sit. When you need that record, the same function recalculates the address and the system goes straight there.

Direct access is best suited for applications that need quick retrieval of specific records, such as fetching customer data based on a customer ID. Its main advantage is speed for random lookups. The disadvantages are that it is more complex to implement, and it handles range queries poorly because related records may be scattered across the disk rather than sitting next to each other. Direct access devices, such as hard disks, allow data to be reached regardless of its physical location, which is what makes this method possible in the first place. This stands in contrast to a serial device like magnetic tape, where access time depends on how far along the tape the data sits.

How records are stored in blocks 🔗

Fields, records, and files are logical terms. Physically, the disk works in fixed chunks called blocks. A block is the smallest unit of data transfer between the disk and main memory, which means the operating system always reads or writes an entire block at a time, even if it only needs one record from it.

Because records are usually smaller than a block, several logical records are grouped together into one physical block. The number of records that fit into a single block is called the blocking factor. When records are processed together and placed in the same block, a single disk read can bring several useful records into memory at once, which saves additional disk accesses if the next request is for a record already in the buffer.

Calculating the blocking factor 🔗

For fixed-length records, the blocking factor is calculated with a simple formula. If B is the block size and R is the record size, then the blocking factor bfr equals the floor of B divided by R. The floor function means we round down, because we cannot store a fraction of a record.

Consider a worked example. Suppose a disk has a block size of 1024 bytes and a file of 40,000 fixed-length records, each 100 bytes long. The blocking factor would be the floor of 1024 divided by 100, which gives 10 records per block, requiring 4,000 blocks to hold the entire file. Since 10 records use only 1000 of the 1024 available bytes, 24 bytes in each block are left unused. Multiply that across thousands of blocks and the wasted space becomes significant.

Spanned and unspanned records 🔗

What happens to that leftover space at the end of a block? The answer depends on whether the file uses spanned or unspanned organisation.

In unspanned organisation, a record must fit entirely within one block. If the remaining space in a block is too small for the next full record, that space stays empty and the record moves to the next block. This keeps each record at a known starting location and makes processing simple, but it wastes some disk space.

In spanned organisation, a record can stretch across more than one block. When a record does not fully fit, part of it is stored in the current block and the rest in another, with a pointer in the first block pointing to the block containing the remainder. This eliminates wasted space but adds complexity. Spanned organisation must be used when the record size is larger than the block size, since such a record cannot possibly fit in a single block.

The choice affects retrieval. With unspanned records, fetching one record means accessing only a single block, which is faster. With spanned records, a record split across two blocks needs two block accesses, and reading a block is far slower than reading within memory. So spanned organisation saves space at the cost of retrieval speed, while unspanned organisation favours speed at the cost of some wasted space.

Block size and record allocation 🔗

Block size is a structural property set when the operating system formats the disk, with typical sizes of 1K, 2K, 4K, 8K, 16K and so on. A database can use blocks at the same size as the operating system’s disk block or at a multiple of it. This single choice ripples through the entire system’s performance.

The trade-off of larger blocks 🔗

A larger block holds more records, which raises the blocking factor and tends to reduce the number of separate disk reads needed to process a file. This is an advantage when the application reads many sequential records, because one read brings in a larger batch of useful data. The flip side is that a larger page takes more time to read from storage and write back. If the application only wants a single record from a large block, the extra data transferred is wasted effort.

Smaller blocks reduce that overhead for single-record lookups but mean more disk reads when scanning large files. There is no universally correct block size; the right answer depends on whether the workload is mostly bulk sequential processing or mostly individual random lookups.

Matching record size to block size 🔗

A database administrator usually cannot control the block size, but can often tailor record sizes to fit the blocks better. When there is a poor fit between record size and block size, a lot of space is wasted. For variable-length unspanned records, a useful rule is that the larger the ratio of block length to mean record length, the smaller the proportion of space wasted in each block.

This is why physical database design is more than a technical afterthought. By choosing the right file organisation, deciding between spanned and unspanned storage, and matching record sizes sensibly to block sizes, designers minimise disk input and output operations, reduce access time, and improve overall system performance. The logical structure of the data may be the same, but these physical decisions are what determine how the database actually behaves under real workloads.

Bringing it together 🔗

File storage decisions form a connected chain. The file organisation method sets how records are ordered and accessed. The blocking factor decides how many records share a block. The spanned or unspanned choice governs whether leftover block space is filled or left empty. And the block size sits underneath all of it, shaping how much data moves between disk and memory with each read. A well-designed system aligns these choices with the way the data will actually be used, balancing the constant tension between saving space and saving time.

What do you think? If you were designing the storage for a college library catalogue that is searched far more often than it is updated, which file organisation would you choose and why? And would you favour larger or smaller blocks for a system where most queries fetch just one record at a time?

References

1. https://anandgharu.wordpress.com/wp-content/uploads/2024/05/unit-6-file-organization-2024.pdf
2. https://www.vaia.com/en-us/textbooks/computer-science/fundamentals-of-database-systems-4-edition/chapter-13/problem-16-what-is-the-difference-between-a-file-organizatio/
3. https://studyglance.in/dbms/display.php?tno=51&topic=File-Organization-and-Indexing-in-DBMS
4. https://www.geeksforgeeks.org/dbms/isam-in-database/
5. https://www.dremio.com/wiki/indexed-sequential-file/
6. https://www.baeldung.com/cs/file-access
7. http://samples.jbpub.com/9781449606008/CWMaterials/Appendices/Appendix%20A-Physical%20Data%20Organization.doc
8. https://web.uettaxila.edu.pk/CMS/seADMSbsSp09/notes/ADBMS-Lecture-8%20Storage%20Structures.pdf
9. https://www.cs.uct.ac.za/mit_notes/database/htmls/chp11.html
10. https://www.tutorialspoint.com/dbms/dbms_placing_file_records_on_disk.htm
11. https://byjusexamprep.com/gate-cse/file-organization
12. https://www.geeksforgeeks.org/dbms/mapping-strategies-for-file-records-into-blocks/
13. https://holowczak.com/database-record-storage-index-structures/
14. https://image-ppubs.uspto.gov/dirsearch-public/print/downloadPdf/7421446