PostgreSQL Architecture

Introduction to PostgreSQL Architecture

PostgreSQL is a Relational Database Management System available freely and is not controlled by any corporation and the source code is available for free. PostgreSQL is developed by a team of volunteers worldwide. PostgreSQL has many features that are useful to store the data from large to small and also keep the data most securely with advanced security features. PostgreSQL versions are released from time to time. Many PostgreSQL versions are available from PostgreSQL95 to PostgreSQL 12. Let’s see in detail about PostgreSQL Architecture.

Architecture of PostgreSQL

PostgreSQL Architecture has two models called Client, Sever. The client sends the proposal to PostgreSQL, and then PostgreSQL gives a response to the client through a TCP/IP network connection. PostgreSQL has its background processes which are useful to maintain the PostgreSQL Server. The PostgreSQL had the following components

• Shared Memory
• Background Processes
• Data Directory Structure / Data files

What is Share Memory?

Share Memory is a Memory used to reserve the memory for log caching in the transaction and caching database. It contains the following elements

• Shared Buffer: This was mainly to help to reduce the DISK IO which can be useful in accessing the large files most quickly.
• WAL Buffer: It is used to storing the data temporarily in the database as a WAL file which can be used in the time of recovery.
• Work Buffer: Work Buffer is used to set the specific memory to each client connection and is used by internal sort of operations to write the data and store it in temporary disk files.
• Maintenance Work Memory: It was used to specify a large amount of memory for database maintenance like VACUUM, ALTER TABLE, ADD FOREIGN KEY, etc.

What are Background Processes?

PostgreSQL operation has a list of background processes is given below:

• Logger: It can be used in writing error information in the project file.
• Checkpointer: The write up was started is called dirty buffer when the checkpointer occurs.
• Writer: It can be used to write a dirty buffer periodically.
• WAL writer: It can be used in the WAL file to write the WAL Buffer.
• Auto vacuum Launcher: It can be responsible for carrying out the specified operations called vacuum on blown up tables on need.
• Archiver: Archiver can be used in copying the data from the file named WAL to the particular listing as a directory when in Archive.log mode.
• Stats Collector: In this Statistics information like pg_stat_activity and pg_stat_database is collected.

Data files/Data Directory Structure

• Database: PostgreSQL contains numerous databases which are called database clusters. When we create the template files like template0, Postgres databases, initdb() execution was started. System catalog tables are the type of databases that are useful for the users to develop the database. The template0, template1 have the same category of tables which is immediately after the initdb(), but template1 can create the objects according to the user demand. The database worn by the user was created by clone the database of template1.
• Tablespace: When table creation, if you did not mention the tablespace it can be stored in [pg_default.]. Tables are maintained in a database cluster, it can be stored in [pg_global.] are generated immediately after initdb (). Multiple databases are used only one tablespace.
• Table: Each table has three files. The first file is used for storing the data of a table named OID. The second file is used to manage the table containing a free space called OID_fsm. The third file is used to maintaining the blocks of a table named OID_vm.

Conclusion – PostgreSQL Architecture

PostgreSQL is a Relational database management system and freely available which is useful to store and maintain the data and keep the data integrity as well. Hope this article explains the architecture of PostgreSQL and PostgreSQL Architecture functions respectively.

Recommended Articles

This is a guide to PostgreSQL Architecture. Here we discuss the introduction to PostgreSQL Architecture, Share Memory, and its background processes. You can also go through our other suggested articles to learn more –

1. What is PostgreSQL?
2. PostgreSQL Variables
3. How to Install PostgreSQL?
4. PostgreSQL Versions