How Relational Databases Work: Architecture, Query Processing, and Transaction Management

Relational databases have been in use for over 40 years, and understanding their internal mechanisms is essential for developers who want to master the technology. The article focuses on how databases process SQL queries, dividing the discussion into three parts: low‑level and high‑level database components, query‑optimization workflow, and transaction & buffer‑pool management.

Basic concepts review – The article begins with a reminder of algorithmic time‑complexity (O(1), O(log n), O(n), O(n log n), O(n²)) and illustrates how different complexities scale with data size, emphasizing why algorithmic efficiency matters for database operations.

Merge sort – It explains merge sort as a fundamental algorithm for ordering data, especially when sorting results from a database, and shows how merging two sorted halves produces a fully sorted set.

Overall database architecture – The core components are listed: Process manager, Network manager, File‑system manager, Memory manager, Security manager, and Client manager. Supporting tools such as backup, recovery, monitoring, metadata management, query manager, and data manager are also described.

Client manager – Handles client connections via APIs like JDBC, ODBC, and OLE‑DB, performing authentication, request routing, load checking, and result buffering.

Query manager – Performs query validation, rewrite (eliminating unnecessary operations), optimization, compilation, and execution. Common rewrite rules include view merging, sub‑query flattening, redundant operator removal, duplicate join elimination, partition pruning, and custom rules.

Data manager – Executes queries, manages transactions, and retrieves data efficiently. It highlights the importance of caching and buffer‑pool management to mitigate disk I/O bottlenecks.

Cache manager and replacement policies – Most mainstream databases use LRU (Least Recently Used) or its variant LRU‑K to keep frequently accessed pages in memory, reducing disk reads.

Transaction manager and ACID – Describes the four ACID properties (Atomicity, Consistency, Isolation, Durability) and explains why they are crucial for reliable data modifications.

Concurrency control – Discusses shared vs. exclusive locks, lock escalation, and how lock managers track which transactions hold or wait for locks.

Deadlocks and two‑phase locking – Shows how circular wait conditions cause deadlocks and how databases use the two‑phase locking protocol (growing phase then shrinking phase) to resolve them.

In conclusion, the author encourages developers to deeply understand relational database internals, as they remain foundational despite the rise of NoSQL technologies.