Why scaling up an app by 50x exceed the PostgreSQL max_connection value of 100? [closed]

Question

I went into this issue on purpose by scaling up a dockerized Python application 50x using this command docker-compose up --scale app=50 app:

This app connects to a single PostgreSQL database (also as a Docker container).

As monitored by pgAdmin4, the server activity is as follow:

I've read that PostgreSQL allows a number of maximum concurrent connections of 100 by default. This should be fine because my app is "only" scaled by a factor of x50.

But I have plenty of such messages from Python:

sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) FATAL:  sorry, too many clients already
sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) FATAL:  sorry, too many clients already
sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) FATAL:  sorry, too many clients already

plus this one sometimes:

psycopg2.errors.DiskFull: could not resize shared memory segment "/PostgreSQL.1997913688" to 196736 bytes: No space left on device

The computer on which I'm running this application has 256 GB of RAM and 2x 1TB almost empty hard drives. I've also noticed that the RAM is only used at ~30%-40% max when the app is actively writing data to the database.

I can then change the max_connections parameters but I went through a nice article on this topic, and it seems tricky to set up correctly, so I prefer not to touch the PostgreSQL default's configuration (unless absolutely needed of course): https://www.cybertec-postgresql.com/en/tuning-max_connections-in-postgresql/

But prior to changing anything in the config, or studying more about pgBouncer which may be better suited here(?), I need to understand why when scaling up my app by a factor of "only" 50x, the PostgreSQL max_connections value, which is 100, is overtaken?
Any hints on this?

Version: I am using postgis/postgis:13-3.1 docker image for the database.

Answer 1

You should look at pg_stat_activity to see what the connections are doing (or have done recently, in the case of idle connections), plus what user and database they logged on to, and what the IP address they come from and what application_name they report. There is a good chance that this will immediately let you know why your app is making more than one connection per app-server. Since you haven't told us anything about your application, what more can we tell you? In general it is not surprising that an app server would have more than one connection. But it seems you are surprised by it, but we don't know why you find it surprising. Was your app carefully designed to not use more than one connection?

could not resize shared memory segment "/PostgreSQL.1997913688" to 196736 bytes: No space left on device

I think the device being referenced here is not some physical device, like RAM or disk. It is a virtual "device" (well, so are RAM and disks, but this is even more virtual than them). I would think you have parallel workers or something trying to use more shared memory than your kernel is configured to allow.

I can then change the max_connections parameters but I went through a nice article on this topic, and it seems tricky to set up correctly, so I prefer not to touch the PostgreSQL default's configuration (unless absolutely needed of course)

I think you have too much respect for the default. There is no reason not to change the default, except that in this case, there is no reason to change it. It sounds like the whole point of this scaling exercise was to see what problems it would cause. And you found a problem: that it uses more connections than you thought it would. So go fix that problem, rather than papering over it by increasing max_connections. You could fix the problem by increasing max_connections. But you could also fix the problem by scaling to only 40 rather than 50. But in either case, what would have been the point?

Same goes for pgbouncer. If you want to study pgbouncer, that's fine. But is that what the point of this exercise was, to give yourself an excuse to go do that?