PostgreSQL
Use the EventFlow.PostgreSql
integration when you want EventFlow to persist
events, snapshots, and read models in PostgreSQL. The package wraps the Npgsql
driver and DbUp migrations, giving you consistent configuration, retries, and
schema provisioning across the stack.
Prerequisites
- A .NET application already wired with
EventFlow
. - PostgreSQL 12 or later. The bundled scripts rely on
GENERATED ... AS IDENTITY
columns and user-defined types. - Credentials that can execute
CREATE TABLE
,CREATE TYPE
, andCREATE INDEX
statements in the target database. - Network access for every service that emits commands or processes read models.
Install the NuGet package
Add the PostgreSQL integration to every project that configures EventFlow.
1 |
|
Configure EventFlow
Call ConfigurePostgreSql
once to register the shared connection, migrator, and
transient retry strategy, then opt into the specific stores you need.
1 2 3 4 5 6 7 8 9 10 11 12 13 |
|
ConfigurePostgreSql
wires up IPostgreSqlConnection
, the DbUp-based
IPostgreSqlDatabaseMigrator
, and the PostgreSqlErrorRetryStrategy
used by
the event store and read models.
Optional tuning
- Call
SetConnectionString("read-models", ...)
when you want read models to connect to a different database or replica. - Adjust
SetTransientRetryCount
/SetTransientRetryDelay
to tune retries for deadlocks (SqlState 40P01
) and active-transaction conflicts (SqlState 25001
). - Increase
SetUpgradeExecutionTimeout
when migration batches take longer than five minutes.
Event store
Enable the PostgreSQL event store
Replace the in-memory default by calling UsePostgreSqlEventStore()
after
ConfigurePostgreSql
.
1 2 3 |
|
Provision the schema
Run the embedded scripts once per environment to create the EventFlow
table,
the (AggregateId, AggregateSequenceNumber)
unique index, and the
eventdatamodel_list_type
composite type used for batch inserts.
1 2 3 |
|
The migrator is idempotent—rerunning it simply ensures the schema is present.
Lack of CREATE TYPE
or CREATE TABLE
permissions causes install-time failures.
Operational notes
PostgreSqlEventPersistence
surfaces duplicate key violations (SqlState 23505
) asOptimisticConcurrencyException
; investigate aggregate concurrency if you see these at runtime.- Event batches are appended inside a transaction. Monitor WAL growth and plan for appropriate autovacuum settings.
- The built-in retry strategy only retries deadlocks and active-transaction errors; unexpected exceptions bubble immediately.
Snapshot store
Enable PostgreSQL snapshots with .UsePostgreSqlSnapshotStore()
and run the
companion migration to create the EventFlowSnapshots
table.
1 2 3 4 5 |
|
Snapshots share a single table keyed by (AggregateName, AggregateId)
and store
the serialized data plus metadata needed for upgrades. Duplicate writes are
ignored when a snapshot with the same sequence number already exists.
Read model store
Register the store
UsePostgreSqlReadModel<T>
(or the locator overload) plugs the SQL read-store
implementation into EventFlow.
1 2 3 4 |
|
Implement the read model
PostgreSQL read models should implement IReadModel
and either derive from
PostgreSqlReadModel
or decorate key properties with the provided attributes.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
|
The base class marks AggregateId
with [PostgreSqlReadModelIdentityColumn]
and
LastAggregateSequenceNumber
with [PostgreSqlReadModelVersionColumn]
. Use
[PostgreSqlReadModelIgnoreColumn]
to skip properties that are not persisted.
Create the table
EventFlow does not auto-create read model tables. Deploy DDL that matches your
read model shape—by convention the table name is ReadModel-[TypeName]
.
1 2 3 4 5 6 7 8 9 10 11 12 |
|
At a minimum, keep the identity column, the optimistic concurrency column, and the fields mined by your query handlers. Add additional indexes to match your query patterns.
Run read model migrations
Package the DDL alongside your application and execute it with the shared
IPostgreSqlDatabaseMigrator
.
1 2 3 4 5 |
|
The tests in Source/EventFlow.PostgreSql.Tests
demonstrate this pattern: embed
versioned SQL files and invoke the migrator during startup or deployment.
Local development quickstart
Run a disposable PostgreSQL container and point ConfigurePostgreSql
to it.
1 2 3 4 |
|
Troubleshooting
SqlState 23505
(duplicate key) – the unique index on(AggregateId, AggregateSequenceNumber)
rejected a reinsert. Inspect aggregate concurrency or idempotency guards.eventdatamodel_list_type
does not exist – rerunEventFlowEventStoresPostgreSql.MigrateDatabaseAsync
; the composite type is required for batch inserts.- Missing read model rows – confirm the table exists, the identity column is
marked with
[PostgreSqlReadModelIdentityColumn]
, and the process has write access; otherwise updates are ignored. - Permission errors during migration – grant
CREATE TABLE
,CREATE TYPE
, andCREATE INDEX
to the login executing the migrator.