How does a database really work?

The journey of an INSERT inside InnoDB

You execute a query and get a result in milliseconds.

But inside the database, nothing is simple, and nothing is direct.

Between your SQL and the actual disk sits a full system of memory buffers, logs, and tree structures working together to make it look effortless.

Let’s open the hood of InnoDB and follow a single INSERT, step by step.

Image generated by ChatGPT

Under the hood

If you’ve ever worked with databases like MySQL or MariaDB, you know you interact with them through Structured Query Language (SQL), for example:

SELECT * FROM users WHERE id = 10;

But this is only an interface. The query itself doesn’t directly manipulate data. The actual work is handled by the storage engine — by default, InnoDB in both MySQL and MariaDB.

The tablespaces

When using InnoDB, your data is stored in units called tablespace. Each tablespace corresponds to a file on your disk.

Note: There are two main modes of handling the tablespaces depending on the innodb_file_per_table value (on or off). In this article will consider the default value: file per table enabled.