Stale Cache Issues with LIMIT/OFFSET Pagination in DuckDB

Stale Query Cache Results with LIMIT/OFFSET Pagination in DuckDB

27K reputation · 25 Aug 2026, 15:43 UTC

128.7K views0

Goal

Implement reliable pagination for large result sets using DuckDB’s LIMIT and OFFSET clauses.

Constraints

DuckDB’s optional query cache can store the results of a SELECT to accelerate repeated executions. The cache is not documented to automatically invalidate entries when the underlying table is modified, which may lead to stale pages being returned after updates.

Unresolved Questions

Does DuckDB automatically invalidate cached SELECT results when the underlying table is altered?
If automatic invalidation is absent, what is the recommended approach to guarantee that paginated results reflect recent data?
Are there configuration options or cache policies that can control invalidation for queries using LIMIT and OFFSET?

Keep exploring this topic

Unsupported file format version error when opening a partially upgraded DuckDB file

Investigating whether DuckDB should allow read‑only access to a partially upgraded database when opened with an older binary after a failed upgrade.

DuckDB 0.9.0 query_timeout pragma – impact on cancellation and interrupt_on_timeout behavior

Explores how DuckDB 0.9.0's query_timeout interacts with interrupt_on_timeout and safe‑point checks, asking about abort behavior, error messages, and observed timeout variance.

DataGrip 2023.3 fetch size default change when Limit rows is enabled

Investigating DataGrip 2023.3's fetch size behavior when Limit rows is enabled, server‑side cursors, and the hidden registry key.

Inconsistent page size when using Hibernate fetch join with setMaxResults

Inconsistent page sizes occur when Hibernate applies LIMIT/OFFSET before deduplication in fetch‑join queries.

Stale Query Cache Results with LIMIT/OFFSET Pagination in DuckDB

Goal

Constraints

Unresolved Questions

0 answers

Your perspective belongs here.

0 question comments