2026-08-25 · run 2026-08-25-index-skip-threshold

Keycloak silently skips index creation above 300,000 rows

Upgrade: 26.0.0 → 26.7.1
Scale: 100,002 users, typical profile; EVENT_ENTITY varied per run
Database: PostgreSQL 16.15, single node, container
Host: Hetzner CCX33 (fsn1) — 8 vCPU dedicated, 30GB RAM, local NVMe
Condition: Admin-event volume

Environment

Field	Value
Keycloak from → to	26.0.0 → 26.7.1
Database	PostgreSQL 16.15, single node, container
Postgres tuning	stock — `shared_buffers` 128MB, `maintenance_work_mem` 64MB, `statement_timeout` 0
Dataset	100,002 users, `typical` profile; `EVENT_ENTITY` varied per run
Seeding	users via `partialImport`; events via direct SQL `INSERT … generate_series`, then `ANALYZE`
Host	Hetzner CCX33 (fsn1) — 8 vCPU dedicated, 30GB RAM, local NVMe
JVM heap	`-Xms1g -Xmx4g`

Procedure

For each row count: restore baseline-26.0.0-typical-100k, insert N rows into EVENT_ENTITY, ANALYZE, upgrade to 26.7.1, then check pg_indexes for idx_event_entity_user_id_type and grep the log for the skip warning.

Results

`EVENT_ENTITY` rows	Time to ready	Index created	Skip warning
0	15.7s	yes	no
1,000	15.4s	yes	no
100,000	15.6s	yes	no
200,000	15.7s	yes	no
300,000	15.9s	yes	no
301,000	15.5s	NO	yes
350,000	15.5s	NO	yes
400,000	16.0s	NO	yes
450,000	15.3s	NO	yes
500,000	15.4s	NO	yes
5,000,000	14.9s	NO	yes

The threshold is exactly 300,000 rows. Time to ready is flat across a range of 0 to 5,000,000 rows — a 2.1 GB database at the top end — precisely because the expensive work is skipped.

Outcome

Upgrade reached ready in every run · [x] realm intact · [x] login works
Threshold reproduced across 11 runs with a clean boundary at 300,000

Verification

Claim	Primary source	Checked
Index absent above threshold	`pg_indexes`, queried directly	☑
Changeset recorded EXECUTED	`DATABASECHANGELOG.exectype`	☑
Keycloak warns at skip and at startup	container log, both messages quoted	☑
Threshold is 300,000	300,000 creates; 301,000 skips	☑
The 300,000 constant applies to other tables/indexes	confirmed on `USER_ENTITY` — bisected to the same inclusive 300,000 boundary in `2026-08-26-index-precondition-anatomy` §3b	☑
Same behaviour in majors other than 26.7.1	not tested	☐
Whether row count comes from statistics or an exact count	answered — the planner estimate. Falsifying `pg_class` for `USER_ENTITY` at 2M made Keycloak build the index; see `2026-08-25-runA-indexes-manually-created` L5	☑

Publishable extract

Keycloak will skip creating a database index during an upgrade if your table is too big, and the only place it tells you is a WARN in the startup log.

Upgrading 26.0.0 → 26.7.1 adds an index to EVENT_ENTITY. We ran that upgrade eleven times against the same 100,000-user realm, varying only the number of rows in EVENT_ENTITY (Postgres 16.15 at stock settings, Hetzner CCX33 — 8 vCPU dedicated, 32 GB, local NVMe).

At 300,000 event rows the index is created. At 301,000 it is not.

The upgrade still succeeds. The server starts. MIGRATION_MODEL reads 26.7.1. And DATABASECHANGELOG records changeset 26.4.0-51321 as EXECUTED, not as skipped — so a schema-version check, which is what most people verify with, shows everything applied. Only two WARN lines say otherwise:
WARN [CustomCreateIndexChange] Following index should be created:
  CREATE INDEX IDX_EVENT_ENTITY_USER_ID_TYPE ON public.EVENT_ENTITY(USER_ID, TYPE, EVENT_TIME);
WARN [DatabaseIndexChecker] Missing database index IDX_EVENT_ENTITY_USER_ID_TYPE
  on table EVENT_ENTITY. Create the index manually: ...
This is deliberate. CustomCreateIndexChange exists so a large table is not locked during startup, and that is a reasonable design choice — a slow upgrade is worse than a late index. We measured what it is avoiding: at 5,000,000 rows the index takes about 3 seconds to build on this hardware.

The consequence is what nobody mentions. The installations that skip the index are exactly the ones large enough to need it, small deployments get it automatically, and every future release that indexes a large table will behave the same way, so the gap grows with each upgrade.

If you have upgraded Keycloak recently and your event table is bigger than 300,000 rows, check whether IDX_EVENT_ENTITY_USER_ID_TYPE exists. Then add "list indexes Keycloak wanted and did not create" to your post-upgrade checklist permanently, because this will happen again.

Incidentally, this also explains something that surprised us. We had expected migration time to grow with data volume. It does not — 15 seconds whether the database holds 0 or 5,000,000 events — and this is why. Keycloak keeps the upgrade fast by declining to do the expensive part.