Severe performance issues on PostgreSQL at scale: N+1 queries, missing foreign-key indexes, and CHAR/VARCHAR UUID mismatch

[eirisdg]

Prerequisites

• I have searched the existing Closed AND Open Issues AND Discussions
• I have searched and read the documentation

Vaultwarden Support String

Your environment (Generated via diagnostics page)

• Vaultwarden version: v1.37.1
• Web-vault version: v2026.6.4
• OS/Arch: linux/x86_64
• Running within a container: true (Base: Debian)
• Database type: PostgreSQL
• Database version: 15.x
• Uses config.json: false
• Uses a reverse proxy: true
• Internet access: true
• Internet access via a proxy: false
• DNS Check: OK
• Browser/Server Time Check: OK
• Server/NTP Time Check: OK
• Domain Configuration Check: OK
• HTTPS Check: OK
• Websocket Check: OK

(Note: reconstructed in the exact format produced by the admin diagnostics page's "Generate Support String" button, using representative values — not a literal capture from a specific instance, to avoid sharing real deployment details. Database version rounded to the minor series.)

Vaultwarden Build Version

v1.37.1

Deployment method

Official Container Image

Custom deployment method

No response

Reverse Proxy

nginx (exact version not relevant to this report — all findings below are server/database-side and reproducible independently of the reverse proxy)

Host/Server Operating System

Linux

Operating System Version

Linux, containerized (e.g. Kubernetes/Docker)

Clients

• Web Vault

Client Version

v2026.6.4 (the underlying issue is server/DB-side and not specific to this web-vault version)

Steps To Reproduce

This was found on a self-hosted instance with several thousand users spread across a few dozen organizations, some with a few thousand shared collections and members. Steps to reproduce the most visible symptom:

1. As an organization admin/owner, open an organization that has a few thousand shared collections and a few hundred members.
2. Click on a member to open the "Edit member" dialog (calls GET /organizations/{orgId}/collections/details).
3. Separately, as a server admin, open /admin → Users, and /admin → Organizations, on an instance with a few thousand users across dozens of organizations.

Expected Result

All three pages should load in well under a second, regardless of organization/instance size, since none of the underlying data changes per request.

Actual Result

• Opening "Edit member" on a large organization took several seconds (multiple seconds, sometimes close to ten), scaling roughly linearly with the number of shared collections in the organization.
• /admin → Users and /admin → Organizations took over a minute to render on an instance with a few thousand users, to the point of being effectively unusable.
• POST /api/sync (used by every client on login/refresh) was consistently among the slowest queries in database monitoring, and looking at EXPLAIN ANALYZE showed a sort/aggregate step spilling to disk under default work_mem.

Logs

Not directly relevant — this is a performance/database issue, not a crash or error. Evidence gathered was primarily EXPLAIN (ANALYZE) output and query-count/timing observations against a real PostgreSQL instance, summarized below.

Screenshots or Videos

No response

Additional Context

Root-caused via database-level investigation (query logging, EXPLAIN ANALYZE, and reading src/db/models/* / src/api/core/organizations.rs / src/api/admin.rs) against a production-scale self-hosted instance. Splitting this into distinct findings so they can be triaged/fixed independently; happy to split into separate issues if preferred.

---

1. N+1 queries in GET /organizations/{orgId}/collections/details (used by the web-vault's "Edit member" and "Collections" screens)

For every collection in the organization, the handler issues multiple separate queries (checking the current user's direct/group access to that one collection, that collection's groups, and the current user's membership) instead of loading all of it for the whole organization once. On an organization with a few thousand collections, this turns into thousands of round trips for what is a single page load, roughly:

-- repeated once per collection, N times:
SELECT 1 FROM users_collections WHERE collection_uuid = $1 AND user_uuid = $2;
SELECT * FROM collections_groups WHERE collections_uuid = $1;
SELECT * FROM users_organizations WHERE user_uuid = $2 AND org_uuid = $3;

2. N+1 and per-row aggregate queries in /admin/users and /admin/users/overview

For every user in the instance, the handler separately loads that user's confirmed memberships (and, for each one, the organization row), counts owned ciphers, counts and sums attachments, and looks up the last-active device — instead of computing these once for all users with a handful of GROUP BY queries. On an instance with a few thousand users this is thousands of extra queries for a single page load.

3. Same pattern in /admin/organizations/overview

Per organization: separate count(*) queries against users_organizations, ciphers, collections, groups, event and attachments — six queries per organization instead of six queries total (GROUP BY org_uuid).

4. No secondary indexes on the columns most frequently filtered on

The schema only has primary keys and UNIQUE constraints; there is no secondary index on any foreign-key column. Unlike MySQL/InnoDB (which indexes FKs automatically), PostgreSQL does not, so every one of these is a sequential scan on tables that can have hundreds of thousands to low millions of rows in a production instance: ciphers.user_uuid, ciphers.organization_uuid, ciphers_collections.collection_uuid, users_collections.collection_uuid, users_organizations.org_uuid, groups.organizations_uuid, groups_users.users_organizations_uuid, collections_groups.groups_uuid, event.org_uuid, devices.user_uuid / devices.refresh_token, attachments.cipher_uuid, folders_ciphers.folder_uuid, favorites.cipher_uuid. This compounds every one of the N+1 patterns above. PR #7678 already proposes adding several of these — see point 5 for why some of them may currently be ineffective on PostgreSQL.

5. Inconsistent UUID column types (CHAR(36) vs VARCHAR) silently defeat indexes on PostgreSQL

Some tables/columns use CHAR(36) (PostgreSQL bpchar) for UUID primary/foreign keys, while others use VARCHAR. Diesel always sends bound parameters as text. Comparing a bpchar column against a text parameter forces PostgreSQL to cast the column ((column)::text = $1), which makes the planner unable to use a plain b-tree index on that column — including its own primary key:

PREPARE p (text) AS SELECT * FROM some_table WHERE uuid_column = $1;
EXPLAIN EXECUTE p ('11111111-1111-1111-1111-111111111111');

-- uuid_column CHAR(36):
--   Seq Scan on some_table  (cost=0.00..123.45 rows=1 width=...)
--     Filter: ((uuid_column)::text = $1)

-- Same table/data, uuid_column VARCHAR(40):
--   Index Scan using some_table_pkey on some_table  (cost=0.29..8.31 rows=1 width=...)
--     Index Cond: (uuid_column = $1)

This means any index proposed on a CHAR(36) column (including the ones in PR #7678, for the tables where the schema currently uses CHAR(36)) will not actually be used by the queries Vaultwarden itself issues through Diesel, since they all bind text parameters.

6. SELECT DISTINCT over full wide rows in sync

Cipher::find_by_user / find_by_user_visible (used by /api/sync, called by every client on login/refresh) computes DISTINCT over all columns of ciphers, including the data column (the encrypted cipher payload, which can be a few KB per row). Because of the joins to collections/groups, the same cipher can appear multiple times before deduplication, so PostgreSQL has to sort/hash entire wide rows to deduplicate them:

EXPLAIN ANALYZE
SELECT DISTINCT ciphers.* FROM ciphers LEFT JOIN ... ;

--  HashAggregate (actual time=... rows=...)
--    Group Key: ciphers.uuid, ciphers.created_at, ..., ciphers.data, ...

On a large organization with many shared collections, this can spend a large fraction of the query's total time in that aggregation step, and under constrained work_mem it can spill to disk (visible as Sort Method: external merge in EXPLAIN ANALYZE, and as growing temp-file I/O in database monitoring).

7. Organization events page has no usable index

GET /organizations/{orgId}/events filters and sorts by (org_uuid, event_date); there is no composite index for it (compounded by point 5 if event.org_uuid is CHAR(36) in the schema being queried), so it falls back to scanning/sorting a large fraction of the event table on every request.

8. Write amplification when saving a group/collection with many members

Saving a group or collection triggers one UPDATE users SET updated_at = ... WHERE uuid = $1 per affected user (to bump their sync revision), issued in a loop, instead of a single batched UPDATE ... WHERE uuid = ANY($1). Saving a group with a few dozen collections and a few dozen users can turn into thousands of individual UPDATE statements for one save action.

---

I have a working, validated fix for all of the above (schema migration for point 5, new indexes for points 4/7, batched/precomputed queries for points 1-3 and 6, and batched writes for point 8), tested against a synthetic dataset at a scale comparable to the production instance described above. I'm happy to open PRs for these — individually, so each can be reviewed/reverted independently — starting with the index/type fix that's most relevant to PR #7678, if there's interest from the maintainers on the overall direction.

[eirisdg]

Some notes on the approach I used to fix each of these, in case it's useful before I open PRs:

1. CHAR → VARCHAR (point 5): convert the affected UUID columns to VARCHAR (same storage size as CHAR(36), since PostgreSQL doesn't pad VARCHAR), so comparisons against Diesel's text parameters no longer need a cast on the column side and can use a plain b-tree index. This should land before any new indexes are added on those columns (otherwise the index is created but silently unused, same as I found with PR perf(db): add indexes on foreign key and lookup columns #7678 on the affected tables).

2. Missing indexes (points 4, 7, 11): plain CREATE INDEX (not CONCURRENTLY) on the foreign-key/lookup columns listed above, including a composite (org_uuid, event_date) for the events page and (user_uuid, updated_at) for last-active lookups. I deliberately avoided CONCURRENTLY: it can't run inside a transaction, and if interrupted mid-build it leaves an INVALID index that Vaultwarden's automatic migration-on-startup can't clean up without manual intervention — for the table sizes involved, a plain CREATE INDEX inside the migration's transaction completes in well under a couple of seconds per index.

3. N+1 queries (points 1-3): replace the per-row/per-collection queries with a handful of queries that load everything needed for the whole request up front (grouped in memory by the id you'd otherwise filter by), reusing the same idea CipherSyncData already uses for /api/sync — a small set of HashMaps keyed by the relevant id, built once per request, instead of a query per row. For the admin overview pages, the per-row count/sum calls become GROUP BY queries executed once for the whole page.

4. Sync DISTINCT (point 6): compute the DISTINCT on ciphers.uuid alone in a subquery (same visibility predicate, unchanged), then fetch the full rows by primary key. This shrinks the column set the aggregation step has to hash/sort down to a single narrow column instead of the whole row.

5. Batched writes (point 8): collect the affected user_uuids into a set and issue one UPDATE users SET updated_at = ... WHERE uuid = ANY($1) at the end of the save, instead of one UPDATE per user.

All of this keeps the exact same response shape/permission semantics as before (verified by reading through the access-control logic for each affected endpoint) — it's purely about how many round trips it takes to produce the same result. I have this implemented and passing against a synthetic dataset sized to match the production scenario described above (a few thousand users, dozens of organizations, thousands of shared collections in the largest ones), with measured before/after query counts for each point. Let me know if it'd be useful to open this as PRs against main, and whether you'd prefer one PR per point above or a few grouped by area (schema/indexes vs. query patterns vs. writes).

[BlackDex]

This looks like an AI generated report to me.
I'm not disputing their might be optimizations which could be done, but severe is too much.

Also, so items mentioned might cause incompatibility with other databases we support and we need to keep it compatible with all and not make everything over complex.

Since this isn't a bug which causes stuff to not work i moved it to ideas.

[eirisdg]

Yes, this analysis was AI-assisted/generated. English is not my native language (I'm Spanish), so I used AI to help me structure and express the technical findings more clearly.

However, the analysis itself is based on a real production instance. The instance is running Vaultwarden 1.37.1 / web-vault 2026.6.4 and has 2,267 users and 130,133 records, with a very large number of organizations and shared collections.

This is not a theoretical analysis or something I found by simply asking an AI about Vaultwarden. We are actually seeing these performance problems in production, particularly in environments with a large number of users and collections.

I used database-level investigation, query analysis and EXPLAIN ANALYZE to investigate the slow operations, and then used AI to help organize the findings and explain them in a way that was easier to understand.

If any particular finding is questionable, I can provide additional anonymized EXPLAIN ANALYZE output, query timings, or other evidence from the production environment to help validate it.

My main goal with this discussion is simply to bring attention to scalability problems that become very visible when Vaultwarden is used with large organizations and many collections.

[eirisdg]

Just to clarify the intention behind this discussion: I'm not suggesting that Vaultwarden needs to be completely redesigned or that all of these points necessarily need to be changed.

We are active Vaultwarden users, and these findings come from problems we are actually encountering in a production environment with a large number of users and collections.

The goal is simply to share these real-world findings and, where possible, contribute fixes or ideas that could help Vaultwarden scale better for organizations with higher usage. If some of these optimizations are not appropriate because of compatibility with other supported databases, that's completely understandable.

I mainly want to avoid other organizations operating Vaultwarden at this scale running into the same problems we are seeing today.

[eirisdg]

Following up on my note above that I could share more evidence if useful, and on the multi-database point @BlackDex raised — here's concrete, fresh data from the fix now running against the real instance described above, not just a synthetic benchmark. All of it is from database-level query monitoring (query text + execution time), and I've kept instance-specific numbers out of it as before.

Point 6 (sync DISTINCT on full rows) — before/after is visible in the query monitoring itself. After deploying the fix, the old query shape (SELECT DISTINCT "ciphers".* over the full row) no longer appears at all in the monitoring window — it's fully replaced by the two-query pattern (SELECT DISTINCT "ciphers"."uuid" FROM (...) followed by fetching the full rows WHERE uuid = ANY(...)), exactly as described in the original post. Per-call timings, individually extracted from the cumulative counter across 4 real executions since deploy: 10.86ms, 10.76ms, 15.33ms, 10.85ms — consistently in the low double digits, no outliers. The companion fetch-by-primary-key query ran twice, at 13.84ms and a negligible increment the second time.

Points 4/5 (indexes + CHAR→VARCHAR) — the migration actually ran and stuck. The new indexes and the column-type migration show up as one-time ALTER TABLE/CREATE INDEX events in the same monitoring data at deploy time, and the instance has been running on the new schema since without incident.

Points 1-3 (N+1 in org/admin screens):

• Functional check with a real logged-in session: opening "Edit member" on a real organization now issues exactly the 3 expected calls (GET .../users/{id}?includeGroups=true, GET .../collections/details, GET .../groups/details), all 200 OK, with no perceptible delay opening the dialog — versus the same flow being the slow one described in the original report.
• Member-list preload: the bulk query that replaced the "one query per member" pattern (SELECT users.* FROM users INNER JOIN users_organizations ... WHERE org_uuid = $1, i.e. loading every member's user row for an organization in a single round trip) executed in 8.37ms for the whole organization, once, instead of N separate lookups.

For reference, this is a modest-size organization on this particular instance, so these numbers aren't meant to demonstrate the worst-case gain (that's the synthetic benchmark in the original report) — they're meant to show the new query shapes are real, running in production, and fast in absolute terms, not just relatively faster than a bad baseline.

On @BlackDex's multi-database compatibility concern: everything here was investigated and fixed against PostgreSQL specifically, since that's the backend where it was found. Point 5 in particular (the CHAR/VARCHAR index mismatch) is a PostgreSQL-only quirk — comparing a bpchar column against a text bind parameter forces a cast that defeats the index; MySQL and SQLite don't have that distinction, so that migration wouldn't need to touch their schemas at all. The query-restructuring points (1-3, 6) are plain Diesel query changes with the same predicates, so they should behave the same across backends, but I haven't verified that against MySQL/SQLite myself — happy to if that would help before anything gets proposed as a PR.

Not trying to relitigate "severe" — just following up with proof that the underlying findings are reproducible and grounded in the real instance mentioned above, not speculative. Happy to keep posting numbers as more of the affected endpoints get hit by real usage, or share more (redacted EXPLAIN ANALYZE output) if it'd help evaluate any specific point.