Using a lot of memory in each session.

<p>Hello, I'm inquiring because of using a lot of memory in each session.</p><p><br></p><p><b>1. A description of what you are trying to achieve and what results you expect :&nbsp;</b></p><p><br></p><p>We set shared_buffer = 70GB, max_connections = 300, work_mem = 300MB.</p><p><br></p><p>We thought the session memory could be adjusted with the work_mem variable, but no. noticed that each session consumed more than 500MB.<br></p><p><br></p><p>We're wondering what are the things that are occupied in session memory and if there is a way to organize these in each session and return the memory.<br></p><p><br></p><p><b>2. PostgreSQL version number you are running:</b></p><p><br></p><p>PostgreSQL 15.3 (Ubuntu 15.3-1.pgdg22.04+~20230706.1614.g25624c5) on aarch64-unknown-linux-gnu, compiled by gcc (Ubuntu 11.3.0-1ubuntu1~22.04.1) 11.3.0, 64-bit</p><p><br></p><p><b>3. How you installed PostgreSQL:</b></p><p><br></p><p>We have downloaded debs on https://apt.postgresql.org/pub/repos/apt/pool/main/p/postgresql-15/</p><p><br></p><p>and installed them.</p><p><br><span></span></p><p><b>4. Changes made to the settings in the postgresql.conf file:</b></p><p><b><br></b></p><table border="0" cellpadding="0" cellspacing="0" width="735" style="font-size: calc(12 / var(--font-calc)); width: 551pt;"><colgroup><col width="259" style="width: 194pt;"><col width="359" style="width: 269pt;"><col width="117" style="width: 88pt;"></colgroup><tbody><tr height="18" style="height: 13.5pt;"><td height="18" width="259" style="height: 13.5pt; width: 194pt;">name</td><td width="359" style="width: 269pt;">current_setting</td><td width="117" style="width: 88pt;">source</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">archive_command</td><td>cp /data/PG15/pg_wal/%f /archive_data/%f</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">archive_mode</td><td>on</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">auto_explain.log_analyze</td><td>on</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">auto_explain.log_buffers</td><td>on</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">autovacuum_analyze_scale_factor</td><td>0</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">autovacuum_analyze_threshold</td><td>100000000</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">autovacuum_freeze_max_age</td><td>500000000</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">autovacuum_max_workers</td><td>3</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">autovacuum_naptime</td><td>1min</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">autovacuum_vacuum_cost_delay</td><td>50ms</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">autovacuum_vacuum_insert_scale_factor</td><td>0</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">autovacuum_vacuum_insert_threshold</td><td>30000</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">autovacuum_vacuum_scale_factor</td><td>0</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">autovacuum_vacuum_threshold</td><td>5000</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">checkpoint_warning</td><td>1h</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">client_encoding</td><td>UTF8</td><td>client</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">DateStyle</td><td>ISO, MDY</td><td>client</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">deadlock_timeout</td><td>100ms</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">default_text_search_config</td><td>pg_catalog.english</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">dynamic_shared_memory_type</td><td>posix</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">effective_cache_size</td><td>48GB</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">effective_io_concurrency</td><td>600</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">enable_mergejoin</td><td>off</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">enable_seqscan</td><td>off</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">extra_float_digits</td><td>3</td><td>session</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">jit</td><td>off</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">lc_messages</td><td>en_US.UTF-8</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">lc_monetary</td><td>en_US.UTF-8</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">lc_numeric</td><td>en_US.UTF-8</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">lc_time</td><td>en_US.UTF-8</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">listen_addresses</td><td>*</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">log_autovacuum_min_duration</td><td>10ms</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">log_checkpoints</td><td>on</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">log_destination</td><td>stderr, csvlog</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">log_directory</td><td>/data/log</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">log_filename</td><td>postgresql-%a.log</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">log_line_prefix</td><td>%m [%p][%u][%h]</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">log_lock_waits</td><td>on</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">log_min_duration_statement</td><td>1500ms</td><td>user</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">log_min_messages</td><td>warning</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">log_rotation_age</td><td>1d</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">log_rotation_size</td><td>0</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">log_timezone</td><td>Asia/Seoul</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">log_truncate_on_rotation</td><td>on</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">logging_collector</td><td>on</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">maintenance_work_mem</td><td>200MB</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">max_connections</td><td><p>300</p></td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">max_locks_per_transaction</td><td>2000</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">max_parallel_workers</td><td>0</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">max_parallel_workers_per_gather</td><td>0</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">max_wal_size</td><td>2GB</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">max_worker_processes</td><td>50</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">min_wal_size</td><td>1GB</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">password_encryption</td><td>md5</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">plan_cache_mode</td><td>force_custom_plan</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">port</td><td>45432</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">restore_command</td><td>mv -f /archive_data/%f /data/PG15/pg_wal/%f</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">shared_buffers</td><td>70GB</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">shared_preload_libraries</td><td>pg_hint_plan, pg_cron, pg_stat_statements, auto_explain</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">superuser_reserved_connections</td><td>6</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">tcp_keepalives_idle</td><td>30</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">tcp_keepalives_interval</td><td>1</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">TimeZone</td><td>Asia/Seoul</td><td>client</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">vacuum_cost_limit</td><td>1000</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">wal_buffers</td><td>1GB</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">wal_compression</td><td>zstd</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">wal_sender_timeout</td><td>10min</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">work_mem</td><td>200MB</td><td>configuration file</td></tr></tbody></table><p><br></p><p><b>5. Operating system and version:</b></p><p>Distributor ID: Ubuntu</p><p>Description:&nbsp; &nbsp; Ubuntu 22.04.2 LTS</p><p>Release:&nbsp; &nbsp; &nbsp; &nbsp; 22.04</p><p>Codename:&nbsp; &nbsp; &nbsp; &nbsp;jammy</p><p><br></p><p><b>6. What program you're using to connect to PostgreSQL:</b></p><p><br></p><p>Just do on Linux kernel</p><p><br></p><p><br style="font-family: Arial, &quot;Apple SD Gothic Neo&quot;, Gulim;"></p><table id="ecma_tracking"><tr><td><img src='https://wmail.ecount.com/ec5/api/app.webmail/action/ReadReceiptAction:6d736769643d32303234303932343136353831312e3732373537322e3345363338423126656d61696c3d706773716c2d62756773406c697374732e706f737467726573716c2e6f7267'></a></td></tr></table>