How do databases physically store data on a filesystem?

Question

I need to know how data from databases is stored on a filesystem. I am sure, that different databases use different ways of storing data, but I want to know what the general rule is (if there is one), and what can be changed in settings of a particular DB.

1. How is the whole database stored? In one big file or one file per table?
2. What if a table is enormous? Would it be split into few files?
3. What is typical size of file in that case?

Answer 1

The answer to this question is both database dependent and implementation dependent. Here are some examples of how data can be stored:

• As a single file per database. (This is the default for SQL Server.)
• Using a separate file system manager, which could be the operating system. (MySQL has several options, with names like InnoDB.)
• Using separate files for each table. (If we consider Access a database.)
• As multiple physical files, spread across multiple file systems, but represented as a single "file". (HIVE, for instance, that uses a parallel file system to store the data.)

However, these are the default configurations. Real databases typically let you split the data among multiple physical devices. SQL Server and MySQL call this partitions. Oracle calls this table spaces. These are typically set up by knowledgeable DBAs who understand the performance requirements of the system.

The final questions are easy to answer, though. Most databases give you the option of either growing the databases as space is needed or giving the database a fixed (or fixed maximum) size. I have not encountered a database engine that will split the underlying data into multiple files automatically, although it is possible that newer column oriented databases (such as Vertica) do something similar.