I am trying to paginate through records in Cassandra. I am using paging state for this. CASE 1: When I set the LIMIT and fetch size in the query, it returns paging state up until the limit is not reached. Say, the fetch size 10 and limit is 100, it will return paging state up until 100 records are not retrieved and then returns null at the end. How do I paginate further?
CASE 2: When I just set the fetch size and no limit, I am able to paginate through all the records.
I want to understand that if I do not set limit, is it going to have negative performance impact?
I have tried with and without LIMIT.
The behavior you described is correct/expected:
All requests in Cassandra are paged whatever you do (default page size is 5000).
LIMIT X does not prevent the request to do a full scan of the table. In a Cassandra query, the partition key is always required in the where clause for good performance, otherwise you can easily hit client timeouts (Cassandra tables can hold billions of rows with no issue).
// BOTH are quite bad
select * from TABLE LIMIT 1;
select * from TABLE;
if you set page size to 10 and limit to 100 client will page until it get the big total of 100 or get out or records no more. You driver fetch page by page.