Relational Database Storage

Databases are highly complex pieces of software that we developers constantly use but we might not always know how it actually works under the hood. One of the complex steps is how the data is actually stored and understanding this will help us make smarter queries and avoid stupid mistakes.

The presentation showed that databases are actually stored in large binary blocks(pages) of data and that the usual mental model of everything being saved per row is wrong. Since the data is tightly packed next to each other it means that updating or adding one column to all your rows will cause a total rewrite of your database table, even if the data you are adding is much smaller. We also learned that all columns, with some exceptions, are read from the database even if we only have a few of them in our select statement. This is usually not an issue but is something to keep in mind when working on something performance critical.

We also went through indexes and how they are stored in a smart way to prevent updating them too much even if the database rows themselves move around. It also showed that very small tables do not really use indexes since the whole table itself could easily fit into one page.