I want to use CockroachDb with Asp.Net Core Identity. Cockroach uses Postgres' wire protocol, so Npgsql.EntityFrameworkCore.PostgreSQL package works with Cockroach as well.
Simply adding all the necessary services, generating migration and applying it doesn't work, because generated migration uses NpgsqlValueGenerationStrategy.IdentityByDefaultColumn, which results in columns with GENERATED BY DEFAULT AS IDENTITY, which is not supported by Cockroach. So I changed NpgsqlValueGenerationStrategy to SerialColumn in all the generated migration files (migration, designer, model snapshot). After this change applying migration works, but another error arises when calling UserManager.AddClaim. Overflow exception. So I figured this is because Cockroach for SERIAL columns by default does not use sequences, but instead generates unique numbers based on current timestamp and node id, which results in some pretty huge numbers. Cockroach provides a way to overwrite the behavior of SERIAL keyword for current session by executing SET experimental_serial_normalization = sql_sequence. So I dropped all the tables and applied migration again, but before doing it added code, which executes above mentioned command, to AppDbContext's constructor. After applying migration this code can be removed, because it is not needed anymore.
Doing all these things makes Identity work with CockroachDb, but it is super slow. Creating new user takes ~4 seconds. Adding new claim - roughly the same. Compared to PostgreSQL's ~1 second.
Using EF Core with Cockroach outside of Identity is slow, but not unreasonably so. But combining EF Core and Cockroach with Identity makes performance unacceptable.
What can be the problem? Maybe changing all these things to make Identity work with Cockroach messes it up somehow? Functionality-wise everything works fine.