Skip to content

Slow snapshot fetches return already-expired cache entries and cause immediate repeated reads #6209

Open

Description

@NikitaYurasov

Summary

CachingStateSync.get_snapshots() calculates an entry's expiration before fetching missing snapshots. If that fetch takes longer than the TTL, the successfully loaded entry is already expired when it is returned. The next lookup immediately fetches it again.

Snapshot loading includes more than the state database read. Deserialization and query preparation can make a cold load exceed the default 120-second TTL. Repeated snapshot access during auditing can then repeat that preparation work.

Environment

  • SQLMesh: 0.236.4
  • SQLGlot: 30.8.0
  • Python: CPython 3.14.7

Reproduce

Save this as reproduce.py:

import json
import os
from unittest.mock import patch

os.environ["SQLMESH__DISABLE_ANONYMIZED_ANALYTICS"] = "true"

from sqlglot import exp
from sqlmesh.core.model import SqlModel
from sqlmesh.core.snapshot import Snapshot
from sqlmesh.core.state_sync import cache as cache_module


class StateReader:
    def __init__(self, clock, snapshot, fetch_ms):
        self.clock = clock
        self.snapshot = snapshot
        self.fetch_ms = fetch_ms
        self.reads = 0

    def get_snapshots(self, snapshot_ids):
        self.reads += 1
        self.clock[0] += self.fetch_ms
        return {
            self.snapshot.snapshot_id: self.snapshot
            for snapshot_id in snapshot_ids
            if snapshot_id == self.snapshot.snapshot_id
        }


def observe(fetch_ms):
    model = SqlModel(name="demo.example", query=exp.select(exp.Literal.number(1).as_("id")))
    snapshot = Snapshot.from_node(model, nodes={model.fqn: model}, ttl="in 1 day")
    clock = [1_000_000]
    state = StateReader(clock, snapshot, fetch_ms)

    with patch.object(cache_module, "now_timestamp", lambda: clock[0]):
        cache = cache_module.CachingStateSync(state)
        expected = {snapshot.snapshot_id: snapshot}
        assert cache.get_snapshots([snapshot]) == expected
        expired_on_return = cache.snapshot_cache[snapshot.snapshot_id][1] < clock[0]
        assert cache.get_snapshots([snapshot]) == expected

    return {"backend_reads": state.reads, "expired_on_first_return": expired_on_return}


results = {"fast_fetch": observe(0), "slow_fetch": observe(121_000)}
print(json.dumps(results, indent=2))
assert results["fast_fetch"]["backend_reads"] == 1
assert results["slow_fetch"]["backend_reads"] == 1, (
    "A successfully fetched snapshot is fetched again immediately"
)

Run it in an isolated environment:

uv run --isolated --no-project --python 3.14.7 \
  --with 'sqlmesh==0.236.4' --with 'sqlglot==30.8.0' python reproduce.py

The example uses a real SQLMesh snapshot and cache with a fake state reader and clock. The clock advances inside the fake fetch, so the example runs without waiting 121 seconds.

Actual result

{
  "fast_fetch": {
    "backend_reads": 1,
    "expired_on_first_return": false
  },
  "slow_fetch": {
    "backend_reads": 2,
    "expired_on_first_return": true
  }
}

The final assertion fails. The fast-fetch control shows that a second lookup normally uses the cache.

Expected result

A successfully fetched snapshot should be reusable by an immediate subsequent lookup. For a newly filled entry, starting its TTL after the successful fetch completes would give the returned result a useful cache lifetime.

Existing cache hits should keep their deadlines. A change should also preserve seed hydration behavior, cache expiry after the TTL, and retries after a failed backend read.

If expiry relative to fetch start is an intentional freshness requirement, is there a supported way to avoid repeated cold loads when preparation alone exceeds that requirement?

Relevant implementation

CachingStateSync.get_snapshots() in v0.236.4 sets expire_at = now + self.ttl * 1000, then calls the underlying state reader and stores the result with that earlier deadline.

Context.audit() can access snapshots when resolving a selected model and again when supplying the evaluator's snapshot map. The reproducer isolates the cache behavior without replacing the SQLMesh cache implementation or executing an audit query.

Activity

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions