Why Key Lookups and SQL Belong on the Same Records

Think of a cloakroom. At one window you hand over a ticket and get your coat back. Nobody searches, nobody asks what colour it was. At another window you can ask the attendant for every blue coat left after nine o’clock, and somebody has to go through the racks to answer. Both are useful. A database that only has the first window is a key-value store. One that only has the second is an SQL database.

Plenty of programs need both windows. A web app looks up a session by its token on every request, and once an hour someone wants to know how many users are active per country. A gateway stores the latest reading from each sensor and also needs the average over the last day. The common answer is two stores: something like RocksDB or Redis for the lookups, and SQLite or Postgres for the questions.

An application talks to KvSQL through two doors. The key door does get, put and get_row straight on the B+tree. The SQL door parses, plans and executes, then reads the same B+tree. Both doors share one file, the same secondary indexes and one transaction.

KvSQL’s two doors. A lookup by key goes straight to the storage tree; SQL goes through the parser and planner first. Both end up at the same bytes.

What Two Stores Cost

Two stores mean two copies of the data. Code has to write each change twice and keep the copies in step. When a crash lands between the two writes, they disagree, and somebody writes a repair job. A transaction can’t cover both stores, so “delete this user and every one of their sessions” is two operations that can half happen. The dashboard query runs on a copy that may be a few seconds or a few bugs behind.

How KvSQL Keeps One Copy

In KvSQL a table is a keyspace. Each row is stored under its primary key, in the same B+tree the key-value calls use. So there’s one set of bytes, reached two ways:

get_row("sessions", token) walks the tree to that key and decodes the row. No SQL text, no parser, no plan. In the recorded case study, that took 2.00 µs a lookup.
SELECT user_id FROM sessions WHERE token = ? is parsed (once, then cached by its text), planned as a key lookup, and reads the same leaf. The same case study measured 4.10 µs.
SELECT country, count(*) FROM sessions GROUP BY country scans that table, through the same pages.

Writes work the same way in reverse. put_row through the key door checks the row’s types and updates every secondary index, exactly as an SQL INSERT does. A single write transaction can mix both: read a cart by key, insert an order with SQL, delete the cart by key, commit once. Readers on a snapshot see the state before that commit or after it, never a mix.

Where It Fits

KvSQL fits a program that wants fast access by key and real queries over the same data, and doesn’t want to run or sync two systems. That covers an embedded store in an application, a small service that keeps its own data, and an edge gateway that collects readings from devices. Embed the engine in Rust, or run kvsqld and talk to it over HTTP from any language.

It is not a replacement for a large server database. There is one writer at a time, and SQL covers a solid subset without subqueries yet. The reference lists what’s in and what isn’t, and the case studies show it at work.