Hundreds of databases connected to metabase

I have around 100 small databases connected to metabase each for a different client. These databases contain the same columns and tables, but having them seperated into their own databases allows for nice access control to data between clients.

The issue I am starting to face is metabase having too many open "idle" connections to the postgres instance. I know that the number of connections per database is set to 15 per database, but if i want to support lets say 500 databases on my metabase instance - my postgres instance will struggle to keep up to with all of the requested open connections.

Maybe someone has faced a similar issue and willing to share some tips?

Thanks!

Hi @BroBan
You should probably tweak the amount of connections:
https://www.metabase.com/docs/latest/configuring-metabase/environment-variables#mb_jdbc_data_warehouse_max_connection_pool_size
And you could also have a cronjob script that automatically closes idle connections after some time.
Example:

DO $ BEGIN
  PERFORM pg_terminate_backend("pid")
  FROM "pg_stat_activity"
  WHERE "pid" <> pg_backend_pid()
    AND "application_name" LIKE 'Metabase v%'
    AND "state" = 'idle';
END $;
1 Like