DB
Back homeUse case

Caching & real-time serving

Put the cache tier inside the database: in-memory keyed tables with microsecond point reads, read-your-writes, exact counts, and TTL eviction — that speak SQL.

The problem

Why this is hard today

The standard serving stack is two systems glued together: a cache in front of a database, kept in sync by application code. The cache is fast but dumb — no joins, no exact aggregates, no SQL — and every write path has to invalidate it correctly or serve stale data.

Cache invalidation is the hard problem for a reason: the moment the cache and the store can disagree, they eventually do, and the bug surfaces as a user seeing a value that no longer exists. Read-your-writes across two systems is a distributed-systems problem you did not sign up for.

What real-time serving needs is a store that is already in memory, already exact, and already speaks SQL — so the "cache" is just a table with a memory-resident policy, not a second system to keep coherent.

Architecture

How NYXDB does it

A keyed, row-layout table on a memory-resident policy is the cache: point reads hit memory, writes are read-your-writes because there is only one copy, counts are exact, and TTL or a byte budget bounds it — all in SQL (ADR-036).

  1. 01

    Memory-resident

    A keyed table on storage_policy='ephemeral_memory' (in-memory, resets on reboot — the cache tier) or 'memory_data' (in-memory and journal-durable — the serving tier) lives in RAM.

  2. 02

    Point read

    The latest value per key is an O(1) read — the same shape as a cache GET, expressed in SQL.

  3. 03

    Read-your-writes

    One copy means the read after a write sees it — there is no invalidation protocol to get wrong.

  4. 04

    Evict

    A ttl predicate expires entries lazily during compaction; storage_limit_bytes caps the table FIFO by byte budget.

Real SQL

In practice

In-memory keyed cache table
-- ephemeral_memory: pure in-memory, resets on reboot — the cache tier
CREATE TABLE session_cache (
  session_id String NOT NULL,
  user_id    UInt64,
  payload    String,
  PRIMARY KEY (session_id)
) SETTINGS mode='keyed', layout='row', storage_policy='ephemeral_memory';
Point GET — read the latest value
SELECT user_id, payload FROM session_cache WHERE session_id = 'sess-abc';
Bounded, self-expiring durable cache
-- memory_data: in-memory and journal-durable — the serving tier
CREATE TABLE feature_cache (
  key   String NOT NULL,
  value String,
  seen  DateTime,
  PRIMARY KEY (key)
) SETTINGS mode='keyed', layout='row', storage_policy='memory_data',
           ttl='seen < now() - INTERVAL 15 MINUTE';

Every statement follows the engine’s own test SQL shapes. See the SQL reference for full syntax.

Capabilities

What you get

In-memory tables

ephemeral_memory (cache) and memory_data (durable serving) keep hot data in RAM (ADR-036).

Microsecond point reads

Keyed point reads on warm postings.

Read-your-writes

One copy — no cache-invalidation protocol.

Exact counts

count() is O(1) and exact — not a probabilistic cache stat.

TTL & byte budgets

Lazy TTL and storage_limit_bytes bound the table.

SQL, not a KV API

Joins, filters, and aggregates over the cache.

Proof

Measured on the vetted benchmark lane

71.6ns

point consult on warm postings

in-memory keyed read

vetted table

View benchmark230 ns

keyed count(), O(1) exact

flat 1k–16k keys

vetted table

View benchmark12.45M rows/s

in-memory columnar append ceiling

narrow 4-column rows

vetted table

View benchmark

Measured on Apple M4 Max (dev), macOS — server-class validation pending. Release build, median of 5, commit-pinned (d4a3885b, 2026-07-07). Ingest figures are engine-side. See the full benchmark suite.

Learn more

Related documentation

Storage model

Policies, residency, and durability.

Tables

Keyed current-state tables.

Functions

Aggregates over the cache.

Retire the cache-in-front-of-a-database

Define a memory-resident table and serve reads from it.

Get started Read the docs