1.2 The PostgreSQL Relational Model & Architecture Principles

1.2 The PostgreSQL Relational Model & Architecture Principles

Architecture Principle: PostgreSQL is an Object-Relational Database Management System (ORDBMS) built upon a process-based client-server model. It guarantees full ACID transaction compliance through Multi-Version Concurrency Control (MVCC) and Write-Ahead Logging (WAL), coordinating private backend processes via shared memory data caches (shared_buffers).

Understanding PostgreSQL's underlying architectural philosophy is fundamental to mastering its administration. Unlike systems designed as simple relational engines or multithreaded monolithic servers, PostgreSQL was conceived as an extensible, object-relational platform capable of defining complex data types, operators, and functions directly within the database catalog.

To succeed on the EDB Associate exam, administrators must understand the historical design choices that shaped PostgreSQL, the process-per-connection execution model, the memory structure, and the concurrency mechanisms that enforce relational integrity without locking out read operations.

---

Historical Heritage: From UC Berkeley POSTGRES to Open-Source PGDG

PostgreSQL’s lineage is rooted in academic research that fundamentally reshaped database systems theory:

1. The POSTGRES Project (1986–1994)

In 1986, Professor Michael Stonebraker (recipient of the 2014 ACM A.M. Turing Award) founded the POSTGRES (Post-Ingres) research project at the University of California, Berkeley. The goal was to solve the limitations of first-generation relational systems (such as Ingres and System R), which could only store simple flat scalar types (integers, strings, floats).

Stonebraker's team pioneered several foundational concepts:

• Object-relational modeling: Support for complex objects, composite attributes, and table inheritance.
• User-defined abstract data types: Enabling developers to define new data types and specify how storage, indexing, and comparison operators behave.
• Active database rules: An automated trigger and rule system capable of executing code in response to database events.
• POSTQUEL query language: An early proprietary declarative query language used by POSTGRES before SQL became the universal industry standard.

2. Postgres95: The Transition to SQL (1994–1995)

In 1994, two UC Berkeley graduate students, Andrew Yu and Jolly Chen, made a critical contribution: they replaced the complex and non-standard POSTQUEL query language with an ANSI SQL translation engine. The resulting project was released to the public on the internet as Postgres95.

3. The PostgreSQL Global Development Group (1996–Present)

In 1996, development of Postgres95 moved beyond the academic confines of UC Berkeley into the global software community. The project was renamed PostgreSQL to reflect its adherence to SQL standards while retaining its POSTGRES architectural identity (releasing version 6.0 in 1997).

The project is governed by the PostgreSQL Global Development Group (PGDG), an independent, vendor-neutral open-source consortium. PostgreSQL is distributed under the liberal PostgreSQL License (a permissive open-source license akin to BSD or MIT). Crucially, no single commercial corporation owns PostgreSQL; vendors like EnterpriseDB, Microsoft, Amazon, and Red Hat contribute engineers to the core team and community alongside independent developers worldwide.

---

Object-Relational Capabilities and Schema Architecture

PostgreSQL is formally classified as an Object-Relational Database Management System (ORDBMS). While fully supporting standard relational tables and relational calculus, its catalog-driven design allows administrators and developers to extend the database engine dynamically:

1. Extensible Type System

Unlike traditional databases with a static list of hardcoded data types, PostgreSQL stores type definitions as rows in the system catalog (pg_type). Administrators can create:

• Composite Types: Structures combining multiple attributes (CREATE TYPE address_t AS (street text, city text, postal_code text);).
• Enumerated Types (ENUM): Static, ordered sets of values (CREATE TYPE order_status AS ENUM ('pending', 'processing', 'shipped', 'cancelled');).
• Range Types: Built-in and user-defined intervals (int4range, tsrange, daterange) that support discrete boundary comparisons and overlap operators (&&).
• Domain Types: User-defined types based on underlying primitive types with attached integrity constraints:

  CREATE DOMAIN valid_email AS text 
  CHECK (VALUE ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');
  

2. User-Defined Functions and Procedural Languages

PostgreSQL executes functions inside the server engine across multiple pluggable languages:

• PL/pgSQL: PostgreSQL's native procedural language, offering control structures, loops, cursors, and exception handling.
• External trusted/untrusted languages: PL/Python, PL/Perl, PL/Tcl, and compiled C functions.
• Function Volatility Classifications:
  • VOLATILE: Function can modify the database or return different values across calls with identical arguments (e.g., random(), clock_timestamp()).
  • STABLE: Function cannot modify data and returns identical values for identical arguments within a single table scan or statement execution (e.g., now(), current_date).
  • IMMUTABLE: Function cannot modify data and always returns the exact same result given the same arguments forever (e.g., 2 + 2, mathematical functions). This classification enables the query planner to pre-evaluate expressions and build functional indexes.

3. Schemas and Logical Namespaces

Within any PostgreSQL database, objects (tables, views, sequences, functions) reside inside schemas. Schemas act as logical namespaces:

• Avoid naming collisions between different applications sharing a database.
• Allow granular role-based security: DBAs grant usage on specific schemas rather than entire databases (GRANT USAGE ON SCHEMA billing TO app_billing;).
• Every database contains a default schema named public.

4. Table Inheritance

PostgreSQL provides table inheritance modeled on object-oriented programming:

CREATE TABLE cities (
    name       text,
    population real,
    elevation  int
);

CREATE TABLE capitals (
    state      char(2)
) INHERITS (cities);

Querying SELECT * FROM cities; returns rows from both cities and its child table capitals. To query only the parent table without child rows, the SQL syntax requires the ONLY keyword: SELECT * FROM ONLY cities;.

Exam Tip: In modern PostgreSQL versions, declarative table partitioning (CREATE TABLE ... PARTITION BY RANGE / LIST / HASH) has superseded table inheritance for performance and data warehousing. However, understanding inheritance remains an essential object-relational concept on EDB exams.

---

Core Server Architecture: The Process and Memory Hierarchy

PostgreSQL does not use a multithreaded architecture for handling client sessions. Instead, it relies on a robust process-based client-server model that provides operating-system-level fault isolation.

+-------------------------------------------------------------------------+
|                       PostgreSQL Server Architecture                    |
+-------------------------------------------------------------------------+
| [Client Session 1]        [Client Session 2]        [Client Session N]  |
|         │                          │                          │         |
|   (libpq / TCP)              (libpq / TCP)              (libpq / TCP)   |
|         ▼                          ▼                          ▼         |
| ┌───────────────┐          ┌───────────────┐          ┌───────────────┐ |
| │ Backend (PID) │          │ Backend (PID) │          │ Backend (PID) │ |
| │ Local Memory  │          │ Local Memory  │          │ Local Memory  │ |
| │ (work_mem)    │          │ (work_mem)    │          │ (work_mem)    │ |
| └───────┬───────┘          └───────┬───────┘          └───────┬───────┘ |
|         │                          │                          │         |
| ════════╪══════════════════════════╪══════════════════════════╪════════ |
|         ▼                          ▼                          ▼         |
| ┌─────────────────────────────────────────────────────────────────────┐ |
| │                        SHARED MEMORY REGION                         │ |
| │  ┌─────────────────────────────────┐  ┌───────────────────────────┐ │ |
| │  │         shared_buffers          │  │        wal_buffers        │ │ |
| │  │   (Cached Table & Index Pages)  │  │ (Staged WAL Record Pages) │ │ |
| │  └─────────────────────────────────┘  └───────────────────────────┘ │ |
| │  ┌────────────────────────────────────────────────────────────────┐ │ |
| │  │            Lock Tables & IPC Coordination Semaphores           │ │ |
| │  └────────────────────────────────────────────────────────────────┘ │ |
| └──────────────────────────────────┬──────────────────────────────────┘ |
|                                    │                                    |
| ┌──────────────────────────────────┴──────────────────────────────────┐ |
| │                      BACKGROUND HELPER PROCESSES                    │ |
| │  [Postmaster]  [Checkpointer]  [BgWriter]  [WalWriter]  [Autovacuum] │ |
| └──────────────────────────────────┬──────────────────────────────────┘ |
|                                    │ (fsync)                            |
|                                    ▼                                    |
| ┌─────────────────────────────────────────────────────────────────────┐ |
| │                        PHYSICAL STORAGE (PGDATA)                    │ |
| │    Base Data Files (/base)    │    WAL Segment Files (/pg_wal)      │ |
| └─────────────────────────────────────────────────────────────────────┘ |
+-------------------------------------------------------------------------+

1. The Postmaster (Master Supervisor Process)

The main server binary is postgres (historically referred to as postmaster). When PostgreSQL starts, the postmaster process:

• Allocates the shared memory segments (shared_buffers, wal_buffers, lock tables).
• Spawns the background helper processes.
• Listens on configured network sockets (default TCP port 5432) and Unix domain sockets (/var/run/postgresql or /tmp).
• Accepts incoming client connection handshakes, authenticates the client credentials against pg_hba.conf, and forks a dedicated backend server process for each accepted connection.

2. Backend Server Processes

Each connected client application interacts exclusively with its own dedicated backend operating system process (postgres).

• Memory isolation: If a client executes a poorly constructed query that crashes its backend process, operating system memory protection prevents other client sessions from being directly corrupted.
• Process coordination: Backend processes communicate and coordinate with one another using shared memory, semaphores, and lightweight spinlocks.

3. Key Background Helper Processes

In addition to user backend processes, the postmaster coordinates specialized background processes:

4. Memory Layout: Shared vs. Local Memory

PostgreSQL divides its memory architecture into two distinct categories:

• Shared Memory (Allocated once at server startup):

  • shared_buffers: The primary data cache for table and index pages read from disk. The standard production recommendation is 25% of total system RAM.
  • wal_buffers: Memory buffer used to stage Write-Ahead Log data before it is flushed to disk. Defaults to -1 (auto-tuned to 1/32nd of shared_buffers, capped at 16MB).
  • Lock Space: Shared memory tables tracking heavyweight table/row locks and lightweight latch synchronization.

• Local Memory (Allocated dynamically per backend process):

  • work_mem: Memory allocated for internal sort operations (ORDER BY, DISTINCT) and hash tables (hash joins, hash aggregations). Crucially, work_mem can be allocated multiple times per query if a complex query contains multiple sort or hash operations!
  • maintenance_work_mem: Memory used for maintenance operations such as VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY. Defaults to 64MB; increasing this parameter accelerates index builds and vacuum cycles.
  • temp_buffers: Memory dedicated to holding session-specific temporary tables.

---

Standards Compliance and ACID Guarantees

PostgreSQL strictly adheres to the ACID model of transactional reliability:

• Atomicity: Changes within a transaction are all committed or all rolled back. The commit status of each transaction is recorded in the transaction status log (pg_xact, historically pg_clog).
• Consistency: Database state transitions must preserve all schema constraints (CHECK, NOT NULL, FOREIGN KEY, UNIQUE, EXCLUSION). If any constraint is violated, the transaction is rejected.
• Isolation: Concurrent transactions execute without cross-transaction interference, governed by Multi-Version Concurrency Control (MVCC).
• Durability: Once a transaction is acknowledged as committed, its changes are guaranteed to survive power failure or crash. This is achieved via Write-Ahead Logging (WAL): WAL records describing modifications are flushed to non-volatile disk storage via fsync before the transaction's commit status is confirmed to the client.

Multi-Version Concurrency Control (MVCC)

In traditional database locking models, readers acquire shared locks that block writers, and writers acquire exclusive locks that block readers.

PostgreSQL implements Multi-Version Concurrency Control (MVCC):

• Readers never block writers, and writers never block readers.
• When a row is updated, PostgreSQL does not overwrite the existing data in-place. Instead, it marks the original row version (tuple) as expired and inserts a brand new version of the row.
• Every tuple stored on a data page header contains hidden system columns:
  • xmin: The transaction ID (XID) of the transaction that inserted the tuple.
  • xmax: The transaction ID of the transaction that updated or deleted the tuple (set to 0 for active, non-deleted rows).
• A transaction's visibility rules determine which tuple versions it can see based on its snapshot of active, committed, and aborted transaction IDs.
• Old, unreferenced tuple versions ("dead tuples") remain on disk until removed by VACUUM.

---

Transaction Isolation Levels in PostgreSQL

The ANSI/ISO SQL standard defines four transaction isolation levels based on three phenomena: Dirty Read, Non-Repeatable Read, and Phantom Read.

Operational Details of Isolation Levels

1. Read Committed (Default):

   • If Transaction A updates and commits a row while Transaction B is open, Transaction B will see the updated row as soon as Transaction B runs its next SELECT statement.

2. Repeatable Read:

   • Transaction B will see the exact same snapshot of the database throughout its entire lifecycle, regardless of how many other transactions commit changes.
   • If Transaction B attempts to update or lock a row that was concurrently modified and committed by another transaction after Transaction B's snapshot began, PostgreSQL aborts Transaction B with a serialization failure:

     ERROR: could not serialize access due to concurrent update
     

   • Applications using Repeatable Read must be engineered to catch and retry serialization failures.

3. Serializable:

   • Provides the highest level of isolation. PostgreSQL monitors SIREAD locks (which do not block execution) to detect dependency cycles (such as write skew). If a cycle is detected, one of the transactions is terminated with:

     ERROR: could not serialize access due to read/write dependencies among transactions