Keyset (Seek) Pagination Guide

Status: Stable — available from v5.x onward.

When to use this

Use keyset pagination (RunViewParams.AfterKey) when your code iterates through all records of a large entity in batches — typically a background job, scheduled action, vectorization run, or bulk export. It stays O(log N) per page regardless of how deep you go.

Keep using OFFSET-based pagination (RunViewParams.StartRow) for UI grid pagination — a few hundred pages of a few hundred rows each, where users jump around to arbitrary pages. OFFSET is correct for that workload and supports arbitrary OrderBy.

Workload	Use
Background job iterating a large entity	AfterKey
Vectorization / classification / sync of large entities	AfterKey
Bulk maintenance (“find missing X across the table”)	AfterKey
UI grid paged by user (jump-to-page)	StartRow
Small result sets (a few thousand rows)	Either is fine

Why keyset is fast

OFFSET N FETCH N requires the DB engine to enumerate the first N rows and discard them before returning the page. At offset 2,000,000 on a 2.6M-row table that’s millions of rows of work for every page.

Keyset pagination instead does WHERE pk > @lastSeenKey ORDER BY pk LIMIT N, which the optimizer resolves as a single clustered-index seek + range scan. Every page costs roughly the same, whether it’s page 1 or page 50,000.

Page (on a 2.6M-row entity, warm cache)	OFFSET	Keyset
0 (first page)	6 ms	6 ms
1 (offset 500)	<1 ms	<1 ms
100 (offset 50,000)	~25 ms	~1 ms
5000 (offset 2,500,000)	~770 ms	~5 ms

How to use it

Single-column PK (the common case)

import { CompositeKey, RunView } from '@memberjunction/core';

const rv = new RunView();
let lastSeenKey: CompositeKey | undefined; // undefined => first page

while (true) {
    const result = await rv.RunView({
        EntityName: 'Tax Returns',
        ExtraFilter: '(AddressLine1 IS NOT NULL)',
        AfterKey: lastSeenKey,         // undefined on first call
        MaxRows: 500,
        ResultType: 'entity_object',
        BypassCache: true,              // optional — AfterKey already bypasses cache, this is just explicit
    }, contextUser);

    if (!result.Success || result.Results.length === 0) break;

    for (const record of result.Results) {
        // ... process record ...
    }

    // End-of-data signal: partial page means we've reached the last page.
    if (result.Results.length < 500) break;

    // Advance the keyset cursor to the last record's PK.
    const last = result.Results[result.Results.length - 1];
    lastSeenKey = CompositeKey.FromID(last.ID);
}

Custom PK column name (not ID)

const last = result.Results[result.Results.length - 1];
lastSeenKey = CompositeKey.FromKeyValuePair('CustomerCode', last.CustomerCode);

Using the helper to check entity compatibility upfront

When you’re writing a generic helper that might be called for any entity, guard with IsKeysetPaginationOrderableType and entity.PrimaryKeys.length === 1:

import { IsKeysetPaginationOrderableType } from '@memberjunction/core';

const pkField = entity.FirstPrimaryKey;
const canUseKeyset = entity.PrimaryKeys.length === 1
    && pkField != null
    && IsKeysetPaginationOrderableType(pkField.Type);

if (canUseKeyset) {
    // … iterate with AfterKey
} else {
    // … fall back to StartRow / pageNumber
}

Constraints — and the errors you’ll see if you violate them

RunView throws AfterKeyNotSupportedError (in @memberjunction/core) when AfterKey can’t be honored. The error carries an EntityName and a Reason code so caller logic can branch sensibly.

Reason	When it fires	What to do
CompositePK	The entity has more than one PK column.	Fall back to StartRow (OFFSET), or restructure to query a single-PK projection.
UnsupportedPKType	PK column type is exotic (xml, sql_variant, varbinary). Essentially never happens with real MJ entities.	Use StartRow.
StartRowConflict	You passed both AfterKey and a non-zero StartRow.	Pick one.
AfterKeyShape	The CompositeKey shape doesn’t match the entity’s PK (wrong column name, > 1 pair, null/empty value).	Build the key correctly via CompositeKey.FromID() or CompositeKey.FromKeyValuePair(<pk-col>, value).
IncompatibleOrderBy	You passed OrderBy that references columns other than the PK.	Drop the OrderBy (the framework auto-applies ORDER BY pk) or use <pk-col> ASC/DESC.

import { AfterKeyNotSupportedError } from '@memberjunction/core';

try {
    await rv.RunView({ EntityName: 'Something', AfterKey: key, MaxRows: 500 }, user);
} catch (e) {
    if (e instanceof AfterKeyNotSupportedError && e.Reason === 'CompositePK') {
        // Fall back to OFFSET-based iteration
    } else {
        throw e;
    }
}

Cache interaction

Keyset queries automatically bypass both the client-side and server-side caches. This is intentional — each call uses a different seek key, so a cached entry would never be reusable. You can pass BypassCache: true explicitly for clarity, but it’s not required.

This also means keyset queries do not trigger OnDataChanged callbacks via Redis pub/sub — they’re not subscribing to anything cacheable in the first place.

Direction handling

The seek predicate inverts automatically based on the OrderBy direction:

OrderBy	Predicate generated
(unset)	WHERE pk > @lastSeenKey ORDER BY pk ASC
'ID' or 'ID ASC'	WHERE pk > @lastSeenKey ORDER BY pk ASC
'ID DESC'	WHERE pk < @lastSeenKey ORDER BY pk DESC

End-of-data signal

Same as StartRow-mode: when a page returns fewer rows than MaxRows, you’ve reached the end. Stop iterating.

What about RunQuery?

RunQuery (saved queries with arbitrary SQL) doesn’t support keyset pagination in v1, because the framework can’t safely rewrite a user-authored query to add a seek predicate. If you’re iterating through a saved query’s results, OFFSET-mode StartRow is the only option today. A future enhancement may add saved-query conventions for keyset.

Reference implementations

These callers in the codebase use the pattern and are good examples to copy from:

• packages/Actions/CoreActions/src/custom/geo/scheduled-geocoding.action.ts — processMissingForEntity() iterates entities marked SupportsGeoCoding, using keyset when the entity has a single-column PK and falling back to OFFSET otherwise.
• packages/AI/Vectors/Core/src/models/VectorBase.ts — PageRecordsByEntityID() accepts an optional AfterKey in its PageRecordsParams, with the helper CanUseKeysetPagination(entityID) for callers to check compatibility upfront.
• packages/AI/Vectors/Sync/src/models/entityVectorSync.ts — startDataPaging() auto-promotes to keyset when possible, falls back to OFFSET when StartingOffset is set (since keyset can’t skip to an arbitrary offset).

Migration checklist for existing OFFSET callers

When you find a caller iterating a large entity with StartRow, ask:

1. Does the entity have a single-column PK? If composite, leave it alone for now.
2. Does the OrderBy reference only the PK? If it sorts by something else (e.g. RetryCount ASC, CreatedAt ASC), keyset doesn’t apply — the workload may not need keyset anyway if the result set is small.
3. Is the StartRow value typically growing > a few thousand? If always small (UI pagination), leave it on OFFSET. If it climbs into the millions on big tables, migrate it.

The migration itself is small:

• Remove StartRow: offset + 1
• Add AfterKey: lastSeenKey
• Track the last record’s PK after each page and rebuild the CompositeKey
• That’s it — MaxRows and ExtraFilter stay the same

Implementation details (for framework maintainers)

• The seek predicate is built by GenericDatabaseProvider.BuildKeysetSeekClause() and appended to viewSQL (data query only) — not to countSQL (total count should reflect all matching rows, not “rows after the seek key”).
• Seek values are inlined as SQL literals with type-aware escaping in formatKeysetSeekValue() rather than parameter-bound, consistent with how the rest of the WHERE clause is assembled. UUID values are validated against a strict pattern; numeric values are validated as finite numbers; string/date values are escape-quoted and rejected if they contain semicolons, comment markers, or other suspicious patterns.
• Cache bypass is enforced at three layers: LocalCacheManager.SetRunViewResult short-circuits writes; ProviderBase.RunView/PreRunView/PreRunViews short-circuit reads; GenericDatabaseProvider.RunViewsWithCacheCheck routes keyset items to the no-cache-check pipeline.
• The GraphQL surface uses the existing CompositeKeyInputType so the AfterKey field doesn’t require any new scalar shape on the wire.