Choosing a database

Spring DDD runs on PostgreSQL, MySQL, MariaDB, SQL Server, H2 and HSQLDB. All six are correct: none loses or reorders a message, on one instance or on many. They differ in what waits for what while the application writes. Engine choice does not change application code: aggregates, projections and sagas are written the same way on every engine.

Comparison

PostgreSQL MySQL, MariaDB, SQL Server H2, HSQLDB

Event appends

Concurrent

One at a time

One at a time

Captured messages

Concurrent

One at a time

One at a time

Applies to one read-model topic

Concurrent

One at a time

One at a time

An ad-hoc call meets a message missing from the capture topic that retention did not delete

Not detected: the call answers from what it finds

Fails the call

Fails the call

Minimum version

None

MySQL 8.0.1, MariaDB 10.6

None

For

Production

Production

Tests and local development

What an ad-hoc call does when a message is missing is described under When history has been deleted.

What one at a time costs

On MySQL, MariaDB, SQL Server, H2 and HSQLDB, each write marked "one at a time" waits for the one before it, across every instance of the application:

Event appends

Every append, of any aggregate on any instance, waits for the one before it to end. The turn is taken when the events are appended and held until the transaction ends, so every other append also waits for whatever the transaction still does after appending.

Captured messages

Every transaction that publishes a message onto the capture topic, on any instance, waits for the one before it. The turn is taken only when the transaction commits, and held through the commit.

Applies to one read-model topic

Every apply of one projection that writes its read-model topic, on any instance, waits for the one before it. The turn is taken at the apply’s first read-model change and held until the apply commits. Other projections do not wait for it.

Because each append waits for the one before it, how long each append holds its turn caps how many appends the application can sustain, however many instances it runs.

On PostgreSQL none of these writes wait for each other.

To keep the waits short:

  • Append late in the transaction, and keep transactions that append or publish short. When a handler also updates other rows next to saving the BankAccount, update them before the save, not after.

  • Expect a waiting append to fail once the database’s lock wait timeout passes, on MySQL innodb_lock_wait_timeout. A transaction that locks other rows after appending can also deadlock with one that locks the same rows before appending.

  • On SQL Server, write under READ COMMITTED, not SNAPSHOT: under SNAPSHOT a concurrent append does not wait but fails with an update conflict (error 3960).

MariaDB and SQL Server have native sequences, yet they wait for each other as MySQL does. Neither lets a reader tell soundly which positions transactions still in flight hold, and waiting is what keeps every message readable in order. This is a correctness decision, not an oversight.

Minimum versions

MySQL needs version 8.0.1 or later, and MariaDB version 10.6 or later. On an older server, a projection that keeps its read models in the database and a route consumer never get their partitions, so they never apply a message.

What engine choice does not change

  • Messages are read in the same order on every engine.

  • No engine loses or reorders a message, under concurrent writes and on many instances.

  • There is nothing to tune: how far a consumer reads follows from the database’s own transactions, not from a timeout; see How quickly the mark moves.

Which DataSource and transaction manager Spring DDD writes with is described under DataSource and transactions.