Tutorials/Streaming

Streaming large result sets

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.

Series · The Storm way4 min readKotlin

01The task

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.

02Read as a Flow

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:

ExportJob.kt Kotlin · Storm
1
2
3
4
5
6
7
8
9
// 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.

03Filter in the database

A stream is a query like any other, so the where runs in the database and only the matching rows travel:

ResidentsJob.kt Kotlin · Storm
1
2
3
4
// Filter in the database, stream what remains
val residents: Flow<User> = orm.selectFrom<User>()
    .where(User_.city eq city)
    .resultFlow

04Write back in windows

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:

ReprocessJob.kt Kotlin · Storm
1
2
3
4
5
6
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:

ReprocessJob.kt Kotlin · Storm
1
2
3
4
5
6
7
8
9
10
// 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.

05The write direction

Batch writes take a list or a Flow and write in batches of 1000 rows by default. The Flow forms take a batch size:

ImportJob.kt Kotlin · Storm
1
2
3
4
5
6
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

06Keep going

The reference documentation covers the mechanics in depth: