Stale Query Cache Results with LIMIT/OFFSET Pagination in DuckDB

Resolution

DuckDB automatically invalidates cached result sets when the underlying tables are modified via INSERT, UPDATE, or DELETE operations. If you are seeing stale data while using LIMIT and OFFSET, it is likely not a failure of the result cache, but rather result drift caused by data mutations occurring between separate pagination requests.

Cache Invalidation vs. Result Drift

It is important to distinguish between these two behaviors:

• Result Cache: DuckDB tracks dependencies between cached queries and the tables they reference. A mutation to a table triggers the invalidation of dependent cache entries. Therefore, a repeated query for "Page 1" will reflect the most recent data.
• Result Drift: This occurs when a user requests "Page 1," then a row is deleted or inserted, and the user subsequently requests "Page 2." Because the underlying indices have shifted, a row that was at the bottom of Page 1 may move to the top of Page 2 (appearing twice) or move from Page 2 to Page 1 (being skipped entirely).

Recommended Approach for Reliable Pagination

To guarantee that paginated results reflect recent data without duplication or gaps, move away from OFFSET and implement Keyset Pagination (also known as the Seek Method). Instead of skipping rows, filter by the last seen unique identifier or timestamp.

Example implementation:

-- Instead of: SELECT * FROM logs LIMIT 10 OFFSET 100;
-- Use: 
SELECT * FROM logs 
WHERE id > [last_id_from_previous_page] 
ORDER BY id ASC 
LIMIT 10;

Verification Steps

To verify if your specific environment is experiencing a cache failure or drift, run the following test:

1. Execute a paginated query: SELECT * FROM table LIMIT 5 OFFSET 0;
2. Modify a value in one of those five rows: UPDATE table SET col = 'new' WHERE id = X;
3. Re-execute the exact same query. If the result reflects the update, the cache is invalidating correctly.

Diagnostic Detail Needed: Are you using DuckDB in READ_ONLY mode or accessing the database via a specific middleware/wrapper that implements its own application-level caching?