Exploring PostgreSQL: Internal Architecture Made Easy

PostgreSQL is an open-source, object-relational database management system (ORDBMS) known for its advanced features, performance, and robustness. Understanding its architecture is crucial for anyone working with PostgreSQL, as it enables efficient management, troubleshooting, and optimization. In this blog, we will explore the main layers of PostgreSQL’s architecture, including the Client Interface Layer, Postmaster Process, Back end Server Processes, Shared Memory Segments, Background Processes, and the Storage Layer.

Client Interface Layer

The Client Interface Layer is the first point of contact between the user or application and the PostgreSQL database engine. It handles communication, SQL query execution, and administrative commands. It is responsible for transferring data between the user/application and the server, ensuring smooth interaction.

Key Components of the Client Interface Layer:

psql (PostgreSQL Interactive Terminal): A command-line interface for interacting with PostgreSQL databases, often used by DBAs and developers for running SQL queries and performing administrative tasks.

Libpq (C Library): The foundational client library used by many PostgreSQL tools, drivers (JDBC, psycopg2, ODBC), and middleware to communicate with PostgreSQL.

Language-Specific Drivers: Libraries that facilitate communication between PostgreSQL and various programming languages (e.g., JDBC for Java, psycopg2 for Python, Npgsql for .NET).

ORMs and Query Builders: Tools like SQLAlchemy, Sequelize, and Prisma that sit on top of PostgreSQL drivers and dynamically generate SQL.

Middleware/API Gateways: Tools like PostgREST and Hasura that allow users to interact with PostgreSQL through RESTful or GraphQL APIs.

What Happens in This Layer?

Connection Initiation: A client sends a connection request over TCP/IP or Unix socket.

Authentication: PostgreSQL verifies the credentials using methods such as md5, scram-sha-256, or peer.

Session Establishment: After authentication, the server creates a backend process to handle the client’s session.

SQL Communication: The client sends SQL statements, and PostgreSQL sends back results, errors, and status messages.

Postmaster Process

The Postmaster Process is the central server process in PostgreSQL. It is the first process started when the database server boots up. Its primary role is to initialize the system and manage communication between clients and backend processes.

What the Postmaster Does:

• Startup: Initializes shared memory, background processes, and system catalogs.
• Connection Manager: Listens for client connections (default on port 5432).
• Forks Backend Processes: For each client connection, it creates a backend process to handle SQL queries.
• Supervises Background Workers: Manages important processes such as autovacuum, checkpointer, and walwriter.
• Crash Recovery: In case of a crash, it initiates recovery using Write-Ahead Logs (WAL).
• Signal Handling: Handles shutdown signals and reloads configuration changes.

Lifecycle Overview:

1. The Postmaster starts up and initializes the system.
2. It listens for client connection requests.
3. Upon receiving a connection request, it forks a backend process for the client.
4. The backend process handles SQL execution and data communication.
5. The Postmaster supervises the backend and background processes, ensuring the server runs efficiently.

Back end Server Processes

Backend Server Processes in PostgreSQL are individual processes created by the Postmaster to handle client connections. Each time a client connects to the server, the Postmaster forks a backend process to manage that session.

What Back end Processes Do:

• SQL Execution: Parses, plans, and executes SQL queries.
• Transaction Management: Handles transactions such as commit and rollback.
• Data Access: Reads/writes data to shared memory buffers or disk if necessary.
• Communication: Exports data to the client.
• Error Handling: Ensures that issues in one session do not affect others.

Life cycle of a Back end Process:

1. A connection is established when a client sends a request to the Postmaster.
2. The Postmaster forks a new back end process to manage the session.
3. The back end authenticates the client and begins the session.
4. The backend handles SQL commands from the client and returns the results.
5. Once the client disconnects, the backend process terminates.

Each back end runs independently, ensuring isolation between client sessions and reducing the risk of data corruption.

Shared Memory Segments

In PostgreSQL, Shared Memory Segments are critical areas in memory that multiple back end processes can access. Shared memory is used to store global data that all back end processes may need to access. This is vital for performance, as it minimizes the need for frequent disk access.

What Are Shared Memory Segments?
Shared memory stores global information like data buffers, locks, transaction statuses, and other runtime statistics, enabling communication between backend processes without needing to read from disk frequently.

Key Components of PostgreSQL Shared Memory:

• Shared Buffers: Caches disk pages in memory to improve performance.
• WAL Buffers: Temporarily holds Write-Ahead Log (WAL) data before it’s written to disk.
• Lock Tables: Keeps track of locks (row or table) held by transactions.
• Commit Log (CLOG/XLOG): Tracks transaction commit statuses, aiding crash recovery.
• Statistics Collector Data: Stores runtime statistics for query planning and optimization.
• Background Writer Info: Monitors dirty buffers and flushing activity.

Background Processes

PostgreSQL uses Background Processes (also known as background workers) to manage critical internal tasks, such as maintaining database health, ensuring consistency, and improving performance.

Key PostgreSQL Background Processes:

• Autovacuum Launcher: Automatically triggers vacuuming to reclaim space and maintain MVCC (Multi-Version Concurrency Control).
• WAL Writer: Flushes WAL data from memory buffers to disk.
• Background Writer: Writes dirty shared buffers to disk.
• Checkpointer: Periodically writes all dirty pages to disk during a checkpoint.
• Stats Collector: Gathers statistics about query performance.
• Logical Replication Launcher: Starts workers for logical replication.
• Archiver: Archives WAL files for Point-In-Time Recovery (PITR).
• Logger: Manages logging.
• WAL Receiver/Sender: Handles replication for standby servers.

Key Differences Between Data Files and WAL:

Feature	Data Files	WAL Files
Stores	Tables, indexes, metadata	Change logs
Format	8 KB pages	Sequential log segments
Location	$PGDATA/base/	$PGDATA/pg_wal/
Used For	Reading/writing user data	Crash recovery, replication
Flushed By	Background Writer, Checkpointer	WAL Writer
Durability	Final state of data	Guarantees no data loss on crash

Conclusion:

PostgreSQL’s architecture is a well-designed system that ensures efficient performance, high availability, and reliability. Understanding its components—especially the Client Interface Layer, Postmaster, Back end Server Processes, Shared Memory, Background Processes, and Storage Layer—will empower you to optimize PostgreSQL in your applications. By delving deeper into how these parts interact, you can take full advantage of PostgreSQL’s capabilities and maintain high performance in mission-critical applications.