Performance Issues with User Listing in Large-Scale Vaultwarden Deployments

[Lowaiz]

Context

Hi,

First off, thank you for the amazing work on Vaultwarden! We’ve been running it successfully for our organization, but as our usage has grown, we’ve noticed performance bottlenecks when managing users, likely due to increased data volume and query inefficiencies.

Performance Issues

We are experiencing significant slowdowns when listing users:

• A heavy Bitwarden Organization: Approximately 450 users, 1,000 collections, and 2,000 ciphers. The members panel takes around two minutes to load.
• Admin Dashboard (Overall Instance Usage): Approximately 500 users and 9,000 ciphers. The admin users dashboard takes about a minute and a half to load.

Root Causes

From our analysis, the slowdowns seem to originate from:

• SQL queries inside loops when retrieving user information, particularly within the /admin/users/overview endpoint.
• Multiple SQL queries when generating JSON dumps of a user in the to_json function, especially when fetching organization-related data.

Optimization Efforts

I started optimizing queries for the /admin/users and /admin/users/overview endpoints and I'm also exploring improvements to the User.to_json function.

We are eager to contribute to Vaultwarden to help other users address these performance issues and ensure the project can scale for larger adoption. However, we are facing challenges, as the optimization of the User.to_json function will certainly need a big re-factorization, so we would greatly appreciate your guidance before we proceed with a pull request.

Thanks again for your time and effort on Vaultwarden!

Best regards

[Lowaiz]

Hello,

Just to do a quick follow up, after further testing, our diagnostic was not a bit mislead by our configuration:

The slowness did come from the SQL queries, but it was not the optimization or the number of them that was in cause, the database pooling system was.
Our Vaultwarden instance was connected to our PostgreSQL servers, which are by default behind a connection pooler, but as Vaultwarden already has one implemented, bypassing the Postgre one resolved our problems, with loading times down to few seconds.

Anyway, if I have some time in the next weeks, I'll do a cleanup on some reworked queries and open a pull request, so you could use them if you find them OK.

I also close the discussion, as our problems are now fixed.