Common database Object names

Postgres use processes not thread, the postmaster process acts as a supervisor. There are several ultility processes perform background work Postmaster starts them, restarts them if they die, one backend process per user session, Postmaster listens for new connections

• Postmaster : Spawn database process, only one postmaster process in database instance
• Shared Buffers : Use for Reading and Writing operations on data files
• WAL Buffers : User to perform write transaction logs area, change write to wal file in each commit
• BGWRITER : Continue sync dirty pages from Shared Buffers to data files
• WAL WRITER : Write change from WAL Buffers to WAL Segmments file
• CHECKPOINT : Check all changes are saved to data files, it can be time-based or transaction-based
• STATS COLLECTOR : Track server activities, provide stats information
• WRITER : Write Error Log Files, checking slow queries and auditing
• LOGICAL REPLICATION: Launch replicate process when require
• AUTOVACUUM: Analyze, free up space, on individual tables ... base con configuration settings
• ARCHIVER : Reponse for acrhiver activities

Process and memory Demo

    bash-5.1# ps -ef | grep postgres
    1 postgres  1:32 postgres
    23 postgres  1:51 postgres: checkpointer   
    24 postgres  0:05 postgres: background writer   
    25 postgres  0:06 postgres: walwriter   
    26 postgres  1:34 postgres: autovacuum launcher   
    27 postgres  0:00 postgres: archiver   last was 00000001000000290000002F
    28 postgres  6:56 postgres: stats collector   
    29 postgres  0:00 postgres: TimescaleDB Background Worker Launcher   
    30 postgres  0:00 postgres: logical replication launcher  

Find current backend process of session

Find current backend process of session

    bash-5.1# ps -ef | grep 10920
    503 root      0:00 grep 10920
    10920 postgres  0:00 postgres: db_dev db_dev [local] idle

How postmaster work :

Read buffering

Write Buffering

Commit and checkpoint

• Before commit : Uncommitted updates are in memory
• After commit : WAL buffers are written to disk and shared buffers are marked as commited
• After checkpoint : Modified data pages are written from shared memory to the data files

Memory Settings:

• shared_buffers- Size of shared buffer pool for a cluster
• temp_buffers- Amount of memory used caching temporary tables
• work_mem- Amount of memory used sorting and hashing operations
• maintenance_work_mem- List of SSL ciphers that may be used for secure connections
• temp_file_limit- Amount of disk space used for temporary files