DB performance issues after upgrading to v25.4.0

[Pedr0Rocha]

Preflight checklist

• I could not find a solution in the existing issues, docs, nor discussions.
• I agree to follow this project's Code of Conduct.
• I have read and am following this repository's Contribution Guidelines.
• I have joined the Ory Community Slack.
• I am signed up to the Ory Security Patch Newsletter.

Ory Network Project

No response

Describe the bug

Hi,
we upgraded our self-hosted Kratos version from v1.3.0 to v25.4.0 yesterday and we've been having DB performance issues since then.

I tried to pinpoint what's going wrong by looking at the query plans from before/after. The new ones indeed look better in paper, but this is what I actually see in the cpu resources:

This is a period of 2 hours, ~2M calls to this query

Before:

SELECT
  COALESCE(identity_credential_identifiers.identifier, $3) cred_identifier,
  ict.id cred_type_id,
  ict.name cred_type,
  identity_credentials.config cred_config,
  identity_credentials.created_at created_at,
  identity_credentials.id cred_id,
  identity_credentials.identity_id identity_id,
  identity_credentials.nid nid,
  identity_credentials.updated_at updated_at,
  identity_credentials.version cred_version
FROM
  identity_credentials AS identity_credentials
INNER JOIN
  identity_credential_types ict
ON
  (identity_credentials.identity_credential_type_id = ict.id)
LEFT JOIN
  identity_credential_identifiers
ON
  identity_credential_identifiers.identity_credential_id = identity_credentials.id
  AND identity_credential_identifiers.nid = identity_credentials.nid
WHERE
  ((identity_credentials.identity_id = $1
      AND identity_credentials.nid = $2))

---

After:

SELECT
  COALESCE(identity_credential_identifiers.identifier, $3) cred_identifier,
  identity_credentials.config,
  identity_credentials.created_at,
  identity_credentials.id,
  identity_credentials.identity_credential_type_id,
  identity_credentials.identity_id,
  identity_credentials.nid,
  identity_credentials.updated_at,
  identity_credentials.version
FROM
  identity_credentials AS identity_credentials
LEFT JOIN
  identity_credential_identifiers
ON
  identity_credential_identifiers.identity_credential_id = identity_credentials.id
  AND identity_credential_identifiers.nid = identity_credentials.nid
WHERE
  (identity_credentials.identity_id = $1)
  AND (identity_credentials.nid = $2)
ORDER BY
  identity_credential_identifiers.identifier ASC

Important

Notice how the 50th percentile is almost the same, but the 95th, 99th is way higher. So it could be some kind of edge case or another query/operation affecting it. The database load went up by a lot for this query as a consequence.

---

Other metrics

These are other metrics that spiked quite a lot after the upgrade (~14h in the graphs) and I don't understand why yet:

The number of new connections is a lot higher, same amount of pods are connecting to it 4~6 instances of Kratos. Is there any change around the connection pool that would explain this?

Amount of fetched/returned rows used to be 7x less, but I cannot find where this is happening. If you could point me into the right direction I could investigate which query could be fetching/returning more.

And of course, the CPU usage

---

I'm not super familiar with Kratos internals so maybe I'm looking at the wrong query or plans. Please let me know which other information you need to make this clearer.

Reproducing the bug

The only change we did was to upgrade kratos from v1.3.0 to v25.4.0
This is in a Postgres DB

Relevant log output

Relevant configuration

Version

v25.4.0

On which operating system are you observing this issue?

None

In which environment are you deploying?

None

Additional Context

No response

[vinckr]

Hi, thank you for the detailed report.
I know the Ory team already reached out to you via email to discuss this further, but wanted to leave a notice for others who might encounter a similar issue.

Issues like this can be difficult to diagnose and resolve through community support alone, so we wanted to connect directly.

If anyone else encounters similar problems, feel free to reach out at ory.com/contact to explore support options.

[Pedr0Rocha]

Hi, I noticed a couple new things that might be relevant.

This query avg. execution time is 13s, it returns 0 rows all the time. Luckily it doesn't run very often, but the DB definitely spends considerable resources on it.

SELECT "identities"."available_aal", "identities"."created_at", "identities"."external_id", "identities"."id", "identities"."metadata_admin", "identities"."metadata_public", "identities"."nid", "identities"."organization_id", "identities"."schema_id", "identities"."state", "identities"."state_changed_at", "identities"."traits", "identities"."updated_at" FROM identities INNER JOIN identity_credentials ON identities.id = identity_credentials.identity_id AND identities.nid = identity_credentials.nid AND identity_credentials.identity_credential_type_id = ( SELECT id FROM identity_credential_types WHERE name = $1 ) WHERE identity_credentials.config ->> $4 = $2 AND identity_credentials.config ->> $5 IS NOT NULL AND identities.nid = $3 LIMIT $6

---

I also see

SELECT identity_verifiable_addresses.created_at, identity_verifiable_addresses.id, identity_verifiable_addresses.identity_id, identity_verifiable_addresses.nid, identity_verifiable_addresses.status, identity_verifiable_addresses.updated_at, identity_verifiable_addresses.value, identity_verifiable_addresses.verified, identity_verifiable_addresses.verified_at, identity_verifiable_addresses.via FROM identity_verifiable_addresses AS identity_verifiable_addresses WHERE identity_id = $1 AND nid = $2 ORDER BY id ASC

and

SELECT identity_recovery_addresses.created_at, identity_recovery_addresses.id, identity_recovery_addresses.identity_id, identity_recovery_addresses.nid, identity_recovery_addresses.updated_at, identity_recovery_addresses.value, identity_recovery_addresses.via FROM identity_recovery_addresses AS identity_recovery_addresses WHERE identity_id = $1 AND nid = $2 ORDER BY id ASC

being run 50M times a day and always returning 0 rows.

I assume this is for recovery and verification flows, but both are disabled in our kratos config. We only use passkeys and oidc.

[alnr]

Hey y'all. Seems like a legitimate issue. I'll see if I can get #4522 to be reviewed.

[alnr]

Fixed via 65b27fd