Skip to content
Tutorials Center
My learning

Topics

Lesson 3 of 79 ยท Introduction to Databases

DBMS Architecture and Components

Tour the parts of a DBMS (query processor, storage manager, transaction manager, buffer manager, catalog), learn 1-, 2- and 3-tier designs, and read SQLite's own catalog.

A DBMS is a large program, but it is not a single lump. Almost every system divides its work among a handful of cooperating components. A query arrives, gets understood, gets turned into a plan, runs against data that lives in pages on disk, and the answer returns, all while other users do the same, and while the system keeps a log so that a crash cannot corrupt anything.

This lesson draws the map. Later chapters zoom into each region: transactions, concurrency control, recovery, storage and query processing. Knowing the overall picture first makes each of them easier to place.

The major components

Query side

Parser Checks the SQL text and builds a tree.
Optimizer Chooses the cheapest way to get the answer.
Executor Runs the chosen plan step by step.
1 / 3

The query processor

The query processor turns SQL into results. The parser checks syntax and that the named tables and columns exist. The optimizer rewrites the query and compares possible plans using statistics. The executor (also called the evaluation engine) runs the chosen plan, calling the storage layer for rows. See The Query Processing Pipeline.

The storage manager

The storage manager is the interface between the database and the disk. It owns the data files, the index structures and the layout of records inside fixed-size pages. Its sub-parts include the file manager (space allocation), the buffer manager, and the index manager.

The buffer manager is a cache. Disk is thousands of times slower than memory, so the buffer pool holds recently used pages in RAM and decides which page to evict when full (Disks, Pages and the Buffer Pool).

The transaction manager

The transaction manager makes sure that groups of operations obey the ACID properties (ACID Properties in Depth). It works with the concurrency-control manager (locks or versions, so users do not interfere) and the recovery manager (the log, so that a crash does not lose committed work or keep half-finished work).

The catalog (data dictionary)

The catalog is a set of internal tables that describe the database itself: tables, columns, types, indexes, constraints, users and optimizer statistics. It is metadata, "data about data". Since the catalog is just tables, you can often query it with SQL, as you will do below.

Following one query through the system

The sequence below is simplified. Real systems add caches for parsed plans, permission checks and many optimizations, but the flow is the same.

sequenceDiagram
    participant App as Application
    participant QP as Query processor
    participant TM as Transaction manager
    participant BM as Buffer manager
    participant Disk as Disk files
    App->>QP: SELECT ... FROM books
    QP->>QP: parse, optimize
    QP->>TM: begin, take locks
    QP->>BM: get page 7
    BM->>Disk: read page 7 (if not cached)
    Disk-->>BM: page bytes
    BM-->>QP: rows
    QP-->>App: result set

A read query passes through the query processor, the transaction manager and the buffer manager before touching disk.

Application architectures: one, two and three tiers

A DBMS rarely stands alone. How an application reaches it is described in tiers.

  • One-tier. The application and the database engine run in one process, on one machine. SQLite works this way: it is a library linked into your program, and the database is a single file. This is called an embedded database.
  • Two-tier (client-server). A client application (a desktop program, or a script) connects directly to a DBMS server over the network. PostgreSQL, MySQL and SQL Server work this way.
  • Three-tier. A client (browser or mobile app) talks to an application server, which holds business logic and is the only part that talks to the database server. This is the shape of most websites: the browser never gets database credentials.
flowchart LR
    B["Browser or mobile app"] --> A["Application server<br/>(business logic)"]
    A --> D[("Database server")]

In a three-tier design only the application server can reach the database.

Property Embedded (SQLite) Client-server (PostgreSQL, MySQL)
Process Inside your application Separate server process
Setup None; one file Install, configure, users
Concurrent writers One at a time Many, with fine-grained locking
Network access No Yes
Best for Apps, tests, small sites, device storage Shared multi-user systems

Reading the catalog yourself

SQLite keeps its catalog in a table called sqlite_master. It has one row for every table, index, view and trigger, including the original CREATE statement. The next example builds a small schema and then asks the engine to describe itself.

SQL SQLite describes its own schema

Press Run (or Ctrl + Enter) to see the result here.

You see two tables, an index and a view; the catalog treats all schema objects uniformly. The extra column sql holds the text you typed, which is how SQLite can recreate the schema later.

Column-level metadata is available too:

SQL Columns, types and keys

Press Run (or Ctrl + Enter) to see the result here.

pragma_table_info is SQLite's way of returning catalog information as rows. Other systems expose the same idea differently: the standard information_schema views in PostgreSQL, MySQL and SQL Server, or ALL_TABLES and friends in Oracle.

SQL The catalog of the bookstore ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

What is read-only here. The query processor, buffer manager and lock manager inside a real server cannot be inspected from SQL. SQLite has them too, but in a small form: a single writer, a page cache, and a rollback journal or write-ahead log. Server features such as separate roles and network protocols are not available in the browser.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Which component decides which of several equivalent execution plans to use?

    The optimizer estimates the cost of alternative plans, using catalog statistics, and picks the cheapest.
  2. 2. What is the main job of the buffer manager?

    Disk access is slow, so the buffer pool caches pages in RAM and chooses pages to evict when it is full.
  3. 3. Why is SQLite called an embedded database?

    SQLite is a library. There is no separate server process or network protocol.
  4. 4. In a three-tier web application, which tier normally connects to the database server?

    Only the application server holds database credentials. Clients talk to it, never to the database directly.
  5. 5. What is the catalog?

    The catalog (data dictionary) is data about data, stored in system tables such as sqlite_master.

Summary

  • A DBMS has a query processor (parser, optimizer, executor), a storage manager (files, indexes, buffer manager), a transaction manager (concurrency control and recovery) and a catalog.
  • The buffer manager caches pages; the transaction manager provides ACID; the catalog stores metadata and statistics.
  • Applications are one-tier (embedded), two-tier (client-server) or three-tier (client, application server, database server).
  • SQLite is embedded; PostgreSQL, MySQL and SQL Server are client-server.
  • The catalog is queryable: sqlite_master and pragma_table_info in SQLite, information_schema elsewhere.

Next: how the system separates what users see from how data is stored, in Schemas, Instances and Data Independence.