SQL: replace the scroll with PIT for data batching

[bpintea]

Since the scroll functionality replacement is underway, the SQL plugin should move away from it, replacing it with the newly added point-in-time (PIT) API.

[elasticmachine]

Pinging @elastic/es-ql (:Query Languages/SQL)

[jimczi]

I wonder if this should be an option in the SQL request like we have for _search. I always disliked the fact that SQL uses a persistent scroll for pagination. That could be an opt-in but imposing a scroll or a PIT that you need to close explicitly is not consistent with _search. The question is different for SQL queries that use multiple requests internally (group_by) since they could benefit from using a PIT that they could close when the request is fulfilled.

[costin]

The main reason why SQL relies on scroll is that by default, the vast majority of queries require pagination since the result set is too large.
Having a session-like (open/query/close) and stateless (run query/consume results) is appealing though we need to figure out the workflow in the later case.
One option for stateless queries would be to limit them to return only one page (and thus impose a hard limit). The challenge though is the mechanism for asking the next page; would could potentially use search_after but how will this parameter be returned to the user in the first place?

[bpintea]

could potentially use search_after but how will this parameter be returned to the user in the first place?

Generally with SQL, one simply can't server-side paginate outside a cursor-bound context (such as xDBC; or REST API-aware app, in our case) -- it's always just one result set.

Querying with or without a scroll/PIT is appealing imo due to the equivalence to SQL's transactional vs. non-transactional mode. However, maybe unlike typical RDBMSes, the "non-transactional" mode is more expensive for us (when paginating), so I guess we'd generally default to the "transactional" mode anyways? At least for our CLI/xDBC clients; which also automatically close the scroll, part of their internal cursor lifecycle.

[costin]

At least for our CLI/xDBC clients; which also automatically close the scroll, part of their internal cursor lifecycle.

Right, I envision the stateless request on the HTTP/REST front due to its request/response contract.

[jimczi]

The main reason why SQL relies on scroll is that by default, the vast majority of queries require pagination since the result set is too large.

But that's the same problem for normal search. I don't understand why there would be a difference in SQL. Paginating with a cursor is a feature in SQL too.

[bpintea]

The difference might stem from expectations, since RDBMSs are generally expected to return all results matching a query, or up to a given limit the user provides (like ... LIMIT 10001).
We could add an SQL API parameter to control the usage of a scroll/PIT and maybe simply reuse the existing cursor parameter to return the search_/after parameter (which would also contain or not the scroll/PIT ID). Pagination would then work in both scenarios and we could also default xDBC clients to use a search context by default. Would this make sense?