{"id":285,"date":"2023-11-15T17:26:28","date_gmt":"2023-11-15T17:26:28","guid":{"rendered":"https:\/\/test.samplewebsitelink.com\/ordba\/?p=285"},"modified":"2023-11-23T16:37:32","modified_gmt":"2023-11-23T16:37:32","slug":"postgresql-shared-memory-and-other-memory-usage","status":"publish","type":"post","link":"https:\/\/test.samplewebsitelink.com\/ordba\/2023\/11\/15\/postgresql-shared-memory-and-other-memory-usage\/","title":{"rendered":"PostgreSQL – Shared Memory and Other Memory usage"},"content":{"rendered":"\n

Internals of PostgreSQL
Srinivas Maddali
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
i. Semaphores
ii. Light-waited locks
iii. Shared and exclusive locks
iv. Checkpointing
v. Auto vacuum
vi. Save-point.
vii. Commit<\/p>\n\n\n\n


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.<\/p>\n\n\n\n


The PostgreSQL documentation available onhttps:\/\/www.postgresql.org\/docs\/current\/bgworker.html <\/a>the background worker processes are spawned to serve<\/p>\n\n\n\n

1. BGWORKER_SHMEM_ACCESS
2. BGWORKER_BACKEND_DATABASE_CONNECTION<\/p>\n\n\n\n


A request for shared memory is made by the client process.<\/p>\n\n\n\n

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.<\/p>\n\n\n\n

BGWORKER_BACKEND_DATABASE_CONNECTION related parameters as listed by the docs of the PostgreSQL. Version 13 ( the functions called by the PostgreSQLstart command)<\/p>\n\n\n\n


a. bgw_start_time
b. BgWorkerStart_PostmasterStart
c. BgWorkerStart_ConsistentState
d. BgWorkerStart_RecoveryFinished — related to hot standby
e. bgw_restart_time
f. bgw_never_restart —— never restart time
g. bgw_library_name
h. bgw_function_name — the function to be started first
i. bgw_function_name
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.<\/p>\n\n\n\n


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.<\/p>\n\n\n\n

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 \u2013 temp_buffers
b. For vacuum, indexing or reindexing purposes \u2013—— maintenance _work_mem
c. For sorting etc., ——- work_mem<\/p>\n","protected":false},"excerpt":{"rendered":"

Internals of PostgreSQLSrinivas MaddaliWhen we discuss internals of PostgreSQL we need to discuss about:a. Shared memory usage by the PostgreSQLb. Usage of the sub-systems by the PostgreSQL instancec. Other memory usage fori. Semaphoresii. Light-waited locksiii. Shared and exclusive locksiv. Checkpointingv. Auto vacuumvi. Save-point.vii. Commit Shared Memory purpose is to meet the inter Process Communication (IPC) …<\/p>\n

PostgreSQL – Shared Memory and Other Memory usage<\/span> Read More »<\/a><\/p>\n","protected":false},"author":1,"featured_media":277,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-gradient":""}},"footnotes":""},"categories":[12],"tags":[],"class_list":["post-285","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-postgresql"],"yoast_head":"\nPostgreSQL - Shared Memory and Other Memory usage - Ordba<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/test.samplewebsitelink.com\/ordba\/2023\/11\/15\/postgresql-shared-memory-and-other-memory-usage\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"PostgreSQL - Shared Memory and Other Memory usage - Ordba\" \/>\n<meta property=\"og:description\" content=\"Internals of PostgreSQLSrinivas MaddaliWhen we discuss internals of PostgreSQL we need to discuss about:a. Shared memory usage by the PostgreSQLb. Usage of the sub-systems by the PostgreSQL instancec. Other memory usage fori. Semaphoresii. Light-waited locksiii. Shared and exclusive locksiv. Checkpointingv. Auto vacuumvi. Save-point.vii. Commit Shared Memory purpose is to meet the inter Process Communication (IPC) … PostgreSQL – Shared Memory and Other Memory usage Read More »\" \/>\n<meta property=\"og:url\" content=\"https:\/\/test.samplewebsitelink.com\/ordba\/2023\/11\/15\/postgresql-shared-memory-and-other-memory-usage\/\" \/>\n<meta property=\"og:site_name\" content=\"Ordba\" \/>\n<meta property=\"article:published_time\" content=\"2023-11-15T17:26:28+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-11-23T16:37:32+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/test.samplewebsitelink.com\/ordba\/wp-content\/uploads\/2023\/11\/postgresql.png\" \/>\n\t<meta property=\"og:image:width\" content=\"332\" \/>\n\t<meta property=\"og:image:height\" content=\"152\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"ordba\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"ordba\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/test.samplewebsitelink.com\/ordba\/2023\/11\/15\/postgresql-shared-memory-and-other-memory-usage\/\",\"url\":\"https:\/\/test.samplewebsitelink.com\/ordba\/2023\/11\/15\/postgresql-shared-memory-and-other-memory-usage\/\",\"name\":\"PostgreSQL - Shared Memory and Other Memory usage - Ordba\",\"isPartOf\":{\"@id\":\"https:\/\/test.samplewebsitelink.com\/ordba\/#website\"},\"datePublished\":\"2023-11-15T17:26:28+00:00\",\"dateModified\":\"2023-11-23T16:37:32+00:00\",\"author\":{\"@id\":\"https:\/\/test.samplewebsitelink.com\/ordba\/#\/schema\/person\/7365beabb32b7aa77cf0e890ee90ea0f\"},\"breadcrumb\":{\"@id\":\"https:\/\/test.samplewebsitelink.com\/ordba\/2023\/11\/15\/postgresql-shared-memory-and-other-memory-usage\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/test.samplewebsitelink.com\/ordba\/2023\/11\/15\/postgresql-shared-memory-and-other-memory-usage\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/test.samplewebsitelink.com\/ordba\/2023\/11\/15\/postgresql-shared-memory-and-other-memory-usage\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/test.samplewebsitelink.com\/ordba\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"PostgreSQL – Shared Memory and Other Memory usage\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/test.samplewebsitelink.com\/ordba\/#website\",\"url\":\"https:\/\/test.samplewebsitelink.com\/ordba\/\",\"name\":\"Ordba\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/test.samplewebsitelink.com\/ordba\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/test.samplewebsitelink.com\/ordba\/#\/schema\/person\/7365beabb32b7aa77cf0e890ee90ea0f\",\"name\":\"ordba\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/test.samplewebsitelink.com\/ordba\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/dd4335bb4e225c36ce7c56a0aff84bb202ebb523ebdc5d1e1a11cc3f3e6085f3?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/dd4335bb4e225c36ce7c56a0aff84bb202ebb523ebdc5d1e1a11cc3f3e6085f3?s=96&d=mm&r=g\",\"caption\":\"ordba\"},\"sameAs\":[\"https:\/\/test.samplewebsitelink.com\/ordba\"],\"url\":\"https:\/\/test.samplewebsitelink.com\/ordba\/author\/ordba\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"PostgreSQL - Shared Memory and Other Memory usage - Ordba","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/test.samplewebsitelink.com\/ordba\/2023\/11\/15\/postgresql-shared-memory-and-other-memory-usage\/","og_locale":"en_US","og_type":"article","og_title":"PostgreSQL - Shared Memory and Other Memory usage - Ordba","og_description":"Internals of PostgreSQLSrinivas MaddaliWhen we discuss internals of PostgreSQL we need to discuss about:a. Shared memory usage by the PostgreSQLb. Usage of the sub-systems by the PostgreSQL instancec. Other memory usage fori. Semaphoresii. Light-waited locksiii. Shared and exclusive locksiv. Checkpointingv. Auto vacuumvi. Save-point.vii. Commit Shared Memory purpose is to meet the inter Process Communication (IPC) … PostgreSQL – Shared Memory and Other Memory usage Read More »","og_url":"https:\/\/test.samplewebsitelink.com\/ordba\/2023\/11\/15\/postgresql-shared-memory-and-other-memory-usage\/","og_site_name":"Ordba","article_published_time":"2023-11-15T17:26:28+00:00","article_modified_time":"2023-11-23T16:37:32+00:00","og_image":[{"width":332,"height":152,"url":"https:\/\/test.samplewebsitelink.com\/ordba\/wp-content\/uploads\/2023\/11\/postgresql.png","type":"image\/png"}],"author":"ordba","twitter_card":"summary_large_image","twitter_misc":{"Written by":"ordba","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/test.samplewebsitelink.com\/ordba\/2023\/11\/15\/postgresql-shared-memory-and-other-memory-usage\/","url":"https:\/\/test.samplewebsitelink.com\/ordba\/2023\/11\/15\/postgresql-shared-memory-and-other-memory-usage\/","name":"PostgreSQL - Shared Memory and Other Memory usage - Ordba","isPartOf":{"@id":"https:\/\/test.samplewebsitelink.com\/ordba\/#website"},"datePublished":"2023-11-15T17:26:28+00:00","dateModified":"2023-11-23T16:37:32+00:00","author":{"@id":"https:\/\/test.samplewebsitelink.com\/ordba\/#\/schema\/person\/7365beabb32b7aa77cf0e890ee90ea0f"},"breadcrumb":{"@id":"https:\/\/test.samplewebsitelink.com\/ordba\/2023\/11\/15\/postgresql-shared-memory-and-other-memory-usage\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/test.samplewebsitelink.com\/ordba\/2023\/11\/15\/postgresql-shared-memory-and-other-memory-usage\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/test.samplewebsitelink.com\/ordba\/2023\/11\/15\/postgresql-shared-memory-and-other-memory-usage\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/test.samplewebsitelink.com\/ordba\/"},{"@type":"ListItem","position":2,"name":"PostgreSQL – Shared Memory and Other Memory usage"}]},{"@type":"WebSite","@id":"https:\/\/test.samplewebsitelink.com\/ordba\/#website","url":"https:\/\/test.samplewebsitelink.com\/ordba\/","name":"Ordba","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/test.samplewebsitelink.com\/ordba\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/test.samplewebsitelink.com\/ordba\/#\/schema\/person\/7365beabb32b7aa77cf0e890ee90ea0f","name":"ordba","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/test.samplewebsitelink.com\/ordba\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/dd4335bb4e225c36ce7c56a0aff84bb202ebb523ebdc5d1e1a11cc3f3e6085f3?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/dd4335bb4e225c36ce7c56a0aff84bb202ebb523ebdc5d1e1a11cc3f3e6085f3?s=96&d=mm&r=g","caption":"ordba"},"sameAs":["https:\/\/test.samplewebsitelink.com\/ordba"],"url":"https:\/\/test.samplewebsitelink.com\/ordba\/author\/ordba\/"}]}},"_links":{"self":[{"href":"https:\/\/test.samplewebsitelink.com\/ordba\/wp-json\/wp\/v2\/posts\/285","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/test.samplewebsitelink.com\/ordba\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/test.samplewebsitelink.com\/ordba\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/test.samplewebsitelink.com\/ordba\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/test.samplewebsitelink.com\/ordba\/wp-json\/wp\/v2\/comments?post=285"}],"version-history":[{"count":2,"href":"https:\/\/test.samplewebsitelink.com\/ordba\/wp-json\/wp\/v2\/posts\/285\/revisions"}],"predecessor-version":[{"id":692,"href":"https:\/\/test.samplewebsitelink.com\/ordba\/wp-json\/wp\/v2\/posts\/285\/revisions\/692"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/test.samplewebsitelink.com\/ordba\/wp-json\/wp\/v2\/media\/277"}],"wp:attachment":[{"href":"https:\/\/test.samplewebsitelink.com\/ordba\/wp-json\/wp\/v2\/media?parent=285"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/test.samplewebsitelink.com\/ordba\/wp-json\/wp\/v2\/categories?post=285"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/test.samplewebsitelink.com\/ordba\/wp-json\/wp\/v2\/tags?post=285"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}