PostgreSQL 9.0 Architecture

Published on Fri, Apr 22, 2011

Its my pleasure to be here, publishing my first blog on PostgreSQL Architecture.

For a quite sometime, am working, learning the vast and most happening Database PostgreSQL. As a beginner,thought of giving a try to represent PostgreSQL Architecture in pictorial format. PostgreSQL Architecture includes sevaral things memory,process and storage file system, it is complex to show everything in one Picture. My efforts here to give an overview on PostgreSQL Architecture.

Most of the designing made with the help of Our PostgreSQL Committers (Heikki,Robert Haas,Bruce), from whom I have learned alot about the PostgreSQL internals. Am very much thankful for their cooperation for making me to understand about the PostgreSQL in-and-out. Am not the hacker or architectural designer, its just an article for those who are new to PostgreSQL. Please post your comments, suggestion or if you find any mistakes to correct myself.

PostgreSQL 9.0 Architecture Overview

PostgreSQL instance consists of set of Process and Memory. PostgreSQL uses a simple “process per-user” client/server model. PostgreSQL types of process.

The ‘postmaster’, supervisory daemon process, ‘postmaster’ is attached to shmmem segment but refrains from accessing to it.
Utility processes (bgwriter,walwriter,syslogger,archiver,statscollector and autovacuum lancher) and
User Backend process (postgres process itself, Server Process)

When a client request for connection to the database, firstly request is hit to Postmaster daemon process after performing Authentication and authorization it spawns one new backend server process(postgres). From that point on, the frontend process and the backend server communicate without intervention by the postmaster. Hence, the postmaster is always running, waiting for connection requests, whereas frontend and backend processes come and go. The libpq library allows a single frontend to make multiple connections to backend processes.

However, each backend process is a single-threaded process that can only execute one query at a time; so the communication over any one frontend-to-backend connection is single-threaded. Postmaster and postgres servers run with the user ID of the PostgreSQL “superuser”.

One Postgres process exists for every open database session. Once authenticated with user connection, it directly connects (with who and for what purpose) with shared memory.

Memory :

Shared Buffers

WAL Buffers

clog Buffers

Other Buffers

PostgreSQL shared memory is very big and all the buffers are not in sync, mean all are independent. Many experts/commiters has given maximum information on web with their experience on PostgreSQL. PostgreSQL documention with this diagram will give a basic understanding on the Architecture. Following links will brief more.

http://www.postgresql.org/docs/9.0/interactive/runtime-config-resource.html

http://www.enterprisedb.com/docs/en/8.4/pg/runtime-config-resource.html

http://www.postgresql.org/files/documentation/books/aw_pgsql/hw_performance/0.html

Utility Process :

Mandatory process: These process are not having an option of Enable/Disable.