Akka.Persistence.Sql.Hosting 1.5.73

Akka.Persistence.Sql

A Cross-SQL-DB Engine Akka.Persistence plugin with broad database compatibility thanks to Linq2Db.

This is a port of the amazing akka-persistence-jdbc package from Scala, with a few improvements based on C# as well as our choice of data library.

Please read the documentation carefully. Some features may have specific use case and have trade-offs (namely, compatibility modes).

If you're migrating from legacy Akka.Persistence.Sql.Common based plugins, you can read the migration guide documentation, the migration tutorial, and watch the migration tutorial video.

Table Of Content

• Akka.Persistence.Sql
• Getting Started
  • The Easy Way, Using Akka.Hosting
    • Health Checks
  • The Classic Way, Using HOCON
  • Supported Database Providers
    • Tested Database Providers
    • Supported By Linq2Db But Untested In Akka.Persistence
• Sql.Common Compatibility modes
• Migration Guide
  • Migrating Using Compatibility Mode
    • Akka.Hosting Migration
    • HOCON Migration
  • Upgrading to Tag Table (Optional)
  • Enable WriterUuid Anti-Corruption Layer Feature (Recommended)
• Migration Tutorial
• Features/Architecture
  • Currently Implemented
  • Incomplete
• Performance Benchmarks
• Configuration
  • Journal
  • Snapshot Store
• DDL Scripts for Database Schema Management
• Database-Side Journal Retention
  • Optional Tag-Table Foreign Keys
• Tag-Table Insert Batching
• Tuning Akka.Persistence.Query for Low-Latency Projections
• Building this solution
  • Conventions
  • Release Notes, Version Numbers, Etc

Getting Started

The Easy Way, Using Akka.Hosting

Assuming a MS SQL Server 2019 setup:

var host = new HostBuilder()
    .ConfigureServices((context, services) => {
        services.AddAkka("my-system-name", (builder, provider) =>
        {
            builder.WithSqlPersistence(
                connectionString: _myConnectionString,
                providerName: ProviderName.SqlServer2019);
        });
    });

You can also provide your own LinqToDb.DataOptions object for a more advanced configuration. Assuming a setup with a custom NpgsqlDataSource:

NpgsqlDataSource dataSource = new NpgsqlDataSourceBuilder(_myConnectionString).Build();

DataOptions dataOptions = new DataOptions()
    .UseDataProvider(DataConnection.GetDataProvider(ProviderName.PostgreSQL, dataSource.ConnectionString) ?? throw new Exception("Could not get data provider"))
    .UseProvider(ProviderName.PostgreSQL)
    .UseConnectionFactory((opt) => dataSource.CreateConnection());
    
var host = new HostBuilder()
    .ConfigureServices((context, services) => {
        services.AddAkka("my-system-name", (builder, provider) =>
        {
            builder.WithSqlPersistence(dataOptions);
        });
    });

If dataOptions are provided, you must supply enough information for linq2db to connect to the database. This includes setting the connection string and provider name again, if necessary for your use case. Please consult the Linq2Db documentation for more details on configuring a valid DataOptions object. Note that MappingSchema and RetryPolicy will always be overridden by Akka.Persistence.Sql.

Health Checks

Starting with Akka.Persistence.Sql v1.5.51 or later, you can add health checks for your persistence plugins. Akka.Persistence.Sql supports two types of health checks that integrate with Microsoft.Extensions.Diagnostics.HealthChecks and can be used with ASP.NET Core health check endpoints.

Standard Health Checks

Standard health checks monitor the persistence plugins themselves and verify that journals and snapshot stores are properly initialized and accessible.

To configure standard health checks, use the journalBuilder and snapshotBuilder parameters with the .WithHealthCheck() method:

var host = new HostBuilder()
    .ConfigureServices((context, services) => {
        services.AddAkka("my-system-name", (builder, provider) =>
        {
            builder.WithSqlPersistence(
                connectionString: _myConnectionString,
                providerName: ProviderName.SqlServer2019,
                journalBuilder: journal => journal.WithHealthCheck(HealthStatus.Degraded),
                snapshotBuilder: snapshot => snapshot.WithHealthCheck(HealthStatus.Degraded));
        });
    });

Standard health checks are tagged with akka, persistence, and either journal or snapshot-store for filtering and organization purposes.

Connectivity Health Checks (Akka.Persistence.Sql.Hosting v1.5.X+)

Connectivity health checks proactively verify database connectivity regardless of recent operation activity. This helps detect database outages during idle periods when the standard health checks might not catch issues.

Using Akka.Hosting 1.5.55.1 or later (Simplified API):

services.AddAkka("my-system-name", (builder, provider) =>
{
    builder.WithSqlPersistence(
        connectionString: connectionString,
        providerName: ProviderName.SqlServer2022,
        journalBuilder: journal =>
        {
            journal.WithHealthCheck();           // Monitor plugin status
            journal.WithConnectivityCheck();     // Verify database connectivity
        },
        snapshotBuilder: snapshot =>
        {
            snapshot.WithHealthCheck();          // Monitor plugin status
            snapshot.WithConnectivityCheck();    // Verify database connectivity
        });
});

The connectivity checks will automatically:

• Verify connectivity to the underlying SQL database
• Test database responsiveness with a lightweight "SELECT 1" query
• Report Healthy when the database is accessible
• Report Unhealthy (configurable) when the database is unreachable or unresponsive

Connectivity checks are tagged with akka, persistence, sql, and either journal or snapshot-store for filtering and organization purposes.

Exposing Health Checks via ASP.NET Core

For ASP.NET Core applications, you can expose these health checks via an endpoint:

var builder = WebApplication.CreateBuilder(args);

// Add health checks service
builder.Services.AddHealthChecks();

builder.Services.AddAkka("my-system-name", (configBuilder, provider) =>
{
    configBuilder.WithSqlPersistence(
        connectionString: _myConnectionString,
        providerName: ProviderName.SqlServer2019,
        journalBuilder: journal => journal.WithHealthCheck(),
        snapshotBuilder: snapshot => snapshot.WithHealthCheck());
});

var app = builder.Build();

// Map health check endpoint
app.MapHealthChecks("/healthz");

app.Run();

Customizing Health Check Configuration

You can customize the names, tags, and unhealthy status for both standard and connectivity health checks:

services.AddAkka("my-system-name", (builder, provider) =>
{
    builder.WithSqlPersistence(
        connectionString: connectionString,
        providerName: ProviderName.SqlServer2022,
        journalBuilder: journal =>
        {
            // Customize standard health check
            journal.WithHealthCheck(
                unHealthyStatus: HealthStatus.Degraded,
                name: "sql-journal",
                tags: new[] { "backend", "database", "sql" });

            // Customize connectivity health check
            journal.WithConnectivityCheck(
                unHealthyStatus: HealthStatus.Unhealthy,
                name: "sql-journal-connectivity",
                tags: new[] { "backend", "database", "sql", "connectivity" });
        },
        snapshotBuilder: snapshot =>
        {
            // Customize standard health check
            snapshot.WithHealthCheck(
                unHealthyStatus: HealthStatus.Degraded,
                name: "sql-snapshot",
                tags: new[] { "backend", "database", "sql" });

            // Customize connectivity health check
            snapshot.WithConnectivityCheck(
                unHealthyStatus: HealthStatus.Unhealthy,
                name: "sql-snapshot-connectivity",
                tags: new[] { "backend", "database", "sql", "connectivity" });
        });
});

Default tags when not specified:

• Standard health checks: ["akka", "persistence", "journal"] or ["akka", "persistence", "snapshot-store"]
• Connectivity health checks: ["akka", "persistence", "sql", "journal", "connectivity"] or ["akka", "persistence", "sql", "snapshot-store", "connectivity"]

The Classic Way, Using HOCON

These are the minimum HOCON configuration you need to start using Akka.Persistence.Sql:

akka.persistence {
    journal {
        plugin = "akka.persistence.journal.sql"
        sql {
            class = "Akka.Persistence.Sql.Journal.SqlWriteJournal, Akka.Persistence.Sql"
            connection-string = "{database-connection-string}"
            provider-name = "{provider-name}"
        }
    }
    query.journal.sql {
        class = "Akka.Persistence.Sql.Query.SqlReadJournalProvider, Akka.Persistence.Sql"
        connection-string = "{database-connection-string}"
        provider-name = "{provider-name}"
    }
    snapshot-store {
        plugin = "akka.persistence.snapshot-store.sql"
        sql {
            class = "Akka.Persistence.Sql.Snapshot.SqlSnapshotStore, Akka.Persistence.Sql"
            connection-string = "{database-connection-string}"
            provider-name = "{provider-name}"
        }
    }
}

• database-connection-string: The proper connection string to your database of choice.
• provider-name: A string constant defining the database type to connect to, valid values are defined inside LinqToDB.ProviderName static class. Refer to the Members of LinqToDb.ProviderName for included providers.

Note: For best performance, one should use the most specific provider name possible. i.e. LinqToDB.ProviderName.SqlServer2012 instead of LinqToDB.ProviderName.SqlServer. Otherwise certain provider detections have to run more frequently which may impair performance slightly.

Supported Database Providers

Tested Database Providers

• Microsoft SQL Server
• MS SQLite
• System.Data.SQLite
• PostgreSQL using binary payload
• MySql

Supported By Linq2Db But Untested In Akka.Persistence

• Firebird
• Microsoft Access OleDB
• Microsoft Access ODBC
• IBM DB2
• Informix
• Oracle
• Sybase
• SAP HANA
• ClickHouse

Sql.Common Compatibility modes

• Delete Compatibility mode is available.
  • This mode will utilize a journal_metadata table containing the last sequence number
  • The main table delete is done the same way regardless of delete compatibility mode

Delete Compatibility mode is expensive.

• Normal Deletes involve first marking the deleted records as deleted, and then deleting them
  • Table compatibility mode adds an additional InsertOrUpdate and Delete
• This all happens in a transaction
  • In SQL Server this can cause issues because of page locks/etc.

DDL Scripts for Database Schema Management

Pre-generated DDL scripts are available in docs/ddl/ for creating Akka.Persistence.Sql tables through your own migrations instead of relying on automatic table creation at runtime.

Directory Structure:

• docs/ddl/default/ - For new deployments using table-mapping = default
• docs/ddl/compat/ - For migration from legacy plugins (uses provider-specific table mappings)

The initialization SQL files are checked into this repository:

Each directory contains SQL scripts for each supported provider (sqlserver, postgresql, mysql, sqlite):

• journal.sql - Event journal table
• journal-tags.sql - Tag table for TagMode.TagTable
• snapshot.sql - Snapshot store table
• metadata.sql - Metadata table for delete-compatibility-mode

When to use DDL scripts:

• When using Entity Framework Core or FluentMigrator for schema management
• When you need explicit control over database schema versioning
• When auto-initialization is disabled (auto-initialize = false)

See the DDL documentation for detailed usage instructions and examples.

Database-Side Journal Retention

Akka.Persistence's DeleteMessagesTo deletes an entity's events and their tag rows together. If you purge old events with your own SQL instead, delete their tag rows too, or the tags table grows forever.

See the journal retention guide for the rules and example scripts for PostgreSQL, SQL Server, MySQL, and SQLite.

Optional Tag-Table Foreign Keys

The journal-to-tag foreign key (tags.ordering_id -> journal.ordering ON DELETE CASCADE) is an optional, operator-applied migration for PostgreSQL and SQL Server. Runtime auto-initialization and the base DDL scripts create tables without it. With the FK installed, deleting a journal row also removes its tag rows. Existing databases must clean up orphaned tags with 01 before installing it.

The FK adds overhead to tagged writes and especially to deletes. Review the measured FK overhead and the maintenance guide before opting in.

Tag-Table Insert Batching

SQL Server, PostgreSQL, and SQLite can batch journal inserts using INSERT ... OUTPUT/RETURNING when tag-write-mode = TagTable. Parent batching is off by default. To enable parameterized parent inserts and parameterized tag SQL:

akka.persistence.journal.sql {
    tag-write-mode = TagTable
    tagtable-asqueryable-insert-mode = parameterized
    prefer-parameters-on-multirow-insert = true
}

The parent mode accepts off, parameterized, or inline. Inline mode embeds scalar values in SQL while keeping persistence IDs, messages, manifests, and writer UUIDs parameterized. Other providers and tag modes retain the standard insert path. Batching does not install an FK or change transaction boundaries.

This backport upgrades LinqToDB to 6.5.1 for all users, including those with batching off. The plugin retains its .NET Standard 2.0/.NET 6 targets and Akka 1.5 dependencies, but advanced LinqToDB integrations need an upgrade review:

• Custom LinqToDB 5 IDataProvider implementations must be adapted as needed and recompiled against LinqToDB 6; this is not a transparent binary upgrade for those extensions.
• Register interceptors through DataOptions.UseInterceptor/UseInterceptors. The replacement connection clone preserves those registrations, but does not snapshot AddInterceptor/RemoveInterceptor mutations made directly on a DataConnection after construction.

Tuning Akka.Persistence.Query for Low-Latency Projections

Default polling intervals cause 500-1200ms projection latency. For low-latency CQRS, tune BOTH settings together:

akka.persistence.query.journal.sql {
    refresh-interval = 10ms          # Query stream polling
    max-buffer-size = 50
    journal-sequence-retrieval {
        query-delay = 10ms           # Sequence actor polling - CRITICAL!
    }
}

Important: Tuning only refresh-interval does NOT work. The sequence actor polls independently via query-delay.

Performance Guide

Trade-off: Lower = faster but higher DB load. In clusters, each node polls independently.

See persistence.conf for detailed settings documentation.

Building this solution

To run the build script associated with this solution, execute the following:

Windows

c:\> build.cmd all

Linux / OS X

c:\> build.sh all

If you need any information on the supported commands, please execute the build.[cmd|sh] help command.

This build script is powered by FAKE; please see their API documentation should you need to make any changes to the build.fsx file.

Conventions

The attached build script will automatically do the following based on the conventions of the project names added to this project:

• Any project name ending with .Tests will automatically be treated as a XUnit2 project and will be included during the test stages of this build script;
• Any project name ending with .Tests will automatically be treated as a NBench project and will be included during the test stages of this build script; and
• Any project meeting neither of these conventions will be treated as a NuGet packaging target and its .nupkg file will automatically be placed in the bin\nuget folder upon running the build.[cmd|sh] all command.

Release Notes, Version Numbers, Etc

This project will automatically populate its release notes in all of its modules via the entries written inside RELEASE_NOTES.md and will automatically update the versions of all assemblies and NuGet packages via the metadata included inside Directory.Build.props.