Avoid updates to IDX_USER_SESSION_EXPIRATION_LAST_REFRESH on every token refresh - #51904

Closes #51900

I did a minimal local performance test with MSSQL - just counting the updates on tables, not so much measuring congestion:

./bin/kcb.sh --scenario=keycloak.scenario.authentication.AuthorizationCode --server-url=http://0.0.0.0:8080 --realm-name=test-realm --username=user-0 --users-per-sec=5 --ramp-up=1 --client-id=gatling --client-secret=setup-for-benchmark --client-redirect-uri=http://0.0.0.0:8080 --measurement=300 --log-http-on-failure --refresh-token-count=10 --refresh-token-period=2 --refresh-close-http-connection=false --logout-percentage=10

Before: IDX_USER_SESSION_EXPIRATION_LAST_REFRESH had 15020 leaf_inserts_delta

After: IDX_USER_SESSION_EXPIRATION_LAST_REFRESH had 1502 leaf_inserts_delta, matching the number of logins performed.

The log volume didn't reduce much (66584 -> 62756).

As I was comparing with 26.7.3, I saw that IDX_OFFLINE_USS_BY_BROKER_SESSION_ID now has zero leaf_inserts_delta due to the filtering applied.

I also asked:

With the segmentation, I might no longer have PAGELATCH_EX contention. Is it still worth it?

Then Claude replied:

Yes, still worth it. Even without PAGELATCH_EX contention (which SESSION_BUCKET already solved), each index reposition still generates log records — a delete from the old B-tree position and an insert at the new position. On
Azure SQL Hyperscale with its ~100 MB/sec log governance ceiling, every byte of log throughput matters.

Eliminating repositions means:

• Less log volume per refresh — the expiration index generates zero log records instead of two (delete + insert)
• Smaller working set — fewer index pages touched means less buffer pool pressure
• Cheaper I/O — fewer page writes to flush

These benefits are independent of contention. SESSION_BUCKET solved the "many writers fighting over the same page" problem. LAST_SESSION_REFRESH_COARSE solves the "unnecessary work on every refresh" problem. They're complementary.

That said, the coarse column adds complexity (the two-pass cleanup, the per-session offset logic). If your production workload is nowhere near the log governance ceiling even without this change, the SESSION_BUCKET optimization alone might be sufficient. The question is whether the reduced log volume justifies the added complexity for your deployment.

I also asked:

How costly is an index update on the different databases supported by Keycloak?

and Claude responded:

Index update cost across databases supported by Keycloak

SQL Server (MSSQL)

Most affected. Uses PAGELATCH_EX for page-level concurrency, so concurrent inserts into the same B-tree leaf page serialize. The transaction log has a single sequential write point (log buffer), and Azure SQL Hyperscale has
a hard governance ceiling (~100 MB/sec). Index repositions directly reduce throughput capacity.

PostgreSQL

Uses a multi-version concurrency control (MVCC) model. Every UPDATE creates a new row version in the heap AND in every index — even indexes whose key columns didn't change (HOT updates are the exception, but only when the
updated columns aren't indexed and the new version fits on the same heap page). So PostgreSQL actually has a worse index write amplification than MSSQL for non-HOT updates. However, PostgreSQL handles concurrent writes to
the same page better (lighter-weight buffer locks) and WAL is typically less of a bottleneck. The coarse column helps PostgreSQL too by enabling HOT updates on more refreshes (the index key doesn't change).

MySQL/MariaDB (InnoDB)

Uses clustered index like MSSQL, but InnoDB has a change buffer that defers non-clustered index updates for non-unique indexes when the target page isn't in the buffer pool. This makes index repositions cheaper when pages
are cold. However, when pages are hot (high refresh rates), the change buffer doesn't help and repositions hit the buffer pool directly. Log writes (redo log) are still generated per operation.

Oracle

Similar to MSSQL in index maintenance cost. Uses redo log entries per operation. No change buffer optimization. Concurrent inserts into the same leaf block cause buffer busy waits (Oracle's equivalent of PAGELATCH_EX).

Summary

Database	Reposition cost	Concurrency bottleneck	Coarse column benefit
MSSQL	High	PAGELATCH_EX + log ceiling	High
PostgreSQL	High (MVCC write amplification)	Moderate (lighter locks)	High (enables HOT updates)
MySQL/InnoDB	Medium (change buffer helps)	Moderate	Medium
Oracle	High	Buffer busy waits	High

The optimization is beneficial across all supported databases, not just MSSQL.

After testing on PostgreSQL, I saw 99%+ hot updates on the offline user session table, where before I didn't see any hot updates due to updates to the indexed column.

With 99% HOT updates on PostgreSQL:

• No index maintenance on ~99% of refreshes — no writes to any non-clustered index, not just the expiration index
• Less WAL volume — HOT updates generate smaller WAL records since there are no index changes to log
• Less VACUUM work — HOT chains are pruned in-page on access, reducing autovacuum load
• Less I/O — only the heap page is dirtied, not the index pages

This is a larger improvement on PostgreSQL than on MSSQL. On MSSQL, the coarse column eliminates repositions on the expiration index. On PostgreSQL, it enables HOT updates which skip ALL indexes on the table.

Without the coarse column, LAST_SESSION_REFRESH being an index key column forced every refresh to be a non-HOT update, which wrote new entries into every index on the table — even indexes unrelated to
LAST_SESSION_REFRESH.

Note: the test ran for less than maxIdle / 2, so no session crossed a coarse boundary. In production with long-lived sessions, the HOT percentage would be slightly lower as boundary crossings trigger index writes.

SQL statement to extract the data on MSSQL:

truncate table OFFLINE_USER_SESSION;

truncate table OFFLINE_CLIENT_SESSION;

SELECT i.name AS index_name, ios.index_id,
       ios.leaf_insert_count, ios.leaf_delete_count, ios.leaf_update_count
INTO #idx_before
FROM sys.dm_db_index_operational_stats(DB_ID(), OBJECT_ID('OFFLINE_USER_SESSION'), NULL, NULL) ios
         JOIN sys.indexes i ON i.object_id = ios.object_id AND i.index_id = ios.index_id;

SELECT vfs.num_of_bytes_written AS log_bytes_written
INTO #log_before
FROM sys.dm_io_virtual_file_stats(DB_ID(), NULL) vfs
         JOIN sys.database_files f ON f.file_id = vfs.file_id
WHERE f.type_desc = 'LOG';

select count(*) from OFFLINE_USER_SESSION;

SELECT
    i.name AS index_name,
    a.leaf_insert_count - b.leaf_insert_count AS leaf_inserts_delta,
    a.leaf_delete_count - b.leaf_delete_count AS leaf_deletes_delta,
    a.leaf_update_count - b.leaf_update_count AS leaf_updates_delta
FROM sys.dm_db_index_operational_stats(DB_ID(), OBJECT_ID('OFFLINE_USER_SESSION'), NULL, NULL) a
         JOIN #idx_before b ON a.index_id = b.index_id
         JOIN sys.indexes i ON i.object_id = a.object_id AND i.index_id = a.index_id
ORDER BY i.name;

SELECT
    (vfs.num_of_bytes_written - b.log_bytes_written) / 1024 AS log_kb_written_delta
FROM sys.dm_io_virtual_file_stats(DB_ID(), NULL) vfs
         JOIN sys.database_files f ON f.file_id = vfs.file_id
         JOIN #log_before b ON 1=1
WHERE f.type_desc = 'LOG';

DROP TABLE #idx_before;
DROP TABLE #log_before;

Statement to extract the data on PostgreSQL:

SELECT
    n_tup_upd AS total_updates,
    n_tup_hot_upd AS hot_updates,
    n_tup_upd - n_tup_hot_upd delta,
    CASE WHEN n_tup_upd > 0
             THEN round(100.0 * n_tup_hot_upd / n_tup_upd, 1)
         ELSE 0
        END AS hot_pct
FROM pg_stat_user_tables
WHERE relname = 'offline_user_session';

  -- Before
SELECT n_tup_upd, n_tup_hot_upd
INTO TEMP idx_before
FROM pg_stat_user_tables
WHERE relname = 'offline_user_session';

-- After
SELECT
    a.n_tup_upd - b.n_tup_upd AS updates_delta,
    a.n_tup_hot_upd - b.n_tup_hot_upd AS hot_updates_delta,
    round(100.0 * (a.n_tup_hot_upd - b.n_tup_hot_upd)
              / NULLIF(a.n_tup_upd - b.n_tup_upd, 0), 1) AS hot_pct
FROM pg_stat_user_tables a, idx_before b
WHERE a.relname = 'offline_user_session';

DROP TABLE idx_before;
well