Postgres connections optimization in multiple vhosts scenario

[sandrobordacchini]

Hello everyone!
I am looking for an hint about configuration.

I am investigating the feasibility of using a single ejabberd instance in a multiple vhosts scenario, where each vhost has very little traffic but there are (possibly) hundreds of vhosts, let's say max 500 vhosts.

I thought about using postgres as db for performance and reliability.

Whether I use the legacy sql schema (1 db for each vhost) or the new one (one big db with server_host column), at boot ejabberd opens up
sql_pool_size * <number_of_vhosts>
connections to the postgres process, even with no activity.
That means that even if I squeeze down sql_pool_size to 2, I am going to have up to 1000 connections open to pgsql!

I searched the docs/forums and actually it seems that nobody else has this issue.

So I am wondering if my design is flawed or if I miss something really big.

Thanks!

[prefiks]

Hello,

indeed, right now each host get separate sql pool (mostly relict of each host having different sql database setup). I did look if we can do something with it and come with this: prefiks@0164a34 - it adds option sql_global_pool: true for using shared pool, but it need a bit more work to be merged, like checking if all hosts use same database, detection when workers are no longer needed and can be stopped, etc.

[sandrobordacchini]

Thank you @prefiks

I saw your commit, it seems that it would take a bit of time for this feature to be rolled out in a release, right?

I thought that a workaround could be the "single db" deployment + pgbouncer.
Do you have any case history on this kind of deployment?