Explain the Architecture Fundamentals in PostgreSQL

Architecture Fundamentals of PostgreSQL

PostgreSQL employs a client/server architecture. A PostgreSQL session is made up of the following interconnected processes (programs):

• A server process that manages database files accepts connections from client applications and performs database actions on the client's behalf. Postgres is the name of the database server program.
   
• The client (frontend) application of the user that wishes to perform database operations. Client applications can take many forms: they can be text-oriented tools, graphical applications, web servers that access the database to display web pages, or specialized database maintenance tools. Some client applications are included with the PostgreSQL distribution, but users create the majority.
   

PostgreSQL's physical structure is straightforward; it consists of the following components:

• Background processes
   
• Shared memory
   
• Structure of data directories / Data files
   

The PostgreSQL Architecture is depicted in the figure below:

Shared Memory

The memory reserved for transactional and other log catches is referred to as shared memory. Shared memory is made up of the following elements:

Shared Buffers

• We must allocate memory to a database server to use shared buffers. In 9.2 and earlier versions, the default value of shared buffers is 32 megabytes (32 MB), but in 9.3, the default value of sharable buffers is 128 megabytes (128 MB).
   
• If we have a dedicated PostgreSQL server, a reasonable starting point for shared buffers is 25% of the total memory. The goal of shared buffers is to reduce server DISK IO.
   

WAL Buffers

• WAL buffers temporarily store database changes written to the WAL file at a predetermined time. WAL buffers and WAL files are essential for recovering data at a specific point in time during backup and recovery.
   
• Shared buffers have a minimum size of 32 KB. If we set this parameter to wal buffers = -1, it will use shared buffers to determine the size of the buffers.
   

Work Memory

Set the specific memory that will be used by internal sort of operational processes and hash tables to write data into temporary disk files for each client connection.

Maintenance Work Memory

• We need to specify the maximum amount of memory for database maintenance operations like VACUUM, ANALYZE, ALTER TABLE, CREATE INDEX, ADD FOREIGN KEY, etc.
   
• This is safe to set maintenance work memory to be larger than work memory. More extensive settings will improve maintenance performance (for example, VACUUM, ANALYZE, ALTER TABLE, CREATE INDEX, and ADD FOREIGN KEY).

Background Processes

PostgreSQL's background processes are listed below. Each process has its own set of characteristics and PostgreSQL internals. The following details will be provided for each process: 

• Background Writer process: In PostgreSQL 9.1, the background writer performs checkpoint processing on a regular basis. The checkpointer process was kept separate from the background writer process in PostgreSQL 9.2. It will keep logs and backup data current.
   
• WAL Writer: This process writes and flushes WAL data from the WAL buffer to persistent storage on a regular basis.
   
• Logging Collector: This procedure is also known as a logger. It will create a WAL buffer and save it to the WAL file.
   
• Archiver: If we enable archive mode, this process is responsible for copying the WAL log files to a specified directory.

Data Files / Data Directory Structure

• PostgreSQL comprises multiple databases, which are referred to as a database cluster. When we initialize the PostgreSQL database, we create template0, template1, and Postgres databases.
   
• Template0 and template1 are user database databases containing the system catalogue tables.
   
• The template1 database will be cloned to create the user database.

Creating and Accessing the Database in PostgreSQL

One thing to keep in mind when creating a database is that we must either have the privilege of creating a database or be a superuser. In PostgreSQL, there are two ways to create a database. These are as follows:

• On the PostgreSQL shell prompt, type CREATE DATABASE.
   
• Using the createdb command from the command prompt.
   

After creating a database, you can access it by:
 

• Running the PostgreSQL interactive terminal program, psql allows you to enter, edit, and execute SQL commands interactively.
   
• To create and manipulate a database, use an existing graphical frontend tool such as pgAdmin or an office suite with ODBC or JDBC support. 
   
• Making a custom application with one of the available language bindings.

Frequently Asked Questions

What is PostgreSQL's architecture?

PostgreSQL's system architecture is based on the Process-Per-Transaction Model (Client/Server Model). Postmaster, a central coordinating process, manages a running PostgreSQL site. It is also referred to as the Server Process.

What are the PostgreSQL architecture components?

PostgreSQL's physical structure is straightforward; it includes the following components: Memory Pool. Background operations. Data directory structure / Data files.

In PostgreSQL, how many different types of backups are there?

There are three distinct approaches to backing up PostgreSQL data: The SQL dump. Backup at the file system level. Archiving indefinitely.

Is PostgreSQL a relational database?

PostgreSQL is a high-performance, enterprise-class open-source relational database that can be queried using SQL (relational) or JSON (non-relational).

Conclusion

In this article, we have learned about the architecture fundamentals of PostgreSQL. Also, we have seen how to create and access a database. 

Go through the below articles to get a good understanding of PostgreSQL. 

Connecting to PostgreSQL

PostgreSQL Create Database

PostgreSQL Data Types

Addressing Modes of 8086

Spring Boot Architecture
 

You can refer to our guided paths on Coding Ninjas Studio to learn more about DSA, Competitive Programming, JavaScript, System Design, etc. Enrol in our courses and refer to the mock test and problems available. Take a look at the interview experiences and interview bundle for placement preparations.

Happy Learning!