Storing long value fields

[vramik]

There are some fields which could be very long. An example of such a field would be redirectUri.

There has been some issues with its very long values in legacy jpa event store: https://issues.redhat.com/browse/KEYCLOAK-12473

To support this kind of fields postgresql offers datatype text: https://www.postgresql.org/docs/current/datatype-character.html

It is possible to generate new column with text datatype.

But would it rather make sense to avoid storing such a filed inside json altogether and extract it e.g. to attribute table?

[vramik]

@sguilhen just heads up. This affects event store.

[ahus1]

@vramik - looking at the example you provided, the redirectUri occurs in the detailsJson, and this is a stored as a JSON in PostgreSQL (in the metadata). Therefore, AFAIK the length problem wouldn't occur in the map-JPA implementation.

Having it as a separate column could help for performance if we would load the details without the redirect URI, which is not the case IMHO. Therefore I'd rather not put the redirectUri for the events in a separate column ... it is also no searchable fields for Events, isn't it?

[vramik]

Therefore, AFAIK the length problem wouldn't occur in the map-JPA implementation.

Having it as a separate column could help for performance if we would load the details without the redirect URI, which is not the case IMHO. Therefore I'd rather not put the redirectUri for the events in a separate column ... it is also no searchable fields for Events, isn't it?

No it should not occur in map-jpa in case we let it be stored within the json. My biggest concern is that how big then the json would be when there are stored such a long fields and whether it'd remain usable.

redirectUri is not a searchable fields in neither auth/client-sessions or events store. In auth-session store the redirectUri field is used across all login-flows, token exchange etc. So I was wondering if it could be beneficial to generate a field from json with it.

[ahus1]

@vramik with the examples you provided, there is only a single long field per entity (one uri per event, one uri per auth session, one uri per client, etc. Event if the uri would be the dominating factor in the JSON, and as we're not storing 100s of them in a single JSON, there is no need to optimize this.

[vramik]

Thank you, makes perfect sense!

[sguilhen]

Some pointers on JSON size when used in DBs:

• CRDB: https://www.cockroachlabs.com/docs/stable/jsonb.html#size
• MySQL: https://dev.mysql.com/blog-archive/how-large-can-json-documents-be/