DBMS Architecture and Components

The Life of a Query: A Journey Through the DBMS

A database system is an incredibly complex piece of software. To manage this complexity, engineers structure it as a set of layers or components, each with a specific responsibility. The best way to understand this architecture is to follow the journey of a single SQL query as it travels through the system.

A query's journey generally involves two major stages:

1. Query Processing: Where the DBMS figures out the best strategy to answer the query.
2. Storage & Transaction Management: Where the DBMS retrieves and manipulates the actual data, ensuring safety and correctness.

Let's start our tour with a foundational paper that first laid out this architecture.

The Architectural Layers

As you just read, a query passes through several key components. We can group these into a logical stack of layers.

To get a quick visual and auditory walkthrough of these layers, let's watch a brief segment from a UC Berkeley database course lecture.

Let's consolidate this information and break down the major functional blocks.

1. The "Brain": Relational Query Processor

This is the top-most part of the DBMS, responsible for turning a declarative SQL query (telling the system what you want) into an imperative execution plan (telling it how to get it).

• Query Parser: Checks for correct SQL syntax and translates the query text into an internal representation, often a tree structure.
• Query Analyzer & Rewriter:
  • Verifies the query against the System Catalog (or Data Dictionary), which stores metadata about tables, columns, and indexes. It resolves names and checks semantics.
  • Performs logical transformations. For example, it expands views into their base queries and may simplify predicates (e.g., NOT (price > 100) becomes price <= 100).
• Query Optimizer: This is arguably the most complex component of a DBMS. It explores thousands of possible execution plans to find the most efficient one. For a JOIN query, should it use a hash join or a merge join? Should it use an index or perform a full table scan? The optimizer makes these decisions based on cost estimations, which rely on statistics stored in the catalog.
• Query Executor: Takes the final plan from the optimizer and executes it. It makes calls to the underlying storage system to fetch and process data.

2. The "Engine Room": Transactional Storage Manager

This layer is responsible for storing, retrieving, and managing data in memory and on disk. Its design is dominated by a single, crucial fact: accessing data from disk is many orders of magnitude slower than accessing it from memory.

Let's watch a segment from a CMU lecture by Andy Pavlo that masterfully illustrates this performance gap and its implications for system design.

The Storage Manager contains several key components to tackle this I/O bottleneck:

• Buffer Manager: This is the heart of the storage engine. It manages a large in-memory cache of disk pages called the buffer pool. When the query executor needs a page, it asks the buffer manager. If the page is in the pool (a "cache hit"), it's returned immediately. If not (a "cache miss"), the buffer manager fetches it from disk, potentially evicting another page to make room. This component is critical for performance and is something we'll implement later in the course.
• Access Methods (File & Index Manager): This component is responsible for the on-disk data structures. It organizes data into files, which are collections of pages. It also creates and maintains index structures, like B+ Trees, which provide fast access paths to data, avoiding the need for full table scans.
• Disk Space Manager: Manages the allocation and deallocation of pages on disk.

3. The "Guardians": Cross-Cutting Components

Woven throughout the system are components that ensure the famous "ACID" properties (Atomicity, Consistency, Isolation, Durability).

• Lock Manager (Concurrency Control): When multiple users access the database simultaneously, the lock manager prevents them from interfering with each other's uncommitted changes. It does this by granting locks (e.g., shared locks for reading, exclusive locks for writing) on data items.
• Log Manager (Recovery): To ensure atomicity and durability, every change to the database is first recorded in a sequential, append-only file called the log. This principle is known as Write-Ahead Logging (WAL). If the system crashes, the recovery manager can use the log to redo completed transactions and undo incomplete ones, bringing the database back to a consistent state.

Process Models: Managing Concurrent Users

A final piece of the architectural puzzle is how the DBMS handles thousands of concurrent client connections. This is a core system design choice that relates directly to your knowledge of operating systems.

The paper "Architecture of a Database System" (LINK) discusses three main approaches in Section 2:

1. Process per DBMS Worker: Each client connection gets its own dedicated OS process.

   • Pros: Simple to implement and robust, as the OS provides strong isolation between processes.
   • Cons: High memory overhead per connection and expensive context switching.
   • Examples: PostgreSQL, Oracle (in its default configuration).

2. Thread per DBMS Worker: A single OS process hosts many threads, with one thread dedicated to each client connection.

   • Pros: Much lower memory overhead and faster context switching than processes. Threads can share memory (like the buffer pool) easily.
   • Cons: More complex to program correctly (e.g., race conditions, memory corruption from one thread can affect all others).
   • Examples: MySQL, Microsoft SQL Server, IBM DB2.

3. Process/Thread Pool: A fixed-size pool of worker processes or threads is created. Incoming requests are assigned to an available worker from the pool. This avoids the cost of creating a new process/thread for every connection and limits the total number of concurrent workers.

This choice profoundly impacts how shared data structures like the buffer pool and lock table are managed (e.g., using shared memory for inter-process communication vs. direct access within a multi-threaded process).

Conclusion

In this lesson, we've sketched out the architectural blueprint of a modern relational database system.

Key Takeaways:

• A DBMS is built in layers to manage complexity, primarily separating query processing from data storage and transaction management.
• A query's journey begins at the query processor, where it is parsed, rewritten, optimized into an efficient plan, and then executed.
• The storage manager's design is driven by the need to overcome the massive performance gap between main memory and disk, with the buffer manager being the central component for this.
• Cross-cutting components like the lock manager and log manager provide concurrency control and recovery, ensuring the ACID properties.
• The choice of a process model (e.g., process-per-worker vs. thread-per-worker) is a fundamental design decision that dictates how concurrency is handled.

Preview of the Next Lesson:

We've established that the storage hierarchy is central to DBMS architecture. In the next lesson, we'll take a closer look at the bottom of that hierarchy. We will compare the performance characteristics of HDDs, SSDs, and main memory for database workloads, setting the stage for understanding how we design the data structures that live on them.