#PostgreSQL Database Architecture | Postgres Architecture | PostgreSQL Tutorial

#PostgreSQL Database Architecture | Postgres Architecture | PostgreSQL Tutorial
Agenda
Understanding the Architecture
PostgreSQL Architecture
Memory Structure
Shared Memory
Shared Buffer
WAL Buffer
Process Structure
PostgreSQL Process Types
Postmaster Process
Backend Process
Client Process
Storage Structure
Database Structure
Understanding the Architecture
PostgreSQL was first released in 1989, and since then, there have been a lot of enhancements.
According to db-engines, it is the fourth most used database.
In this session, we will discuss PostgreSQL internals, its architecture, and how the various components of PostgreSQL interact with one another.
PostgreSQL Architecture
The physical structure of PostgreSQL is very simple. It consists of shared memory and a few background processes and data files.
Shared Memory
Shared Memory refers to the memory reserved for database caching and transaction log caching.
The most important elements in shared memory are Shared Buffer and WAL buffers
Shared Buffer
• The purpose of Shared Buffer is to minimize DISK IO.
• The following principles must be met
• You need to access very large (tens, hundreds of gigabytes) buffers quickly.
• You should minimize contention when many users access it at the same time.
• Frequently used blocks must be in the buffer for as long as possible
• It is PostgreSQL Database Memory Area
• Shared by all the Databases in the Cluster
• Modified Buffers are also called as Dirty Buffers
• Parameter : shared_buffers sets the amount of RAM allocated to shared_buffers
• Uses LRU Algorithm to flush less frequently used buffers
• Dirty Buffers written to disk after a CHECKPOINT

WAL Buffer
The WAL buffer is a buffer that temporarily stores changes to the database. The contents stored in the WAL buffer are written to the WAL file at a predetermined point in time. From a backup and recovery point of view, WAL buffers and WAL files are very important.
PostgreSQL Process Types
1. Postmaster (Daemon) Process
2. Background Process
3. Backend Process
4. Client Process
Postmaster Process
The Postmaster process is the first process started when you start PostgreSQL. At startup, performs recovery, initialize shared memory, and run background processes. It also creates a backend process when there is a connection request from the client process.
Process Role
logger Write the error message to the log file.
checkpointer When a checkpoint occurs, the dirty buffer is written to the file.
writer Periodically writes the dirty buffer to a file.
wal writer Write the WAL buffer to the WAL file.
Autovacuum launcher Fork autovacuum worker when autovacuum is enabled.It is the responsibility of the autovacuum daemon to carry vacuum operations on bloated tables on demand
archiver When in Archive.log mode, copy the WAL file to the specified directory.
stats collector DBMS usage statistics such as session execution information ( pg_stat_activity ) and table usage statistical information ( pg_stat_all_tables ) are collected.
Items related to the database
1. PostgreSQL consists of several databases. This is called a database cluster.
2. When initdb () is executed, template0, template1, and postgres databases are created.
3. The template0 and template1 databases are template databases for user database creation and contain the system catalog tables.
4. The list of tables in the template0 and template1 databases is the same immediately after initdb (). However, the template1 database can create objects that the user needs.
5. The user database is created by cloning the template1 database.
Items related to the tablespace
1. The pg_default and pg_global tablespaces are created immediately after initdb().
2. If you do not specify a tablespace at the time of table creation, it is stored in the pg_dafault tablespace.
3. Tables managed at the database cluster level are stored in the pg_global tablespace.
4. The physical location of the pg_default tablespace is $PGDATAbase.
5. The physical location of the pg_global tablespace is $PGDATAglobal.
6. One tablespace can be used by multiple databases. At this time, a database-specific subdirectory is created in the table space directory.
7. Creating a user tablespace creates a symbolic link to the user tablespace in the $PGDATAtblspc directory.
Items related to the table
1. There are three files per table.
2. One is a file for storing table data. The file name is the OID of the table.
3. One is a file to manage table free space. The file name is OID_fsm.
4. One is a file for managing the visibility of the table block. The file name is OID_vm .
5. The index does not have a _vm file. That is, OID and OID_fsm are composed of two files.

Visit www.theskillpedia.com for Online Training on this technology