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

23 comments :

Unknown said...

Excellent information raghav....

Raghavendra said...

Thank you Satheesh :)

Euler Taveira 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.

iCodeiExist 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

Unknown 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 :)

Anonymous said...

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

Anonymous said...

Hello, I haven't found license for the schema (and some other You have published there). I'm trying to prepare a presentation for Oracle DBAs to show them differences and hlep with starting to use PostgreSQL. The schema is perfect, so I'd like to ask you to specify license or just give an approval to use the schema for educational purposes.
Thanks a lot.

Nacho said...

Nice info, raghav. Could you update the 3 links in the article, please?
Regards

Unknown said...

Thank so much, It's useful article.

Anonymous said...

Sir,

Very Nice article. I m new to Postgres. I have knowledge about RDBMS and Oracle Pl/SQL. I intend to Postgres Associate Certification. May you please suggest how to go about preparing for the same. As I could find no sample questions or dumps available(Like Oracle dumps that we have for OCP & OCA certification)

vivy said...

Looks like someone else is using this diagram. Please make sure about your copyrights:
http://bajis-postgres.blogspot.com/2013/10/postgresql-architecture.html

Post a Comment

Creative Commons License
This work is licensed under a Creative Commons Attribution-NonCommercial 4.0 International License