The SQL schema template - indices

[rasander]

IdentityServer version

7.1

.NET version

8.0

Description

I'm wondering, how are peoples experience with the default SQL DB schema template, that is delivered with IdentityServer7 in larger environments - especially the indeces.

Do you stick to the template, or did you have to do own optimisations?

Ref: https://github.com/DuendeSoftware/products/blob/main/identity-server/templates/src/IdentityServerEntityFramework/Migrations/PersistedGrantDb.sql

At first glance, it seems the default template is a bit more "index-hungry" than I would expect. Eg. there are indexes on ServerSideSessions.DisplayName and on Expires-fields.
(Expires can be slided accordingly many times during a session, so I imagine that indices on such field would trigger very frequent index updates - maybe it is better that cleanup background tasks performs full table scan?)

Back in old days, an DBA learned me to only set indices when you find a need, so I'm considering if we a on a better place starting with a few indices and add on later based on findings.

I know that for such kind of questions, it is hard to provide specific answers, as every solution does have its own needs and behaviour, but I'll still be happy to hear you guys considerations and experiences. Especially if you have serverside sessions is enabled.

Thanks.
Micki

Reproduction steps

No response

Expected behavior

No response

Logs

No response

Additional context

No response

[RolandGuijt]

Like you said: every solution has its own needs so for Duende it's hard to implement a one size fits all solution here. Especially since we want to support many database platforms.
We apply indices that we think make sense for most and try to let devs deviate where needed. For the cleanup job for example there's the possibility to rewrite the functionality.
I'm curious if our community has opinions on this and we will continue to monitor this discussion.

[rasander]

@RolandGuijt Thanks for answer. :-)

One thing I noticed about the default schema is that all the Expires / Expiry / Created / CreationTime fields is TEXT data type. Seems to be a bit odd. Can you confirm if this is the correct off-the-shelf schema? Maybe it needs to be updated a bit?

[rasander]

Sorry. I just found out, there is another default schema here: https://github.com/DuendeSoftware/products/blob/main/identity-server/migrations/IdentityServerDb/Migrations/PersistedGrantDb.sql

This one seems to use DateTime2 types on dates fields.

[AndersAbel]

The migrations (both in C# format and exported as SQL) are dependent on the provider used. Our templates use Sqlite, so the migrations included are for Sqlite where TEXT is used. If you switch database providers, you should use the EF tooling to regenerate the migration for the new provider.