Rijmwoordenboek: Serving Pages Under 15ms with Better Caching

-- Every page load was hitting the database
SELECT word, phonetic_code, frequency, definition
FROM words 
WHERE phonetic_code LIKE 'KAT%'
ORDER BY frequency DESC
LIMIT 20;
-- Execution time: 85ms per page load

// Application-level caching implementation
public async Task<List<Word>> GetWordsByPhoneticCode(string phoneticCode)
{
    var cacheKey = $"words:phonetic:{phoneticCode}";
    
    // Try to get from cache first
    var cachedWords = await _redis.GetAsync<List<Word>>(cacheKey);
    if (cachedWords != null)
    {
        return cachedWords; // Return cached result immediately
    }
    
    // If not in cache, query database
    var words = await _database.QueryAsync<Word>(
        "SELECT word, phonetic_code, frequency FROM words WHERE phonetic_code LIKE @code ORDER BY frequency DESC LIMIT 20",
        new { code = phoneticCode + "%" }
    );
    
    // Cache the result for 5 minutes
    await _redis.SetAsync(cacheKey, words, TimeSpan.FromMinutes(5));
    
    return words;
}

-- Create materialized view for common phonetic patterns
CREATE MATERIALIZED VIEW words_common_patterns AS
SELECT phonetic_code, 
       array_agg(word ORDER BY frequency DESC) as words,
       array_agg(frequency ORDER BY frequency DESC) as frequencies
FROM words 
WHERE phonetic_code IN (
    SELECT phonetic_code 
    FROM words 
    GROUP BY phonetic_code 
    HAVING count(*) > 10
)
GROUP BY phonetic_code;

-- Refresh materialized view every hour
CREATE OR REPLACE FUNCTION refresh_words_patterns()
RETURNS void AS $$
BEGIN
    REFRESH MATERIALIZED VIEW CONCURRENTLY words_common_patterns;
END;
$$ LANGUAGE plpgsql;

-- Schedule refresh every hour
SELECT cron.schedule('refresh-words-patterns', '0 * * * *', 'SELECT refresh_words_patterns();');

// HTTP response caching implementation
[HttpGet("phonetic/{phoneticCode}")]
[ResponseCache(Duration = 300, Location = ResponseCacheLocation.Any)]
public async Task<IActionResult> GetWordsByPhonetic(string phoneticCode)
{
    var words = await GetWordsByPhoneticCode(phoneticCode);
    return Ok(words);
}

// Problem: Cache not invalidated when data changes
public async Task UpdateWordFrequency(int wordId, int newFrequency)
{
    await _database.ExecuteAsync(
        "UPDATE words SET frequency = @frequency WHERE id = @id",
        new { frequency = newFrequency, id = wordId }
    );
    // Cache not invalidated - users see stale data!
}

// Smart cache invalidation implementation
public async Task UpdateWordFrequency(int wordId, int newFrequency)
{
    // Get the word to find its phonetic code
    var word = await _database.QueryFirstAsync<Word>(
        "SELECT phonetic_code FROM words WHERE id = @id", 
        new { id = wordId }
    );
    
    // Update the database
    await _database.ExecuteAsync(
        "UPDATE words SET frequency = @frequency WHERE id = @id",
        new { frequency = newFrequency, id = wordId }
    );
    
    // Invalidate related caches
    var cacheKey = $"words:phonetic:{word.PhoneticCode}";
    await _redis.RemoveAsync(cacheKey);
    
    // Also invalidate pattern cache if this affects common patterns
    if (newFrequency > 1000)
    {
        await _redis.RemoveAsync("words:common_patterns");
    }
}

// Problem: Cache misses during peak usage
public async Task<List<Word>> GetWordsByPhoneticCode(string phoneticCode)
{
    var cacheKey = $"words:phonetic:{phoneticCode}";
    var cachedWords = await _redis.GetAsync<List<Word>>(cacheKey);
    
    if (cachedWords == null)
    {
        // Cache miss - slow database query during peak usage
        var words = await _database.QueryAsync<Word>(...);
        await _redis.SetAsync(cacheKey, words, TimeSpan.FromMinutes(5));
        return words;
    }
    
    return cachedWords;
}

// Background cache warming implementation
public class CacheWarmingService : BackgroundService
{
    protected override async Task ExecuteAsync(CancellationToken stoppingToken)
    {
        while (!stoppingToken.IsCancellationRequested)
        {
            // Get most popular phonetic codes
            var popularCodes = await _database.QueryAsync<string>(
                "SELECT phonetic_code FROM words GROUP BY phonetic_code ORDER BY count(*) DESC LIMIT 100"
            );
            
            // Pre-warm cache for popular codes
            foreach (var code in popularCodes)
            {
                var cacheKey = $"words:phonetic:{code}";
                var exists = await _redis.ExistsAsync(cacheKey);
                
                if (!exists)
                {
                    var words = await _database.QueryAsync<Word>(
                        "SELECT word, phonetic_code, frequency FROM words WHERE phonetic_code LIKE @code ORDER BY frequency DESC LIMIT 20",
                        new { code = code + "%" }
                    );
                    
                    await _redis.SetAsync(cacheKey, words, TimeSpan.FromMinutes(5));
                }
            }
            
            // Wait 10 minutes before next warming cycle
            await Task.Delay(TimeSpan.FromMinutes(10), stoppingToken);
        }
    }
}