Friday, April 22, 2011

PostgreSQL 9.0 Architecture


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.


 Utility Process :
   Mandatory process: These process are not having an option of Enable/Disable.
  • BGWriter 
  • WAL Writer
   Optional Process:  These process are having an option of Enable/Disable.
  • Stats-collector
  • Autovacuum launcher
  • Archiver 
  • Syslogger
  • WAL Sender
  • WAL Receiver

Shortly, I will be posting on the Utility and Userbackend Process pictorials.

Regards
Raghav

19 comments:

satheesh said...

Excellent information raghav....

Raghavendra said...

Thank you Satheesh :)

Euler Taveira de Oliveira said...

you forgot about wal sender and wal receiver processes.

Fabrízio de Royes Mello said...

Great post.

But you may remember that also exists the utility processes (optionals) WAL Sender and WAL Receiver.

Raghavendra said...

Thank your suggestions(Euler/Mello).

WAL Sender and WAL Receiver are the base of Streaming Replication. Thought to present them with Streaming Replication setup.

True, it should be in the Architecture Overview diagram. I have altered the Diagram accordinlgy.

Anonymous said...

Very nice post.

I have a question, however. Does the Postmaster process spawn the utility processes? Or are they deamon processes that get run at startup?

Thanks

Raghavendra said...

Thanks,

Postmaster itself a Daemon process, rest all utility processes starts along with it.
Another Daemon kind of optional process is Autovacuum. Default is ON.

Regards
Raghav

Maalini said...

Great post. So much easier to understand. :) Thank you.

ichbinrene said...

Thank you very much! Nice diagram!

Raghavendra said...

Cool. Thanks..

--Raghav

Rodrigo E. De León Plicet said...

Nice post.

Why don't you add your blog to Planet PostgreSQL?

http://planet.postgresql.org/add.html

Anonymous said...

Hi Raghvendra.. can you pls add some more details, regarding archiving and other processes too?.. Actually, today is my first day of learning PostgreSQL.. What I can understand is that WAL is like REDO Log Buffer in Oracle and WAL Files are like Redo log files. May I Know which processes writes Dirty WAL Buffers from WAL Buffer to WAL Files and how they get archived..?
Thanks. - Parry

Raghavendra said...

You can find more details on PostgreSQL process related stuff on http://raghavt.blogspot.in/2011/04/postgresql-90-memory-processes.html

--Raghav

Anonymous said...

Nice post.

Michaela Kim said...

Thanks, very informative

Anonymous said...

Finally! Searched this for ages (30min) - many thanks for the fine work, volker.

Anonymous said...

Excellent information Raghav, just started reading postgres, for a newbie like me, its great. Thanks, Paresh

Anonymous said...

Thanks a lot!!! I come from Oracle world , and needed this deperately...
THANKS :)

suyahuang said...

Just want to say that some of the links in the article are not working...

Post a Comment