PostgreSQL High Memory consumption

Question

We have been getting a lot of issues pertaining memory consumption in Postgres. I am thinking of deploying PgBouncer to resolve this issue, but wanted to know what could be the cause for this.

ps aux --sort=-%mem | head -30
USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
postgres 23438 41.0 6.2 78075200 8258220 ? Ssl Aug09 525:40 postgres: xxxxx: xxxxx(60028) idle
postgres 4225 31.7 5.9 77797888 7903220 ? Ssl Aug09 438:58 postgres: xxxxx: xxxxx(45012) idle
postgres 118999 39.3 4.8 76290020 6395136 ? Ssl Aug09 306:52 postgres: xxxxx: xxxxx(50026) idle
free -g
 total used free shared buff/cache available
Mem: 125 36 35 3 54 84
Swap: 19 0 19
System RAM : 128 GB
CPU(s) : 32
Shared_buffer : 32 GB
DB Size : 24 GB
Max Connections : 200
Average Connections: 50
Effective_Cache_Size : 64 GB
Work_mem : 41MB
PG Version : 11
OS : Ubuntu

A lot of the idle connections are getting reused despite it being closed from the app side. I have tuned the database by changing Shared_buffer, work_mem to values as per PostgreSQL expectations, but still getting these issues.

PS : We have setup HAProxy between App and DB.

Answer 1

This is probably shared memory that is common to all PostgreSQL processes.

ps is not smart about distinguishing private memory and shared memory.

Don't worry.

• Argha: Thank you for your response. "This is probably shared memory that is common to all PostgreSQL processes." - So, the used memory showing in free command is an amalgamation of all processes which are running against Postgres. Correct? Also, what tool to use for monitoring PostgreSQL memory utilization performance as an alternative to "ps".
• Laurenz Albe: Yes, private and shared memory are added. You can look at /proc/<pid>/smaps_rollup for a process for better memory accounting.
• Argha: Thank you!! I will do more research on this.