Enforcing unique constraints in event-sourced systems is tricky because querying a read model before appending an event creates a race condition. In Marten (a .NET event store built on PostgreSQL), you can solve this by using inline projections that run in the same database transaction as the event append. By defining a lightweight read model (e.g., a UserNameGuard) with a unique index on the relevant field, PostgreSQL will reject the transaction if a duplicate is detected. The approach also supports partial indexes for conditional uniqueness, such as allowing only one active cinema seat reservation. The author notes this is a pragmatic tradeoff rather than a pure event-sourcing best practice.
