Internals of PostgreSQL - Chapter 2 Quick summary

Part 1 : Process Architecture

• PostgreSQL is a client/server type RDMS.
• PostgreSQL has multi-process architecture and runs on a single host.

PostgreSQL server

• it is the collection of multiple processes cooperatively managing one database cluster.

Types of processes

• postgres server process
• backend process
• background processes
• replication associated processes
• background worker process

1. Postgres server process ( Parent process )

• it's the parent of all processes related to a database cluster management.
• known as postmaster process ( earlier ).
• process starts by pg_ctl utility with start option.
  • allocates a shared memory area in memory.
  • starts various background processes.
  • starts replication associated processes.
  • starts background worker processes ( if necessary ).
  • waits for connection requests from clients.
  • what about backend process ?
    • starts one whenever receiving a connection request from a client.
• it listens to one network port. ( default port is 5432 )

Note

• multiple PostgreSQL servers can run on same host , but for each server listens to different port number.

2. Backend process

• *started * by Parent process ( server process ) when receiving a connection request from a client.
• handles all queries and statements issued by a connected client.
• also called postgres
• communicates with the client through single TCP connection
• terminates when the client gets disconnected.

Notes :

• backend process allowed to operate only one database , must be specified you want to use explicitly when connecting to PostgreSQL server.
• PostgreSQL allows multiple clients to connect simultaneously.
• PostgreSQL has not implemented a native connection pooling feature.

Connection Pooling

• it's a way to reduce the cost of opening and closing connections by maintaining a “pool” of open connections that can be passed from database operation to database operation as needed.

Advantages :

• promote the reuse of connection objects and reduce the number of times that connection objects are created

• can improve the response time of any application that requires connections, especially Web-based applications.

• as we mention before , PostgreSQL has not implemented a native connection poolingfeature , so this cause If many clients such as WEB applications frequently repeat the connection and disconnection with a PostgreSQL server will increases costs of both :

  • establishing connections.
  • creating backend processes.

Note :

• there are some software that implement and allow pooling feature (pooling middleware)

3. background processes

• they perform processes of each feature.
• Some popular background processes :
  • background writer
  • checkpointer
  • autovacuum launcher
  • WAL writer
  • statistics collector
  • logging collector (logger)
  • archiver

4. Replication associated processes

• they perform the streaming replication.

5. Background worker process

• can perform any processing implemented by users

Part 2 : Memory Architecture

1. Local memory area

• allocated by each backend process for its own use.
• used for query processing.
• each area is divided into several sub-areas ( fixed / variable size )

2. Shared memory area

• allocated by a PostgreSQL server ( Parent / server process ) when it starts up.
• used by all processes of a PostgreSQL server.
• each area is divided into several sub-areas ( but of fixed size )

Book is Internals of PostgreSQL
Author Hironobu SUZUKI.

links :
https://www.interdb.jp/pg/pgsql01.html