I have a product that uses PostgreSQL (v9.6) as database on Windows, and we often observe that the "shared working set" memory reported for the individual connection processes is quite high (around 200MB), but the "private working set" is relatively low (around 5 MB). I assume this is due to the processes utilizing the shared buffers of Postgres, and in these cases everything works fine as the total memory consumption on the system is quite low. But for some deployments we have observed that the "private working set" is around 200 MB as well, and in these cases the server quickly runs out of memory when the number of connections rises. I have never been able to reproduce this behavior myself.
So my question is: When does a postgres process forked for a connection use private memory instead of shared, and what can I do to avoid this?
The reference documentation (https://www.postgresql.org/docs/9.6/runtime-config-resource.html) describes the settings affecting memory usage including the "shared_buffers" setting which I can see has a clear impact on the "shared working set" utilization, but I find no mention of potential "private working set" consumers. This page also gives clues about performance tuning, but again no mention of private memory: https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
All settings typically referred to (like "work_mem" and "temp_buffers") are at default settings, and I have a very hard time seeing how those settings could add up to memory in the magnitude of 200MB. The "max_connections" settings is quite high (>1000), but there are nowhere near that many connections active in the system. The only way I have been able to trigger growth of processes in my tests is to extract large amounts of data, but when I do that, it is always the "shared working set" that grows. "private working set" remains stable at around 5 MB. --
Best regards, Øystein Kolsrud