1 What a DBMS really is — beyond "a place to store data"
Imagine a giant library that millions of people visit at the same time. Some are reading books, some are scribbling new pages, some want every book about dinosaurs right now. If everyone just grabbed shelves themselves it would be chaos — torn pages, two people writing in the same book, and a fire would destroy everything. So the library hires a super-librarian who fetches things fast, never loses a page even if the lights go out, lets everyone work at once without bumping into each other, and can find any book by description. A DBMS is that super-librarian for your data.
A DBMS (Database Management System) is the software that sits between your application and your raw data and manages it for you. The database is the actual collection of data; the DBMS is the program that stores, protects, and answers questions about it. Famous examples are PostgreSQL, MySQL, SQLite, and Oracle.
Why not just use files?
The most natural question for a beginner: I can already save data in a .txt
or .csv file — why do I need a whole system? Because the moment your data
becomes valuable, shared, and large, plain files fall apart. Here's what a DBMS gives you that a folder
of files does not.
| Service | What it means | Why files can't easily do it |
|---|---|---|
| Durability | Once the DBMS says "saved," the data survives crashes, power loss, and restarts. | A half-written file after a power cut is corrupt; you lose data silently. |
| Concurrency | Many users read & write at the same time without corrupting each other's work. | Two programs writing one file overwrite each other — last writer wins, data lost. |
| Querying | Ask high-level questions ("all orders over $100 from June") and get answers fast. | You'd hand-write code to scan and filter every file, every time. |
| Recovery | After a crash, the system rebuilds a clean, consistent state automatically. | No automatic way to undo a half-finished change across many files. |
| Efficiency | Indexes & buffering make lookups in billions of rows take milliseconds. | Linear scans of huge files are painfully slow. |
| Integrity | Rules (types, uniqueness, relationships) are enforced for you. | Nothing stops you writing a phone number into the "age" column. |
A DBMS isn't a fancy filing cabinet — it's a set of guarantees. You hand it your data, and in return it promises your data won't be lost, won't be corrupted by other users, can be queried by describing what you want (not how to fetch it), and can be repaired after a crash. Everything inside the box exists to keep one of those promises.
That bundle of promises around reads and writes has a famous name you'll hear constantly: ACID — Atomicity, Consistency, Isolation, Durability. Don't memorise it yet; just know that "durability," "concurrency," and "recovery" above are the DBMS keeping the ACID promises. We'll devote a whole later session to transactions and ACID.
The 5-layer mental model
The cleanest way to hold a database in your head is as five layers stacked on top of each other. A request enters at the top and travels down toward the disk; data flows back up. Each layer only talks to its neighbours, which is exactly why the system stays understandable.
Reading top to bottom: you write SQL, the query processor figures out the best plan, the execution layer runs that plan using operators like joins and scans, the storage manager fetches the right pages of data (from memory if it can, disk if it must), and underneath sits the operating system and physical disk. The rest of this session zooms into the middle layers — and the rest of this subject builds them, layer by layer.
Goal: "give two users $50 each from a shared $100 budget, and never let the total go negative."
With files: you read budget.txt, subtract, write it back.
If two programs do this at once, both read 100, both write 50 — you gave away $100 but the file says
$50. Money invented out of thin air. If the power dies mid-write, the file is garbage.
With a DBMS: you wrap both updates in a transaction. The system locks the row so the two updates can't interleave, checks the rule, and either commits both changes or neither — and once committed, a crash can't undo it. Same task, but the guarantees come for free.
2 The architecture of a database system
Think of a restaurant. The waiter takes your order and reads it back to make sure it makes sense (that's the part that understands your request). The head chef decides the smartest way to cook everything — what to start first so the meal comes out fast (planning). The line cooks actually fry and chop (doing the work). The pantry manager knows exactly which shelf every ingredient is on and keeps the most-used ones close at hand (storage). And a manager makes sure two cooks don't grab the last egg at once and that nothing is lost if the gas goes out (transactions). A database has these exact same roles.
Inside the box, a DBMS is organised into a handful of major components, each owning one job. Three big ones do most of the heavy lifting: the query processor, the storage manager, and the transaction manager. Let's meet each, then see them wired together.
The labeled component diagram
┌───────────────────────────────────────────────────────────┐
│ CLIENT / APPLICATION │
│ (sends SQL over a connection) │
└───────────────────────────┬───────────────────────────────┘
│ SQL text
┌───────────────────────────▼───────────────────────────────┐
│ QUERY PROCESSOR │
│ ┌──────────┐ ┌───────────┐ ┌──────────────────────┐ │
│ │ Parser │ → │ Optimizer │ → │ Executor (operators)│ │
│ └──────────┘ └───────────┘ └──────────┬───────────┘ │
└─────────────────────────────────────────────┼─────────────┘
│ asks for rows/pages
┌──────────────────────────┐ ┌────────────────▼─────────────┐
│ TRANSACTION MANAGER │◄─┤ STORAGE MANAGER │
│ ┌─────────────────────┐ │ │ ┌────────────┐ ┌─────────┐ │
│ │ Concurrency control │ │ │ │ Buffer pool│ │ Access │ │
│ │ (locks / MVCC) │ │ │ │ (cache) │ │ methods │ │
│ ├─────────────────────┤ │ │ └─────┬──────┘ │(B-trees)│ │
│ │ Recovery / WAL log │ │ │ │ └─────────┘ │
│ └─────────────────────┘ │ └────────┼─────────────────────┘
└──────────────────────────┘ │ read/write pages
┌───────────▼───────────────┐
│ DISK (data files + log) │
└────────────────────────────┘
Component 1 — the query processor
The query processor turns the SQL text you sent into actual work. It has three sub-parts:
- Parser — reads the SQL string and checks it's grammatically valid, then
builds a tree structure from it (the way a sentence diagram breaks "The cat sat" into subject/verb).
It also binds names: does table
usersexist? Is columnagereally there? - Optimizer — there are usually many ways to compute the same answer, and they differ wildly in speed. The optimizer estimates the cost of each plan and picks the cheapest. This is one of the most sophisticated parts of any DBMS; we devote a whole later session to it.
- Executor — runs the chosen plan by pulling rows through a chain of operators (scan, filter, join, sort, aggregate). It's the line cook actually doing the cooking.
Component 2 — the storage manager
The storage manager owns the bytes. The executor never touches the disk directly; it asks the storage manager for data. Its two stars:
- Buffer pool — disk is slow (milliseconds) and RAM is fast (nanoseconds), so the DBMS keeps a big cache of recently-used pages (fixed-size blocks of data, often 4 KB or 8 KB) in memory. If the data you need is already cached, no disk trip is needed. Managing what stays and what gets evicted is a core job (Session 2).
- Access methods — the data structures that organise rows on disk and let you find them fast. The workhorse is the B+ tree index, which turns a "find this value" lookup from scanning millions of rows into a few cheap hops. We build these later too.
Component 3 — the transaction manager
The transaction manager is what makes a DBMS trustworthy under pressure. It has two halves:
- Concurrency control — lets many transactions run at once while making the result look as if they ran one-at-a-time. It does this with locks or with MVCC (Multi-Version Concurrency Control, where readers see an old snapshot instead of waiting for writers). This is the "isolation" in ACID.
- Recovery — before changing the real data, the DBMS first writes what it's about to do into a write-ahead log (WAL). If the system crashes, on restart it replays the log to redo committed work and undo half-finished work, restoring a clean state. This is the "durability" and "atomicity" in ACID.
The components are deliberately separated so each can be understood — and built — on its own. The query processor decides what to do, the storage manager handles where the bytes live, and the transaction manager guarantees correctness under crashes and concurrency. Almost every database in the world follows this same three-way split.
You run UPDATE accounts SET balance = balance - 50 WHERE id = 7;
- Parser: valid SQL? does
accounts.balanceexist? ✔ - Optimizer: best way to find row id 7 — use the primary-key index, not a full scan.
- Executor: ask storage for that row.
- Transaction manager: take a lock on row 7 so no one else edits it; write the change to the WAL first.
- Storage manager: update the page in the buffer pool; it'll be flushed to disk later.
Five components, one small statement, every guarantee upheld.
3 The query processing pipeline — end to end
You tell a travel agent, "I want to be in Paris on Friday, cheaply." You didn't say which flights, trains, or buses — you described the goal. The agent checks your request makes sense, looks up real routes, compares prices, picks the best plan, books it, and hands you a ticket. SQL works the same way: you describe the result you want, and the database figures out the cheapest route to get it. This topic follows your request through that whole agency.
SQL is a declarative language: you say what you want, not how to compute it. That's wonderful for you, but it means the database has to do real work to translate your wish into machine steps. Here is that translation, stage by stage.
What each stage does
Let's trace one query the whole way through:
SELECT u.name, COUNT(o.id) AS orders FROM users u JOIN orders o ON o.user_id = u.id WHERE u.country = 'IN' GROUP BY u.name;
- 1. SQL — the raw text above arrives as a string over the connection.
- 2. Parse — the parser checks grammar (keywords in the right order, balanced
parentheses) and builds a parse tree / abstract syntax tree. A typo like
SELCTdies here with a syntax error. (We'll dig into parsing in a later session.) - 3. Bind — also called analysis or
semantic analysis. It resolves names against the
catalog (the database's internal directory of tables and columns): do
usersandordersexist? Iscountrya real column? Is comparing it to a string valid? This is where "column does not exist" errors come from. - 4. Optimize — the heart of the pipeline. The optimizer rewrites the query into an
equivalent but cheaper form and chooses a physical query plan: should it use
the index on
orders.user_idor scan the whole table? Should the join be a hash join or a nested-loop join? It uses statistics about your data to estimate costs. (A whole session is coming on the optimizer.) - 5. Execute — the executor runs the chosen plan, pulling rows through operators (scan → join → group/aggregate), asking the storage manager for pages as needed.
- 6. Results — the finished rows stream back to your client.
Every real database lets you peek at the chosen plan with EXPLAIN:
EXPLAIN SELECT * FROM users WHERE id = 7; -- e.g. PostgreSQL output: -- Index Scan using users_pkey on users (cost=0.29..8.30 rows=1 ...) -- Index Cond: (id = 7)
That "Index Scan" line is the optimizer telling you it decided to jump straight to row 7 via the primary-key index instead of reading every row. Change the query and the plan changes — you're watching stage 4 make decisions.
The optimizer actually produces two things in sequence: a logical plan (the relational operations — "join these, then filter, then group") and a physical plan (the concrete algorithms — "hash-join, then index scan"). Same answer, but the physical plan commits to how. Keeping these separate is what lets the optimizer shop around for the fastest implementation.
Two queries that return identical results can differ in speed by thousands of times purely because of the plan chosen. Most "the database is slow!" problems are really "the optimizer picked a bad plan" problems — often because it lacked an index or had stale statistics. That's why understanding this pipeline pays off for the rest of your career.
EXPLAIN
to see it.
4 A tour of real systems
Databases come in different shapes for different jobs, just like vehicles. A city bus (PostgreSQL) is big, shared, and carries many passengers at once on its own dedicated route. A bicycle (SQLite) is tiny, lives in your backpack, and is just for you — no driver, no schedule, no station. Both get you somewhere; you pick based on whether you're moving a crowd or popping to the shop. Let's look at two real databases built on the same ideas we just learned, but shaped very differently.
Everything in Topics 1–3 is the theory. Real systems implement it with very different trade-offs. Comparing two famous ones — PostgreSQL and SQLite — makes the architecture concrete.
PostgreSQL — the classic client/server, disk-based database
PostgreSQL is a full-featured database that runs as its own
server process your applications connect to over the network. Its defining design choice
is process-per-connection: when a client connects, a supervisor (the
postmaster) forks a dedicated backend process to serve just that
connection. Shared resources — most importantly the buffer pool and the WAL — live in
shared memory that all backends can reach.
- It is disk-oriented: the source of truth lives on disk, and the buffer pool caches hot pages in RAM. It assumes your data is bigger than memory.
- It uses MVCC for concurrency, so readers don't block writers and vice versa.
- It has a sophisticated cost-based optimizer — exactly the stage-4 machinery from Topic 3.
- It's built for many simultaneous users, durability, and rich SQL — the "city bus."
SQLite — the embedded, single-file database
SQLite takes the opposite stance. There is no server: it's a library you link directly into your program, so the database engine runs inside your application's own process. The entire database — tables, indexes, everything — is one ordinary file on disk.
- Embedded: no separate process, no network, no configuration. Your app calls functions; SQLite reads and writes the file.
- Concurrency is simpler and coarser (historically one writer at a time), which is fine for its target uses: phone apps, browsers, desktop software, small websites. It is the most widely deployed database on Earth precisely because it's invisible.
- Still fully ACID, still has a query optimizer and a B-tree storage engine — the same ideas, just packaged tiny. The "bicycle."
| Dimension | PostgreSQL | SQLite |
|---|---|---|
| Deployment | Standalone server process | Library embedded in your app |
| Architecture | Process-per-connection + shared memory | Runs inside the app's process |
| Storage | Many files in a data directory | One single file |
| Concurrency | Many writers via MVCC | Coarse-grained; typically one writer |
| Best for | Multi-user apps, servers, large data | Phones, browsers, local/embedded apps |
| Both have | SQL, a query optimizer, B-tree indexes, ACID transactions, a WAL | |
Disk-oriented vs in-memory
A second axis worth knowing. A disk-oriented database (like PostgreSQL)
assumes data lives on disk and is paged into a buffer pool as needed — built for datasets bigger than RAM.
An in-memory database (like Redis, or VoltDB, or SQLite running in
:memory: mode) keeps the whole dataset in RAM for blazing speed, trading away
the ability to exceed memory and (sometimes) some durability. The buffer pool you'll build in Session 2 is
precisely the bridge a disk-oriented system uses to feel as fast as memory for hot data.
The whole point of this subject is that you'll build a small but real database from scratch — the
MiniDB capstone — over the coming sessions and lab assignments. We'll start
disk-oriented and SQLite-flavoured (embedded, single-file, one process), because it's the simplest shape
that still teaches every core idea. Roughly: Session 2 a page-based storage engine and
buffer pool, then access methods (B+ trees), then a parser, an executor with real operators, an
optimizer, and finally transactions with a WAL. By the end, MiniDB will run a real
SELECT end-to-end through every layer of Topic 1's stack.
This identical statement runs on both systems and gives the same answer:
CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT); INSERT INTO users VALUES (7, 'Ada'); SELECT * FROM users WHERE id = 7;
On PostgreSQL a dedicated backend process handles it, reading shared-memory buffer-pool pages. On SQLite the very same logic runs as function calls inside your program, reading one file. Same architecture from Topic 2, two wildly different packagings.
★ Putting it all together
You just got the full map of a database. Here's the one-paragraph story that connects all four topics:
A DBMS exists to give your data a bundle of guarantees — durability, concurrency, querying, recovery — that plain files can't, and we picture it as five layers from SQL down to disk. Inside, three components do the work: a query processor (parser → optimizer → executor), a storage manager (buffer pool + B+ tree access methods), and a transaction manager (concurrency control + WAL recovery). A query flows through the pipeline — SQL → parse → bind → optimize → execute → results — where the optimizer's plan choice decides most of the speed. Real systems package this differently: PostgreSQL as a disk-oriented client/server bus, SQLite as an embedded single-file bicycle — and our MiniDB capstone will rebuild the whole stack ourselves in the sessions ahead.
Quick self-check
Name three things a DBMS gives you that a folder of plain files does not.
Any three of: durability (survives crashes), concurrency (many safe simultaneous users), declarative querying, automatic crash recovery, efficient indexed lookups, and integrity rules.
What are the three major components of a database, and what does each own?
The query processor (parser/optimizer/executor) decides what to do; the storage manager (buffer pool + access methods) owns the bytes and caches hot pages; the transaction manager (concurrency control + recovery/WAL) keeps things correct under crashes and concurrent users.
Put the query pipeline stages in order.
SQL → parse → bind → optimize → execute → results. Parsing builds a tree, binding resolves names/types against the catalog, the optimizer picks the cheapest plan, the executor runs it.
Why can two queries returning the same answer have wildly different speeds?
Because the optimizer can choose different physical plans (e.g. index scan vs full table scan, hash join vs nested-loop join). The chosen plan — not the result — determines the work done.
How is SQLite architecturally different from PostgreSQL?
SQLite is an embedded library that runs inside your application's process and stores the whole database in a single file, with coarse concurrency. PostgreSQL is a standalone server using process-per-connection and shared memory, built for many concurrent writers via MVCC. Both still use SQL, an optimizer, B-trees, ACID, and a WAL.
What's the difference between a disk-oriented and an in-memory database?
A disk-oriented database keeps the source of truth on disk and pages data into a buffer pool as needed (handles data bigger than RAM); an in-memory database keeps the whole dataset in RAM for speed, trading away the ability to exceed memory.
📚 References & Further Reading
Class material
- 📄 Original course notes / handout (source sheet) — open the shared class material for this session.
- DBMS Session 1 — DBMS Architecture: the class handout accompanying this session, including the MiniDB capstone project brief and the related lab assignments referenced throughout the course.
Papers, docs & deep dives
- CMU 15-445/645 — Database Systems (Andy Pavlo) — the gold-standard free university course on database internals; its lecture order closely mirrors the layers and components we use here.
- Hellerstein, Stonebraker & Hamilton — "Architecture of a Database System" — the classic survey paper that lays out the exact component breakdown (query processor, storage, transactions) from Topic 2.
- PostgreSQL Internals documentation — the official reference for how a real disk-oriented, process-per-connection database is structured (Topic 4).
- SQLite — Architecture of SQLite — a short, readable overview of the embedded single-file engine and its parser/optimizer/B-tree layers.
- PostgreSQL — Using EXPLAIN — the official guide to reading query plans, the perfect companion to the pipeline in Topic 3.