Estimate the read capacity consumption of limit queries

When processing a query that uses the LIMIT clause, Amazon Keyspaces reads rows up to the maximum page size when trying to match the condition specified in the query. If Amazon Keyspaces can't find sufficient matching data that meets the LIMIT value on the first page, one or more paginated calls could be needed. To continue reads on the next page, you can use a pagination token. The default page size is 1MB. To consume less read capacity when using LIMIT clauses, you can reduce the page size. For more information about pagination, see Paginate results in Amazon Keyspaces.

For an example, let's look at the following query.

SELECT * FROM my_table WHERE partition_key=1234 LIMIT 1;”

If you don’t set the page size, Amazon Keyspaces reads 1MB of data even though it returns only 1 row to you. To only have Amazon Keyspaces read one row, you can set the page size to 1 for this query. In this case, Amazon Keyspaces would only read one row provided you don’t have expired rows based on Time-to-live settings or client-side timestamps. To consume less read capacity, we recommend to set your page size equal to the LIMIT value to reduce the amount of data Amazon Keyspaces reads.