Outbox Pattern in Postgres
Outbox Pattern in Postgres The outbox pattern handles this by saving the message in a separate table, in the same database transaction as the order. Either both are saved or neither is. This is the Atomicity in ACID. SQL: CREATE TABLE outbox_messages ( id UUID PRIMARY KEY , type VARCHAR ( 255 ) NOT NULL , content JSONB NOT NULL , occurred_on_utc TIMESTAMP WITH TIME ZONE NOT NULL , processed_on_utc TIMESTAMP WITH TIME ZONE NULL , error TEXT NULL ) ; -- We can consider adding this index since we will be querying for unprocessed messages often -- and it will contain the rows in the correct sort order for our query. CREATE INDEX IF NOT EXISTS idx_outbox_messages_unprocessed ON outbox_messages ( occurred_on_utc , processed_on_utc ) INCLUDE ( id , type , content ) WHERE processed_on_utc IS NULL ; C# EF model: public sealed class OutboxMessage { public Guid Id { get ; init ; } publi...