1

This SO answer outlines how MySQL organizes its database into files, but doesn't describe the structure of the files themselves.

I am wondering, at the physical level, how relational database management systems store their records in files. What the structure of the file is roughly, where the columns/values are (if it's just a CSV), and how it is traversed.

  • This is DBMS specific. Please tag one – Michael Kutz Jun 29 '18 at 17:40
  • I tagged postgresql, but any DBMS is fine. – Lance Pollard Jun 29 '18 at 17:42
5

I think it is opportune to start pointing out that, in a relational database —as per Dr. E.F. Codd— information is represented by means of one and only one structure, the relation, which is usually portrayed as a table.

The relational model deals exclusively with aspects of the logical level of abstraction, so a table (an abstraction) is part of the logical level of a database (it is susceptible of undergoing logical operations, e.g., projections, joins, restrictions, etc.), so it is completely independent from the physical storage utlized by the database management system (DBMS for brevity) of use.

That being so, since the relational model has to do solely with logical-level aspects, it offers physical data independence, which sets DBMS (e.g., SQL platforms like IBM DB2, Microsoft SQL Server, PostgreSQL, etc. —that although quite powerful, strictly speaking, are semi-relational, i.e., they do not comply fully with the stipulations of the relational framework—) free to (a) store at the physical level of abstraction in any way their developers/vendors deem convenient (b) the data represented via logical-level tables.

It is important to note that the physical-level storage can in turn be divided in distinct sub-levels of abstraction.

Therefore, the physical arrangements depend on the particular DBMS under consideration, hence they can vary from platform to platform.

Examples of physical structures utilized by different SQL platforms are detailed

In case you are interested, you may find of help this answer to a question about some of the different levels of abstraction of a computerized information system.

  • We dont really care about the physical structure of the data on disk - that's an implementation detail. If you must know, you may download the source for postgresql or mysql and read the code - that will tell you how they store their data on disk. – David Buttrick Jun 29 '18 at 18:26
  • 2
    (1) The OP is asking for details about how the DBMSs structure and store the data on disk. (2) Of course, everything at the physical level of abstraction is an implementation detail. (3) And, yes, I agree that reading the PostgreSQL, MySQL or Firebird source code directly can help to understand the arrangements of those specific DBMSs at the lowest sub-levels of abstraction within the physical level itself. – MDCCL Jun 29 '18 at 18:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.