One query per read: key_hash and the unique index

The junior course "Solid Cache: database caching" ended at the table: SolidCache::Entry, one row per cached fragment, with a real byte_size column you could sum. This course opens the gem and reads the code that puts those rows there. Every claim below was run against this application's development cache database, with the query log on, using solid_cache 1.0.10.

Start with the smallest possible operation. Rails.cache.read("probe:sql:demo") produced one SQL statement:

SELECT "solid_cache_entries"."key", "solid_cache_entries"."value"
FROM "solid_cache_entries"
WHERE "solid_cache_entries"."key_hash" IN (-9004577815424453708)

Read the WHERE clause again. The key you passed in isn't in it. The database is asked for a 64-bit integer it has never been told about, and that integer is the subject of this lesson.

The table and its indexes

This app's db/cache_schema.rb is what the gem's install generator wrote, unchanged:

create_table "solid_cache_entries", force: :cascade do |t|
  t.integer "byte_size", null: false
  t.datetime "created_at", null: false
  t.binary "key", null: false
  t.bigint "key_hash", null: false
  t.binary "value", null: false
  t.index ["byte_size"], name: "index_solid_cache_entries_on_byte_size"
  t.index ["key_hash", "byte_size"], name: "index_solid_cache_entries_on_key_hash_and_byte_size"
  t.index ["key_hash"], name: "index_solid_cache_entries_on_key_hash", unique: true
end

key and value are binary, which is bytea in Postgres. There's no index on key at all. There's a unique index on key_hash, a second index on byte_size, and a composite index on the pair. The read path uses only the unique one. Lesson 4 is about the other two, and they exist for a reason that has nothing to do with reading.

Where the number comes from

The gem's SolidCache::Entry, in app/models/solid_cache/entry.rb, computes the hash like this:

def key_hash_for(key)
  # Need to unpack this as a signed integer - Postgresql and SQLite don't support unsigned integers
  Digest::SHA256.digest(key.to_s).unpack("q>").first
end

SHA-256 of the key gives 32 bytes. unpack("q>") reads the first 8 as a signed, big-endian 64-bit integer, and the comment explains the sign. Postgres has no unsigned bigint, so the top bit becomes a sign bit and half of all keys hash negative. That's why the captured query carries a negative number. Run it yourself and the two agree:

SolidCache::Entry.send(:key_hash_for, "development:probe:sql:demo")
# => -9004577815424453708
Digest::SHA256.digest("development:probe:sql:demo").unpack("q>").first
# => -9004577815424453708

Notice the development: prefix. Before hashing, the store normalizes the key the way every ActiveSupport::Cache::Store does: it expands arrays into strings and prepends the namespace. Solid Cache also forces the result to binary encoding. The namespace comes from this app's config/cache.yml, namespace: <%= Rails.env %>, which is why every row in the development database starts with development:.

One more normalization is specific to this store. Keys longer than 1024 bytes (DEFAULT_MAX_KEY_BYTESIZE) are cut and given a suffix, so two long keys with the same first kilobyte still hash differently:

def truncate_key(key)
  if key && key.bytesize > max_key_bytesize
    suffix = ":hash:#{ActiveSupport::Digest.hexdigest(key)}"
    truncate_at = max_key_bytesize - suffix.bytesize
    "#{key.byteslice(0, truncate_at)}#{suffix}".b
  else
    key
  end
end

A 2000-character key came out at 1024 bytes, ending in :hash: followed by a 32-character hex digest of the full key.

Why hash the key instead of indexing it

A B-tree over key would work. It would also be a B-tree over binary strings of up to a kilobyte each, compared byte by byte, with the index growing with the keys. A B-tree over bigint holds eight fixed bytes per entry, compared as a single machine integer. The hash trades a tiny chance of collision for an index that's small, dense and fast however long your cache keys are.

Here's the trade, measured on this app's development table when it held about two thousand rows:

EXPLAIN (ANALYZE, BUFFERS) SELECT key, value FROM solid_cache_entries
  WHERE key_hash IN (-9004577815424453708)

Index Scan using index_solid_cache_entries_on_key_hash on solid_cache_entries
  (cost=0.28..8.30 rows=1 width=870) (actual time=0.008..0.008 rows=0 loops=1)
  Index Cond: (key_hash = '-9004577815424453708'::bigint)
  Buffers: shared hit=4
Execution Time: 0.014 ms

EXPLAIN ANALYZE SELECT key, value FROM solid_cache_entries
  WHERE key = '\x00'::bytea

Seq Scan on solid_cache_entries  (cost=0.00..1101.42 rows=1 width=870)
  (actual time=3.144..3.144 rows=0 loops=1)
  Filter: (key = '\x00'::bytea)
  Rows Removed by Filter: 2018
Execution Time: 3.152 ms

Four buffer hits and 14 microseconds through the index, against a full scan that touched every row. The gap widens as the table grows, and a busy production cache can hold millions of rows.

What a collision would do

Two keys can share the first 8 bytes of their SHA-256. The row stores the full key so that this degrades safely. On read, Entry.read_multi returns {stored_key => value}, and the store looks that hash up by the key it asked for. A colliding row has a different stored key, so the lookup returns nothing and the read is a miss, never a wrong value. On write, the upsert in lesson 2 replaces the colliding row, so the other key becomes a miss too. With n rows, the chance that any pair collides is roughly n² / 2^65: about 3% at a billion rows, effectively zero at a million.

Many keys, one statement

read_multi is the same query with a longer IN list. The gem builds and memoizes one SQL template per key count, so read_multi with three keys reuses a cached string with three placeholders:

SELECT "solid_cache_entries"."key", "solid_cache_entries"."value"
FROM "solid_cache_entries"
WHERE "solid_cache_entries"."key_hash" IN (-1024625935248787865, 3052837480685391812, 4478237884703623321)

Above MULTI_BATCH_SIZE, 1000 keys, it slices into several statements. Every one runs inside without_query_cache, which is uncached(dirties: false). Active Record's per-request query cache must not serve a stale cache row, and cache reads must not count as writes that clear it.

The cache in front of the cache

SolidCache::Store prepends ActiveSupport::Cache::Strategy::LocalCache, and this app's middleware stack (bin/rails middleware) includes ActiveSupport::Cache::Strategy::LocalCache::Middleware. Inside one request, repeated reads of the same key hit an in-memory hash after the first query.

Measured: three reads of one key in a bin/rails runner script issued three SELECTs. The same three reads inside Rails.cache.with_local_cache { ... } issued one. It can't help across different keys. That's why the junior course "ActiveRecord querying: N+1 detection" saw one SolidCache::Entry Load per course card on the roadmap: each card has its own key, and each key is its own index lookup.

What this course covers

1. This lesson: the read path, key_hash, and the one index reads use.
2. The write path: a single upsert, compression, and where byte_size comes from.
3. FOR UPDATE, and the ten-thread test that shows where unless_exist and increment stop being atomic.
4. Estimating the size of the whole cache from the two indexes reads never touch.
5. Expiry: first in, first out by id, not least recently used, and why there's no index on created_at.
6. Failsafe, shards, and the background thread that does the deleting.
7. One fetch traced end to end, and the choice between this and Redis.

Lesson 2 starts where this one stopped: the row a write puts behind that integer, and the one column the write path is careful never to update.