Rows Tables

The second table family. A timeseries table observes ("entity X looked like this at time T"); a rows table registers — one live row per id, the current state. Device registries, name↔id mappings, configuration, catalogs: the data an IoT stack usually parks in a separate document store lives here, next to its telemetry.

CREATE ROWS TABLE devices (name TEXT, model TEXT, active BOOL);

The id is the engine's

Every rows table carries an implicit, immutable xid. The engine mints it on INSERT and returns it in the result — MongoDB's _id pattern:

INSERT INTO devices (name, model) VALUES ('Vehicle 1', 'S8');
-- → xid: 019f31db42672981e4f90c0975200000

The id is an XID: a 128-bit, time-ordered identifier rendered as 32 lowercase hex characters — [48-bit unix ms][64-bit sequence][16-bit node id]. Because the timestamp leads, ids sort in insert order (string order = numeric order = time order): id indexes append instead of splitting random pages — in the engine and in every client replica — and the id itself tells you when the row was born. The sequence reseeds randomly each millisecond and increments within it, so same-millisecond inserts stay ordered and collisions are practically impossible. The node id is 0000 today; it is the reserved seat for multi-node writing.

You can never supply, change or reuse an id; an xid column in INSERT, SET xid in UPDATE, or a reserved (xid, x_…) column in CREATE are all hard errors. Natural keys (a DTC code, a serial number) are ordinary columns of your own — look rows up through them:

SELECT * FROM devices;                          -- every current row
SELECT * FROM devices WHERE xid = '019f...';    -- by the engine's id
SELECT * FROM devices WHERE name = 'Vehicle 1'; -- by your own column (scan)
SELECT * FROM devices WHERE revision > 41250;   -- numeric range (scan)

One column comparison per statement: = works on any column; >, >=, <, <= need a numeric one. A range result is ordered by the compared column (ascending), so LIMIT pages it deterministically.

Sync-native: x_rev, x_deleted, VACUUM

Every rows table carries two more implicit columns, as engine-owned as the id: x_rev, the table's monotone write sequence (stamped on every INSERT, UPDATE and DELETE), and x_deleted, the soft tombstone. They never appear in SELECT *, cannot be declared, written or altered — ask for them by name.

DELETE is soft: the row leaves every normal read (scans, point reads, count(*), natural-key lookups) but its trace stays. A statement whose WHERE filters on x_rev or x_deleted is the feed — it sees deleted rows too, which is how deletions travel to replicas:

SELECT xid, x_rev, x_deleted, code FROM dtc WHERE x_rev > 41250 LIMIT 500;
-- apply the batch, advance the cursor to its max x_rev, repeat

The feed is ordered by x_rev and pages with LIMIT; a client that remembers the highest x_rev it has applied pulls everything newer — inserts, updates and deletions — with one idiom. Under concurrent writers the feed answers only up to its watermark (the last write sequence that has fully landed), so an advancing cursor can never skip a row whose write is still in flight — the next pull sees it.

Push rides x_rev too — it is the row's version. Carry the x_rev your replica knows and the engine writes only if it is still current:

UPDATE dtc SET name_center_tr = '...' WHERE xid = '019f...' AND x_rev = 41250;
-- tag UPDATE 1: yours won (the row moved to a new x_rev)
-- tag UPDATE 0: someone wrote first — nothing overwritten, resolve and retry
DELETE FROM dtc WHERE xid = '019f...' AND x_rev = 41250;  -- same contract

No silent last-write-wins: a stale push is reported, not applied.

Tombstone traces live forever by default. VACUUM <table> purges them physically and records the highest purged x_rev as the table's vacuum horizon: a feed cursor below the horizon is refused (the deletions it would need are gone) — that client resyncs in full. Vacuum when every replica has caught up, and the contract stays airtight.

A full scan (no xid filter) resolves the latest of every column in a single pass, so it stays linear even after the delta has folded into a segment.

Counting

count(*) returns the live row count — the whole table or a filtered slice:

SELECT count(*) FROM devices;                        -- how many rows
SELECT count(*) FROM devices WHERE region = 'north'; -- how many match

It rides the same single-pass scan, so it is linear (≈0.1 s for 20k rows), not a probe per row. The other aggregates (avg, sum, count(col)) are timeseries-only; a rows table is a current-state store, and count(*) is the aggregate it answers.

UPDATE and DELETE

UPDATE devices SET name = 'Vehicle 1B' WHERE xid = '019f...';
DELETE FROM devices WHERE xid = '019f...';  -- soft: the feed still sees it

UPDATE is a partial write: untouched columns keep their values. There is no upsert — INSERT always creates a new row with a new id, and the uniqueness of your natural keys is your application's contract (no unique constraints, no secondary indexes, no joins — by design; a relational appetite belongs to a relational database).

No time axis

FILL, STALENESS, SAMPLE BY, LATEST, ts ranges, retention, MUTABLE and ILP ingest are timeseries concepts; a rows table refuses them all. Internally a rows table rides the same mutable machinery as everything else — WAL, checkpoint and the backup contract are identical.

The pairing

The registry and its telemetry meet through the id:

CREATE ROWS TABLE devices (name TEXT, model TEXT);
CREATE TABLE telemetry (speed DOUBLE FILL HOLD) WITH (ENTITY UUID);

-- provision: register, hand the returned id to the device
INSERT INTO devices (name) VALUES ('Vehicle 1');           -- → id
INSERT INTO telemetry (entity, ts, speed) VALUES ('<id>', 1700000000000, 50);

WITH (ENTITY UUID) is the wall: that telemetry table refuses any entity that is not a UUID, so a display name can never leak into the history's key. Rename the device in devices — the telemetry never notices.