DynamoDB pagination: why a Query stops at 1 MB without an error

DynamoDB pagination: why a Query stops at 1 MB without an error

By Aarthi Honguthi

A DynamoDB Query never returns more than 1 MB of data per call, no matter what Limit you pass. In our test, a Query with Limit=100000 on a 10,000 item partition returned 2,537 items and no error. The only sign that 7,463 items were left behind was a LastEvaluatedKey in the response, and code that ignores that key silently works on a quarter of the data.

This post shows where a DynamoDB page ends, what breaks when code reads only the first page, and how to follow LastEvaluatedKey to the end. It also covers the cases where reading every page is the wrong fix. More on how we build our backend is in our software engineering posts.

The short version

  • A Query with Limit=100000 returned 2,537 of 10,000 items, because each response stops at 1 MB.
  • Following LastEvaluatedKey took 4 pages to return all 10,000 items.
  • A cleanup that read only the first page found 26 of one owner's 100 rows and left 74 behind.
  • With Limit=50 and a filter, the Query read 50 items and returned 1, because Limit counts items read, not items returned.

Why a Query returns fewer items than exist

We store one small record per pet file in a single partition per household, with the files themselves in S3, as described in why we trust Amazon S3 with pet moments. To test paging, we filled one partition on DynamoDB Local with 10,000 items of about 410 bytes each, about 3.91 MB in total, spread across 100 owners.

What we tried first

The first version asked for everything with a large limit:

response = table.query(
    KeyConditionExpression=Key("pk").eq("TENANT#t1#FILE"),
    Limit=100000,
)
items = response["Items"]

It returned 2,537 items. Nothing failed, nothing logged, and the code carried on as if it had the whole partition. A large Limit does not raise the 1 MB ceiling; it only sets a second, lower ceiling that this request never reached.

How DynamoDB decides where a page ends

A Query reads items in sort key order and stops at whichever comes first: Limit items read, or 1 MB of data read. If more items match the key condition, the response includes LastEvaluatedKey, the key of the last item read. You pass it back as ExclusiveStartKey to continue from the next item.

A FilterExpression runs after that read, on the page that was already read. So the page size is decided before the filter, which is why a filtered page can come back almost empty. We covered the cost side of this in why a filtered Query still scans.

Setting What it limits When it is applied What tells you there is more
1 MB page size Data read per call Always, during the read LastEvaluatedKey
Limit Items read per call During the read, before any filter LastEvaluatedKey
FilterExpression Items returned After the read Nothing; Count drops below ScannedCount
Select="COUNT" Returns a count, not items Still one page per call LastEvaluatedKey

The last row surprises people. In our test, a single Select="COUNT" call reported 2,537. Looping over all 4 pages reported 10,000.

Three ways a missing loop breaks a feature

A one-page read rarely fails in testing, because test partitions are small. It starts failing quietly once real data passes one page.

A cleanup that reports success

Suppose a job removes every file owned by one user: read the partition, keep the rows where the owner matches, delete them. In our test, owner u7 had 100 rows. The first page held 26 of them, so a job reading one page would delete 26, report success, and leave 74 behind. Our dry run only counted the rows, without deleting anything.

A list that stops showing new items

Our sort keys are time-ordered ids, so ascending order means oldest first. A list capped at 500 items in ascending order returned items 0 to 499. The newest item was not in it, and it would never appear, however many new items arrived. The same cap read newest first did include it.

A filter that returns almost nothing

With Limit=50 and a filter on owner u7, the Query read 50 items and returned 1, with LastEvaluatedKey set. Code that treats "1 result and a cursor" as "1 result" will show a nearly empty page and stop.

The fix: follow LastEvaluatedKey to the end

Step 1: loop in jobs that need every item

Background jobs, exports and deletes need the whole result. Keep calling until there is no LastEvaluatedKey:

def query_all(table, pk):
    """Read every item in one partition, page by page."""
    items, start = [], None
    while True:
        kwargs = {"KeyConditionExpression": Key("pk").eq(pk)}
        if start:
            kwargs["ExclusiveStartKey"] = start
        page = table.query(**kwargs)
        items.extend(page["Items"])
        start = page.get("LastEvaluatedKey")
        if not start:
            return items

On our partition this took 4 calls, returning 2,537, 2,534, 2,534 and 2,395 items. DynamoDB Local estimated 128.5 read capacity units (RCUs) for the first page and 507 for all four. A full 1 MB page at half an RCU per 4 KB comes to about 128, so the estimate lines up. boto3's built-in paginators do the same loop if you use the low-level client.

Step 2: hand the cursor to the client in screens

A screen should not load 10,000 items to show 20. Return one page plus an encoded LastEvaluatedKey as a "next" cursor, and let the app ask for more. The cursor carries the partition key, so validate that it belongs to the caller's tenant before using it, in line with our DynamoDB multi-tenant isolation rules.

Step 3: read newest first where the newest matter

For feeds, logs and history, set ScanIndexForward=False. Then a cap trims old items, which users rarely scroll to, instead of new ones, which they look for first.

Make truncation loud

The real bug is not the missing loop; it is that nothing notices. When we audited our backend's read paths, we found 21 reads that stopped after one page or a fixed cap and then used the result as if it were complete.

Two changes make this class of bug visible. First, the shared repository read helpers should log a warning, or raise, when they return a page that has a LastEvaluatedKey and the caller did not ask for a cursor. Second, a lint rule can flag a one-page read followed by a Python filter, the same way we already block Scan at build time. We wrote about why rules like these exist in every lint rule is a scar.

When reading every page is the wrong fix

Paging to the end is correct for a job that must touch every row. It is wrong when the code only needs a few rows and is reading everything to find them. A search box that pages through a 10,000 item partition on every keystroke is complete but slow and expensive.

In that case the key is the problem, not the loop. Put the thing you ask about at the front of the sort key, or add an index, so a Query reads only the rows you need, as in the sort key design post. A long-running loop also spends read capacity steadily, which is the theme of three background jobs that wasted read capacity.

What this lets EverBowl do

EverBowl™ learns a dog's eating routine from each bowl visit, and that history only means something if it is whole. Reads that follow every page mean a weekly view includes the latest visits rather than stopping at an old cap, and a request to remove a person's data reaches every row.

The honest limit: complete reads make the record complete, not correct. EverBowl does not diagnose; the value is the record.

Conclusion

A DynamoDB Query returns at most 1 MB per call and says so only through LastEvaluatedKey. Any code that reads once and moves on works in testing and fails silently later. Loop to the end in jobs, return a cursor in screens, and read newest first where recent items matter. Then make your repository warn when a page is dropped, so the next missing loop shows up in the logs instead of in a user report.

FAQs

Does DynamoDB Limit guarantee that many items are returned?

No. Limit is the most items DynamoDB will read in one call, not the number it returns. The 1 MB page size can stop the read earlier, and a filter can remove items after the read.

How do I know a DynamoDB Query has more results?

Check for LastEvaluatedKey in the response. If it is present, there may be more items; pass it as ExclusiveStartKey in the next call. When it is absent, you have read everything that matches the key condition.

Does Select COUNT read the whole partition in one call?

No. A count is still subject to the 1 MB page size, so you must loop and add up Count from each page. In our test, one call reported 2,537 while the loop reported 10,000.

Should I set ScanIndexForward to false for paginated lists?

For feeds, logs and histories, usually yes. Reading newest first means any cap drops the oldest items, not the newest ones users are looking for.

Is it safe to send LastEvaluatedKey to a client?

Only after encoding it and checking it on the way back. It contains key values, including the partition key, so the server must confirm the cursor belongs to the caller before using it.

References

More on Software →

Read more