Memory leak when selecting a large number of rows

[yassenb]

Doing a simple EntityClass.find results in a memory leak (or perhaps more correctly bloat) when iterating through millions of records. The culprit is Transaction.entityCache which only gets filled and never freed during the iteration. As a result I'm forced to use a workaround like:

EntityClass.find { ... }.chunked(100_000).forEach {
    // read the chunk
    entityCache.data.clear()
}

instead of the simple

EntityClass.find { ... }.forEach {
    // read the entity
}

There should be a way to disable the cache or configure its size so that it doesn't grow forever.

[yassenb]

Interesting that you would label the ability to work with real life databases with more than a few thousand records in them without crashing due to running out of memory an "enhancement".

[bdshadow]

I just guess from the reply of @Tapac here that it was supposed that cache would be used for "mostly dictionary-like" entities, which contain a rather limited amount of data. That's why it is an enhancement/new feature to make it possible to cache big amounts of data without requiring user to clear cache him/herself

[yassenb]

It may be "supposed" that caching would be used in certain scenarios but at the moment it's used everywhere and there's no way to disable it. That's what this issue is about.
As per "requiring user to clear cache him/herself" - the workaround I've found is hardly public API. I found it only after reading the source code. What's more, the workaround splits the sequence/iterable in chunks which doesn't preserve the original behaviour which is a lot more convenient and optimal.

[Tapac]

New maxEntitiesToStore param on EntityCache will be available in the next release along with possibility to define it globally via DatabaseConfig.maxEntitiesToStoreInCachePerEntity