Offset versus keyset pagination for bounding an Alpakka JDBC source without a unique ordered column

Question

When streaming a large relational dataset through an Akka Streams Alpakka JDBC source, the goal is to bound memory and query cost while preserving back-pressure. Offset-based pagination is straightforward to express using .drop(offset).take(limit), but deep offsets force the database to scan and discard preceding rows. Keyset pagination avoids that cost by re-querying with a WHERE id > lastSeen ORDER BY id LIMIT pageSize bound, yet it requires a unique, monotonic ordering column that the current schema may not provide. Assuming a recent Alpakka JDBC release, both patterns rely on the source's built-in back-pressure rather than client-side buffering.

The unresolved decision is whether to introduce a synthetic ordered key to enable keyset paging or accept the offset performance trade-off. Concurrent inserts and deletes also affect each approach differently: offset pages can drift or duplicate rows, while keyset boundaries depend on the ordering column remaining stable during traversal.

Which factors should dominate the choice when the underlying table lacks a suitable unique ordered column? Is a synthetic monotonic key justified by the query plan, or does the risk of concurrent modifications make offset pagination safer in this context? How should the ordering column's immutability be validated before committing to keyset pagination?

Tasadduq Burney · Answer

Decision Framework: Keyset vs. Offset Pagination

When a table lacks a unique, monotonic ordering column, keyset pagination is only justified if the dataset size makes the O(N) cost of deep offsets prohibitive. If the dataset is small enough that the database can handle the scan cost of the final pages without timing out or spiking CPU, offset pagination is safer because it requires no schema changes and handles non-unique data naturally.

Comparing the Trade-offs

Factor
      Offset Pagination
      Keyset (with Synthetic Key)

Query Cost
      Linear increase (scans and discards)
      Constant (index seek)

Consistency
      Prone to drift (missed/duplicate rows)
      Stable boundaries

Schema Impact
      None
      Requires new column and index

Complexity
      Low (.drop().take())
      Higher (state tracking of last key)

Implementation Guidance
If you determine that the performance cost of offsets is too high, follow these steps to implement a synthetic key for keyset pagination:

Add a Monotonic Identifier: Introduce an auto-incrementing primary key or a BIGINT IDENTITY column.
  Index the Key: Ensure the new column has a B-tree index to allow the database to jump directly to the last seen value.
  Update the Source Logic: Replace OFFSET logic with a WHERE synthetic_id > :last_id ORDER BY synthetic_id LIMIT :pageSize clause.
  Handle State: Pass the last processed ID from the stream's output back into the next query request to maintain the cursor.

Validating Immutability and Performance
Before committing to a keyset strategy, verify the ordering column's behavior and the query plan:

Plan Analysis: Run EXPLAIN ANALYZE on a query with a deep offset. If the "rows removed by filter" or "rows scanned" count is excessively high relative to the page size, the offset approach is unsustainable.
  Immutability Check: Verify that the chosen ordering column is not subject to UPDATE statements that change its value. If the value changes while the Alpakka source is streaming, the record may be skipped or duplicated.
  Concurrency Test: Insert a record into a page already processed by the stream; if using offsets, you will observe the same record appearing again in the next page (drift).

Missing Diagnostic: What is the approximate total row count of the table and the expected frequency of concurrent writes during the streaming window?

Offset versus keyset pagination for bounding an Alpakka JDBC source without a unique ordered column

1 answer

Decision Framework: Keyset vs. Offset Pagination

Comparing the Trade-offs

Implementation Guidance

Validating Immutability and Performance

Your perspective belongs here.

0 question comments

Factor	Offset Pagination	Keyset (with Synthetic Key)
Query Cost	Linear increase (scans and discards)	Constant (index seek)
Consistency	Prone to drift (missed/duplicate rows)	Stable boundaries
Schema Impact	None	Requires new column and index
Complexity	Low (`.drop().take()`)	Higher (state tracking of last key)