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