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);

Add WITH (FLEXIBLE) for schema-on-write — an INSERT naming a column the table has not seen adds it, inferring the type from the value, instead of failing. The default is strict; toggle either way later with ALTER TABLE … SET FLEXIBLE|STRICT. It suits the document-store shape, where records grow fields over time.

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.