will14smith/SelectQuery

Add key/sort based offsetting

Closed this issue · 0 comments

Basic offsetting doesn't work (if you want LIMIT 10 OFFSET 1000 then you need to return the first 1010 items from each worker (and have a consistent, unique ordering)). Go with key/sort based ordering instead.

e.g.
Query 1: ORDER BY Quantity LIMIT 10 KEY (AssetId) with the result: { ..., "NextPage": [5, "AssetIdExample"] }
Query 2: ORDER BY Quantity LIMIT 10 KEY (AssetId) AFTER (5, 'AssetIdExample')
(^ probably base64'd though for simplicity)

Advantages:

  • Each worker only needs to return 10 (and can push some of the filtering to the query layer)

Disadvantages:

  • Can't go to arbitrary page

Alternatives:

  • This but don't do it explicitly (i.e. let the application handle it)
  • Don't do anything, get the application to do an un-limited query and let it cache it somewhere for paging