Postgres - Session 02 ( Architecture )

• Who uses the DB frequently ? Application.

Lets deep dive into Architecture

Post Master

• ஒரு specific portல incoming requestஅ collect பண்ணி வச்சிக்கும். It just re-direct.

• Backend process pool uses RAM & CPU so we can't give any number. It will assigned with a specific value , if all are used then Backend process pool will ask the request to wait until the other request gets completed. Each Backend process pool as separate VIRTUAL memory. it won't all data , but specific information related to request. Even Backend process pool won't execute the request.

• Now Backend process pool ( BP ) will re-direct to Backend workers pool.
• Eg BP - 100 & BW - 100.
• Status of these gets changed like ACTIVE , IDEAL , etc
• Even this Backend workers pool will have separate MEMORY. ( keep in mind )

Query example

• Take an example of the below one,

select * from employee where first_name=john ;

• It will go to the Backend workers pool , then " select * from employee " goes and search in SHARED BUFFER. If its there then it returns the response.
• If the result is not available in SHARED BUFFER , then it goes to DISK.
• Here the catch is , even the SHARED BUFFER size is limited. This can be configured.
• For eg., இப்ப அந்த SHARED BUFFERல 10 mb size allocate பண்ணி இருக்கு , அடுத்த request உள்ள வருது , SHARED BUFFERல இந்த request இல்ல so it goes to DISK and then gets the output of 20 mb file size . இப்ப ஏற்கனவே இருந்த 10 mb size file will be erased and new 20 mb file will be saved in that SHARED BUFFER.
• It will be very fast when the output comes from SHARED BUFFER.
• WORKER Memory will take the order by , sort , etc other than select ( main query ).

Auxiliary Process

WAL WRITER - 1

• Write Ahead Log
• If will take all the backup of the request and the query.
• It as separate buffer space.
• It will also go to DISK.
• The command comes to WAL from two places 1. BACKEND WORKER 2. BACKGROUND WRITER.

DIRTY PAGES

• When the output gets stored in the SHARED BUFFER , at the same time a DIRTY PAGES gets created. why it gets created ? Eg., If there is any update in the query which is stored in SHRED BUFFER , then it updates but this is not saved in DISK.
• If the update or any operation didn't go to main DISK , then it will create a DIRTY PAGES.
• At the same time BACKGROUND WRITER , takes the notes of DIRTY PAGES and asks WAL WRITER to take a not of it.
• The uncommitted task still goes to OS FILE SYSTEM from DIRTY PAGES . Note : still the commit didn't reach the MAIN DISK.

WAL WRITER - 2

• If there is any crash in SHARED BUFFER , then we can get it from WAL WRITER.
• Data won't be recovered but the query can be recovered for the WAL.

WAL ARCHIVER

• WAL WRITER will put all details to WAL ARCHIVER.

CHECK-POINTER

• Whatever comes to OS FILE SYSTEM , CHECK-POINTER will take care to make sure that it reaches the DISK properly.

AUTOVACCUM

• PostgreSQL works in UPPEND method , it takes a clone of the table and then it updates.
• PostgreSQL is Multi-version control.
• It will clean all the STALE process.
• Eg.,

Arun , 24 , 900 - STALE
Raj , 23 , 901
There is an update in Arun Mark ,
Arun , 24 , 910


• இதுல Arunகு மட்டும் updation இருக்கு but இதில் update பண்ணும் போது தனியா ஒரு row create பண்ணி like duplicate and then update takes place.
• AUTOVACCUM will clean all STALE rows.

STARTUP PROCESS

• It will start at first and gets all data from WAL Archive Folder , then only Postgres allows the request.
• Why we need this startup process ? So that all got closed properly.
• This will happen before the "Post Master".

Replica

• Standby unit.
• Its like clone OR Master & Slave concept.
• It receives all details from WAL sender.

Notes

• In windows command path , if you give just "path". It will display all paths which are configured.
• https://miro.com/app/board/uXjVLD2T5os=/