关于IdentityServer4 PersistedGrants表设计及过期数据清理的问询

Great questions—let's tackle them one by one, since they touch on both design philosophy and practical configuration.

Why PersistedGrants Uses nvarchar Instead of Foreign Keys #

The choice to store ClientId, SubjectId, and Type as nvarchar values (without foreign key constraints) boils down to three core design priorities in IdentityServer4:

• Flexibility and loose coupling
  IdentityServer4 is built to work with any user store (not just ASP.NET Core Identity) and any client registration source (in-memory, database, custom providers). Adding foreign key constraints would tie the PersistedGrants table directly to specific schema (like AspNetUsers for subjects or a custom Clients table), forcing users to conform to that structure. This loose coupling lets you swap out user/client stores without breaking the operational data layer.

• Performance for high-frequency operations
  Persisted grants (refresh tokens, authorization codes, consents) are read and written constantly. Foreign key constraints add overhead: each write requires checking referential integrity, and each read may join across tables. By using indexed nvarchar fields instead, you avoid these extra checks while still maintaining fast lookups (modern databases optimize string indexes very well, especially for short, consistent values like GUIDs or client IDs). The minor space tradeoff is negligible compared to the performance gain in high-traffic scenarios.

• Data independence for auditing and cleanup
  Foreign keys enforce strict referential integrity—if you delete a client or user, you'd have to either cascade-delete their grants or face database errors. Using nvarchar values lets you retain grant records for auditing purposes even after the associated client/user is removed. It also simplifies cleanup logic, since you don't have to worry about constraint violations when purging old data.

How to Configure Expired Grant Cleanup #

IdentityServer4 includes a built-in TokenCleanup service that automatically removes expired rows from the PersistedGrants table. Here's how to set it up:

1. Enable Automatic Cleanup via Operational Store Configuration #

If you're using Entity Framework Core (the default operational store), add this to your service registration (in Program.cs for .NET 6+ or Startup.cs for older versions):

services.AddIdentityServer()
    // Your existing IdentityServer configuration (clients, resources, etc.)
    .AddOperationalStore(options =>
    {
        options.EnableTokenCleanup = true; // Turn on automatic cleanup
        options.TokenCleanupInterval = 3600; // Interval in seconds (1 hour by default)
        // Optional: Set a batch size for cleanup to avoid locking the table
        options.CleanupBatchSize = 1000;
    });

• TokenCleanupInterval: Adjust this based on your token expiration policies—if you use short-lived tokens, you might set it to 1800 (30 minutes) instead.
• CleanupBatchSize: Limits how many rows are deleted in one operation to prevent long-running database locks.

2. Manual Cleanup (Optional) #

If you need more control over when cleanup runs (e.g., off-peak hours), you can inject the ITokenCleanup service and trigger it manually, like in a hosted background service:

public class GrantCleanupService : BackgroundService
{
    private readonly ITokenCleanup _tokenCleanup;
    private readonly ILogger<GrantCleanupService> _logger;

    public GrantCleanupService(ITokenCleanup tokenCleanup, ILogger<GrantCleanupService> logger)
    {
        _tokenCleanup = tokenCleanup;
        _logger = logger;
    }

    protected override async Task ExecuteAsync(CancellationToken stoppingToken)
    {
        _logger.LogInformation("Grant cleanup service started");

        while (!stoppingToken.IsCancellationRequested)
        {
            // Wait until off-peak hours (example: 2 AM daily)
            var now = DateTime.UtcNow;
            var nextRun = new DateTime(now.Year, now.Month, now.Day, 2, 0, 0, DateTimeKind.Utc);
            if (now > nextRun) nextRun = nextRun.AddDays(1);
            
            await Task.Delay(nextRun - now, stoppingToken);
            
            _logger.LogInformation("Starting grant cleanup");
            await _tokenCleanup.CleanupAsync();
            _logger.LogInformation("Grant cleanup completed");
        }
    }
}

Then register the service:

services.AddHostedService<GrantCleanupService>();

3. Index Optimization #

To make cleanup faster, ensure your PersistedGrants table has an index on the Expiration column. If you used IdentityServer's EF migrations, this index should already exist—but if you created the table manually, add it:

CREATE INDEX IX_PersistedGrants_Expiration ON PersistedGrants (Expiration);

内容的提问来源于stack exchange，提问作者Gutek