Scale session storage beyond a single database node and region with distributed databases #52485

ahus1 · 2026-09-07T10:34:52Z

ahus1
Sep 7, 2026
Collaborator

TL;DR: I'm proposing a denormalized single-table session store that works with distributed SQL databases (CockroachDB, YugabyteDB, Spanner, TiDB), enabling horizontal scaling and geo-sharding of session data. The existing 4-table schema stays as the default for traditional databases.

Note: the shortlisted databases are candidates — any of them may be dropped as we progress if they prove not to be a good fit for reasons listed here, or other reasons that we discover. There is no commitment to any timeline.

We'd love your input on:

Does your deployment need session storage to scale beyond a single database node? If so, which mode are you running (stateless, persistent sessions, non-persistent)?
Is re-authentication acceptable when a region goes down, or do you need session survival across region failures?
Do you use or plan to use any of the shortlisted distributed databases?

Description

Keycloak currently supports single-node relational databases (PostgreSQL, MySQL, MariaDB, Oracle, MSSQL) for session persistence. As deployments grow, session storage can outgrow a single database node — in stateless mode (where all session operations hit the database directly), and with persistent user sessions enabled (where session data is written through to the database even in clustered mode).

Distributed databases can horizontally shard session data across nodes and geo-shard it across regions, enabling Keycloak to scale session throughput linearly with the number of database nodes — with low operational overhead compared to manually sharding or replicating traditional databases. This proposal introduces a denormalized session storage SPI implementation optimized for distributed databases, alongside the existing normalized 4-table implementation for traditional databases.

Key trade-off: region failure means re-authentication

With geo-sharding, sessions are pinned to the region where the user authenticated. If that region goes down, sessions are lost and users must re-authenticate. No distributed SQL database today supports consensus-based automatic failover with async cross-region replication, and sync replication is not viable due to write latency. This assumes re-authentication on region failure is an acceptable trade-off for transient session data, but we need community feedback on this assumption — does your deployment require session survival across region failures?

Value Proposition

Horizontal session scalability: Session write throughput scales linearly with database nodes, removing the single-node bottleneck regardless of whether Keycloak runs in stateless or clustered mode.
Low operational cost: Distributed databases handle sharding, rebalancing, and intra-region failover automatically — no manual partitioning, replication setup, or failover scripts.
Geo-sharding: Sessions can be pinned to the region where the user authenticated, reducing latency and enabling data residency compliance (e.g., GDPR).
Transparent cross-region access: All shortlisted databases allow any node to query any data regardless of where it is pinned, with cross-region reads adding latency but requiring no application changes.

Goals

Introduce a denormalized single-table session store as an alternative SPI implementation for distributed databases.
The single table combines data from the current 4 session tables (ROOT_AUTH_SESSION, AUTH_SESSION, OFFLINE_USER_SESSION, OFFLINE_CLIENT_SESSION) keyed by session ID.
Any distributed database can shard this single table by session ID, guaranteeing that all session data for a login transaction is on the same shard — no co-partitioning feature required.
Support geo-sharding via an optional region column, allowing sessions to be pinned to the region where the user authenticated.
Preserve the existing normalized 4-table session store for traditional databases (PostgreSQL, MySQL, Oracle, MSSQL), which remains optimal for single-node deployments.
Validate compatibility with the shortlisted distributed databases.
Revoked tokens and single-use tokens (e.g., authorization codes) also need region-aware handling. It is not yet clear whether these can be tied to a user session and stored in the same denormalized table, or whether they require a separate mechanism.

Shortlisted databases

Database	Horizontal sharding	Geo-sharding	Filtered indexes	Hibernate dialect	PG compatible	Cloud managed	Self-managed
CockroachDB	Yes	Yes	Yes (`WHERE`)	`CockroachDialect` (core)	Yes	CockroachDB Cloud	Yes (BSL license)
YugabyteDB	Yes	Yes	Yes (`WHERE`)	`PostgreSQLDialect`	Yes	YugabyteDB Managed	Yes (Apache 2.0)
Spanner	Yes	Yes	Yes (`NULL_FILTERED`)	`SpannerDialect` (core)	Partial (PGAdapter)	Google Cloud only	No
TiDB	Yes	Yes	Yes	Community dialect	No (MySQL)	TiDB Cloud	Yes (Apache 2.0)

We would value true Open Source, self-managed variants over other variants as Keycloak is also an Open Source project.
Using an extra Hibernate dialect is a big burden for maintenance, and will be counted into the decision.
Expect this list of databases to shrink as this develops. Other databases might appear if they provide the features we ask for.

Non-Goals

Replacing the existing session store: The normalized 4-table schema remains the default for traditional databases. The denormalized store is an opt-in alternative.
Cross-region disaster recovery: None of the shortlisted distributed SQL databases support consensus-based automatic failover with async cross-region replication (sync is not viable due to 50-150ms write latency). Leaderless databases like Cassandra offer automatic failover but accept writes from any region, making misdirected requests a correctness risk. For now, re-authentication on region failure is the accepted recovery model.
Co-partitioning support: No PostgreSQL-compatible distributed database currently supports co-partitioning (sharding multiple tables by the same key with guaranteed co-location). The denormalized single-table design eliminates this requirement entirely.
Geo-replication: Cross-region data replication is not in scope for the initial implementation. Geo-sharding (data pinned to a region without cross-region copies) is sufficient for session data.
Zero-downtime upgrades: As sessions are local in a region, zero-down downtime does not apply. Upgrading to different versions of Keycloak in each region is also out of scope, and distributed databases will not solve that.

Notes

Why a single denormalized table?

During login, Keycloak touches 4 session tables in one transaction, all keyed by the same session ID:

ROOT_AUTH_SESSION (PK: ID)
AUTH_SESSION (PK: ROOT_AUTH_SESSION_ID, TAB_ID)
OFFLINE_USER_SESSION (PK: USER_SESSION_ID, OFFLINE_FLAG)
OFFLINE_CLIENT_SESSION (PK: USER_SESSION_ID, CLIENT_ID, ...)

In a distributed database, each table has its own independent set of shards. Even though all 4 tables have the session ID as the leading PK column, there is no mechanism to guarantee that rows with the same session ID across different tables land on the same node ("co-partitioning"). Without co-partitioning, every login becomes a distributed transaction spanning 4+ shards across potentially 4 different nodes, adding significant latency.

A single denormalized table eliminates this problem: one table, one shard key, all session data for a given session ID guaranteed to be on the same shard. This works on every distributed database without any vendor-specific feature.

Co-partitioning landscape

Co-partitioning is a tracked feature request in YugabyteDB (#79, #11219) but not implemented. CockroachDB had it (interleaved tables) but removed it in v22.2 due to implementation complexity. Only Citus (PostgreSQL extension) and Google Spanner support it today, but Citus lacks geo-sharding and Spanner is cloud-only.

Impact on traditional databases

The denormalized table is not suitable for traditional single-node databases:

Wider rows with many NULLs reduce cache efficiency.
Type-specific indexes require filtered/partial indexes, which MySQL/MariaDB don't support.
FK cascades (e.g., AUTH_SESSION -> ROOT_AUTH_SESSION ON DELETE CASCADE) are lost.
The existing SESSION_BUCKET and LAST_SESSION_REFRESH_COARSE optimizations (designed for B-tree page latch contention on MSSQL/Oracle) would need redesign for mixed row types.

This is why the denormalized store is a separate SPI implementation, not a replacement for the existing schema.

Filtered indexes on the denormalized table

Type-specific columns are naturally NULL for other entry types, enabling efficient filtered indexes:

-- PostgreSQL, CockroachDB, YugabyteDB
CREATE INDEX idx_expiration_created ON sessions (realm_id, offline_flag, remember_me, session_bucket, created_on)
    WHERE entry_type = 'user_session';

-- Spanner (NULL_FILTERED works because created_on is NULL for non-user-session rows)
CREATE NULL_FILTERED INDEX idx_expiration_created
    ON sessions (realm_id, offline_flag, remember_me, session_bucket, created_on);

-- TiDB, MySQL (no filtered indexes — use composite index with entry_type as leading column)
CREATE INDEX idx_expiration_created ON sessions (entry_type, realm_id, offline_flag, remember_me, session_bucket, created_on);

Geo-sharding

With an optional region column, sessions can be pinned to the authenticating region:

session_id	region	entry_type	offline_flag	data
abc-123	eu	user_session	0	{...}
abc-123	eu	client_session	0	{...}
abc-123	eu	root_auth		{...}
abc-123	eu	auth_tab		{...}

All entries share session_id + region — they shard together and geo-locate together automatically. Cross-region access remains transparent (any node can query any data), with added latency for cross-region reads.

Region-aware routing (optional): An intelligent reverse proxy in front of Keycloak could reduce cross-region latency by routing requests to the Keycloak instance in the region that owns the session. The region can be derived from identifiers already present in cookies, tokens, and authorization codes. This avoids cross-region database reads for the common case (user continues in the same region they authenticated in), while still allowing transparent fallback when routing is imprecise.

Architecture

--db=postgres/mysql/oracle/mssql  →  JpaUserSessionPersisterProvider (4 normalized tables)
--db=cockroach/yugabyte/spanner/tidb   →  DistributedUserSessionPersisterProvider (1 denormalized table)

The SPI selection is driven by the Database.Vendor enum, adding new vendors (COCKROACH, YUGABYTE, SPANNER, TIDB) alongside the existing ones.

Cross-region disaster recovery

The ideal cross-region DR model for Keycloak is async replication with consensus-based automatic failover: one region owns session data, writes are fast (local only), and ownership transfers automatically via consensus when a region fails. Sync replication is not viable (50-150ms per write). Manual failover has too high an operational cost.

Current state: No distributed SQL database supports this today. Raft-based systems (CockroachDB, YugabyteDB, TiDB) require manual operator intervention to promote a standby region during async replication, because async mode breaks Raft's consistency guarantees and automatic promotion risks split-brain.

Leaderless databases (Cassandra): Offer automatic failover but use a fundamentally different model — any node in any DC accepts writes, with last-write-wins conflict resolution. This means a misdirected request (e.g., a load balancer routing to the wrong region) silently writes in the wrong place, and the owning region's data can be overwritten. For session data this is a correctness risk that makes leaderless replication a poor fit for Keycloak.

Outlook: Consensus-based async cross-region failover is an active area of development. If a distributed SQL database adds this capability, Keycloak's denormalized session store would be ready to use it with no schema changes — only a database configuration change.

xgp · 2026-09-08T08:11:26Z

xgp
Sep 8, 2026

Thanks @ahus1. Glad to see this getting some interest.

For your testing of CockroachDB, this is our Apache v2 licensed fork that we've shipped for ~4 years. Each branch is named with the Keycloak version + _crdb (e.g. https://github.com/p2-inc/keycloak/tree/26.7.3_crdb) so you can see a diff.

Couple of notes for CRDB:

The existing Liquibase DDL didn't work despite Postgres "compatibility" so we had to fork the changelog at v17 https://github.com/p2-inc/keycloak/blob/26.7.3_crdb/model/jpa/src/main/resources/META-INF/jpa-changelog-master-crdb.xml#L20
DDL changes are slow in CRDB because of the quorum mechanism it uses. We see ~2-8 minute initial database bootstrap times depending on what type of cluster is deployed and the performance of the nodes and network. There are workarounds that allow you to restore the schema from a native format CRDB backup, but we don't ship those in our fork because they are CRDB version dependent.
CRDB already has a concept of regional tables that survive zone/region outage, but as you say, for region survivability, it requires synchronous replication. The workaround for this would be to have 2 "databases" one for the denormalized sessions, set to zone survivability, and one for the rest of the tables, set to region survivability. As you know, we sidestep this whole thing by storing the sessions elsewhere in production, but we've tested a similar architecture and it works with CRDB regional tables with zone survivability.

Happy to help with this if you have an idea of what you want a contribution to look like and how it will be tested/validated.

3 replies

ahus1 Sep 8, 2026
Collaborator Author

Thank you for your input on CRDB.

Could you please provide input on the following questions from my original post:

Does your deployment need session storage to scale beyond a single database node? If so, which mode are you running (stateless, persistent sessions, non-persistent)?

Are you choosing CRDB only for simplicity of operations, multi-region support, or for scaling beyond a single database node?

Is re-authentication acceptable when a region goes down, or do you need session survival across region failures?

Replicating sessions synchronously to another region is slow. So in order to design what users need, I need your and other community input on this question.

xgp Sep 8, 2026

Does your deployment need session storage to scale beyond a single database node? If so, which mode are you running (stateless, persistent sessions, non-persistent)?

Yes. Regarding what is built-in to Keycloak, we have tried the new stateless feature, but our experience is more with persistent sessions. Depending on use case, and cross zone/region latency, we've found that the user latency can be acceptable as-is.

Are you choosing CRDB only for simplicity of operations, multi-region support, or for scaling beyond a single database node?

All of the above.

Is re-authentication acceptable when a region goes down, or do you need session survival across region failures?

Replicating sessions synchronously to another region is slow. So in order to design what users need, I need your and other community input on this question.

Re-auth is okay. Our target is for sessions to survive zone failure but not region failure.

ahus1 Sep 8, 2026
Collaborator Author

our experience is more with persistent sessions.

Reading about distributed database, and Keycloak updating two session tables which are then distributed to different nodes, the current DB schema is not great for scaling horizontally (see below).

Each branch is named with the Keycloak version + _crdb (e.g. https://github.com/p2-inc/keycloak/tree/26.7.3_crdb) so you can see a diff.

It is a hard by looking at the code to get the rationales you followed and lessons learned for CRDB. Any insights about the offline session tables? As usually two tables are touched, I assume this is a distributed transaction, which is slow and prevents proper scaling.

Depending on use case, and cross zone/region latency, we've found that the user latency can be acceptable as-is.

Please provide context with region pairs/triplets used, inter-region ping network latency observed, Keycloak login latencies observed around logins and token refreshes, and requests per seconds. With numbers, other people can assess if this is acceptable for them as well or not.

As you stated "All of the above" for "simplicity of operations, multi-region support, or for scaling beyond a single database node", please rank them and provide insights in which scenario you value one over the other.

I'm especially interested in "scaling beyond a single database node" - is it for transaction throughput or high availability? A PostgreSQL instance can scale horizontally quite well (I've seen 15k logins per second with a single instance).

denniskniep · 2026-09-11T10:26:28Z

denniskniep
Sep 11, 2026

Hi @ahus1 ,

thanks for bringing that up, we are very interested!

Does your deployment need session storage to scale beyond a single database node?

yes, but not due to load (at least at the moment)
We are aiming for zero downtime (wherever possible, but in the first place during upgrades, evictions, etc.)

which mode are you running (stateless, persistent sessions, non-persistent)?

persistent sessions
(aiming for stateless in the future)

Is re-authentication acceptable when a region goes down, or do you need session survival across region failures?

yes

Do you use or plan to use any of the shortlisted distributed databases?

TiDB

Filtered indexes on the denormalized table

TiDB support partial indexes which is basically apply a where statement to an index (new feature of v8.5.7)

Hibernate dialect

TiDB fixed the major blocker to use MySQL Hibernate Dialect in Keycloak: pingcap/tidb#63035
(There are more of those Hibernate related issues, but maybe these statements are not used in KC: pingcap/tidb#65159)
We can look into that. Maybe also a good moment to revive CI/CD for additional DB´s ?

Region-aware routing

That makes total sense. Especially embedding region info directly into the identifiers (cookies, tokens, authorization codes) that Keycloak already issues. Everyone will have their own opinionated approach to multi-region load balancing and traffic routing, so it's reasonable to leave the actual routing logic up to whoever operates Keycloak. Keycloak just needs to expose the region info.

3 replies

ahus1 Sep 11, 2026
Collaborator Author

We are aiming for zero downtime (wherever possible, but in the first place during upgrades, evictions, etc.)

During upgrades of what part? Upgrades of Keycloak would be out of scope. I don't know what evictions are in your context.

aiming for stateless in the future

Why would you like to use stateless? For multi-cluster v2 setups, or something else?

Everyone will have their own opinionated approach to multi-region load balancing and traffic routing

It would be tedious and error prone for people do to that themselves. Instead, Keycloak would ship its own reverse proxy (probably Quarkus based) to put in front of Keycloak to do the routing.

The traffic path will then be as follows:

Global LB -> Keycloak Router (one or more in each region) -> Keycloak (multiple instances in each region)

You can choose any strategy to route your traffic to a region (for example proximity)
The Keycloak router will inspect any token or cookie in the request and route it to the region where this session belongs to, and then route the request to a regional LB in that region. If no information is found, it will route it to instances in its own region.
If a Keycloak router fails to identify a token or cookie, this would lead to multiple cross-region DB access, which would be slow, but not fatal.

ahus1 Sep 11, 2026
Collaborator Author

In the meantime, I've added "Zero-downtime upgrades" as a non-goal, especially as sessions would be stored in one region only, and they would be inaccessible when a region is not available as I rules out synchronous replication.

denniskniep Sep 11, 2026

Zero Downtime

I mean zero downtime of the database (This for sure depends on the DB, but its more likely to achieve that compared to a single node DB). Especially during version updates of that DB or if the underlying node needs to be updated and the DB pod is evicted.

Why would you like to use stateless? For multi-cluster v2 setups

correct

Keycloak Router

didn´t expect that, even better :-)

Maybe worth to think about using an existing proxy for this like envoy. Because it has already battle-tested code for connection pooling etc. Envoy has an extension mechanism where you can write custom logic as go, rust or C++ code.

dveeden · 2026-09-21T15:09:17Z

dveeden
Sep 21, 2026

TiDB now has support for partial indexes:

sql> create table t1 (id int primary key, name varchar(255));
Query OK, 0 rows affected (0.0535 sec)

sql> alter table t1 add index idx_name (name) where name is not null;
Query OK, 0 rows affected (0.1995 sec)

sql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,
  KEY `idx_name` (`name`) WHERE `name` is not null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.0009 sec)

See also https://docs.pingcap.com/tidb/stable/sql-statement-create-index/#partial-indexes-new-in-v857

1 reply

ahus1 Sep 21, 2026
Collaborator Author

Thanks, updated!

dveeden · 2026-09-25T07:20:37Z

dveeden
Sep 25, 2026

What is good for B-Tree/B+Tree based databases is often not good for TiDB.

For a B-Tree/B+Tree you usually want to avoid page splits that happen because a page gets too big that sits in the middle of the keyspace. The way to do this is to have incrementing ID's that all get written to the same page in the end of the keyspace.

What TiDB/TiKV does is this: A table is split into data ranges based on either the primary key or an auto generated rowid. Each data region is a raft group with a leader and usually two followers. There are many more regions than nodes as a region is about 256MiB. So records with PK's close to each other (e.g. AUTO_INCREMENT) are likely in the same data region and are written to the same leader node. Randomly chosen primary keys are likely ending up in different data regions and end up on different nodes.

For example a table t1 of 1GiB would have 4 data regions. With 6 TiKV nodes this could lead to the following situation:

Regions:

R1    0-1000
R2    1001-2000
R3    2001-3000
R4    3001-4000

      R1    R2    R3    R4
kv1:  P           F
kv2:  F     P      
kv3:  F                 F
kv4:        F           P   
kv5:        F     F     
kv6:              P     F

(P=Primary, F=Follower)

So if you insert 5 records with AUTO_INCREMENT:

3050
3051
3052
3053
3054

These would all end up in region 4, with a leader on kv4.

With AUTO_RANDOM:

201
3012
2900
1111
3015

These would end up in all 4 regions, with leaders on 4 separate nodes.

Incremental PK:

AUTO_INCREMENT
UUIDv7

Random PK:

AUTO_RANDOM (TiDB enhancement)
UUIDv1, UUIDv4

Note that this is usually not too much of an actual problem in practice as TiDB/TiKV is able to split data regions quick enough. And multiple tables might have hotspots on different nodes, leading to a more even load with many tables.

More details: https://docs.pingcap.com/best-practices/high-concurrency-best-practices/

3 replies

ahus1 Sep 25, 2026
Collaborator Author

With Keycloak, when it would write into the 4 tables related to one user session, all would have the same user session ID, though other elements of the PK would be different. Is that sufficient to put them in the leader node in 90+% of the time?

User session IDs are already random.

dveeden Sep 25, 2026

If the PK is clustered (so using the actual PK for storage) this is fine if the random sessionID is at the start.

If a _tidb_rowid is used instead (so nonclustered) then this should also be ok. However this means that PK lookups need an index lookup first and then a lookup based on the _tidb_rowid instead of just a single lookup.

Note that beside the table itself there are also secondary indexes as @mjonss explained.

More details: https://docs.pingcap.com/tidb/stable/clustered-indexes/

mjonss Sep 25, 2026

Just to clarify, TiDB does not have a 'leader node', it stores the data (both table and index) into key-value data, where the keys are composed like t<table_id>_r<PK column values> / t<table_id>_i<index_id><indexed column values> and splitted into ~256MB (internally called TiKV regions). Each such TiKV region forms its own Raft region that makes sure that we have the data copied into 3 different AZ (that is how we enforce High Availability). TiDB only reads and writes to the leader of each raft group. Currently we have no way to co-locate the region (or their leaders), but distributes them depending on load (size, read and write load). We have some optimizations for key lookups on the same node.

So if you only used a single table and the sessionID would be defined as the first column in the PK, then all the related rows would almost always be in the same TiKV region. If the data would be large enough (multiple regions), then index entries may be in different regions, requiring 2PC. If using 4 tables, it would be at least 4 regions (by default we are not sharing regions between tables).

mjonss · 2026-09-25T14:00:21Z

mjonss
Sep 25, 2026

@ahus1 a few points from the TiDB side (I work on TiDB at PingCAP) on "one table, one shard key, all session data for a given session ID guaranteed to be on the same shard": this holds for the rows, but not for the secondary indexes.

In short: the single table does not make a login single-range, but it does make the most frequent write, the token refresh, a 1PC.

Below, "range" means a TiKV Region (a key range replicated as one Raft group, target size is a quarter of a gigabyte), and "cloud region" means a geographic region.

How indexes are stored in TiDB

Rows are keyed t<table_id>_r<PK>, each secondary index has its own key space t<table_id>_i<index_id><values>. At scale each is split into its own ranges, with leaders on different nodes.
A transaction whose writes all fall into one range commits as one phase commit, 1PC. Otherwise it is a distributed commit (2PC), with all ranges prewritten in parallel.
1PC and hotspot avoidance are two separate things to consider when designing tables and indexes for their write patterns: 1PC needs the keys written by one transaction in the same range, hotspot avoidance needs the keys written by concurrent transactions spread over different ranges. AUTO_RANDOM is an example that usually gives both: rows inserted in one transaction get the same shard bits, different transactions get different ones.
A non-clustered PK is also a separate index. The current AUTH_SESSION and OFFLINE_* tables get their PK via ALTER TABLE ... ADD PRIMARY KEY, which is always non-clustered in TiDB. A new table should declare its PK in CREATE TABLE as CLUSTERED.

What this means for the single table

A user_session insert writes 1 row + 3-4 index entries (by user, 2 x expiration, broker session) in both designs. The single table only co-locates the row with the other rows of the session.
So a login is still a distributed transaction: the single table saves the row ranges of the other tables, but all index ranges remain. Less load, but not the latency win of a single-range transaction.
Where it does help is the most frequent write, the token refresh, which updates the user session and client session row in one transaction. Measured on TiDB v8.5.8 (@@tidb_last_txn_info, 200 random sessions): 4 tables always multi-range (2PC), single table 1PC in all cases. That depends on LAST_SESSION_REFRESH_COARSE (Avoid updates to IDX_USER_SESSION_EXPIRATION_LAST_REFRESH on every token refresh #51904) keeping the expiration index unchanged on most refreshes.
SESSION_BUCKET should stay (the proposal lists it as needing a redesign): without it all new expiration index entries hit one range (measured 100% of writes to 1 of 48 ranges), with it they spread (35 of 36 ranges, busiest 6%).
TiDB partial indexes (since v8.5.7; the SQL block in the proposal still says otherwise) accept a single comparison only (AND/IN are rejected), and queries must repeat the predicate, e.g. ENTRY_TYPE = 'user_session'.

CockroachDB and YugabyteDB (not my expertise)

CockroachDB: same model. Each index is its own key span (/Table/<id>/<index_id>/...) with its own ranges. Single-range writes use a 1PC fast path, otherwise parallel commits. REGIONAL BY ROW partitions the indexes by crdb_region, so index entries stay in the cloud region, but still in other ranges.
YugabyteDB: each index is a separate table with its own tablets. A write to one tablet is a single-shard (fast path) transaction, index updates make it a distributed transaction.
Only Spanner can interleave an index with its parent rows.

Cloud-regional vs global cluster

Cloud-regional cluster (one cluster per cloud region, nodes across availability zones): the extra ranges of a multi-range commit cost round trips within the cloud region, so mainly extra load rather than latency. This matches the proposal's model of sessions local to a cloud region, with the Keycloak router sending requests to the cloud region that owns the session.
For disaster recovery, add asynchronous replication of each regional cluster to another cloud region (e.g. TiCDC), instead of losing all sessions when a cloud region fails.
Failover would be manual and may lose the last seconds of session writes, but far fewer users need to re-authenticate. How to automate it, also for automatic failover, is probably better as its own discussion.
Global cluster with geo-partitioning (one cluster across cloud regions): every range a transaction touches, rows and each index, needs its leader and Raft majority in the session's cloud region to stay fast. That takes region partitioning, placement policies and local indexes. I would not recommend it for session data: it is complex to set up and tune to get both low local latency and global distribution with regional placement.

2 replies

ahus1 Sep 25, 2026
Collaborator Author

Thank you for these insights, I'm still digesting it. My first takeaways:

1PC will only work when you are not touching a secondary index (would hold true for TiDB, but also for CockroachDB and YugabyteDB to your knowledge).
One expected benefit is for token refresh when both user and client sessions end up in the same table, combined with the PK being a clustered index, and the random user session ID at the start of the PK. Once the table fills up with lots of data, it will have multiple ranges, and scale as expected.
For a login, there will be always secondary indexes involved, and with that it might not make sense to put all 4 tables in to one table.
An alternative could be to split the tables for user/client sessions, and authentication sessions. The authentication sessions table would the small enough to possibly drop the secondary index for realm and timestamp, and do a full table scan for cleaning stale entries and deleting by realm. OTOH, while the table is small with its current rows, it might have a lot of tombstones / deleted rows. That could still make it a "big" table depending on the database.

@dveeden / @mjonss - maybe you could comment on the idea to keep those separate, and if those benefits would actually materialize.

This reminds me that the root authentication session doesn't have a coarse timestamp today, and that I should look into that.

mjonss Sep 25, 2026

@ahus1 I will experiment a little bit, but I wonder if some of these are not pre-mature optimizations?

Going back to your original Value Proposition:

Horizontal session scalability: The throughput should be higher and practically unlimited for distributed databases (single node databases might be faster and have lower latency until they cannot scale). I think there are some things that could be considered here, like possibly de-normalize, use CLUSTERED primary keys etc. to improve performance. I will try to understand a keycloak a bit more before coming with suggestions.
Low operational cost: This is where distributed databases like TiDB shine, built in High Availability, automatic balancing and even scaling in some setups, rolling upgrades, fully online DDL etc.
Geo-sharding/partitioning, i.e. having a global database that could pin a region column to specific cloud regions. In general I think geo-partitioning is hard to get right with locality, fault tolerance, latency etc. So I would move that out from this discussion, and focus on how a database for a single cloud region could be improved (if needed).
Transparent cross-region access: I would reframe it to 'Multi-node awareness' removing the 'cross-region' due to my points with geo-sharding/partitioning. While keeping the multi-node impact on performance, to better understand it.

So that basically leaves how can we optimize keycloak for distributed databases within a single region, and leave out Global databases/geo-partitioning as well as cloud regional database failure handling to separate discussions.

Do you have any benchmark tests that could be useful for testing and validating changes against various databases?

Scale session storage beyond a single database node and region with distributed databases #52485

Uh oh!

Uh oh!

ahus1 Sep 7, 2026 Collaborator

Description

Key trade-off: region failure means re-authentication

Value Proposition

Goals

Shortlisted databases

Non-Goals

Notes

Why a single denormalized table?

Co-partitioning landscape

Impact on traditional databases

Filtered indexes on the denormalized table

Geo-sharding

Architecture

Cross-region disaster recovery

Replies: 5 comments · 12 replies

Uh oh!

Uh oh!

ahus1 Sep 8, 2026 Collaborator Author

Uh oh!

Uh oh!

ahus1 Sep 8, 2026 Collaborator Author

Uh oh!

Uh oh!

ahus1 Sep 11, 2026 Collaborator Author

Uh oh!

ahus1 Sep 11, 2026 Collaborator Author

Uh oh!

Uh oh!

Uh oh!

ahus1 Sep 21, 2026 Collaborator Author

Uh oh!

Uh oh!

Uh oh!

Uh oh!

ahus1 Sep 25, 2026 Collaborator Author

Uh oh!

Uh oh!

Uh oh!

Uh oh!

ahus1 Sep 25, 2026 Collaborator Author

Uh oh!

ahus1
Sep 7, 2026
Collaborator

Replies: 5 comments 12 replies

ahus1 Sep 8, 2026
Collaborator Author

ahus1 Sep 8, 2026
Collaborator Author

ahus1 Sep 11, 2026
Collaborator Author

ahus1 Sep 11, 2026
Collaborator Author

ahus1 Sep 21, 2026
Collaborator Author

ahus1 Sep 25, 2026
Collaborator Author

ahus1 Sep 25, 2026
Collaborator Author