Use Postgres connection pooling for Nominatim. Resolve "too many clients already" error.

[shumailxyz]

I did a fresh install of Nominatim 4.4 along with Postgis 15-3.4. Most of the things work fine as expected however one of my scripts broke.

That script basically was doing around 500 reverse geocoding requests on /reverse.php concurrently. It was working fine with Nominatim 4.2 and Postgres 11 with Postgis 2.5 (was using the docker image mdillon/postgis).

With the updated installation, around 100 (probably due to the default value of max_connections = 100 in postgresql.conf) out of 500 requests are successful and for the rest, nominatim returnes 500 internal server error. I didn't find anything in Nominatim or apache2 logs but when Ichecked the Postgres logs, i saw these logs:

FATAL:  sorry, too many clients already
FATAL:  sorry, too many clients already

Did something change in newer version in regards to how connection with Postgres is initialized? Can i configure nominatim to initiate the connection pool with Postgres and reuse connections? Would appreciate if someone has any pointers on how to approach this. Thanks!

[mtmail]

I ran into the same issue in earlier Nominatim versions, too. Just set the max_connections higher. Both 500 and 1000 works.

[shumailxyz]

Thanks, sure, i could do that.

However, I wonder if there's a way to make nominatim use a connection pool and reuse connections?

[mtmail]

Nominatim starting version 4.3 has two options for the frontend: PHP and Python. You can see them documented at https://nominatim.org/release-docs/latest/admin/ . PHP will be removed in future versions.

You asked a question in the media docker repository so I'm guessing you use their docker image. That still uses PHP.

NOMINATIM_API_POOL_SIZE is configurable (https://nominatim.org/release-docs/latest/customize/Settings/#nominatim_api_pool_size) for the Python frontend. I haven't looked closely at tuning the parameter, so far increasing the max_connections in Postgres worked well enough for me.

[shumailxyz]

Thanks. Increasing the max_connections does resolve the issue indeed.

Good to know about the new Python option. All the best! :)