13.11

List two advantages and two disadvantages of each of the following strategies for storing a relational database:

  1. Store each relation in one file.

  2. Store multiple relations (perhaps even the entire database) in one file.

  1. Store each relation in one file.
Advantage Disadvantage
Since each relation is stored in its own file, it is easier to put the relations that are frequently used on SSDs, while the relations that are used rarely can be stored on magnetic disk drives. Optimizations such as Multitable clustering file organization cannot be performed since each relation is stored in its own file.
Assuming the blocks of a given file are stored nearby on the platters of the hard disk, reading a relation from hard disk to memory is faster since the blocks are closer, reducing movement of the disk arm. Every access to a relation must first go through the Data Dictionary/System Catalog to get the corresponding file’s path of the relation. Once the path is found, opening the file (using open() syscall for example) incurs overhead.
  1. Store multiple relations (perhaps even the entire database) in one file.
Advantage Disadvantage
Optimizations such as Multitable clustering file organization can be performed if needed If the database stores all relations in a single file, the Data Dictionary may note the blocks containing records of each relation in a data structure such as a linked list. However, doing so deprive us from the benefits of sequential reading from the hard drive to main memory.
Assuming that the entire database is stored in one file (like sqlite), we only have to call the open() syscall once Since all of the relations of the database are stored in the same file, optimizations like putting some relations on SSDs and others on magnetic disks are not possible (or hard to do).