The Application Developer's View
For many application developers, a relational database (like PostgreSQL, MySQL, or Oracle) is a "magic black box." You send it a SQL query, and it reliably returns the correct data. You send it an INSERT command, and it safely stores the data forever. How it does this is a mystery, and usually, that's fine.
The DBRE's View
As a Database Reliability Engineer (DBRE), "it's magic" is not an acceptable answer. When things go wrong—when queries get slow, when the server runs out of disk space mysteriously, or when data seems inconsistent after a crash—you need to know exactly how the engine works to fix it.
Understanding the internals is the difference between blindly restarting the server and hoping for the best, and surgically fixing the root cause of a performance issue.
In this topic, we are going to open the black box and look at the fundamental machinery that makes a relational database management system (RDBMS) work. The concepts here apply across PostgreSQL, MySQL (InnoDB), Oracle, and SQL Server — the implementations differ, but the principles are universal.