Table of Contents

What is It?

Open-source object-relational database management system (ORDBMS) known for its high reliability, data integrity, and ACID compliance. It supports both SQL and JSON querying, making it ideal for complex data workloads, web applications, and analytics.

  • Support for JSON alongside traditional SQL
  • ACID compliance for transaction consistency
  • MVCC for concurrent processing
  • Extensibility with features like PostGIS (geospatial data) and TimescaleDB (time-series data)
Why another RDBMS DB?

While there are many RDMS all of them differ with how they are implemented under the hood and how they store the data.

Some important PostgreSQL Concepts

MVCC (Multi-Version Concurrency Control)

PostgreSQL does not overwrite rows on UPDATE. Instead, UPDATE creates a new tuple version.Old version remains until vacuumed. Visibility determined by transaction snapshots.

  • Readers never block writers
  • Writers don’t block readers
  • No read locks needed for SELECT
Storage Model
  • Data stored in 8KB pages
  • WAL stored separately
  • Heap tables: Regular tables without a predefined order.
  • Indexes: Separate structures that help speed up queries (e.g., B-trees by default).
  • TOAST: A mechanism for storing large objects or values that don’t fit in a single page.
  • Write-Ahead Log (WAL): Ensures durability and allows for crash recovery.

Architecture

  • Postmaster is the starting point: It handles incoming client connections by forking backend processes.
  • Shared Memory stores critical components like the Shared Buffer Cache, WAL Buffers, Lock Management, and Transaction State, ensuring that all backend processes can interact with them.
  • Query Execution Flow shows the stages from parsing SQL to executing queries and returning results.
  • Background & Auxiliary Processes: This group includes key auxiliary processes like Autovacuum (for cleaning up dead tuples), Background Writer (for flushing dirty pages), Checkpointer (to maintain consistency), and WAL-related processes (for durability and replication).
  • Replication: WAL Sender, Receiver, and Archiver handle replication for high availability and fault tolerance.
graph TD %% Main process (Postmaster) A[Postmaster] -->|Forks| B[Backend Process 1] A -->|Forks| C[Backend Process 2] A -->|Forks| D[Backend Process 3] %% Shared Memory subgraph Shared_Memory[Shared Memory] direction TB A1[Shared Buffer Cache] A2[WAL Buffers] A3[Lock Management] A4[Transaction State] end %% Query Pipeline subgraph Query_Pipeline[Query Execution Flow] direction TB A5[Parser] A6[Analyzer] A7[Rewriter] A8[Planner/Optimizer] A9[Executor] A10[Result] end %% Connections between components B -->|Handles Queries| A5 C -->|Handles Queries| A5 D -->|Handles Queries| A5 A5 --> A6 --> A7 --> A8 --> A9 --> A10 %% Background & Auxiliary Processes subgraph BG_Workers[Background & Auxiliary Processes] direction TB E1[Autovacuum] E2[Background Writer] E3[Checkpointer] E4[Logger] E5[WAL Writer] E6[WAL Sender] E7[Startup Process] end %% Replication subgraph Replication[Replication & WAL] direction TB F1[WAL Sender] F2[WAL Receiver] F3[WAL Archiver] end %% Connections for shared memory A1 --> A2 A1 --> A3 A1 --> A4 %% Connections between processes and background workers A --> E1 A --> E2 A --> E3 A --> E4 A --> E5 A --> E6 A --> E7 %% Replication link to WAL processes E5 --> F1 F1 --> F2 F1 --> F3

Multi-process

graph TD; A[Postmaster] -->|forks| B[Backend Process 1]; A[Postmaster] -->|forks| C[Backend Process 2]; A[Postmaster] -->|forks| D[Backend Process 3]; B -->|handles| E[Client Query]; C -->|handles| F[Client Query]; D -->|handles| G[Client Query];

When a client connectes the PostMaster forks a new process to handle that connection.

  • If one process crashes it doesn’t take the whole system with it.
  • OS-level process Isolation improves fault tolerance.
  • Avoids MultiThreading Sync Issues.
  • Higher Memory usage Per connection.
  • As with high number of process TLB size increases context switching becomes time consuming.
  • Needs Connection Pooling to scale.

Shared-Memory

graph TD; A[Shared Memory] --> B[Shared Buffer Cache]; A[Shared Memory] --> C[WAL Buffers]; A[Shared Memory] --> D[Lock Management]; A[Shared Memory] --> E[Transaction State];

All the processes share a big momory where many coordination info is stored.

  • This not the normal process memory, that is not shared.

    Private to each process: work_mem, maintenance_work_mem, Query execution memory, Sort memory, Hash tables. This is why too many connections can exhaust RAM.

  • PostgreSQL allocates a shared memory region at server startup, all backend processes attach to it.
  • Without this it would have been too slow for this DB to run.
  • It has following components.
    • Shared Buffer Cache
      • Stores cached table and index pages.
      • Prevents disk reads
      • If two connections read the same page, they access the same shared buffer page.
      • This is PostgreSQL’s main in-memory cache.
    • Lock Management Structures
      • Heavyweight locks (table-level)
      • Lightweight locks (LWLocks)
      • Spinlocks
    • WAL Buffers
      • Stores changes before they’re flushed to disk
      • Critical for durability
    • Transaction State
      • MVCC visibility
      • Snapshot tracking
      • Deadlock detection

Background Workers

In order to assis the main processes PostgreSQL has something called as background workers. These can offload operation from main processes which don’t need immediate response and can be run in background.

  • They run Independently and do not interact with clients.
  • Run in the same memory as the PostgrSQL server, so that they can share memory and access its internals.
  • Can be customized as per needs, to do batch jobs, monitoring data and maintaining indexes.

Auxillary Processes

graph TD; A[Background Writer] --> B[Flushing Dirty Pages]; A[Checkpointer] --> C[Flushing to Disk]; A[Autovacuum] --> D[Removing Dead Tuples]; A[WAL Workers] --> E[Archiving WAL]; A[WAL Workers] --> F[Sending WAL to Replicas];

Side processes which do the other tasks which ensures that DB is safe and up.

  • They run Independently and do not interact with clients.
  • Types of Auxillary Processes:
    • Background Writer
      • The background writer process flushes dirty pages (modified data) from shared buffers to OS(not disk, OS will keep that inits file system and then eventually update in the disk), allowing PostgreSQL to avoid page-write bottlenecks during busy query processing.
      • Runs asynchronously and periodically writes pages from the shared buffer pool to disk.
    • Checkpointer
      • The checkpointer process flushes dirty pages (modified data) and the WAL from shared buffers to DISK.
      • It maintains a checkpoint record which says at what point of time the db was consistent.
    • Logger
      • It does the logging.
    • Autovaccum Launcher
      • It launches autovaccum workers
      • They are responsible for removing those tuples who are no more needed.
      • If there are multiple tuples for same data due to MVCC, autovaccum workers removes it and frees disk space logically, Marks space reusable.
    • WAL workers
      • WAL archiver: Archives the WALs.
      • WAL reciever: In case of replication the different replicas can share the WALs and this is what does job of receiving it.
      • WAL writer: It flushes the WAL(not pages) to disk.
      • WAL Sender: It sends the WAL to other replicas.
    • Startup Process
      • This is the first process ever to load after postmaster when postgreSQL starts.
      • Its job is to bring the system in the last state which was there before it crashed.
      • Assume there were 100 page updates, and after those page updates WAL writer commited everything, but checkpointerdidn't run nd system crashed. When the system starts again startup process is going to see that from the last checkpoint wehave done 100 operations, so it going to stop postmaster from spawning any other process and load up all those 100 pages inmemory and mark them all as dirty, this is how it achieves the previous state.

Why PostgreSQL Over MySQL?

  1. MVCC (Multi-Version Concurrency Control)
  2. Advanced Indexing and Query Optimization
  3. JSON and other Data-type Support
  4. Highly Scallable
  5. Following the Hype

Reference

I have taken above info from these Resources: