Performance implications of offset pagination in moleculer-db

Question

When using the moleculer-db package, the standard find action supports limit and offset parameters to paginate results. While the parameter validator can enforce a maximum page size to prevent oversized requests, the underlying database execution behavior varies significantly based on the offset value.In many adapters, such as SQL-based or MongoDB, a high offset requires the database to scan and discard numerous rows before returning the requested set. This can lead to linear latency growth as users navigate deep pages. While keyset pagination offers a more scalable alternative, it requires a unique indexed sort key and custom cursor handling beyond the basic find action parameters.
How does moleculer-db handle the performance cost of deep-offset queries across different adapters? Is there a recommended pattern for transitioning from offset-based to keyset-based pagination without bypassing the built-in find action logic?

Zoha · Answer

Direct answermoleculer-db does not optimize deep-offset queries itself. Its find action passes limit and offset straight to the adapter, which translates them into native database syntax such as SQL LIMIT/OFFSET or MongoDB skip(). Consequently, the database must read and discard every preceding row before returning the requested page, producing roughly linear latency growth as the offset increases. There is no built-in keyset (cursor) pagination in the standard find action.Likely explanation vs. confirmed behaviorConfirmed: Adapters map offset to native skip/offset semantics. The find action schema accepts limit and offset as first-class parameters, and service-level validators can cap limit but do not alter execution cost.Likely: The exact latency curve and memory impact vary by adapter and storage engine. For example, MongoDB skip() and SQL OFFSET both scan preceding documents/rows, but index usage and sort spill behavior differ. Very large offsets can trigger database-side timeouts or memory pressure, especially when the query includes an unindexed sort.Recommended transition without bypassing findChoose a stable, unique, indexed sort key—typically the primary key or a composite of created_at plus id.In a custom service action, call the adapter's find method (or the built-in find action) but replace offset with a where filter such as { id: { $gt: lastId } } for ascending order, or { id: { $lt: lastId } } for descending.Return the last record's sort key in the response payload as a cursor. The client sends that cursor back instead of an offset.Keep the limit validator to bound page size.This approach preserves the adapter's query-building logic while eliminating the scan-and-discard cost of deep offsets.Verification and uncertaintyTo confirm the cost in your environment, run the same query with offset: 0 and offset: 10000 on a collection with more than one million rows and compare response times. For SQL adapters, inspect the EXPLAIN plan to verify how many rows are scanned before the limit is applied.If you share which adapter you are using and your current sort field, I can suggest the exact index and cursor expression to add.

Performance implications of offset pagination in moleculer-db

1 answer

Direct answer

Likely explanation vs. confirmed behavior

Recommended transition without bypassing `find`

Verification and uncertainty

Your perspective belongs here.

0 question comments