KV Tables

The third table family. A timeseries table observes, a rows table registers; a kv table stores — one value per user-supplied string key, the Redis shape. Session tokens, leases, static-IP maps, presence, feature flags: the data an IoT stack usually parks in a separate Redis lives here, next to the registry and the telemetry.

CREATE KV TABLE cache (val TEXT, hits INT);
CREATE KV TABLE sessions (token TEXT, device TEXT) WITH (ttl='1h');

The value is ordinary columns with the same type labels as everywhere (TEXT, DOUBLE, BOOL, INT); WITH (ttl='…') is the only table option. FILL and STALENESS are read-time mechanics of the time axis — a kv table refuses them.

The key is yours

Every kv table carries an implicit string column key — the mirror image of a rows table, where the id belongs to the engine. Here you choose the address, and writing to the same key overwrites: INSERT is the upsert. The Redis verbs map onto SQL you already speak:

Redis	xcon-db
SET k …	INSERT INTO t (key, …) VALUES ('k', …)
GET k	SELECT … FROM t WHERE key = 'k'
DEL k	DELETE FROM t WHERE key = 'k'
SCAN	SELECT * FROM t — every live key, ordered
DBSIZE	SELECT count(*) FROM t

key is reserved: it cannot be declared as a column, and it must be a string. Result tags say what happened — SET 2, DEL 1.

SET — the upsert

INSERT INTO cache (key, val, hits) VALUES ('a', 'apple', 1);   -- SET 1
INSERT INTO cache (key, val, hits) VALUES ('a', 'apricot', 5); -- same key: overwrite
INSERT INTO cache (key, hits) VALUES ('a', 6);                 -- partial: val stays 'apricot'

A SET that lists a subset of columns updates just those; the others keep their values (every column is an independent stream underneath). Multi-row VALUES sets many keys in one statement.

GET, scan, count

SELECT val, hits FROM cache WHERE key = 'a';   -- GET: the point read
SELECT * FROM cache;                           -- scan: every live key
SELECT count(*) FROM cache;                    -- how many live keys

The key is the only filter. A scan comes back ordered by key (ascending), and LIMIT/OFFSET page it deterministically. count(*) is the one aggregate a kv table answers — it is a store, not an analytics table.

DEL

DELETE FROM cache WHERE key = 'b';   -- DEL 1

The key leaves every read immediately (a tombstone, physically dropped at the next merge). SET the same key later and it simply exists again.

TTL — the lease

CREATE KV TABLE sessions (token TEXT) WITH (ttl='30m');   -- s, m, h, d

With a TTL every key is a lease: a key whose last SET is older than the TTL expires lazily on read — GET returns nothing, scans and count(*) skip it. Any SET refreshes the clock, a partial SET too, so a ttl='1h' table is exactly a Redis session store: touch the key and it lives another hour.

No feed, no time axis

A kv table is a cache/store, not a replicated log: there is no sync feed and no x_rev/x_deleted — that is the rows family's contract. FILL, STALENESS, SAMPLE BY, LATEST, entity/ts predicates and ILP ingest are timeseries concepts; kv refuses them all. Internally it rides the same mutable machinery as everything else — WAL, checkpoint and the backup contract are identical, and the merge worker keeps one value per key, so the store never grows without bound.

The Redis protocol (RESP) is not spoken: a kv table is reached over pg-wire SQL like every other table — which means every language's PostgreSQL driver is already the client.