PostgreSQL - Shared Memory and Other Memory usage
Internals of PostgreSQL
When we discuss internals of PostgreSQL we need to discuss about:
a. Shared memory usage by the PostgreSQL
b. Usage of the sub-systems by the PostgreSQL instance
c. Other memory usage for
ii. Light-waited locks
iii. Shared and exclusive locks
v. Auto vacuum
Shared Memory purpose is to meet the inter Process Communication (IPC) of process that work for the server of the PostgreSQL database server.
The following diagram may help understand those communications.
The PostgreSQL documentation available onhttps://www.postgresql.org/docs/current/bgworker.html the background worker processes are spawned to serve
A request for shared memory is made by the client process.
1. A server process connection is requested by the incoming process (client).
2. The server process that receives the client process forks and communicates with
a. the BG writer process and checkpointer
b. the shared memory which forks to
i. auto vacuum process
ii. WAL (write ahead log) writer process.
iii. Stats collector
iv. Log writer and archiver
c. Shared memory communicates bidirectional.
d. The shared memory communicates with the database bidirectional
3. Client communication taps the doors of the port open for multiple processes (in coming) from the clients.
4. The parent process is owned by postgres and forked processes are also owned by postgres.
BGWORKER_BACKEND_DATABASE_CONNECTION related parameters as listed by the docs of the PostgreSQL. Version 13 ( the functions called by the PostgreSQLstart command)
d. BgWorkerStart_RecoveryFinished --- related to hot standby
f. bgw_never_restart ------ never restart time
h. bgw_function_name --- the function to be started first
j. exec_backend ------------- windows only parameter
k. bgw_extra ----------------- can contain extra data to be passed to the background worker.
l. bgw_notify_-----------------PID of a PostgreSQL backend process to which the postmaster should send SIGUSR1 when the process is started or exits.
m. bgworker_bypass_allowconn ---- flags it is possible to bypass the restriction to connect to databases not allowing user connections.
n. bgw_restart_time ------------- for a background worker is configured as bgw_never_restart, or if it exits with an exit code of 0 or is terminated by TerminateBackgroundWorker, it will be automatically unregistered by the postmaster on exit.
o. bgwh_postmaster_died ---- when the status is bgwh_stopped
p. max_worker_processes ------ The maximum number of registered background workers is limited.
The background processes (at the high level as seen by a user) are as under:
a. the background writer
b. the check pointer c. auto vacuum launcher d. WAL writer e. The statistics collector f. Logging collector (log) g. Archiver
The memory architecture
Background processes access shared memory (IPC) and then they access data buffers (they are also shared).
Background processes access shared memory to write WAL buffers.
Background processes access shared memory to write to Commit Log (CLOG).
Background processes access shared memory to write to data buffers.
The access is bidirectional.
Non-shared memory access by the client processes which spawn forked processes in the hosting server. Those processes can access using the following parameters
a. Temporary buffers – temp_buffers
b. For vacuum, indexing or reindexing purposes –------ maintenance _work_mem
c. For sorting etc., ------- work_mem