2026-08-25 · run 2026-08-25-runA-indexes-manually-created

the indexes Keycloak declined, created manually

Why this run existed

bin/upgrade.sh prints, after every run:

>> create these manually, preferably with CREATE INDEX CONCURRENTLY.

We had never once run an upgrade against a database where that advice had been followed. 2026-08-25-index-skip-threshold and 2026-08-25-26.0.0-to-26.7.1-2m both name this as the largest untested configuration in the lab, and it ranked first on our own untested list.

The question was whether our own advice breaks the next upgrade. It does not. Asking only that question would have missed both of the findings below.

Environment

Every leg starts from a restore of the same baseline — 71s, verified against re-migration by bin/restore.sh each time — so the legs are comparable to each other. The Liquibase phase is measured identically in every leg: from QuarkusJpaUpdaterProvider: Updating database to DefaultMigrationManager: Migrating older model to 26.1.0.

Results

Every leg reached ready, ended at migration_model 26.7.1 with a 211-row DATABASECHANGELOG, and kept 2,000,003 users. L1's admin token endpoint returned 200.

L1 — following our own advice is safe

CREATE INDEX CONCURRENTLY idx_user_created_timestamp
  ON user_entity (realm_id, created_timestamp);        -- 970 ms, 14 MB

Liquibase's precondition sees the index, records changeset 26.6.0-43829-user-created-timestamp-index as MARK_RAN, and says nothing — no CustomCreateIndexChange warning, no DatabaseIndexChecker warning. The checker runs (Running database index checker) and reports a clean database.

No "already exists" error, no failure, and no measurable change in migration time. The advice we have been giving is sound.

L2 — the control, and the reason L1 means anything

Same restore, nothing created. Changeset EXECUTED, index absent, both WARNs present — reproducing 2026-08-25-26.0.0-to-26.7.1-2m exactly, this time on a restored rather than freshly seeded database.

1.69s against L1's 1.72s: pre-creating the index neither costs nor saves migration time, because the work being avoided is under a second (L4).

L3 — a wrong index with the right name is accepted, silently ⭐

An operator creates the index Keycloak asked for, with the name Keycloak logged, and gets the columns wrong — one instead of two:

CREATE INDEX CONCURRENTLY idx_user_created_timestamp
  ON user_entity (created_timestamp);                   -- 628 ms

• changeset → MARK_RAN
• pg_indexes still shows the single-column index; Keycloak never replaced it
• warn_count=0 — CustomCreateIndexChange silent
• DatabaseIndexChecker silent too, declaring the database correct

The precondition matches on index name only. That is visible in Keycloak's own changelog, not merely inferred from the behaviour — META-INF/jpa-changelog-26.6.0.xml:

<changeSet author="keycloak" id="26.6.0-43829-user-created-timestamp-index">
    <preConditions onSqlOutput="TEST" onFail="MARK_RAN">
        <not>
            <indexExists tableName="USER_ENTITY" indexName="IDX_USER_CREATED_TIMESTAMP" />
        </not>
    </preConditions>
    <createIndex tableName="USER_ENTITY" indexName="IDX_USER_CREATED_TIMESTAMP">
        <column name="REALM_ID" type="VARCHAR(255)" />
        <column name="CREATED_TIMESTAMP" type="BIGINT" />
    </createIndex>
</changeSet>

indexExists takes a table and a name. It does not take columns. Anything with the right name satisfies it, whatever it indexes — no check of columns, order or uniqueness — and Keycloak's own post-upgrade index verifier accepts it as well.

The database ends in a state where the index Keycloak wanted does not exist, the index that does exist does not serve the query it was added for, every Keycloak-provided diagnostic reports success, and — unlike the skip case — not even a WARN is left in the log. The skip leaves evidence. This leaves none.

The route into that state is our own runbook telling the customer to create the index by hand.

L4 — what the deferred DDL costs at 2,000,003 rows

Index (realm_id, created_timestamp) on USER_ENTITY, measured directly:

The work Keycloak declines is under one second at two million users. The index-skip finding is not "Keycloak avoids an expensive rebuild"; it is "Keycloak avoids 750 ms and a brief ACCESS EXCLUSIVE lock on USER_ENTITY". The lock is the real reason. The duration is not.

maintenance_work_mem does not matter for this index. Our own scenario notes claimed that "a 2M-row index build is dominated by" those settings. Raising it 16× changed the build by 1.5%: the index is 14 MB and the sort fits inside the stock 64 MB either way. The field is still worth recording — the reason given for it is wrong at this scale.

L5 — the threshold is a planner estimate, not a row count ⭐⭐

2026-08-25-index-skip-threshold left open "whether row count comes from statistics or an exact count". L2 could not separate them: a freshly restored 2M database carries an accurate reltuples (2000003, relpages 53392) even with no last_analyze, because the index builds during pg_restore set it.

So we lied to the planner instead, changing nothing else:

UPDATE pg_class SET reltuples = 1000, relpages = 100 WHERE relname = 'user_entity';
-- actual rows: 2000003

Keycloak built the index. Correct two-column definition, changeset EXECUTED, zero warnings — a genuine CREATE INDEX against a 2,000,003-row table, during the migration, because a catalogue row said the table was small.

An exact SELECT count(*) is unaffected by pg_class, so that hypothesis is refuted. CustomCreateIndexChange reads the planner's estimate.

And it produced the first non-flat migration this lab has measured:

+1.82s, at identical true scale, from the same baseline. Everything this lab has said about migration duration being flat holds only while Keycloak is declining the DDL, and what makes it decline is a number that is stale by design.

Breakage observed

A manually created index with the wrong columns is accepted silently

• Symptom: upgrade succeeds, changeset MARK_RAN, zero warnings, and the database holds an index that does not match what the changeset defines.
• Evidence: L3. pg_indexes.indexdef reads btree (created_timestamp) where the changeset wants btree (realm_id, created_timestamp); warn_count=0 from both CustomCreateIndexChange and DatabaseIndexChecker.
• Cause: confirmed — the Liquibase precondition and the index checker both key on index name.
• Fix: verify by definition, not by existence. Compare pg_indexes.indexdef against the changeset's column list; name equality is not evidence.
• Would this hit a customer? Any customer who followed the "create these manually" advice and mistyped, and nothing in Keycloak will ever tell them.

Whether the index is built depends on when autovacuum last looked

• Symptom: the same database, at the same true size, either skips the index or builds it, according to pg_class.reltuples.
• Evidence: L2 (accurate statistics → skipped) against L5 (statistics say 1,000 → built, +1.82s of migration).
• Cause: confirmed — CustomCreateIndexChange consults the planner estimate. Which of reltuples / relpages it reads was not isolated; both were changed together.
• Fix: none available to the operator, and that is the point. reltuples is only refreshed by ANALYZE, VACUUM and index builds, so a table that has grown since its last autovacuum carries a stale, low estimate.
• Would this hit a customer? Scale-dependent and timing-dependent. Two identical production databases can diverge on autovacuum history alone — and a staging rehearsal is not evidence about production unless the estimates match. It is also the mechanism by which a customer gets a slow migration they have no way to predict, which re-arms S3 (statement_timeout) and S14 (disk) at any realm size.

EXECUTED and MARK_RAN do not mean what they look like

• Symptom: the row that reads as "we did not run this" is the healthy one.

• Evidence: four combinations observed across the legs:

  exectype	Index	What actually happened
  EXECUTED	present	Keycloak built it — L5, and every realm under the threshold
  EXECUTED	absent	Keycloak skipped it — L2, the dangerous case
  MARK_RAN	present, correct	someone created it — L1, healthy
  MARK_RAN	present, wrong shape	someone created the wrong thing — L3, silent

• Fix: exectype is only usable together with the index's presence and its definition. On its own it distinguishes nothing.

• Would this hit a customer? Anyone auditing the changelog rather than the schema — which is exactly what the changelog invites.

Rollback

• Exercised this run? L1 took a pre-upgrade backup through the default path (pre-upgrade-26.0.0-to-26.7.1-20260825T225316Z, 65 MB). L2, L3 and L5 ran --no-backup deliberately: each was preceded by a verified restore of the same baseline, which is the case that flag documents.
• Verdict: unchanged from 2026-08-25-26.0.0-to-26.7.1-3 — rollback on this path is a point-in-time restore.

Verification

Publishable extract

Keycloak decides whether to build a database index during an upgrade by asking Postgres how big the table is — and Postgres answers from a cached estimate that can be wrong.

Some background. Keycloak skips creating an index during an upgrade when the target table has more than 300,000 rows: it logs two warnings, records the changeset as EXECUTED, and leaves the index uncreated. We measured that boundary earlier and it is exact.

What we had not established is where the row count comes from. It is pg_class — the planner's estimate — not a SELECT count(*).

On a 2,000,003-user realm (Keycloak 26.0.0 → 26.7.1, Postgres 16 at stock settings, Hetzner CCX33: 8 vCPU dedicated, 32 GB, local NVMe), we changed one catalogue row to claim USER_ENTITY held 1,000 rows, and changed nothing else. Keycloak built the index — a real CREATE INDEX on two million rows — and the schema migration went from 1.69 seconds to 3.51 seconds.

That matters because reltuples is refreshed only by ANALYZE, VACUUM and index builds. A table that has grown since autovacuum last visited carries a stale, low estimate. So two databases of identical size can take different paths through the same upgrade, and neither operator has any way to know which they will get. Your staging rehearsal tells you about production only if the statistics match, and nobody checks that.

Second finding, and the one we did not go looking for. Keycloak's warning tells you to create the index yourself:

Missing database index IDX_USER_CREATED_TIMESTAMP on table USER_ENTITY.
Create the index manually: CREATE INDEX IDX_USER_CREATED_TIMESTAMP ON
  public.USER_ENTITY(REALM_ID, CREATED_TIMESTAMP);

Doing exactly that is safe: the next upgrade sees the index, marks the changeset MARK_RAN, and warns about nothing. We checked, because "the changeset will try to create an index that already exists" was a real risk and we had never tested it.

But the check Keycloak performs is on the index's name, and nothing else. We created an index called IDX_USER_CREATED_TIMESTAMP on the wrong columns — one column instead of two, the kind of mistake a hurried copy-paste makes — and upgraded. Keycloak marked the changeset satisfied, emitted no warning, and its own DatabaseIndexChecker reported the database correct. The index Keycloak wanted does not exist. The one that does will not serve the query it was added for. Nothing in Keycloak will ever say so again — and unlike the skip, this state does not even leave a warning in the log.

A last number, because it reframes the trade-off. That index takes 754 ms to build on two million rows, or 969 ms with CONCURRENTLY, and occupies 14 MB. Raising maintenance_work_mem from the stock 64 MB to 1 GB changes it by 1.5%. Keycloak is not avoiding an expensive rebuild — it is avoiding a sub-second ACCESS EXCLUSIVE lock. That is a defensible thing to avoid during startup. It is worth knowing that the price of avoiding it is an index your database may never get.

If you have upgraded Keycloak at any real scale: list the indexes its changelogs define, and compare them against pg_indexes.indexdef — the definition, not just the name. Presence is not correctness here.