PostgreSQL - Tuning work_mem and max_connections

Question

I am using PGTune to help tune my web application using PostgreSQL 9.5

Some background - The web app is a real estate search site, with up to 200 active users during the day performing fairly complex location-based queries. GeoDjango is used to host the site.

My question is about tuning work_mem and max_connections. In PGTune, I noticed that when I increase max_connections, the recommended work_mem decreases. I had several questions in mind:

1. As a rule of thumb, should you keep max_connections as low as possible, to allow for more work_mem to be freed up for better performance for each individual query?
2. For a website with up to 200 active users at a time, constantly performing queries - what number of max_connections I should have?
3. I currently have my max_connections set to 500 and the recommended work_mem by PGTune is 8388kB. Is it worth reducing max_connections to 400 or maybe 300, which allows me to increase work_mem?

Answer 1

I noticed that when I increase max_connections, the recommended work_mem decreases

PGTune assumes that if more connections are open, then there will be more queries running simultaneously, and then more multiple of work_mem are likely to be allocated, but such allocations happen on the fly. In practice it depends on what the queries sent to the database will do, which PGTune can't know. There is no hard dependency between max_connections and work_mem.

Since your database is already running in production, you can know much better than PGTune how to tune it by observing it.

Do the queries use temporary storage on disk? Monitor temp_files and temp_bytes in pg_database. If they don't, you don't need to increase work_mem anyway.

Does the server has lots of free memory? If it does, you can increase work_mem without caring about max_connections.

How much connections do you actually have (select count(*) from pg_stat_activity) compared to max_connections? If you're close to the limit and you're reducing it, your apps will fail to connect.