Feature Request: Message Template Normalization for Database Storage Optimization

[na-pel]

Summary

Add an optional feature to normalize message templates by storing them in a separate lookup table and referencing them by hash in the main logs table, similar to how Seq handles event types. This would dramatically reduce database storage requirements and improve query performance for applications with high-volume logging.

Problem Statement

Currently, serilog-sinks-mssqlserver stores the full MessageTemplate string in every log record. This approach creates significant inefficiencies:

Storage Duplication Issue

-- Current approach - same template repeated thousands of times
INSERT INTO Logs (MessageTemplate, Message, ...) VALUES 
('User {UserId} logged in from {IPAddress}', 'User 12345 logged in from 192.168.1.1', ...),
('User {UserId} logged in from {IPAddress}', 'User 67890 logged in from 10.0.0.1', ...),
('User {UserId} logged in from {IPAddress}', 'User 11111 logged in from 172.16.0.1', ...);
-- Template "User {UserId} logged in from {IPAddress}" is stored 3 times (and potentially thousands more)

Real-World Impact

• Storage waste: A typical template like "Processing {ItemCount} items for user {UserId} in {TimeElapsed}ms" (65 characters) repeated 10,000 times wastes 650,000 bytes (~635 KB)
• With normalization: Same scenario uses 10,000 × 8 bytes (hashes) + 1 × 65 bytes (template) = 80,065 bytes (~78 KB)
• Storage savings: 88% reduction (from ~635 KB to ~78 KB) for this single template
• Index inefficiency: Large MessageTemplate columns hurt index performance and increase storage overhead
• Query complexity: Finding all events of a specific type requires expensive string comparisons
• Backup/restore overhead: Redundant data increases backup sizes and restore times

Proposed Solution

Implement message template normalization with a two-table approach:

Database Schema Changes

New table: MessageTemplates

CREATE TABLE [MessageTemplates] (
    [TemplateHash] CHAR(8) NOT NULL PRIMARY KEY,     -- 32-bit hash (hex)
    [Template] NVARCHAR(MAX) NOT NULL                -- Original template
);

Modified main table: Logs

CREATE TABLE [Logs] (
    [Id] INT IDENTITY(1,1) NOT NULL,
    [TemplateHash] CHAR(8) NULL,                     -- Reference to MessageTemplates
    [MessageTemplate] NVARCHAR(MAX) NULL,            -- Legacy column (optional)
    [Message] NVARCHAR(MAX) NULL,
    [Level] NVARCHAR(MAX) NULL,
    [TimeStamp] DATETIME NULL,
    [Exception] NVARCHAR(MAX) NULL,
    [Properties] NVARCHAR(MAX) NULL,
    CONSTRAINT [PK_Logs] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_Logs_MessageTemplates] FOREIGN KEY ([TemplateHash]) 
        REFERENCES [MessageTemplates]([TemplateHash])
);

Configuration Options

Add new configuration properties to enable/control the feature:

var columnOptions = new ColumnOptions();
columnOptions.MessageTemplate.UseNormalization = false;          // Default: disabled for backward compatibility
columnOptions.MessageTemplate.HashAlgorithm = HashAlgorithm.Murmur3; // Hash algorithm (when enabled)
columnOptions.MessageTemplate.KeepLegacyColumn = true;           // Keep original MessageTemplate column (dual-write mode)
columnOptions.MessageTemplate.AutoCreateTemplateTable = true;    // Create lookup table automatically

Configuration Modes:

Mode 1: Legacy (Default) - Full Backward Compatibility

UseNormalization = false;  // Feature disabled, works exactly as today
// KeepLegacyColumn not relevant

Mode 2: Dual-Write - Safe Migration

UseNormalization = true;   // Feature enabled
KeepLegacyColumn = true;   // Write to BOTH TemplateHash AND MessageTemplate columns

Mode 3: Hash-Only - Maximum Optimization

UseNormalization = true;   // Feature enabled  
KeepLegacyColumn = false;  // Write ONLY to TemplateHash column

JSON configuration:

{
  "columnOptionsSection": {
    "messageTemplate": {
      "useNormalization": false,
      "hashAlgorithm": "Murmur3",
      "keepLegacyColumn": true,
      "autoCreateTemplateTable": true
    }
  }
}

Performance Considerations

SqlBulkCopy Compatibility

The implementation is designed to preserve the high-performance characteristics of serilog-sinks-mssqlserver:

• In-memory template cache: Templates are resolved to hashes without database calls during normal operation
• Asynchronous template insertion: New templates are inserted in background without blocking the main logging pipeline
• Bulk copy preservation: The main logs table continues using SqlBulkCopy for maximum throughput
• Minimal overhead: Hash calculation and dictionary lookup add negligible latency

Cache Management

// Configuration options for cache behavior
columnOptions.MessageTemplate.CacheSize = 10000;        // Max templates in memory
columnOptions.MessageTemplate.PreloadTemplates = true;  // Load existing on startup
columnOptions.MessageTemplate.CacheTimeout = TimeSpan.FromHours(1); // Optional expiry

Concurrency Handling

• Uses ConcurrentDictionary for thread-safe template caching
• Handles concurrent template insertion with IF NOT EXISTS
• Gracefully handles primary key violations from race conditions
• No locks or synchronization points in the hot logging path

Implementation Details

Hash Algorithm

Use 32-bit Murmur3 hash (same as Seq) for consistency and proven collision resistance:

• Converts templates like "User {UserId} logged in" → A26D9943
• Stores as 8-character hex string for readability and SQL compatibility
• Collision probability is negligible for typical logging scenarios

Write Process

Performance-Optimized Implementation:

1. Initialization Phase

   // Load existing templates into memory cache on sink startup
   private readonly ConcurrentDictionary<string, string> _templateCache = new();
   
   private async Task LoadExistingTemplatesAsync()
   {
       var templates = await connection.QueryAsync<(string Hash, string Template)>(
           "SELECT TOP 10000 TemplateHash, Template FROM MessageTemplates");
       
       foreach (var (hash, template) in templates)
       {
           _templateCache.TryAdd(template, hash);
       }
   }

2. Fast Template Resolution

   private string GetOrCreateTemplateHash(string template)
   {
       // Fast in-memory lookup first
       if (_templateCache.TryGetValue(template, out string existingHash))
           return existingHash;
           
       // Calculate hash for new template
       string newHash = ComputeHash(template);
       
       // Try to add to cache (handles concurrent access)
       if (_templateCache.TryAdd(template, newHash))
       {
           // This thread "won" - responsible for DB insert
           _ = Task.Run(() => InsertTemplateAsync(newHash, template));
       }
       
       return newHash;
   }

3. SQL Bulk Copy Compatibility

   private async Task InsertTemplateAsync(string hash, string template)
   {
       try
       {
           // Use MERGE or IF NOT EXISTS to handle concurrent inserts
           await connection.ExecuteAsync(@"
               IF NOT EXISTS (SELECT 1 FROM MessageTemplates WHERE TemplateHash = @Hash)
               INSERT INTO MessageTemplates (TemplateHash, Template) 
               VALUES (@Hash, @Template)",
               new { Hash = hash, Template = template });
       }
       catch (SqlException ex) when (ex.Number == 2627) // Primary key violation
       {
           // Another process inserted this template - ignore
       }
   }

4. Batch Processing

   • Templates are resolved to hashes in-memory before bulk copy
   • New templates are inserted asynchronously (fire-and-forget)
   • SqlBulkCopy performance is preserved for the main logs table
   • No synchronous DB calls during high-throughput logging

Read Process

When legacy MessageTemplate column is removed:

-- Query with template resolution
SELECT l.*, mt.Template as MessageTemplate 
FROM Logs l 
LEFT JOIN MessageTemplates mt ON l.TemplateHash = mt.TemplateHash 
WHERE l.TimeStamp > '2024-01-01';

-- Query for specific event types (much faster than string comparison)
SELECT * FROM Logs WHERE TemplateHash = 'A26D9943';

Backward Compatibility Strategy

Default Behavior: Zero Breaking Changes

• UseNormalization = false by default
• Existing installations continue working exactly as before
• No schema changes required unless explicitly enabled

Migration Path:

Step 1: Enable with dual-write (Recommended)

UseNormalization = true;
KeepLegacyColumn = true;  // Write to both TemplateHash AND MessageTemplate

• Creates MessageTemplates table alongside existing schema
• All log records written to both old and new columns
• Existing queries continue working unchanged
• Allows testing and validation of the new feature

Step 2: Optimize storage (Optional)

UseNormalization = true;
KeepLegacyColumn = false;  // Write ONLY to TemplateHash

• Maximum storage savings
• Requires updating queries to use JOIN or accepting hash-only queries
• Can be rolled back by switching KeepLegacyColumn = true

Migration Support

• Provide migration script to populate MessageTemplates from existing data
• Support for analyzing existing templates and their frequency
• Clear documentation for each migration step

Benefits

Storage Optimization

• Dramatic space savings: A template used 10,000 times goes from ~635 KB to ~78 KB (88% reduction)
• Improved index performance: CHAR(8) indexes are much faster than NVARCHAR(MAX)
• Reduced backup sizes: Especially significant for high-volume logging applications

Query Performance

-- Before: Expensive string comparison
SELECT * FROM Logs WHERE MessageTemplate = 'User {UserId} logged in from {IPAddress}';

-- After: Fast hash lookup  
SELECT * FROM Logs WHERE TemplateHash = 'F20BA6E0';

-- Advanced analytics become practical
SELECT TemplateHash, COUNT(*) as EventCount 
FROM Logs 
GROUP BY TemplateHash 
ORDER BY EventCount DESC;

Operational Benefits

• Template discovery: Easily identify all unique templates in your application

  -- Find all templates and their usage frequency
  SELECT mt.Template, COUNT(l.Id) as UsageCount
  FROM MessageTemplates mt
  LEFT JOIN Logs l ON mt.TemplateHash = l.TemplateHash
  GROUP BY mt.Template, mt.TemplateHash
  ORDER BY UsageCount DESC;

• Usage analytics: Track template frequency for optimization
• Faster log analysis: Group and analyze events by type rather than parsing strings
• High-performance logging: Maintains SqlBulkCopy speed with in-memory template caching

Proven Approach

This approach is battle-tested and proven effective:

Seq Implementation

Seq (the popular Serilog log server) has used this exact approach since its inception:

• Seq does this automatically by assigning a type to Serilog events on the server-side
• Seq produces a 32-bit (Murmur) hash of the message template that can be referred to using hex literals
• Storing, and then filtering on a wordy message template isn't always ideal. Instead, it's common to create a numeric hash value from the message template, and store this with the event instead

Community Recognition

• Nicholas Blumhardt (Serilog creator) has written extensively about this pattern
• Multiple blog posts demonstrate the value of event type hashing
• Widely adopted in the structured logging community

Potential Concerns & Mitigations

Hash Collisions

• Risk: Extremely low with Murmur3 32-bit hash
• Mitigation: Monitor for collisions and provide collision detection
• Fallback: Could extend to 64-bit hash if needed in future

Performance Impact

• Concern: Additional overhead affecting SqlBulkCopy performance
• Mitigation: In-memory template caching eliminates database calls during normal operation
• Benefit: Hash lookups (8 chars) are much faster than string comparisons (potentially 100+ chars)

Memory Usage

• Concern: Template cache consuming memory
• Mitigation: Configurable cache size with reasonable defaults (10K templates ≈ 1-2MB)
• Benefit: Tiny overhead compared to storage savings achieved

Migration Complexity

• Concern: Existing installations need migration path
• Mitigation: Dual-write mode provides smooth transition
• Benefit: Opt-in feature, no forced migration

Conclusion

Message template normalization addresses a fundamental inefficiency in current database logging approaches. By following Seq's proven model, we can:

• Reduce storage requirements by 80-95% for individual templates (depends on template length and frequency)
• Improve query performance significantly
• Enable advanced log analytics that aren't practical with current approach
• Maintain full backward compatibility during transition

This feature would make serilog-sinks-mssqlserver more suitable for production environments with high-volume logging, while providing a clear migration path for existing users.

The implementation follows established patterns, has proven benefits, and addresses real pain points experienced by users managing large log databases. I believe this would be a valuable addition to the sink that would benefit the entire Serilog community.

---

Would the maintainers be interested in this feature? I'm happy to contribute to the implementation if there's community interest.

[jonorossi]

Sounds like a great feature for those that log a lot. I probably wouldn't use it personally on the product I'm working on because we just don't log enough of this type of data to make much difference compared to other log tables.

Two concerns that I don't see listed, which I wonder if there are solutions for:

1. There is no clean up for old/unused templates or easily way to perform a clean up without scanning the log table.
2. Templates that accidentally get something dynamic will explode the amount of work and kill performance. It'll be pretty rare and obviously a bug, but we've had a few times when someone has used string interpolation rather than the Serilog structured property syntax accidentally.

[na-pel]

Great points @jonorossi! Both concerns are valid. Here's how I see them:

1. Template Cleanup

This follows the same pattern as existing log cleanup - it's a maintenance responsibility, not a Serilog / SqlServer sink one. You already need to clean up old logs, so just add one step:

-- Existing cleanup
DELETE FROM Logs WHERE TimeStamp < DATEADD(day, -90, GETDATE());

-- Add to clean orphaned templates  
DELETE FROM MessageTemplates 
WHERE TemplateHash NOT IN (SELECT DISTINCT TemplateHash FROM Logs);

The FK relationship actually protects you from accidentally deleting templates still in use.

2. Dynamic Template "Explosion"

You're right this is a bug regardless of this feature. But the impact is manageable:

• Templates can use SqlBulkCopy batching too
• If 50 logs/second all have unique templates: that's two 50 record bulk operations instead of one bulk
• Performance degrades but won't crash the system
• The original string interpolation bug already hurts performance anyway
• We can try to detect such situations and give an alert (via SelfLog) when template cache grows unusually fast:

columnOptions.MessageTemplate.WarnOnRapidGrowth = true;
columnOptions.MessageTemplate.NewTemplatesPerHour = 1000; // threshold

---

For lower-volume use case, you'd keep UseNormalization = false anyway. But for high-volume scenarios, these considerations seem reasonable given the storage benefits.

What do you think?

[jonorossi]

-- Add to clean orphaned templates
DELETE FROM MessageTemplates
WHERE TemplateHash NOT IN (SELECT DISTINCT TemplateHash FROM Logs);

That's why I wrote:

1. There is no clean up for old/unused templates or easily way to perform a clean up without scanning the log table.

With the proposed table structure the maintenance process still needs to scan the whole Logs table to perform clean up, whereas the Logs table is a clustered index delete on time.

• The original string interpolation bug already hurts performance anyway

How does it hurt performance when it's just inserting a string template value into Logs? Or did you mean searching on the template not logging performance?

[na-pel]

Scanning the log table - You are correct. But you don't need to scan the entire log table, just its index (either by SELECT DISTINCT or LEFT JOIN) which is indeed a bit less efficient than clustered index scan, but it is quite manageable.

String interpolation - It probably wasn't accurate to say it hurts performance "anyway". But it cannot be considered a good practice, it can hurt performance, and it can cause bugs. Anyway, one who uses interpolation will probably prefer not to use the suggested normalization, and one who prefers template normalization will probably want to be warned if they accidentally use interpolation.

[vlm---]

As an alternative approach, I suggest our solution. Since we believe that efficient log storage should be solely the responsibility of the database, we developed an enricher for the EventType (same murmur hash as in Seq) to correlate logs from Seq with the ones in the database. Log normalization is handled by an agent job that periodically executes a simple stored procedure.
The MessageTemplate column is cleared after storing its distinct values in a lookup table. A similar approach is used to normalize other low-cardinality string properties—such as IP addresses, hostnames, and usernames—by referencing them via integer-based foreign keys. Additionally, filtered indexes on FK/hash columns help prevent full table scans.

[na-pel]

@vlm--- That's an interesting post-processing approach that offers great flexibility and makes sense for existing systems where DB-level solutions are preferred.

Still, I see a few challenges:

1. The timing gap between writes and normalization means queries always have to check both the original and normalized data (WHERE log.ipAddress = X OR ips.address = X), which complicates things and hurts performance;
2. The double-write and cleanup process creates significant DB overhead (write full data, update indexes, then delete and re-index), plus potential fragmentation issues;
3. Sorting by normalized fields requires expensive cross-table operations instead of fast indexed sorts on a single table.

The real-time approach I’m proposing skips all that by normalizing during write (no timing gaps, single write operations, and simpler queries). Your idea about normalizing multiple properties is excellent, though. We could easily extend the suggested concept beyond MessageTemplate to other AdditionalColumns with repeating values as well

One more thing: a sink-level solution means you set it up once for everyone, while the DB approach needs every user to create and maintain their own stored procedures and routines.

[jonorossi]

There is no clean up for old/unused templates or easy way to perform a clean up without scanning the log table.

With the proposed table structure the maintenance process still needs to scan the whole Logs table to perform clean up, whereas the Logs table is a clustered index delete on time.

Scanning the log table - You are correct. But you don't need to scan the entire log table, just its index (either by SELECT DISTINCT or LEFT JOIN) which is indeed a bit less efficient than clustered index scan, but it is quite manageable.

@na-pel You've not mentioned any additional index on the Logs table, are you proposing a non-clustered index on TemplateHash? Updating that index will be additional work for every insert and will likely limit concurrency when trying to insert new rows while any sort of retention clean up is deleting rows.

[jonorossi]

we developed an enricher for the EventType (same murmur hash as in Seq) to correlate logs from Seq with the ones in the database. Log normalization is handled by an agent job that periodically executes a simple stored procedure.

@vlm--- Do you perform the normalisation on the same database table this sink writes to, or do you use that table as a scratch space until your normalisation job rewrites the log events into other tables?

[na-pel]

@jonorossi You're highlighting a real trade-off: either accept the cost of a TemplateHash index, or live with expensive template cleanup via table scans.
But the index is optional. SQL Server foreign keys don't create indexes automatically. You could implement normalization without any index, accepting slower template cleanup but still gaining significant storage savings. Adding the index becomes a separate performance decision based on the specific use case. This isn't fundamentally different from today's choices around indexing log columns. The normalization benefits (storage, simpler values) exist regardless of indexing decisions.

[vlm---]

@vlm--- That's an interesting post-processing approach that offers great flexibility and makes sense for existing systems where DB-level solutions are preferred.

Still, I see a few challenges:

1. The timing gap between writes and normalization means queries always have to check both the original and normalized data (WHERE log.ipAddress = X OR ips.address = X), which complicates things and hurts performance;
2. The double-write and cleanup process creates significant DB overhead (write full data, update indexes, then delete and re-index), plus potential fragmentation issues;
3. Sorting by normalized fields requires expensive cross-table operations instead of fast indexed sorts on a single table.

The real-time approach I’m proposing skips all that by normalizing during write (no timing gaps, single write operations, and simpler queries). Your idea about normalizing multiple properties is excellent, though. We could easily extend the suggested concept beyond MessageTemplate to other AdditionalColumns with repeating values as well

One more thing: a sink-level solution means you set it up once for everyone, while the DB approach needs every user to create and maintain their own stored procedures and routines.

@na-pel
Back when we started developing our solution, we considered forking and creating a built-in approach within this sink. However, due to the need to normalize more than one column, we opted for a SQL-based solution.

Regarding your first point — the timing gap — as mentioned earlier, we use the database primarily as long-term storage for analytical purposes, where a small degree of inaccuracy is acceptable. The maintenance job runs almost every minute, which helps to keep things consistent.

As for point two, the overhead is virtually negligible. These maintenance queries never appear among the most expensive or exhibit significant wait times.

And finally, on point three: sorting could be offloaded to the application or presentation layer in environments with CPU-starved SQL instances — although that doesn’t apply to our case.

To be clear, I’m not opposed to your proposal. I just wanted to share my experience dealing with a similar challenge. I can definitely see myself using your approach in future projects or even back-porting it to some older ones.

[vlm---]

we developed an enricher for the EventType (same murmur hash as in Seq) to correlate logs from Seq with the ones in the database. Log normalization is handled by an agent job that periodically executes a simple stored procedure.

@vlm--- Do you perform the normalisation on the same database table this sink writes to, or do you use that table as a scratch space until your normalisation job rewrites the log events into other tables?

@jonorossi
We maintain one table per source — such as clients, API servers, and Hangfire hosts — and one database per application. CPU, memory, and read/write performance are not bottlenecks in our setup, as the database only ingests logs at the Information level (typically under 5,000 per second). The most valuable resource by far is disk space, since we don’t delete older logs.