Skip to content

Avoid updates to IDX_USER_SESSION_EXPIRATION_LAST_REFRESH on every token refresh #51904

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Merged
ahus1 merged 5 commits into from
Aug 26, 2026
Merged

Avoid updates to IDX_USER_SESSION_EXPIRATION_LAST_REFRESH on every token refresh #51904

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
5 commits
Select commit Hold shift + click to select a range
e266551
Avoid updates to IDX_USER_SESSION_EXPIRATION_LAST_REFRESH on every to…
ahus1 Aug 19, 2026
be7aacd
Reworked to avoid recomputing a lot of entries at the epoch end, do s…
ahus1 Aug 21, 2026
4b335ca
Potential fix for pull request finding
ahus1 Aug 21, 2026
ca23bb9
Added a test
ahus1 Aug 21, 2026
47ebc9b
Fixing tests
ahus1 Aug 22, 2026
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
5 changes: 2 additions & 3 deletions docs/documentation/upgrading/topics/changes/changes-26_8_0.adoc
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -87,10 +87,9 @@ This changes the behavior of identity provider account linking by email, of admi
and of the verifiable credential offer emails of the experimental OID4VCI feature.
The *Trust Email* setting of identity providers and LDAP federation is not affected, as it is an explicit decision of the administrator to trust the email from the external source.

=== Offline session expiration indexes rebuilt with session bucket column

The `OFFLINE_USER_SESSION` table now has updated indexes `IDX_USER_SESSION_EXPIRATION_CREATED` and `IDX_USER_SESSION_EXPIRATION_LAST_REFRESH`. On PostgreSQL and Microsoft SQL Server, `IDX_OFFLINE_USS_BY_BROKER_SESSION_ID` is also rebuilt as a filtered index.
=== Offline session expiration indexes rebuilt with session bucket and coarse refresh columns

The `OFFLINE_USER_SESSION` table has a new `LAST_SESSION_REFRESH_COARSE` column and updated indexes `IDX_USER_SESSION_EXPIRATION_CREATED` and `IDX_USER_SESSION_EXPIRATION_LAST_REFRESH`. On PostgreSQL and Microsoft SQL Server, `IDX_OFFLINE_USS_BY_BROKER_SESSION_ID` is also rebuilt as a filtered index.
If the table contains more than 300000 entries, {project_name} will skip the index creation by default during the automatic schema migration and instead log the SQL statement on the console during migration to be applied manually after {project_name}'s startup.
See the link:{upgradingguide_link}[{upgradingguide_name}] for details on how to configure a different limit.

Expand Down
6 changes: 3 additions & 3 deletions ...an/src/main/java/org/keycloak/models/sessions/infinispan/changes/JpaChangesPerformer.java
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -399,7 +399,7 @@ private static <K, V extends SessionEntity> void processUserSessionUpdate(Keyclo
}
mergeUserSession(session, entry, userSessionModel, realm, sessionUpdates, userSessionPersister, entity);
} else {
createUserSession(userSessionPersister, entity);
createUserSession(userSessionPersister, entity, realm);
}
}
case REPLACE -> {
Expand All @@ -413,7 +413,7 @@ private static <K, V extends SessionEntity> void processUserSessionUpdate(Keyclo
}
}

private static void createUserSession(UserSessionPersisterProvider userSessionPersister, UserSessionEntity entity) {
private static void createUserSession(UserSessionPersisterProvider userSessionPersister, UserSessionEntity entity, RealmModel realm) {
userSessionPersister.createUserSession(new UserSessionModel() {
@Override
public String getId() {
Expand All @@ -422,7 +422,7 @@ public String getId() {

@Override
public RealmModel getRealm() {
return new RealmModelDelegate(null) {
return new RealmModelDelegate(realm) {
@Override
public String getId() {
return entity.getRealmId();
Expand Down
10 changes: 10 additions & 0 deletions ...a/src/main/java/org/keycloak/models/jpa/session/ImmutablePersistentUserSessionEntity.java
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -63,6 +63,16 @@ public void setLastSessionRefresh(int lastSessionRefresh) {
readOnly();
}

@Override
public int getLastSessionRefreshCoarse() {
return 0;
}

@Override
public void setLastSessionRefreshCoarse(int lastSessionRefreshCoarse) {
readOnly();
}

@Override
public boolean isOffline() {
return JpaSessionUtil.offlineFromString(offline);
Expand Down
18 changes: 18 additions & 0 deletions model/jpa/src/main/java/org/keycloak/models/jpa/session/JpaUserSessionPersisterProvider.java
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -95,6 +95,10 @@ public void createUserSession(UserSessionModel userSession, boolean offline) {
String offlineStr = offlineToString(offline);
entity.setOffline(offlineStr);
entity.setLastSessionRefresh(model.getLastSessionRefresh());
RealmModel realm = adapter.getRealm();
int maxIdle = offline ? SessionExpirationUtils.getOfflineSessionIdleTimeout(realm)
: SessionExpirationUtils.getSsoSessionIdleTimeout(realm);
entity.setLastSessionRefreshCoarse(SessionExpirationUtils.computeLastSessionRefreshCoarse(model.getLastSessionRefresh(), maxIdle, model.getStarted()));
entity.setData(model.getData());
entity.setBrokerSessionId(userSession.getBrokerSessionId());
entity.setRememberMe(userSession.isRememberMe());
Expand Down Expand Up @@ -247,9 +251,13 @@ public void onUserRemoved(RealmModel realm, UserModel user) {
@Override
public void updateLastSessionRefreshes(RealmModel realm, int lastSessionRefresh, Collection<String> userSessionIds, boolean offline) {
String offlineStr = offlineToString(offline);
int maxIdle = offline ? SessionExpirationUtils.getOfflineSessionIdleTimeout(realm)
: SessionExpirationUtils.getSsoSessionIdleTimeout(realm);
int granularity = Math.max(maxIdle / 2, 1);
Comment thread
ahus1 marked this conversation as resolved.
Resolved

int us = em.createNamedQuery("updateUserSessionLastSessionRefresh")
.setParameter("lastSessionRefresh", lastSessionRefresh)
.setParameter("granularity", granularity)
.setParameter("realmId", realm.getId())
.setParameter("offline", offlineStr)
.setParameter("userSessionIds", userSessionIds)
Expand Down Expand Up @@ -717,6 +725,16 @@ public void setLastSessionRefresh(int lastSessionRefresh) {
entity.setLastSessionRefresh(lastSessionRefresh);
}

@Override
public int getLastSessionRefreshCoarse() {
return entity.getLastSessionRefreshCoarse();
}

@Override
public void setLastSessionRefreshCoarse(int lastSessionRefreshCoarse) {
entity.setLastSessionRefreshCoarse(lastSessionRefreshCoarse);
}

@Override
public boolean isOffline() {
return offlineFromString(entity.getOffline());
Expand Down
28 changes: 22 additions & 6 deletions model/jpa/src/main/java/org/keycloak/models/jpa/session/PersistentUserSessionEntity.java
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -47,8 +47,9 @@
@NamedQuery(name="deleteUserSessions", query="delete from PersistentUserSessionEntity sess where sess.offline = :offline AND sess.userSessionId IN (:userSessionIds)"),
// The query "findExpiredUserSessions" is deprecated (since 26.5) and may be removed in the future.
@NamedQuery(name="findExpiredUserSessions", query="select sess.userSessionId, sess.userId from PersistentUserSessionEntity sess where sess.realmId = :realmId AND sess.offline = :offline AND sess.lastSessionRefresh < :lastSessionRefresh"),
@NamedQuery(name="updateUserSessionLastSessionRefresh", query="update PersistentUserSessionEntity sess set lastSessionRefresh = :lastSessionRefresh where sess.realmId = :realmId" +
" AND sess.offline = :offline AND sess.userSessionId IN (:userSessionIds)"),
@NamedQuery(name="updateUserSessionLastSessionRefresh", query="update PersistentUserSessionEntity sess set lastSessionRefresh = :lastSessionRefresh," +
" lastSessionRefreshCoarse = FLOOR((:lastSessionRefresh - MOD(sess.createdOn, :granularity)) / :granularity) * :granularity + MOD(sess.createdOn, :granularity)" +
" where sess.realmId = :realmId AND sess.offline = :offline AND sess.userSessionId IN (:userSessionIds)"),
@NamedQuery(name="findUserSessionsCount", query="select count(sess) from PersistentUserSessionEntity sess where sess.offline = :offline"),
@NamedQuery(name="findUserSessionsOrderedById", query="select sess from PersistentUserSessionEntity sess, RealmEntity realm where realm.id = sess.realmId AND sess.offline = :offline" +
" AND sess.userSessionId > :lastSessionId" +
Expand Down Expand Up @@ -79,17 +80,21 @@
" SET sess.rememberMe = :rememberMe" +
" WHERE sess.userSessionId IN (:userSessionIds)"),
@NamedQuery(name = "findExpiredOfflineUserSessionsLastRefresh",
query = "SELECT sess.userSessionId, sess.userId" +
query = "SELECT sess.userSessionId, sess.userId, sess.lastSessionRefresh" +
" FROM PersistentUserSessionEntity sess" +
" WHERE sess.realmId = :realmId AND sess.offline = '1' AND sess.sessionBucket = :sessionBucket AND sess.lastSessionRefresh < :lastSessionRefresh"),
" WHERE sess.realmId = :realmId AND sess.offline = '1' AND sess.sessionBucket = :sessionBucket AND sess.lastSessionRefreshCoarse < :lastSessionRefreshCoarse"),
@NamedQuery(name = "findExpiredOfflineUserSessionsCreatedOn",
query = "SELECT sess.userSessionId, sess.userId" +
" FROM PersistentUserSessionEntity sess" +
" WHERE sess.realmId = :realmId AND sess.offline = '1' AND sess.sessionBucket = :sessionBucket AND sess.createdOn < :createdOn"),
@NamedQuery(name = "setLastSessionRefreshCoarseToExact",
query = "UPDATE PersistentUserSessionEntity sess" +
" SET sess.lastSessionRefreshCoarse = sess.lastSessionRefresh" +
" WHERE sess.offline = :offline AND sess.userSessionId IN (:userSessionIds)"),
@NamedQuery(name = "findExpiredRegularUserSessionsLastRefresh",
query = "SELECT sess.userSessionId, sess.userId" +
query = "SELECT sess.userSessionId, sess.userId, sess.lastSessionRefresh" +
" FROM PersistentUserSessionEntity sess" +
" WHERE sess.realmId = :realmId AND sess.offline = '0' AND sess.rememberMe = :rememberMe AND sess.sessionBucket = :sessionBucket AND sess.lastSessionRefresh < :lastSessionRefresh"),
" WHERE sess.realmId = :realmId AND sess.offline = '0' AND sess.rememberMe = :rememberMe AND sess.sessionBucket = :sessionBucket AND sess.lastSessionRefreshCoarse < :lastSessionRefreshCoarse"),
@NamedQuery(name = "findExpiredRegularUserSessionsCreatedOn",
query = "SELECT sess.userSessionId, sess.userId" +
" FROM PersistentUserSessionEntity sess" +
Expand Down Expand Up @@ -172,6 +177,9 @@ public boolean isAsyncCommitAllowed(EntityOperationType operationType) {
@Column(name="SESSION_BUCKET")
protected Integer sessionBucket;

@Column(name="LAST_SESSION_REFRESH_COARSE")
protected int lastSessionRefreshCoarse;

@PrePersist
void computeSessionBucket() {
if (sessionBucket == null && userSessionId != null) {
Expand Down Expand Up @@ -260,6 +268,14 @@ public void setSessionBucket(Integer sessionBucket) {
this.sessionBucket = sessionBucket;
}

public int getLastSessionRefreshCoarse() {
return lastSessionRefreshCoarse;
}

public void setLastSessionRefreshCoarse(int lastSessionRefreshCoarse) {
this.lastSessionRefreshCoarse = lastSessionRefreshCoarse;
}

public int getVersion() {
return version;
}
Expand Down
54 changes: 48 additions & 6 deletions model/jpa/src/main/java/org/keycloak/models/jpa/session/UserSessionExpirationLogic.java
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -81,12 +81,14 @@ public static void expireOfflineSessions(KeycloakSessionFactory sessionFactory,
String realmId = realm.getId();
final List<UserSessionAndUser> expiredSessions = new ArrayList<>(batchSize);

final List<String> nearMissSessions = new ArrayList<>(batchSize);

for (int bucket = 0; bucket < PersistentUserSessionEntity.SESSION_BUCKET_COUNT; bucket++) {
Consumer<TypedQuery<Object[]>> lsrParams = UserSessionExpirationLogic.<Object[]>setLastSessionRefresh(oldestLastSessionRefresh)
Consumer<TypedQuery<Object[]>> lsrParams = UserSessionExpirationLogic.<Object[]>setLastSessionRefreshCoarse(oldestLastSessionRefresh)
.andThen(setSessionBucket(bucket));
runInBatches(sessionFactory,
s -> findAndRemoveSessions(s, realmId, batchSize, true, Details.USER_SESSION_EXPIRED_REASON, "(by last refresh)", "findExpiredOfflineUserSessionsLastRefresh", lsrParams, expiredSessions),
expiredSessions::clear);
s -> findAndExpireSessionsByCoarseRefresh(s, realmId, batchSize, true, oldestLastSessionRefresh, "findExpiredOfflineUserSessionsLastRefresh", lsrParams, expiredSessions, nearMissSessions),
() -> { expiredSessions.clear(); nearMissSessions.clear(); });

Consumer<TypedQuery<Object[]>> coParams = UserSessionExpirationLogic.<Object[]>setCreatedOn(oldestCreatedOn)
.andThen(setSessionBucket(bucket));
Expand Down Expand Up @@ -120,13 +122,14 @@ public static void expireRegularSessions(KeycloakSessionFactory sessionFactory,

String realmId = realm.getId();
final List<UserSessionAndUser> expiredSessions = new ArrayList<>(batchSize);
final List<String> nearMissSessions = new ArrayList<>(batchSize);

for (int bucket = 0; bucket < PersistentUserSessionEntity.SESSION_BUCKET_COUNT; bucket++) {
Consumer<TypedQuery<Object[]>> lsrParams = UserSessionExpirationLogic.<Object[]>setLastSessionRefresh(oldestLastSessionRefresh)
Consumer<TypedQuery<Object[]>> lsrParams = UserSessionExpirationLogic.<Object[]>setLastSessionRefreshCoarse(oldestLastSessionRefresh)
.andThen(setRememberMe).andThen(setSessionBucket(bucket));
runInBatches(sessionFactory,
s -> findAndRemoveSessions(s, realmId, batchSize, false, Details.USER_SESSION_EXPIRED_REASON, "(by last refresh)", "findExpiredRegularUserSessionsLastRefresh", lsrParams, expiredSessions),
expiredSessions::clear);
s -> findAndExpireSessionsByCoarseRefresh(s, realmId, batchSize, false, oldestLastSessionRefresh, "findExpiredRegularUserSessionsLastRefresh", lsrParams, expiredSessions, nearMissSessions),
() -> { expiredSessions.clear(); nearMissSessions.clear(); });

Consumer<TypedQuery<Object[]>> coParams = UserSessionExpirationLogic.<Object[]>setCreatedOn(oldestCreatedOn)
.andThen(setRememberMe).andThen(setSessionBucket(bucket));
Expand Down Expand Up @@ -247,6 +250,41 @@ private static void createUserSessionDeletedEvent(KeycloakSession session, Realm
.success();
}

private static boolean findAndExpireSessionsByCoarseRefresh(KeycloakSession session, String realmId, int batchSize, boolean offline, int exactThreshold, String queryName, Consumer<TypedQuery<Object[]>> queryParameters, List<UserSessionAndUser> expiredSessions, List<String> nearMissSessions) {
EntityManager em = session.getProvider(JpaConnectionProvider.class).getEntityManager();

TypedQuery<Object[]> query = em.createNamedQuery(queryName, Object[].class);
queryParameters.accept(query);
int candidateCount = 0;
for (Object[] row : query.setParameter("realmId", realmId)
.setHint(AvailableHints.HINT_READ_ONLY, true)
.setMaxResults(batchSize)
.getResultList()) {
candidateCount++;
String userSessionId = (String) row[0];
String userId = (String) row[1];
int lastSessionRefresh = (int) row[2];
if (lastSessionRefresh < exactThreshold) {
expiredSessions.add(new UserSessionAndUser(userSessionId, userId));
} else {
nearMissSessions.add(userSessionId);
Comment thread
ahus1 marked this conversation as resolved.
Resolved
}
}

handleResultsToRemove(session, em, realmId, offline, Details.USER_SESSION_EXPIRED_REASON, "(by coarse last refresh)", expiredSessions);

if (!nearMissSessions.isEmpty()) {
String offlineStr = offlineToString(offline);
int updated = em.createNamedQuery("setLastSessionRefreshCoarseToExact")
.setParameter("offline", offlineStr)
.setParameter("userSessionIds", nearMissSessions)
.executeUpdate();
logger.debugf("Set coarse to exact for %d near-miss sessions in realm query '%s'", updated, realmId);
}

return candidateCount >= batchSize;
}

// returns true if it has more rows to check
private static boolean findAndRemoveSessions(KeycloakSession session, String realmId, int batchSize, boolean offline, String eventReason, String detailsForLog, String queryName, Consumer<TypedQuery<Object[]>> queryParameters, List<UserSessionAndUser> expiredSessions) {
EntityManager em = session.getProvider(JpaConnectionProvider.class).getEntityManager();
Expand Down Expand Up @@ -318,6 +356,10 @@ private static <T> Consumer<TypedQuery<T>> setLastSessionRefresh(int value) {
return query -> query.setParameter("lastSessionRefresh", value);
}

private static <T> Consumer<TypedQuery<T>> setLastSessionRefreshCoarse(int value) {
return query -> query.setParameter("lastSessionRefreshCoarse", value);
}

private static <T> Consumer<TypedQuery<T>> setCreatedOn(int value) {
return query -> query.setParameter("createdOn", value);
}
Expand Down
28 changes: 23 additions & 5 deletions model/jpa/src/main/resources/META-INF/jpa-changelog-26.8.0.xml
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -25,9 +25,11 @@
</createIndex>
</changeSet>

<!-- Add SESSION_BUCKET column to OFFLINE_USER_SESSION for distributing expiration index inserts
across multiple B-tree leaf pages, reducing PAGELATCH_EX contention on MSSQL and similar
page-level latch contention on other databases. -->
<!-- Add SESSION_BUCKET and LAST_SESSION_REFRESH_COARSE columns to OFFLINE_USER_SESSION, then rebuild
expiration indexes. SESSION_BUCKET distributes inserts across multiple B-tree leaf pages,
reducing PAGELATCH_EX contention. LAST_SESSION_REFRESH_COARSE is a coarse-grained version of
LAST_SESSION_REFRESH (rounded to maxIdle/2 boundaries), reducing index repositions by ~83%
since the coarse value only changes every maxIdle/2 seconds instead of on every refresh. -->

<changeSet id="26.8.0-51851-add-session-bucket" author="keycloak">
<preConditions onFail="MARK_RAN" onSqlOutput="TEST">
Expand All @@ -42,6 +44,22 @@
</addColumn>
</changeSet>

<changeSet id="26.8.0-51900-add-last-session-refresh-coarse" author="keycloak">
<preConditions onFail="MARK_RAN" onSqlOutput="TEST">
<not>
<columnExists tableName="OFFLINE_USER_SESSION" columnName="LAST_SESSION_REFRESH_COARSE"/>
</not>
</preConditions>
<addColumn tableName="OFFLINE_USER_SESSION">
<column name="LAST_SESSION_REFRESH_COARSE" type="BIGINT" defaultValueNumeric="0">
Comment thread
ahus1 marked this conversation as resolved.
Resolved
<constraints nullable="false"/>
</column>
</addColumn>
<modifySql dbms="mssql">
<replace replace="DEFAULT 0" with="DEFAULT 0 WITH VALUES"/>
</modifySql>
</changeSet>

<changeSet id="26.8.0-51851-drop-old-expiration-idx-created" author="keycloak">
<preConditions onFail="MARK_RAN" onSqlOutput="TEST">
<indexExists tableName="OFFLINE_USER_SESSION" indexName="IDX_USER_SESSION_EXPIRATION_CREATED"/>
Expand Down Expand Up @@ -76,13 +94,13 @@
</modifySql>
</changeSet>

<changeSet id="26.8.0-51851-add-bucketed-expiration-idx-last-refresh" author="keycloak">
<changeSet id="26.8.0-51900-51851-add-bucketed-and-coarse-expiration-idx-last-refresh" author="keycloak">
<createIndex tableName="OFFLINE_USER_SESSION" indexName="IDX_USER_SESSION_EXPIRATION_LAST_REFRESH">
<column name="REALM_ID" type="VARCHAR(36)"/>
<column name="OFFLINE_FLAG" type="VARCHAR(4)"/>
<column name="REMEMBER_ME" type="BOOLEAN"/>
<column name="SESSION_BUCKET" type="SMALLINT"/>
<column name="LAST_SESSION_REFRESH" type="INT"/>
<column name="LAST_SESSION_REFRESH_COARSE" type="BIGINT"/>
<column name="USER_SESSION_ID" type="VARCHAR(36)"/>
<column name="USER_ID" type="VARCHAR(255)"/>
</createIndex>
Expand Down
Loading
Loading