Choosing and using a DB API
This page explains which Genesis database API to use in Genesis 10, which threads your code runs on, and how the API you choose uses database connections. For the operations each API offers, see Data access APIs (GPAL / Java).
Which API should I use?
- From Java, use
SyncEntityDb. UseSyncDbif you need to work withDbRecords. - From Kotlin, use
AsyncEntityDb. UseAsyncDbif you need to work withDbRecords. - Don't start new code on
RxEntityDborRxDb. Both are deprecated in 10.0, and no removal version is set. Existing code keeps working, andRxDbremains supported. The deprecation warning suggestsAsyncEntityDb; from Java, useSyncEntityDb.
In a GPAL event handler, use the entityDb that the event handler provides, rather than injecting an API.
| Language | JDK | Use | Threading you get |
|---|---|---|---|
| Kotlin | 21 or later | AsyncEntityDb (AsyncDb for records) | Transaction bodies, JDBC work and Flow producers run on virtual threads automatically. Code in a non-transactional event handler runs on a fixed pool of platform threads, one per CPU core |
| Kotlin | 17 | AsyncEntityDb (AsyncDb for records) | Database work runs on platform threads, capped at 2 × CPU cores. Waiting for a connection suspends without holding a thread |
| Java | 21 or later | SyncEntityDb (SyncDb for records) | Database calls, writeTransaction bodies and streams run on the calling thread |
| Java | 17 | SyncEntityDb (SyncDb for records) | Platform threads throughout. See Java and Sync pitfalls |
Virtual threads
On Java 21 and later, the database layer selects virtual threads for its own work automatically. There is nothing to enable.
To use platform threads instead, set the JVM system property -Dgenesis.db.virtualThreads=false in the process's <options> in its processes.xml. Setting it to true requests virtual threads, and falls back to platform threads with a warning if the JVM doesn't support them.
Virtual threads apply to the work the database layer runs, not to all of your code. On Java 21 and later:
| Code | Runs on a virtual thread | Runs on the calling thread |
|---|---|---|
| Async point read or write | The JDBC work | Your code before and after the call, on your coroutine's dispatcher |
Async writeTransaction or readTransaction | The whole transaction body | Nothing inside the block |
Async Flow read | The producer | The collector, on its own dispatcher |
GPAL event handler with transactional = true | The handler body, because it runs inside writeTransaction | |
| GPAL event handler, non-transactional | Only the database calls | Handler code, on a fixed pool of platform threads, one per CPU core |
Sync reads and writes, writeTransaction and readTransaction | Nothing | The whole block or call |
Sync streams (stream(), getBulkStream) | Nothing | The whole stream |
| Sync event handler | Nothing for the handler, by default | Handler code, on the message threads: by default a fixed pool of platform threads, one per CPU core |
Spring non-suspend @Event handler | Nothing for the handler | Handler code, on platform threads |
A Java Sync event handler makes its database calls on its message thread, so the number of Sync handlers running at once is limited by the message threads. On Java 24 and later, the JVM system property -Dgenesis.net.virtualThreads=true runs the message threads as virtual threads instead, with at most genesis.net.virtualThreads.maxInFlight messages (default 256) in flight at once.
Virtual threads also don't raise database concurrency on their own. Once threads stop being the limit, the connection pool is.
Connections and nesting
Each process has one connection pool for the Genesis database, and every API shares it. Nested calls reuse a connection in the cases below. This section describes the SQL database layer.
What reuses a connection
- Anything inside a transaction. Inside an
AsyncEntityDbreadTransactionorwriteTransaction, these all use the transaction's connection, and a rollback covers their writes: suspending calls,getBulk().collect { get() }, a direct blocking or Sync call, and code insidewithContext. - Nested transactions. A nested transaction joins the outer one. A nested
writeTransactionuses a savepoint. - Nested calls in a Sync stream. A blocking
getmade while reading a Sync stream on the same thread reuses the stream's connection, at any depth of nesting.
What needs another connection
-
A non-transactional
getBulk().collect { get() }. The stream holds one connection and the nestedgetneeds a second. Wrap it inreadTransactionto use one:// Needs two connections
asyncDb.getBulk(Trade::class).collect { trade ->
asyncDb.get(trade.byPrimaryKey())
}
// Needs one connection
asyncDb.readTransaction {
getBulk(Trade::class).collect { trade ->
get(trade.byPrimaryKey())
}
} -
An Rx
getBulkwith an interleaved Rxmodify. The stream and the write each need a connection. -
An Rx call inside Sync or Async code that holds a connection but isn't in a transaction.
-
Independent concurrent callers. Each needs its own connection.
-
Code in a new, independent coroutine scope, or on a thread you manage, that calls the database directly rather than through the
writeTransactionreceiver. It can't see the transaction, so it asks for its own connection. If every connection is held, it waits until the connection timeout.
Rules for threads
- Consume and close a Sync stream on the thread that opened it.
- An Rx write made inside a Sync or Async transaction joins that transaction if it is subscribed on the same thread while the transaction is open. Otherwise it throws an
IllegalStateException. Block on it inside the transaction, for example withblockingGet(), and don't usesubscribeOnorobserveOn.
Transactions: writes join
On the SQL database layer, a write made through an injected database API from inside a transaction joins that transaction. If the transaction rolls back, the write rolls back with it.
Sizing the connection pool
This section explains how the mechanism works so that you can reason about your own application. It is not a benchmark, and the numbers are not recommended values.
| Database | Default maximum pool size |
|---|---|
| PostgreSQL | 4 |
| H2, HSQL, Oracle, SQL Server | The greater of 2 × CPU cores and 4, plus 1 |
The PostgreSQL default is small, so check it first if you run on PostgreSQL.
Set the size with the system definition item DbSqlMaxPoolSize. The older DbSqlConnectionPoolSize is still honoured when DbSqlMaxPoolSize is not set.
By default, each process holds its full pool. Multiply the pool size by the number of processes, and check the total against your database's connection limit.
On SQL, DbThreadsMax sizes only the thread pool for the Rx path, so it doesn't change how many connections Sync and Async code can use.
What to size for
With virtual threads, the pool is what limits database concurrency. Size it for the peak number of callers holding a connection at the same moment, not for the number of requests. Each of these holds one:
- A transaction, for its whole body, including any non-database work in it.
- A streaming read, for its whole collection.
- A point operation, briefly.
A process with P concurrent holders needs P connections. If those holders use non-transactional nested reads, it needs 2 × P. Keep non-database work out of transactions so that they hold their connection for as short a time as possible.
How waiting works
When no connection is free, suspending code suspends without holding a thread, and blocking code parks its thread. The wait is limited by the DbSqlConnectionTimeoutMillis system definition item, which defaults to 30000 (30 seconds).
Kotlin pitfalls
- Sync calls from a suspend function. A call to
SyncEntityDbparks the current thread. Inside a transaction it reuses the transaction's connection, but outside one it blocks whichever dispatcher you are on. UseAsyncEntityDbinstead. - Blocking
Dispatchers.Default. Don't blockDispatchers.Defaultthreads on database work, on any JDK. Flowand connection lifetime. Flows are cold. A flow acquires its connection when collection starts, and holds it until the producer finishes or is cancelled, so a slow collector holds a connection for as long as it runs. A flow from the outer database joins a transaction only where it is collected, so collect it inside the transaction you want it to join.
Java and Sync pitfalls
-
Close your streams. A stream holds a connection until it is read to the end or closed. Operations that read to the end, such as
toList,forEachandcount, release it. Operations that stop early, such asfindFirst,limitandanyMatch, do not. Always use try-with-resources, or Kotlin'suse { }:try (Stream<Trade> trades = db.stream().getBulk(TRADE.INSTANCE)) {
Optional<Trade> first = trades.findFirst();
} -
Unreleased streams on PostgreSQL. A stream that hasn't been released holds an open transaction, which blocks
TRUNCATEand DDL on the tables it reads. -
Don't pass a stream between threads. Consume and close it on the thread that opened it.
-
Java 17, or virtual threads turned off. Database work shares a pool of platform threads, capped at 2 × CPU cores. Don't make a blocking read, such as
getBulk(...).toList().blockingGet(), from code that already occupies one of those threads, and don't block those threads on other work that needs them. The waiting thread keeps its slot while the read needs another one. If all the slots are taken this way, nothing progresses and nothing times out.
FoundationDB
FoundationDB is deprecated in 10.0.0 and is scheduled for removal in 11.0.0. Until then, on the FDB and FDB2 database layers:
| API | Behaviour |
|---|---|
SyncEntityDb, AsyncEntityDb, SyncDb, AsyncDb | These work, through the Rx path |
SyncStorageEngine, CoroutineStorageEngine | Not available for direct injection. Inject them with @Inject(optional = true) if your code also runs on other database layers |
sqlConnection(), sqlAsyncConnection() | Throw UnsupportedOperationException |
| Sync streams | Read eagerly into memory |
| Complex criteria | Supported, but evaluated in memory rather than by the database |
Upgrading from 8.15
- Injected writes join transactions. In 8.15 they didn't. Check any code that relied on such a write persisting after the surrounding transaction failed.
RxDbandRxEntityDbgive deprecation warnings. A build that treats deprecation warnings as errors fails until you move away from them.