PostgreSQL 9.0 Architecture

Posted on by Raghav
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

48 Replies to “PostgreSQL 9.0 Architecture”

  5. 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.

  6. 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

  8. 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

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

  17. 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.RegardsRaghav

  19. Hello, I haven&#39t found license for the schema (and some other You have published there). I&#39m 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&#39d like to ask you to specify license or just give an approval to use the schema for educational purposes.Thanks a lot.

  21. 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)

  25. 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)

  29. 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.

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

  37. 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

  42. 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

  43. 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

  44. 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.

Comments are closed.