The Architecture of a DBMS

The architecture of a DBMS is organized in layers, with each layer abstracting the layer below to manage complexity. Each layer makes performance assumptions of the layer below.

However, there are two cross-cutting issues related to storage and memory management: concurrency control (multiple user) and recovery (recover data when the database crashes).

Now lets go through a lifetime of an SQL query, with some details omitted (e.g. process management).

1. The client calls an API that in turn communicates over a network to establish a connection with the client communication manager.
2. Query parsing & optimization: compile the user's SQL query text into an internal query plan.
3. The compiled query plan is handled via the plan executors, consisting of a suite of “operators” (relational algorithm implementations) for executing any query. These operators make calls to fetch data from the DBMS's transactional storage manager.
4. The storage system manages all data access and manipulation calls. The storage system includes algorithms and data structures for organizing and accessing data on disk ("access methods"), including basic structures like tables and indexes, and a buffer management module that decides when and what data to transfer between disk and memory buffers.
5. “Unwinding the stack” after data records are accessed.