A nightly job reads five million rows. As a list they do not fit in the heap. Storm streams them through a Flow in bounded memory, reads them in windows when the loop writes back, and batches the writes.
Export, migrate or reprocess a table that does not fit in memory: filter in the database, write changes back inside a transaction where needed, and keep the writes efficient.
Every query builder has resultFlow. The Flow is cold: building it runs nothing, and the query executes when you collect it. Rows hydrate as they arrive, and the statement closes when the collection completes or the coroutine is cancelled, so there is no close call to forget:
// A cold Flow: the query runs when it is collected val users: Flow<User> = orm.entity(User::class).select().resultFlow users.collect { user -> processUser(user) // one row in memory at a time } // Flow operators apply row by row as the rows arrive val emails: List<String> = users.map { it.email }.toList()
Memory stays bounded because Storm's per-query interner holds an entity only while your code still holds it, so the rows you are done with are garbage-collected as usual. Each collection runs the query again.
A stream is a query like any other, so the where runs in the database and only the matching rows travel:
// Filter in the database, stream what remains val residents: Flow<User> = orm.selectFrom<User>() .where(User_.city eq city) .resultFlow
While a stream has unread rows, its connection belongs to the stream. JDBC drivers differ in what they do with a second statement on that connection: some run it, one rejects it, and others first read the rest of the result into memory. Storm applies one rule on every database: a statement on a connection with an open stream is refused. Inside a transaction every statement uses the transaction's connection, so a loop that writes while it streams fails:
transaction { orm.selectFrom<User>().resultFlow.collect { user -> orm update user.copy(email = user.email.lowercase()) // PersistenceException: a result stream is still open } }
For a loop that needs the database, use windows(size). Each window of rows is read by its own statement, ordered by the primary key, and that statement has closed before the window reaches your code. Between windows the connection is free, and one batched update per window replaces one statement per row:
// One closed statement per 1000 rows; the connection is free in between transaction { val users = orm.entity(User::class) users.select() .where(User_.city eq city) .windows(1000) .collect { window -> users.update(window.content().map { it.copy(email = it.email.lowercase()) }) } }
Each window resumes from the last key it saw, so a long job can store window.nextCursor() and continue from it after a restart. Windows sort by a unique key, so the query takes no orderBy of its own. Use resultFlow to consume a result and windows when the loop reads, fetches or writes.
Batch writes take a list or a Flow and write in batches of 1000 rows by default. The Flow forms take a batch size:
val users = orm.entity(User::class) users.insert(newUsers) // a list, written in batches of 1000 val incoming: Flow<User> = readFromKafka() users.update(incoming, 500) // a Flow, written in batches of 500
The reference documentation covers the mechanics in depth: