33

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?
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
noisy
  • 6,495
  • 10
  • 50
  • 92

1 Answers1

17

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 1
    "Using separate files for each table. (If we consider Access a database.)" Access does not have a separate file for each table, dBASE does. Access is not not and never has been a database, it generally uses the ACE or Jet database, either of which is certainly a perfectly good file-based database. – Fionnuala Aug 19 '12 at 09:01
  • How big can 'one single file' be? – ghchoi Dec 25 '19 at 13:54
  • @GyuHyeonChoi . . . That depends on the operating system. – Gordon Linoff Dec 25 '19 at 14:14
  • @GordonLinoff Can you please check my question (https://stackoverflow.com/questions/59478987/c-binary-file-i-o-operations-slow-down-how-db-handle-binary-files) please? – ghchoi Dec 25 '19 at 14:15