Skip to content

Relational Database Foundations & ACID Guarantees: Relational Algebra to Physical Storage

Created: 2026-11-08  ·  Last updated: 2026-11-08  ·  Nejat Hakan

Executive Summary: Modern enterprise applications depend on relational database management systems (RDBMS) like PostgreSQL and MariaDB/MySQL to guarantee data correctness under heavy concurrency and catastrophic power outages. While developers interact with tables, columns, and declarative SQL queries, the database engine operates on mathematical set theory and low-level kernel storage primitives. This guide explores the architectural bridge between Edgar F. Codd’s relational model and the physical storage layer, dissecting how the four ACID pillars (Atomicity, Consistency, Isolation, Durability) are enforced through Write-Ahead Logging (WAL), Multi-Version Concurrency Control (MVCC), and kernel fsync() synchronization.

  • The Relational Model

    Decouple logical queries from physical storage: represent data as mathematical relations (tables), tuples (rows), and attributes (columns).

  • Atomicity & WAL

    Ensure "All-or-Nothing" transactions: rollback aborted changes instantly and replay uncommitted states via the Write-Ahead Log.

  • Consistency & Constraints

    Enforce business invariants at the engine layer: evaluate foreign keys, check constraints, and unique indexes deterministically.

  • Isolation & Durability

    Eliminate race conditions via MVCC snapshot isolation and guarantee crash survival through synchronous kernel fsync() flushing.

1. The Relational Paradigm: Logical Abstraction vs. Physical Storage

In 1970, mathematician and computer scientist Edgar F. Codd published a landmark paper introducing the relational model. Before Codd, databases relied on hierarchical or network architectures (such as CODASYL), where application code navigated explicit physical disk pointers and memory addresses. If a database index or storage layout changed, application programs broke.

Codd's fundamental breakthrough was data independence: decoupling how data is logically queried from how it is physically organized and stored on disk.

%%{init: {'flowchart': {'wrappingWidth': 600}}}%%
flowchart TB
    APP["Client Application<br/>(Declarative SQL over TCP/Socket)"]
    PARSE["Parser & Rewriter<br/>(Syntax Check & View Expansion)"]
    OPT["Cost-Based Optimizer<br/>(Calculates Optimal Execution Plan)"]
    EXEC["Execution Engine<br/>(Index Scans, Hash Joins, Locks)"]
    BUF["Shared Buffer Pool<br/>(In-Memory 8 KiB / 16 KiB Pages)"]
    WAL["WAL / Redo Buffer<br/>(Sequential Append-Only Log)"]
    DISK["Physical NVMe Storage<br/>(Data Files & Write-Ahead Logs)"]

    APP --> PARSE
    PARSE --> OPT
    OPT --> EXEC
    EXEC <--> BUF
    EXEC --> WAL
    BUF <--> DISK
    WAL --> DISK

Relational Terminology vs. SQL Terminology

In formal relational algebra, terminology differs slightly from standard SQL implementation:

Relational Algebra Term SQL Equivalent Physical Storage Equivalent Description
Relation Table / View Heap File / Clustered Index An unordered set of tuples sharing identical attributes.
Tuple Row / Record Data Page Slot / Heap Tuple An individual entity instance containing ordered attribute values.
Attribute Column / Field Byte Offset inside Tuple Header A named, strongly typed property belonging to a relation domain.
Domain Data Type + Constraints Machine-native Type (INT4, UUID) The set of all permissible atomic values for an attribute.
Degree (Arity) Column Count Schema Definition Metadata The total number of attributes defining the relation schema.
Cardinality Row Count Statistical Sampler (pg_class.reltuples) The current total number of tuples populated in the relation.

2. Deconstructing the Four ACID Pillars

Every mission-critical transaction relies on the ACID guarantee contract between the RDBMS and the operating system.

A. Atomicity: The All-or-Nothing Guarantee

Atomicity guarantees that a transaction composed of multiple SQL statements (INSERT, UPDATE, DELETE) is treated as a single, indivisible unit of work. Either every mutation succeeds and is recorded, or the entire operation is rolled back, leaving the database state identical to the instant before the transaction started.

-- File: /opt/db-ops/atomicity_bank_transfer.sql
BEGIN;

-- Debit Account A
UPDATE bank_accounts 
SET balance_cents = balance_cents - 50000 
WHERE account_id = 'ACC-001' AND balance_cents >= 50000;

-- Credit Account B
UPDATE bank_accounts 
SET balance_cents = balance_cents + 50000 
WHERE account_id = 'ACC-002';

-- If a power loss occurs here, Atomicity guarantees Account A is never debited alone
COMMIT;

How the Engine Enforces Atomicity

The database does not mutate physical table pages on disk in-place during the transaction. In PostgreSQL, each row version is written with transaction identifiers (xmin for creation, xmax for deletion/update) into the buffer pool. If the transaction issues ROLLBACK or the server crashes before COMMIT, the transaction status in the pg_xact commit log is marked as aborted (ABORTED), and all rows written by that transaction ID become permanently invisible to other connections.

B. Consistency: Invariant Preservation

Consistency ensures that a transaction can only transition the database from one valid state to another valid state, satisfying all declared schema rules, constraints, and relational cascades.

%%{init: {'flowchart': {'wrappingWidth': 600}}}%%
flowchart TB
    T_START["Transaction Begins<br/>State: Valid"]
    CHECK1["Evaluate Domain Types<br/>(e.g., Reject Non-Numeric)"]
    CHECK2["Verify NOT NULL & CHECK<br/>(e.g., balance >= 0)"]
    CHECK3["Validate Foreign Keys<br/>(Parent Row Must Exist)"]
    CHECK4["Check Unique Indexes<br/>(No Duplicate Keys)"]
    DEC{"All Invariants<br/>Satisfied?"}
    T_COMMIT["Transaction Committed<br/>State: Valid"]
    T_ABORT["Transaction Aborted<br/>Engine Throws Exception"]

    T_START --> CHECK1
    CHECK1 --> CHECK2
    CHECK2 --> CHECK3
    CHECK3 --> CHECK4
    CHECK4 --> DEC
    DEC -->|Yes| T_COMMIT
    DEC -->|No| T_ABORT

Unlike application-level validation (which can be bypassed by parallel threads or background scripts), consistency enforced by the RDBMS is absolute: - Domain Constraints: Rejecting invalid machine values (e.g., invalid timestamp format or string in an integer column). - Check Constraints (CHECK): Enforcing mathematical boundaries (e.g., CHECK (price_cents > 0)). - Referential Integrity (FOREIGN KEY): Preventing orphaned records when parent entities are modified or deleted. - Unique Constraints (UNIQUE): Eliminating duplicate primary identifiers at the B-Tree index level.

C. Isolation: Concurrency Without State Corruption

When hundreds of client sessions execute transactions simultaneously, isolation dictates whether mutations made by transaction T1 are visible to transaction T2 before T1 commits.

The Four Concurrency Anomalies

  1. Dirty Read: T2 reads data modified by T1 that has not yet been committed. If T1 rolls back, T2 acted upon phantom data that never officially existed.
  2. Non-Repeatable Read (Fuzzy Read): T1 reads a row. T2 updates or deletes that row and commits. When T1 re-reads the row, the values have changed.
  3. Phantom Read: T1 executes a range query (e.g., WHERE status = 'PENDING'). T2 inserts a new matching row and commits. When T1 runs the identical query again, new rows appear.
  4. Serialization Anomaly (Write Skew): Two concurrent transactions read overlapping datasets, satisfy their respective constraints independently, but their combined execution violates a global business invariant.

ANSI SQL Isolation Levels vs. Real-World Behavior

Isolation Level Dirty Read Non-Repeatable Read Phantom Read Serialization Anomaly Default Engine
Read Uncommitted Possible (MySQL) Possible Possible Possible (Treated as Read Committed in PostgreSQL)
Read Committed Prevented Possible Possible Possible PostgreSQL & MariaDB Default
Repeatable Read Prevented Prevented Prevented (PostgreSQL) Possible (Write Skew) MySQL InnoDB Default
Serializable Prevented Prevented Prevented Prevented Available on request

PostgreSQL Snapshot Isolation

In PostgreSQL, the REPEATABLE READ isolation level is implemented via Snapshot Isolation, which strictly prevents phantom reads as well as dirty and non-repeatable reads. True Serializable transactions use Serializable Snapshot Isolation (SSI) to track read-write conflict graphs and abort transactions that could cause write skew.

D. Durability: Surviving Power Failure & Crashes

Durability guarantees that once the database server reports a successful COMMIT to the client over the network, all changes are permanent and will survive an immediate operating system crash, kernel panic, or sudden hardware power cutoff.

The Dual-Write Problem & The Write-Ahead Log (WAL)

Modifying data in relational tables requires writing to multiple arbitrary 8 KiB or 16 KiB pages scattered across the disk (data heap, primary key index, secondary indexes). Performing random physical disk writes for every transaction would bottleneck throughput to single-digit transactions per second.

Instead, modern RDBMS engines utilize Write-Ahead Logging (WAL):

%%{init: {'flowchart': {'wrappingWidth': 600}}}%%
flowchart TB
    C_TX["1. Client Issues COMMIT"]
    WAL_BUF["2. Engine Writes WAL Record<br/>to In-Memory WAL Buffer"]
    FSYNC["3. Kernel Invokes fsync()<br/>Flushes WAL Buffer to Disk"]
    NVME["4. NVMe Confirms Write<br/>Data Safely in Non-Volatile Flash"]
    ACK["5. Engine Returns 'COMMIT OK'<br/>to Client Application"]
    BG_CHK["6. Background Checkpointer<br/>Lazily Flushes Dirty 8 KiB Pages"]

    C_TX --> WAL_BUF
    WAL_BUF --> FSYNC
    FSYNC --> NVME
    NVME --> ACK
    ACK -.-> BG_CHK
  1. When a row changes, the engine writes a compact binary delta record sequentially into the WAL Buffer.
  2. When COMMIT is executed, the engine issues a synchronous write and invokes the kernel fsync() system call to flush the WAL buffer from RAM to the non-volatile storage controller.
  3. Once the NVMe drive confirms that the WAL bytes are persisted, the engine sends the COMMIT OK response to the client.
  4. The actual dirty table pages in RAM (shared_buffers) are flushed lazily and asynchronously in the background by the Checkpointer process. If power fails, the engine restarts, scans the WAL from the last checkpoint, and replays all committed transactions (REDO log).

3. Practical SQL Lab: Demonstrating Transactions & Isolation

Run this demonstration inside a psql session to observe transaction boundaries and savepoints firsthand.

-- File: /opt/db-ops/transaction_mechanics_lab.sql

-- 1. Create a controlled testing schema
DROP TABLE IF EXISTS accounts CASCADE;
CREATE TABLE accounts (
    account_id INT PRIMARY KEY,
    owner_name VARCHAR(100) NOT NULL,
    balance_cents BIGINT NOT NULL CHECK (balance_cents >= 0)
);

INSERT INTO accounts (account_id, owner_name, balance_cents)
VALUES 
    (1, 'Alice Smith', 100000),
    (2, 'Bob Jones', 50000);

-- 2. Demonstration of Savepoints & Selective Rollback
BEGIN;

UPDATE accounts SET balance_cents = balance_cents - 20000 WHERE account_id = 1;
UPDATE accounts SET balance_cents = balance_cents + 20000 WHERE account_id = 2;

-- Establish an intermediate rollback checkpoint
SAVEPOINT transfer_one;

-- Attempt an invalid operation that violates the CHECK constraint
UPDATE accounts SET balance_cents = balance_cents - 200000 WHERE account_id = 1;
-- The above fails: ERROR: new row for relation "accounts" violates check constraint

-- Roll back only to the savepoint without losing the first valid transfer
ROLLBACK TO SAVEPOINT transfer_one;

-- Commit the valid transfer
COMMIT;

-- Verify final balances (Alice: 80000, Bob: 70000)
SELECT account_id, owner_name, balance_cents FROM accounts ORDER BY account_id;

4. Key Engineering Takeaways

%%{init: {'flowchart': {'wrappingWidth': 600}}}%%
flowchart TB
    ACID["ACID Core Architecture"]
    A_NODE["Atomicity<br/>Enforced via WAL & MVCC xmin/xmax"]
    C_NODE["Consistency<br/>Enforced via Schema Engine Constraints"]
    I_NODE["Isolation<br/>Enforced via Snapshot MVCC & Row Locks"]
    D_NODE["Durability<br/>Enforced via fsync() on WAL before Commit"]

    ACID --> A_NODE
    ACID --> C_NODE
    ACID --> I_NODE
    ACID --> D_NODE
  • Data Independence: The relational model isolates applications from physical storage structures. Queries declare what data is desired, while the query planner determines how to access it.
  • WAL Guarantees Throughput: Sequential writes to an append-only WAL log enable safe, durable commits without waiting for random disk writes to data files.
  • MVCC Eliminates Read Locks: Readers do not block writers, and writers do not block readers. Isolation is achieved by maintaining multiple version snapshots of each tuple in storage.