Unveiling MySQL, Oracle, and PostgreSQL: Inside Their Core Architecture

MySQL Architecture

MySQL can be viewed as a two‑layer architecture. The first layer, often called the SQL Layer, handles permission checks, SQL parsing, execution‑plan optimization, query cache processing and other preparatory work before any data is accessed. The second layer, the Storage Engine Layer, implements the actual data‑access operations and consists of multiple pluggable storage engines.

The SQL Layer contains many sub‑modules:

Initialization module : runs at server start‑up to allocate buffers, caches, system variables and initialize storage engines.

Core API : provides highly efficient low‑level operations such as data‑structure handling, special algorithms, string and number processing, small‑file I/O, formatted output and memory management (source code lives in mysys and strings directories).

Network interaction module : abstracts the network API used to receive and send data.

Client‑Server protocol module : implements the protocol used between MySQL clients and the server (built on TCP/IP or Unix sockets).

User module : manages login authentication and user privileges, acting as the gatekeeper.

Access control module : enforces fine‑grained permissions based on user privileges and database constraints.

Connection management, connection threads and thread management : listens for client connections, creates or reuses connection threads, and performs authorization checks.

Query parsing and dispatch module : parses incoming queries, checks the query cache for SELECT statements, and forwards the request to the appropriate handler.

Query cache module : caches result sets of SELECT queries and invalidates them when underlying tables change.

Query optimizer : chooses the best execution plan based on statistics and algorithms.

Table‑change management module : handles DML and DDL statements such as INSERT, UPDATE, DELETE, CREATE TABLE, ALTER TABLE.

Table maintenance module : performs table checks, repairs, and optimizations.

System status module : returns information for commands like SHOW STATUS and SHOW VARIABLES.

Table manager : maintains *.frm definition files and a table‑level lock cache.

Log recording module : writes error log, binary log, slow‑query log, etc.

Replication module : consists of Master and Slave parts; the Master reads binary logs and communicates with Slave I/O threads, while the Slave writes received logs to a relay log and replays them.

Storage engine interface module : provides the pluggable API that allows MySQL to use different storage engines.

After describing each module, the article explains how they cooperate during a typical request lifecycle: server start‑up, client connection, authentication, query parsing, cache lookup, optimization, execution, logging, and finally response delivery.

Oracle Architecture

Oracle follows a classic client‑server model. The client side consists of the User Process, Server Process and PGA (Program Global Area). The server side comprises the Instance (memory structures) and the Database (files).

The Instance includes the System Global Area (SGA) and the Program Global Area (PGA). The SGA contains shared memory components such as the Shared Pool (library cache and data dictionary cache), Database Buffer Cache, Redo Log Buffer, Large Pool, Java Pool, and Streams Pool. Each component is described briefly:

Shared Pool : caches recently executed SQL statements and data‑dictionary information; managed by LRU.

Database Buffer Cache : holds copies of data blocks read from data files, improving read/write performance.

Redo Log Buffer : records all changes for recovery purposes.

Large Pool : optional area used by RMAN, parallel I/O, and shared server sessions.

Java Pool : stores Java‑related code and objects.

Streams Pool : supports Oracle GoldenGate and other stream‑based features.

The Instance also runs several background processes:

PMON : cleans up failed processes, rolls back transactions, releases locks.

SMON : performs instance recovery, opens the database, and releases temporary tablespaces.

DBWn : writes dirty buffers to data files based on checkpoints, buffer limits, timeouts, etc.

LGWR : writes redo log entries to disk on commit, when the buffer is one‑third full, every few seconds, etc.

CKPT : signals DBWn, updates data‑file headers and control files.

ARCn : optional archiver that copies redo logs to archive files when archiving is enabled.

Additional optional background processes include CJQn, Jnnn, RECO, MMAN, MMON, Snnn, Dnnn, and Pnnn.

PostgreSQL Architecture

PostgreSQL is a free, open‑source object‑relational database server released under a BSD‑style license. Its architecture consists of five major subsystems: the connection manager (postmaster), the query compiler/executor, the storage manager, the transaction manager, and the system catalog.

The connection manager accepts client connections, performs initial processing and dispatches requests. The compiler/executor parses and plans queries, turning them into executable operations. The storage manager handles indexes, memory, and disk I/O. The transaction manager ensures ACID properties via a transaction manager, log manager, concurrency control, and lock manager. System catalogs store metadata about database objects and link all modules together.

This overview highlights the similarities and differences among the three major relational database systems and shows how each component contributes to overall performance, reliability, and scalability.