Skip to content

Phase 01: Architecture, Evolution & Sandbox Setup

Chapter 1: The Evolution of PostgreSQL (1986 to v16/v17)

1.1 Inception: The Berkeley POSTGRES Project (1986–1994)

PostgreSQL originated at UC Berkeley under the leadership of Professor Michael Stonebraker as the successor to Ingres (hence POST-GRES). * The Original Philosophy: Object-Relational Database Management System (ORDBMS). Stonebraker envisioned abstract data types, complex objects, rules systems, and active databases (triggers/procedures). * The Query Language: It did not use SQL initially; it used PostQUEL. * Storage Concept: The original design attempted to eliminate write-ahead logging by keeping all historical data permanently on optical disks ("no-overwrite" storage system). This proved impractical for high-throughput write workloads due to disk exhaustion, leading to the later adoption of WAL.

1.2 The Open-Source Renaissance (1995–2000)

  • Postgres95 (1995): Andrew Yu and Jolly Chen replaced PostQUEL with ANSI SQL, wrote an engine parser in SQL, and boosted execution speed by 30-50%.
  • PostgreSQL 6.0 (1996): The project was released to the global open-source community.
  • PostgreSQL 6.5 (1999) — The MVCC Revolution: Before v6.5, readers and writers locked entire tables (like early MySQL MyISAM). v6.5 introduced Multi-Version Concurrency Control (MVCC), completely eliminating read/write mutual blocking.

1.3 Major Architectural Milestones

  • PostgreSQL 7.1 (2001):
  • Introduced Write-Ahead Logging (WAL): Transactions write changes sequentially to a WAL buffer first, ensuring durability (ACID) and crash recovery.
  • Introduced TOAST (The Oversized-Attribute Storage Technique): Overcame the rigid 8KB block limit by compressing and storing large attributes in out-of-line tables.
  • PostgreSQL 8.0 (2005):
  • Native Windows support (no Cygwin emulation layer).
  • Point-In-Time Recovery (PITR) via continuous WAL archiving.
  • Savepoints within transactions.
  • PostgreSQL 8.1 (2006): Integrated the Autovacuum Daemon directly into the core engine.
  • PostgreSQL 9.0 (2010) — The Replication Leap:
  • Native Streaming Replication (binary WAL transfer over TCP).
  • Hot Standby: Replicas could finally serve read-only queries while continuously replaying WAL.
  • PostgreSQL 9.4 (2014) — The NoSQL Disruptor:
  • JSONB: Binary-encoded, decomposed JSON with GIN indexing support, outperforming MongoDB on many read/write benchmarks.
  • Logical Decoding: Foundation for Change Data Capture (Debezium) and logical replication.
  • PostgreSQL 10 (2017): Native Logical Replication (publish/subscribe) and declarative table partitioning.
  • PostgreSQL 12–14 (2019–2021): Inlined CTEs (no longer default optimization fences), B-Tree index deduplication (saving 40-70% disk space), JSON path expressions, and LZ4/ZSTD compression for TOAST/WAL.
  • PostgreSQL 16–17 (2023–2024): pg_stat_io (deep system-wide I/O wait monitoring), logical replication failover slots, and memory optimization for VACUUM.

Chapter 2: PostgreSQL Architecture 101

2.1 Process Model (Process-Per-Connection)

Unlike databases that use lightweight OS threads (e.g., MySQL, Microsoft SQL Server), PostgreSQL uses an isolated multi-process architecture.

+--------------------------------------------------------------------------+
|                            Postmaster (PID 1)                            |
|                  Listens on TCP/5432 & Unix Domain Socket                |
+--------------------------------------------------------------------------+
      |                   |                     |                    |
  (forks on          (forks on              (forks on           (forks on
  connection)        connection)            startup)            startup)
      v                   v                     v                    v
+--------------+    +--------------+    +----------------+    +----------------+
| Backend #1   |    | Backend #2   |    | Checkpointer   |    | WAL Writer     |
| (Client App) |    | (Client App) |    | Background Wtr |    | Autovacuum     |
+--------------+    +--------------+    | Stats Collector|    | Archiver / etc |
       \                  /             +----------------+    +----------------+
        \                /                      |                     |
         v              v                       v                     v
+--------------------------------------------------------------------------+
|                     SHARED MEMORY (IPC / mmap)                           |
|  - shared_buffers (Table/Index cache)                                    |
|  - wal_buffers (Write-Ahead Log cache)                                   |
|  - Lock Tables (LWLock, Heavyweight Lock managers)                       |
|  - ProcArray (Global transaction state & visibility)                     |
+--------------------------------------------------------------------------+
                                     |
                                     v
+--------------------------------------------------------------------------+
|                            DISK FILESYSTEM                               |
|  - $PGDATA/base (Heap data files: 1GB segments)                          |
|  - $PGDATA/pg_wal (WAL files: 16MB segments)                             |
|  - $PGDATA/global (Cluster-wide catalog tables)                          |
+--------------------------------------------------------------------------+

Core Processes Breakdown:

  1. Postmaster (postgres supervisor): The parent process. Initializes shared memory, listens for network connections, forks backend workers, and monitors child process crashes (initiating crash recovery if a backend segfaults).
  2. Backend Process: Dedicated process allocated to a single client session. Executes query parsing, rewriting, planning, and execution. Memory allocated here (work_mem, temp_buffers) is private to this process.
  3. Checkpointer: Periodically flushes all dirty shared buffers to disk and writes a Checkpoint record to WAL. Establishes recovery points.
  4. Background Writer (bgwriter): Continuously searches shared_buffers for dirty pages and writes them to disk to ensure backend processes always find clean, unallocated buffers without waiting for synchronous I/O.
  5. WAL Writer: Periodically flushes wal_buffers to disk even before transactions commit.
  6. Autovacuum Launcher & Workers: Scans catalog tables to identify tables with high dead tuple counts or nearing XID wraparound, spawning autovacuum worker processes to reclaim space.
  7. WAL Archiver / Sender: Ships WAL files to secondary storage or streams them over TCP to read replicas.

2.2 Shared Memory vs Private Backend Memory

Understanding this division is the single most critical factor in preventing Out-Of-Memory (OOM) crashes:

  • Shared Memory (Fixed allocation at startup):
  • shared_buffers: Cached 8KB data and index pages.
  • wal_buffers: Uncommitted transaction log records.
  • Lock tables: Tracks row, table, and memory locks across all backends.
  • Private Backend Memory (Dynamically allocated per session/query):
  • work_mem: Memory for in-memory sorts, hash tables, and bitmap operations. Allocated per operation, not per query.
  • maintenance_work_mem: Memory used by maintenance commands (VACUUM, CREATE INDEX, ALTER TABLE ADD FOREIGN KEY).
  • temp_buffers: Memory used for temporary tables within the session.

Chapter 3: The Sandbox Environment (Constrained Realities)

3.1 Why Test Under Resource Starvation?

On an enterprise server with 128 cores and 512GB RAM, architectural mistakes hide behind brute-force caching. Bad queries stay in cache; unindexed joins sort in memory; connection leaks survive unnoticed for days. When constrained to: * 0.1 CPU core: Lock contention spikes instantly; context-switch latency explodes; slow sequential scans choke all concurrent sessions. * 16MB–64MB RAM: Sorts spill immediately to disk; shared_buffers hit ratio plunges; buffer eviction algorithms are stressed; OOM kills happen rapidly if queries are unoptimized.

3.2 macOS Constrained Local Environment

Our sandbox uses two mechanisms: 1. Config Limits in postgresql.conf: * shared_buffers = 16MB * work_mem = 4MB * maintenance_work_mem = 8MB * max_connections = 100 2. Process CPU Throttling (cpulimit): Attaching cpulimit -p <POSTGRES_PID> -l 10 enforces that the Postmaster and its forked children receive no more than 10% of a single CPU core time slice.

Chapter 4: Configuration Deep Dive (postgresql.conf)

4.1 Memory Sizing Formulas (Production vs Sandbox)

Parameter Sandbox Value Production Formula (Dedicated Server) Purpose & Choke Point
shared_buffers 16MB 25% of Total RAM (up to 32GB-64GB max) Table/index page cache. Setting > 40% causes double-buffering cache competition with Linux Page Cache.
work_mem 4MB ((Total RAM - shared_buffers) * 0.8) / (max_connections * 2) Memory for Sort/Hash/Distinct. Danger: A complex query with 5 joins/sorts can consume 5x work_mem. If 50 queries run concurrently, 250x work_mem is consumed.
maintenance_work_mem 8MB 5%–10% of RAM (up to 2GB) Speed of VACUUM and CREATE INDEX. If too low, vacuuming large tables requires multiple index passes, dragging autovacuum out for hours.
effective_cache_size 48MB 50%–75% of Total RAM Does not allocate memory. Informs the query planner how much data is likely cached in RAM (shared_buffers + OS page cache). Influences index scan vs seq scan decisions.

4.2 Critical Durability & Write Settings

  • synchronous_commit:
  • on (Default): COMMIT blocks until WAL is physically written and flushed to disk (fsync). Zero data loss on power failure.
  • off: COMMIT returns immediately after writing to memory; WAL writer flushes within wal_writer_delay (default 200ms). Boosts insert performance by 5x-10x; risk of losing last ~200ms of transactions on sudden crash.
  • local: Synchronous locally, asynchronous to streaming replicas.
  • wal_buffers: Default -1 auto-tunes to 1/32nd of shared_buffers (capped at 16MB). For heavy write spikes, 16MB–64MB prevents backend processes from blocking on WAL write lock.
  • checkpoint_completion_target: Set to 0.9 in production. Spreads checkpoint I/O writes across 90% of the checkpoint interval, flattening disk I/O spikes into a smooth baseline.

Chapter 5: Connection Management & Pooling

5.1 The True Cost of a PostgreSQL Connection

Every PostgreSQL connection creates an OS process: * Memory footprint: 5MB–10MB base memory per idle connection. 500 connections = 2.5GB–5GB RAM before executing a single query. * Context Switching: As active connection counts exceed available CPU cores, the OS kernel spends more CPU cycles switching process contexts in the run queue than actually executing SQL. * LWLock Contention on ProcArray: All backend processes must read/write to the global ProcArray in shared memory to check transaction visibility. Beyond ~200–500 active concurrent queries, backends spend substantial time waiting for spinlocks on ProcArray.

5.2 PgBouncer Pooling Modes

Client Apps (10,000 connections)
       |
       v
+------------------------------------+
|             PgBouncer              |
+------------------------------------+
       |
       | (Pool size: 50 - 100 connections)
       v
+------------------------------------+
|         PostgreSQL Engine          |
+------------------------------------+
  1. Session Pooling:
  2. A client claims a server connection upon login and keeps it until disconnect.
  3. Pros: 100% compatible with all Postgres features.
  4. Cons: Idle clients hold database processes hostage. Limited scalability.
  5. Transaction Pooling (Recommended for high scale):
  6. A client claims a server connection only for the duration of a transaction (BEGIN to COMMIT). When idle, the connection returns to the pool.
  7. Pros: 10,000 application threads can easily be served by 50-100 Postgres connections.
  8. Cons / Gotchas: Breaks session-level features:
    • SET timezone = ... or SET search_path = ... poisons subsequent clients.
    • LISTEN / NOTIFY cannot be routed properly.
    • Prepared statements (PREPARE) fail unless using PgBouncer 1.21+ with max_prepared_statements enabled.
    • Temporary tables (CREATE TEMP TABLE) leak or disappear across queries.
  9. Statement Pooling:
  10. Connection returned after every single SQL statement.
  11. Multi-statement transactions are strictly forbidden. Rarely used outside of simple read caches.