Database System Architecture Overview

Database System Architecture Overview

Introduction: Why Architecture Matters

When we examine any complex engineering system—from aircraft carriers to operating systems—we find that underlying architectural decisions determine not only what the system can accomplish but also how it can evolve, scale, and fail. Database systems, which serve as the persistent backbone of virtually every significant software application in existence today, are no exception to this principle. Understanding the internal architecture of database systems is not merely an academic exercise; it is essential knowledge for software engineers, architects, database administrators, and anyone tasked with building reliable, high-performance data-intensive applications.

The architecture of a database system defines how data is organized, processed, and maintained across its entire lifecycle. It determines fundamental characteristics such as transaction semantics, concurrency control strategies, recovery mechanisms, and query optimization approaches. When we say that PostgreSQL handles complex queries better than MySQL for analytical workloads, or that Cassandra excels at write-heavy scenarios while maintaining linear scalability, we are fundamentally describing architectural decisions and their consequences. This chapter establishes the conceptual framework necessary to understand these architectural foundations, providing the vocabulary and mental models that will serve as the foundation for every subsequent chapter in this book.

Modern database systems are extraordinarily complex software artifacts. A production-grade relational database engine might contain anywhere from three to fifteen million lines of code, depending on the feature set and maturity of the implementation. This complexity did not emerge overnight; it accumulated over decades of research, engineering refinement, and practical experience with real-world workloads. By understanding the architectural patterns that have emerged from this evolution, we gain not only historical perspective but also practical insight into why contemporary databases are designed as they are—and where they might evolve next.

This chapter proceeds by first examining the layered architecture model that characterizes virtually all database systems, from the client interface down to the physical storage layer. We then explore the distinction between monolithic and modular architectural patterns, followed by an in-depth examination of each major subsystem: the query processor, storage engine, transaction manager, and recovery manager. We analyze how different workload characteristics, particularly the distinction between Online Transaction Processing (OLTP) and Online Analytical Processing (OLAP), drive different architectural choices. The chapter traces the evolution of database systems from early navigational models through relational engines to modern distributed NewSQL systems, and concludes with detailed case studies of three influential open-source database architectures.

The Layered Architecture Model

The most fundamental organizing principle in database system design is the layered architecture, which decomposes the system into distinct abstraction layers, each building upon the services provided by the layers below it. This approach, borrowed from general software engineering principles and operating system design, enables modularity, maintainability, and the possibility of substituting implementations at one layer without affecting others. Understanding these layers—and the interfaces between them—is essential for comprehending how database systems function as integrated wholes.

The Client Interface Layer

At the outermost boundary of the database system sits the client interface layer, which handles all communication between the database and the applications, tools, and users that interact with it. This layer encompasses multiple communication protocols, API implementations, and session management mechanisms that together provide the external interface to the database system.

Modern database systems typically support multiple client protocols simultaneously. The most universally supported is the structured query language itself, transmitted via protocols such as TDS (Tabular Data Stream, used by Microsoft SQL Server and Sybase), the MySQL client-server protocol, or PostgreSQL's frontend/backend protocol. These application-level protocols handle connection establishment, authentication, query submission, result transmission, and connection pooling—all the mechanics of client-server communication.

Consider the sequence of operations when a client application connects to a PostgreSQL database:

```c // Simplified illustration of PostgreSQL connection flow typedef struct { int socket_fd; ProtocolVersion protocol_version; AuthenticationType auth_type; char* database; char* user; char* application_name; } PGConnection;

PGConnection* pg_connect(const char* conninfo) { PGConnection* conn = malloc(sizeof(PGConnection));

// Parse connection string parse_conninfo(conninfo, conn);

// Establish TCP/IP connection conn->socket_fd = socket(AF_INET, SOCK_STREAM, 0); connect(conn->socket_fd, ...);

// Send startup message with protocol version StartupMessage msg = { .protocol_version = PROTOCOL_VERSION, .parameters = {conn->user, conn->database, ...} }; send_message(conn->socket_fd, msg);

// Handle authentication exchange AuthenticationPacket auth = receive_message(conn->socket_fd); switch(auth.type) { case AUTH_MD5: // Send password digest send_md5_response(conn, auth.salt); break; case AUTH_SASL: // Handle SASL authentication handle_sasl_auth(conn, auth); break; case AUTH_OK: // Authentication successful break; }

return conn; } ```

The client interface layer also implements connection pooling mechanisms, which are critical for performance in high-concurrency environments. Connection pooling reduces the overhead of establishing new database connections for each client request—a process that involves network round-trips, authentication, and resource allocation. Libraries like PgBouncer, ProxySQL, or application-level connection pools maintain a pool of pre-established connections that can be rapidly assigned to client sessions as needed.

The Query Processing Layer

Above the client interface sits the query processing layer, which transforms user queries—which arrive in declarative form as SQL statements—into executable plans that interact with lower layers to produce results. This layer is itself typically decomposed into several sub-components: parsing, query rewrite, optimization, and execution.

The parser performs lexical analysis and syntactic analysis, transforming the textual SQL query into an abstract syntax tree (AST) that represents the grammatical structure of the query. Modern SQL parsers are typically generated from formal grammars using tools like Yacc/Bison or ANTLR, ensuring consistency and completeness. Consider how a parser would break down a simple query:

```sql SELECT customer_name, SUM(order_total) FROM customers JOIN orders ON customers.customer_id = orders.customer_id WHERE order_date >= '2024-01-01' GROUP BY customer_name HAVING SUM(order_total) > 1000 ORDER BY SUM(order_total) DESC LIMIT 10; ```

The parser would recognize this as a SELECT statement with multiple clauses: FROM (with JOIN), WHERE, GROUP BY, HAVING, ORDER BY, and LIMIT. The AST representation captures this hierarchical structure, which is then passed to subsequent stages.

The query rewrite stage applies semantic knowledge and transformation rules to simplify and normalize the query without changing its semantics. These transformations include view expansion (replacing references