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.
