Developer Network
Contact
Pricing

Handling Large Datasets in Mendix: Pagination, Batched Processing, and Memory-Safe Microflows

Cover Image for Blog: Handling Large Datasets in Mendix

Moving millions of rows through Mendix without exhausting the heap or holding the database hostage to one long transaction: the patterns that keep large-dataset logic stable in production.

Mendix makes it easy to retrieve a list of objects and loop over it, and that ease is exactly what gets teams into trouble at scale. A microflow that runs fine against ten thousand rows can take down an app engine against ten million. The reason is simple. The obvious pattern of one Retrieve followed by one Loop tries to hold the entire result set in memory and process it inside a single database transaction.

We run into this often in client work, usually right after an app graduates from a departmental tool into something handling real production volume. This article walks through the techniques that keep large-dataset logic stable: paginating retrievals, processing in bounded batches, managing transactions, handling concurrency, and the everyday memory hygiene that stops microflows from eating the heap. Everything here assumes Mendix Studio Pro, and it is written for both developers who are new to the platform and those who have already watched a nightly job fall over and want the deeper detail on cursors, transactions, and parallel work.

When “just retrieve it” stops working

At scale, two failures show up again and again, and it helps to recognize them by their stack traces.

The first is heap exhaustion. Loading a very large list into a single in-memory variable can throw java.lang.OutOfMemoryError: Java heap space. This is the exact error one developer reported on the Mendix forum after trying to loop over a CSV import of almost 260,000 objects at once. Every object, plus everything created or changed while looping, stays alive at the same time.

The second is garbage collector thrashing, which surfaces as GC overhead limit exceeded. Here the JVM spends nearly all of its time collecting because objects are kept alive across a long-running flow.

There is a third problem that has nothing to do with memory, and it is quieter. A microflow that processes everything in one pass keeps a single database transaction open for its whole duration. Even when the heap holds, that long transaction raises lock contention and database load, and a failure near the end rolls back all of the work you just did.

The thread running through all three, and the fix we keep coming back to, is to process less at once. Bound what you load, release it between chunks, and end transactions on a schedule.

Two meanings of “pagination”

The word pagination gets used for two different things in Mendix, and mixing them up causes a lot of confusion.

Display pagination is what list views and data grids do on their own. The platform fetches only the rows visible on the current page using an internal offset, so scrolling a grid over a million-row entity is not what blows up memory. The exception is when you deliberately force a full retrieve inside a data source microflow.

Processing pagination, which most people call batching, is splitting a server-side retrieve into chunks so you never hold the full set in memory while you work on it. This is where large-dataset logic actually lives, and it is the focus of everything below.

The core pattern: batched processing with limit and offset

The foundational technique swaps one unbounded Retrieve for many bounded ones. Instead of a single Retrieve action, you put a custom range on a database Retrieve, set an amount (the limit) and an offset, and repeat it while advancing the offset on each pass. Put simply, the work is split into multiple retrieves, each with its own limit and offset, rather than one retrieve that grabs everything.

The loop below shows the shape of it. The check on whether the batch came back empty is what ends the process.

Batched processing loop in a Mendix microflow
Figure 1. The batch loop: retrieve a bounded slice, process it, release it, then move the offset and repeat.

In Mendix terms, the cleanest version is a sub-microflow that takes the offset as a parameter. It retrieves the bounded batch, processes and commits it, then calls itself with offset plus batch size, stopping once an empty list comes back. If you would rather drive it from the client with a progress bar, you can run the same loop from a nanoflow, which supports a While loop directly. Three details decide whether it works:

  • Sort by a unique, stable attribute inside the retrieve. Without a deterministic order, offset windows shift between calls, so some rows get processed twice and others get skipped. An indexed auto-number or key is ideal.
  • Tune the batch size by measuring, not guessing. The right number depends entirely on how heavy the per-row work is. On a 100-million-row table, one team found 100 was their sweet spot. Lighter retrieve-only work often runs happily at 1,000 to 5,000. Test with your own data.
  • Remember that this is what reclaims memory. Because each batch is scoped, the objects inside it can be cleaned up by the garbage collector after the batch finishes, which is what keeps you clear of the GC overhead limit exceeded error.

Offset versus cursor: the part most guides skip

Offset batching has a weakness that only shows up at scale. The deeper you page, the slower each query gets, because the database still has to walk and throw away every row before the offset. It is also fragile under concurrent writes. If rows are added or deleted while you are paging through, you can end up with duplicates or skipped records.

Cursor-based batching, also called keyset pagination, fixes both problems. Instead of an offset, you remember the last value of an indexed attribute and constrain the next retrieve with something like [SortKey > $Cursor], sorted ascending. Each query seeks straight to where the last one left off.

Offset versus cursor batch retrieval
Figure 2. Offset rescans everything before the window. A cursor seeks straight to the last key and reads forward.

The difference is dramatic. In one published comparison that retrieved 2 million rows in batches of 1,000, cursor-based processing finished in a little over two minutes, roughly twice as fast as offset. Once the cursor attribute was indexed, the same job dropped to about three and a half seconds, which is close to 75 times faster than the offset version.

The trade-offs are real but manageable. Cursor batching needs a unique identifier and a little extra logic to track the cursor, and piling on indexes costs disk space. For small datasets, offset is simpler and perfectly fine. For genuinely large or busy tables, cursors earn their keep.

Keep transactions short

Batching solves the memory problem, but on its own it does not shorten the transaction. Left alone, the database transaction stays open until the microflow has processed every record, so database load stays high the whole time.

The fix is to commit and end the transaction at intervals, using the StartTransaction and EndTransaction Java actions in Community Commons as a pair. StartTransaction opens the transaction for the next stretch of work, and EndTransaction commits and closes it, flushing those changes to the database. Pairing them matters: a Mendix transaction only opens on the first write to the database, and a Retrieve never starts one, so calling EndTransaction on its own means ending transactions you never explicitly started and leaving the scoping unbalanced. The guidance is not to flush after every batch, but on a coarser rhythm, for example once every 100,000 objects, tuned to how many app engines you are running.

One thing to design for. Ending a transaction mid-run commits partial work, so a failure halfway through will not roll everything back. Build the flow so it can restart cleanly. A processed boolean on the record, or the cursor value saved somewhere, lets a re-run pick up where it stopped instead of redoing or losing rows.

Memory hygiene inside microflows

Beyond the batching structure, a handful of modelling habits keep individual microflows from bloating. None of these are new, but they are the ones we end up pointing out most often in reviews.

  • Never retrieve inside a loop. A Retrieve on every iteration multiplies your database round-trips. Retrieve the list once outside the loop and use the Find operation to search within it, so you are not opening a database connection on every pass.
  • Index the attributes you filter and sort on. Indexing makes a big difference to retrieval speed and is easy to forget. The Best Practice bot will flag retrieves on attributes that are not indexed. Cursor batching in particular is pointless without an index on the cursor attribute.
  • Let the platform optimize aggregates. When a retrieve only feeds a count or a sum, do not add a custom range you do not need. If a database retrieve is used purely in list aggregate activities with no custom range, the platform can merge the two into a single action and do the math in the database instead of loading objects into memory.
  • Avoid nested loops over large lists, and reach for association-based retrieves before XPath contains() constructions, which generate much heavier queries. Denormalization is a last resort for aggregation-heavy reads, not a starting point.
  • Keep batches scoped so processed objects can be collected between passes rather than piling up across the whole run.

Offload long jobs to the background: the Task Queue

Even a well-batched job, if it runs synchronously, blocks the user and pins the work to a single request. For anything long-running, the Mendix-native answer is the Task Queue, available from Mendix 9 onward, which replaced the older Process Queue marketplace module that was deprecated from Studio Pro 9.0.3. It lets you run microflows or Java actions asynchronously while capping how many run at once, spread across the nodes of a horizontally scaled cluster.

A natural fit for large datasets is a coordinator microflow that works out the batch ranges and enqueues one task per batch, then lets the cluster chew through them in parallel.

Task Queue fan-out for batch processing
Figure 3. One task per batch, picked up in parallel across the cluster, with every outcome logged for retries.

Two constraints are worth knowing before you reach for it. Only committed persistable entities can be passed as task parameters, and a task is only enqueued once the transaction that scheduled it finishes successfully. Outcomes land in System.ProcessedQueueTask, which you can query to drive follow-up logic or retries. The full details live in the Task Queue documentation.

Concurrency: keeping parallel batches from colliding

Running batches in parallel is how you make big jobs finish faster, but it brings hazards the single-threaded patterns never had to worry about: two workers grabbing the same batch, overlapping writes, or a race on a shared counter. Mendix gives you fewer guard rails here than you might expect, so the safe approach is mostly about architecture.

Start by knowing the platform’s limits. There is no pessimistic row lock you can take and hold inside a microflow to serialize workers. Mendix does have optimistic locking, generally available since Mendix 11.11 and switched on from the Runtime tab, but that is commit-time conflict detection rather than a held lock: it adds an MxObjectVersion to each entity and raises a ConcurrentModificationRuntimeException if another process changed the object since you read it. The Disallow concurrent execution property exists, but it only stops a microflow from starting while another copy of it is already running, and here is the part that catches people out: it applies per node only. There is no cluster-wide locking, so on a horizontally scaled app it will not stop two nodes from running the same flow at the same time. Community Commons offers lock actions for single-node coordination, and true cluster-wide single execution needs an external distributed lock manager, which is usually complex enough that it is better to design the requirement away.

The most reliable strategy is to make collisions impossible by construction. Give each task a key range that does not overlap with any other, so no two workers ever touch the same rows. Overlapping ranges are where double-processing comes from. Disjoint partitions sidestep the whole problem.

Overlapping versus disjoint batch partitions
Figure 4. Overlapping ranges double-process the shared rows. Disjoint partitions remove the conflict entirely.

With partitioning as the foundation, the rest of the rules follow:

  • Let the queue handle mutual exclusion. You rarely need locks for batch processing, because the Task Queue already prevents double pickup. Its executors claim work with a SELECT FOR UPDATE SKIP LOCKS statement, so a task already locked by one executor is simply skipped by the others. No two nodes process the same enqueued task.
  • Partition so batches never overlap. As the diagram shows, give each task a disjoint key range, by ID range, date range, or a modulo of an indexed key, so parallel workers cannot conflict because their data does not intersect.
  • Avoid shared mutable state. A single running-total object that every worker increments is a race waiting to happen. Have each batch write its own partial result and aggregate once at the end, rather than fighting over one object mid-run.
  • Stay idempotent. Combined with the restartability from the transaction section, idempotent batches mean a retried or accidentally repeated task produces the same result. That is your safety net for when timing does go wrong.

In Mendix you get concurrency safety mainly by removing contention, through disjoint partitions, per-batch results, and idempotency, and by letting the Task Queue coordinate the workers. You do not get it by trying to lock your way out.

Worked scenarios

These techniques rarely show up one at a time. Here are four situations we see regularly, and the mix of techniques each one calls for.

1. Nightly recalculation over an active table

A finance app holds 5 million Invoice records, and a scheduled event runs each night to flag the overdue ones. The naive version, retrieve every invoice, loop, change, commit, runs out of heap long before it finishes. Straight batching fixes it. A sub-microflow takes an offset, retrieves 1,000 invoices sorted by an indexed key, sets the flag, commits the batch, then calls itself for the next offset. Because invoices are created and paid throughout the day, this is also a good case for cursor batching, which avoids the duplicate-and-skip problem that offsets cause under concurrent writes. An EndTransaction every 100,000 rows or so keeps the database off a single long-held lock all night.

2. Processing a large CSV import

An integration drops a file of 260,000 rows into staging objects, and a microflow then validates and transforms each one into a domain object. Looping over all 260,000 at once is the textbook source of OutOfMemoryError: Java heap space. Instead, the processing microflow takes the staging rows in batches of around 1,000: retrieve a bounded batch, transform and commit, clear the batch, move on. Each committed batch becomes collectable, so the heap stays flat no matter how big the file is. A processed flag on the staging row makes the import safe to restart if it fails partway.

3. One-time migration that backfills a new attribute

A release adds a computed RegionCode attribute to a 10-million-row Order entity, and every existing order needs it filled in once. This is exactly where offset batching falls apart, because the millionth batch is far slower than the first. Cursor batching on an indexed attribute, an order number or a created date, keeps every batch equally quick by seeking straight to the last processed key. On comparable data, indexing that cursor attribute is the difference between a multi-minute job and a few seconds. Since it is a one-off, run it from a controlled trigger with transaction breaks and a saved cursor so it can resume if interrupted.

4. Bulk document generation that should not block anyone

An admin clicks a button to generate monthly statements for 200,000 customers. Run synchronously, the page hangs behind a spinner and the whole job is tied to one request. With the Task Queue, a coordinator microflow works out disjoint batch ranges and enqueues one task per batch. Runtime nodes across the cluster pick them up and generate statements in parallel while the admin carries on working. Because the ranges do not overlap and the queue prevents double pickup, no statement gets generated twice. Each task’s result lands in System.ProcessedQueueTask, so you have a record of which batches succeeded and which to retry.

A quick way to choose between them: reach for plain offset batching on bounded, one-pass jobs over stable tables; switch to cursor batching when the table is large or actively changing; add transaction breaks whenever a run spans hundreds of thousands of rows; and move to the Task Queue with disjoint partitions when the job is long enough that nobody should have to wait on it.

A checklist to keep handy

A quick recap of the decisions that keep large-dataset logic stable:

  • Bound every retrieve with a limit and an offset. Never load an unbounded list you intend to loop over.
  • Sort each batch by a unique, indexed attribute so windows do not overlap or skip.
  • Prefer cursor (keyset) batching over offset for very large tables, and index the cursor attribute.
  • End the transaction periodically, say every 100,000 rows, with Community Commons, and design the flow to restart cleanly.
  • Index every attribute you filter or sort on.
  • Do not retrieve inside a loop. Retrieve once and use Find.
  • Let the platform merge retrieve and aggregate, and skip custom ranges you do not need.
  • Push long-running jobs to the Task Queue, partitioned into disjoint key ranges so parallel workers never collide.
  • Do not lean on Disallow concurrent execution across a cluster. It is single-node only, so remove contention instead of locking.

Handle these well and the size of the table stops being the thing that decides whether your logic survives. A microflow that bounds its work, releases memory between batches, keeps transactions short, and partitions cleanly will run the same against ten million rows as it does against ten thousand. If you want a second pair of eyes on a job that is straining at scale, that is exactly the kind of work our performance optimization team does every week.

References and further reading