Use Example: A Cache With Expiry Using Key Reads and an SQL Sweep

A response cache has two jobs that pull in different directions. On a hit, it has to find one entry by its key as fast as possible. Now and then it has to throw away everything that has expired, which is a question over all the entries. In KvSQL both jobs run on one table.

db.execute("CREATE TABLE cache (key TEXT PRIMARY KEY, body BLOB, expires INTEGER NOT NULL);
            CREATE INDEX cache_expires ON cache(expires)", &[])?;

Store a response with put_row, which replaces an older entry under the same key:

let mut tx = db.begin_write()?;
tx.put_row("cache", &[Value::Text(path.into()), Value::Blob(body), Value::Int(now + ttl)])?;
tx.commit()?;

Serve from it by key, with no SQL on the hot path:

match db.get_row("cache", &Value::Text(path.into()))? {
    Some(row) => { /* row[1] is the body, row[2] the expiry */ }
    None => { /* miss: fetch, then put_row */ }
}

And sweep with SQL:

db.execute("DELETE FROM cache WHERE expires < ?", &[Value::Int(now)])?;

The project’s use example program runs exactly this with 10,000 cached responses. A get_row for a stored path came back with its row and a made-up path came back empty. The sweep with a cut-off of 1300 removed 5,100 entries and left 4,900, as the expiry times it wrote say it should. The output is in case-studies.md.

A cache entry may carry an expiry check on read, too: compare row[2] with the clock and treat an old entry as a miss. Because the sweep and the reads share one transaction system, a reader never sees an entry half deleted.