📘 How is a DBMS Implemented?
This is a deep systems-level question that FAANG and equivalent companies love — because it tests how well you understand what’s under the hood of a DBMS, not just how to use it.
💡 TL;DR:
A DBMS is a complex software system implemented using a mix of:
-
File systems
-
Data structures
-
Memory management
-
Algorithms
-
Concurrency control
-
Storage engines
Let’s break it down into layers — like an operating system or compiler.
🧱 1. Storage Manager (Lowest Layer)
🔍 What it does:
-
Manages how data is stored on disk
-
Handles files, pages, and blocks
-
Controls buffer management and disk I/O
🔧 Includes:
-
Data files – raw storage of tables and indexes
-
System catalog – stores metadata like schemas, constraints
-
Buffer manager – moves data between RAM and disk
-
File manager – reads/writes blocks of disk
🧠 2. Query Processor
This is the brain of the DBMS that understands SQL and turns it into actual data operations.
🔍 What it does:
-
Parses SQL queries
-
Optimizes the query plan
-
Executes the optimized plan
🔧 Includes:
-
Parser – checks syntax and builds a query tree
-
Optimizer – chooses the best plan (e.g., use index vs full scan)
-
Executor – actually runs the query plan step-by-step
🔒 3. Transaction Manager
Handles ACID properties (Atomicity, Consistency, Isolation, Durability).
🔍 What it does:
-
Starts and ends transactions
-
Maintains logs for recovery
-
Manages locks to handle concurrent users
🔧 Includes:
-
Concurrency control (2PL, MVCC, etc.)
-
Logging system (Write-Ahead Log - WAL)
-
Deadlock detection
🛡️ 4. Authorization & Security Manager
Controls:
-
Who can do what (e.g., SELECT, INSERT)
-
What views and roles exist
-
Data privacy
🧪 Real DBMS Stack:
Let’s take PostgreSQL as an example.
| Layer | Tech/Component |
|---|---|
| Disk I/O | OS + file system |
| Storage Engine | PostgreSQL heap files |
| Buffer Manager | Shared memory buffers, LRU cache |
| Query Processor | SQL parser + planner + executor |
| Transaction Manager | MVCC + WAL logs |
| Concurrency | Lock manager |
| APIs | SQL, C-language functions, PL/pgSQL |
🧠 Interview Line:
“A DBMS is implemented in layered architecture — with a storage manager handling data blocks, a query processor parsing and optimizing SQL, a transaction manager enforcing ACID, and security layers ensuring controlled access.”
🧠 Bonus for FAANG Interviews:
If asked how you would design a DBMS, mention:
-
Storage with B+ Trees or LSM Trees
-
Query parsing with abstract syntax trees (AST)
-
Optimizer with cost-based planning
-
MVCC for isolation
-
WAL for durability
-
Background processes for vacuuming/compaction