PostgreSQL Architecture

 The physical structure of PostgreSQL consists of

• Processes.
• Shared memory.
• Data files.

PostgreSQL Processes

1. Postmaster (Daemon) Process
   
   1. The first process started when you start PostgreSQL.
   2. At startup it starts all other processes; performs recovery, initialize shared memory, and run background processes.
   3. It creates a backend process when there is a connection request from the client process.
   4. Postmaster process is the parent process of all processes.
2. Background Processes
   

   1. List of Background processes:

      logger	Write the error message to the log file.
      checkpointer	When a checkpoint occurs, the dirty buffer is written to the file.
      writer	Periodically writes the dirty buffer to a file.
      wal writer	Write the WAL buffer to the WAL file.
      Autovacuum launcher	Fork autovacuum worker when autovacuum is enabled.It is the responsibility of the autovacuum daemon to carry vacuum operations on bloated tables on demand
      archiver	When in Archive.log mode, copy the WAL file to the specified directory.
      stats collector	DBMS usage statistics such as session execution information ( pg_stat_activity ) and table usage statistical information ( pg_stat_all_tables ) are colle

3. Backend Process
   
   1. Performs the query request of the user process and then transmits the result.
4. Client Process
   1. Refers to the background process that is assigned for every backend user connection.
   2. Usually the postmaster process will fork a child process that is dedicated to serve a user connection.

Shared Memory

Shared Memory refers to the memory reserved for

• Database caching
• Transaction log caching.

The important elements in shared memory are

• Shared Buffer
  
  • The purpose of Shared Buffer is to minimize DISK IO.
• WAL buffers
  
  • A buffer that temporarily stores changes to the database (to the WAL files).
• Temp buffers
  • Stores temporary tables

Data storage

The data is cached both in the operating system level and in the PostgreSQL level:

• PostgreSQL buffer cache in shared memory.
• OS data cache is the Write-Ahead Log (WAL).

In the case of a failure the contents of the RAM disappear and some data may be lost, which is unacceptable as it violates the durability property.

Therefore, during its operation PostgreSQL constantly writes the so-called Write-Ahead Log (WAL) to the disk.

This allows to re-perform lost operations and restore data in a consistent state.

Transaction logging - WAL

WAL = Write-Ahead Log.

Each transaction is written to the WAL File before it written to the data files on the disk (as described above).

WAL files stored in \data\pg_wal.

A single information unit within a WAL file is called a log record.

“Segment” is sometimes used as synonym for WAL file.

SQL Server LDF files ~ Oracle REDO files ~ PostgreSQL WAL files

PostgreSQL main terms

A tuple is a synonym for a row.

A relation is a synonym for a table.

A filenode is an id which represent a reference to a table or an index.

PostgreSQL database Cluster

• It is not a collection of servers,
• It is a collection of databases managed by a single server

PostgreSQL Architecture useful links

https://severalnines.com/database-blog/understanding-postgresql-architecture

http://bajis-postgres.blogspot.com/2013/10/postgresql-architecture.html

https://edu.postgrespro.com/2dintro/02_arch_general.pdf

https://edu.postgrespro.com/2dintro/04_arch_wal.pdf