Indexes

create index on articles (embed) @hnsw(cosine)
create index on articles (body) @text
create index on articles (body) @text(prefix=6)   -- for an inflected language
create index on products (title) @text(chars)     -- short Chinese or Japanese text
create index on articles (title) @sorted
create index on users (email) @unique             -- refused if a value is held twice

A field takes one index. @hash answers equality, @sorted ranges and order, @text ranked text retrieval, @hnsw nearest neighbours. @sorted is for int, float, timestamp and text — the types with an order.

@unique is a @hash index that refuses a second document holding a value one holds already: a put, an insert or a set that would write one is refused, and nothing of the statement is written — 409 over HTTP, as a taken id is. It answers equality and a lookup as @hash does. A block — a /batch, a run of several — is checked against its own earlier writes, so it may free a value and take it again, and a document keeping its own value is no second one. null is no value, as in SQL: any number of documents hold it. Values are one where = finds them one, so -0.0 and 0.0 collide. create index ... @unique over documents that hold a value twice is refused and names the value and two of its documents. A replica takes what its primary took and does not ask again; describe shows the index as @unique. The refusal names the document holding the value and the value, but to a scoped token it names the field alone (`users.email` is unique, and the value is taken): the row is another user's, which the token may not read.

The check asks the bucket the value would go in, so the index has to hold every document: the first write after an open builds it, where a plain @hash waits for the first read that wants it. An open costs what it did — 19 ms for an image of a million rows either way — and that first write takes 15 ms over 100 000 rows and 259 ms over a million; after it a put takes 940 ns against 850 for the same field under @hash, the price of the lookup. A collection with no unique field writes as fast as before (848 and 852 ns).

An @hnsw graph holds a vector once. Documents written with the same vector — the embedding of an empty chunk, a line of boilerplate, a zero vector — share its node, and near hands back each of them at the one distance. A node each, a vector written hundreds of times filled its neighbours' lists with copies of itself: over 20 000 rows of 128 dimensions, a third of them copies of 20 vectors, 1 812 copies were found by no search and other queries' recall fell from 0.82 to 0.67; one node each, none is missed and it is 0.87.

The same pipeline as BEIR (make beir over each set in its layout); English SciFact scores what it did, to the fourth decimal. A query over those sets takes 0.09 to 0.66 ms at the median.

get articles where title >= "k" and title < "l" count
get articles where published >= "2026-01-01" near embed $1 limit 10

await db.from('articles')
  .where('title', '>=', 'k')
  .where('title', '<', 'l')
  .count();

await db.from('articles')
  .where('published', '>=', '2026-01-01')
  .near('embed', [0.9, 0.1, 0])
  .limit(10)
  .rows();

(db.collection("articles")
    .where("title", ">=", "k")
    .where("title", "<", "l")
    .count())

(db.collection("articles")
    .where("published", ">=", "2026-01-01")
    .near("embed", [0.9, 0.1, 0])
    .limit(10)
    .rows())

db.From("articles").
	Where("title", ">=", "k").
	Where("title", "<", "l").
	Count(ctx)

db.From("articles").
	Where("published", ">=", "2026-01-01").
	Near("embed", []float64{0.9, 0.1, 0}).
	Limit(10).
	Rows(ctx)

await db.From("articles")
    .Where("title", ">=", "k")
    .Where("title", "<", "l")
    .CountAsync();

await db.From("articles")
    .Where("published", ">=", "2026-01-01")
    .Near("embed", new[] { 0.9, 0.1, 0 })
    .Limit(10)
    .RowsAsync();

try await db.from("articles")
    .where("title", ">=", "k")
    .where("title", "<", "l")
    .count()

try await db.from("articles")
    .where("published", ">=", "2026-01-01")
    .near("embed", [0.9, 0.1, 0])
    .limit(10)
    .rows()

db.from("articles")
    .where("title", ">=", "k")
    .where("title", "<", "l")
    .count()

db.from("articles")
    .where("published", ">=", "2026-01-01")
    .near("embed", listOf(0.9, 0.1, 0.0))
    .limit(10)
    .rows()

await db.from('articles')
    .where('title', '>=', 'k')
    .where('title', '<', 'l')
    .count();

await db.from('articles')
    .where('published', '>=', '2026-01-01')
    .near('embed', [0.9, 0.1, 0])
    .limit(10)
    .rows();

get articles where title >= $1 and title < $2 count
-- ["k", "l"]
get articles where published >= $1 near embed $2 limit $3
-- ["2026-01-01", [0.9, 0.1, 0], 10]

order over a @sorted field with a limit walks the index in order and stops at the page, testing the rest of the filter on each row it passes; without one, every match's key is read and only offset + limit of them are put in order. A filter that matches too little to fill the page within an eighth of the collection sends the walk back to the scan: over a million rows one matching nothing cost 152 ms against the scan's 121, while one matching 1% fills the page in 0.29 ms against 125. A range on a @sorted field is read from the index as a set of ids — for a filter, a count, or the allowed set of a filtered near.

A comparison with now() — run_at <= now(), at >= now() - 3600000 — is a range of the index as one with a literal is: the time is worked out once, before the query is planned, and is the statement's one time. Left a call, a queue's claim walked past its ready jobs into the leased and delayed ones after them, testing each.

The planner prefers a smaller set when it has one: an equality over a @hash field, or a narrow range on another @sorted field, names fewer rows than the walk would pass, so those are sorted instead. A range wider than half the collection is left to the scan, which is cheaper than gathering and sorting that many ids.

create index on posts (owner, created desc) @sorted        -- built beside the database on a server
drop index on posts (owner, created)                        -- by its fields
explain get posts where owner = 7 order created desc limit 20
-- order: walked the index on (owner, created desc), owner fixed, 20 of 100 rows read, 20 kept

A key is its fields' values laid end to end, each written so that the bytes order as the values do — an int's sign turned over, a float's bits made to order, a text escaped and ended, a descending field's bytes inverted — so an equality on its first fields is a run of the keys and a range on the next one is a run inside it. A text in a collation is kept as written and compared in it. null stands below every value of a field, as order puts it; a row holding a float's NaN, which compares equal to everything, or a json value no order has a place for (a boolean, a list, an object, an int past 2⁵³) is held apart: while one is, the index is walked for no order, and a filter's run takes those rows with it, each tested.

The planner takes the and chain's terms in the index's order — an equality, or an in whose every element it can say exactly, on each field as far as one is fixed, then the comparisons on the next — and tests every other term row by row; a term it cannot say exactly (a null, 12.5 against an int) is left to that test, so the answer never changes. An in makes a run for each element, 256 at most together; under an order on the fields after it the runs are walked together, merged by those fields. Of the plans, the narrower set wins by exact counts: the run is two searches, a @hash bucket its length, so a filter the index says whole reads the page and nothing more, and one it does not is walked only while its run is no longer than any set another index names — and gives up past an eighth of the collection, as a @sorted walk does. A count the index says whole is counted off it without a row read. explain names the index, the fields fixed, the runs and the range.

@unique over several fields is asked before every put, insert and set: a second row holding the same values in every part is refused, 409 over HTTP, naming the fields and the other row — to a scoped token the fields alone. A null in any part is no value. put ... if absent passes over the row holding them, and if absent else set sets it. create index ... @unique over rows already holding a key twice is refused naming two of them.

It is derived data, built from the documents by the first read after an open, kept on every write, put back with a block that does not land, and never written to the file. A field renamed keeps it under the new name; a field dropped takes it off. A sync replica builds what the schema declares, a unique one as an ordered one, as its @unique fields are @hash.

Writes pay for it: a lone put into those million rows took 715 ns with no index, 851 beside created @sorted and owner @hash, and 1 561 beside @sorted(owner, created desc), whose first field puts each write anywhere in it. A unique one asks its run before each write: 1 745 ns a put against 1 285 for @unique on the two joined into one field, and a read by both fields 2.5 µs against 1.3 by the joined field — a hash index over several fields would answer that read sooner, by a microsecond, and was not worth a third index's code beside this one (make composite-bench).

put sessions {user: "ada", seen: now()}
set sessions {seen: now()} where user = "ada"        -- seen again: lives on
get sessions where user = "ada"                       -- nothing once 30 minutes pass

await db.from('sessions').insert({ user: 'ada', seen: expr('now()') });

await db.from('sessions')
  .where('user', 'ada')
  .update({ seen: expr('now()') });

await db.from('sessions').where('user', 'ada').rows();

db.collection("sessions").insert({"user": "ada", "seen": expr("now()")})

(db.collection("sessions")
    .where("user", "ada")
    .update({"seen": expr("now()")}))

db.collection("sessions").where("user", "ada").rows()

db.From("sessions").
	Insert(ctx, fenecdb.D("user", "ada", "seen", fenecdb.Expr("now()")))

db.From("sessions").
	Where("user", "=", "ada").
	Update(ctx, fenecdb.D("seen", fenecdb.Expr("now()")))

db.From("sessions").Where("user", "=", "ada").Rows(ctx)

await db.From("sessions")
    .InsertAsync(new { user = "ada", seen = Computed.Expr("now()") });

await db.From("sessions")
    .Where("user", "ada")
    .UpdateAsync(new { seen = Computed.Expr("now()") });

await db.From("sessions").Where("user", "ada").RowsAsync();

try await db.from("sessions")
    .insert(["user": "ada", "seen": .expr("now()")] as Value)

try await db.from("sessions")
    .where("user", "ada")
    .update(["seen": .expr("now()")] as Value)

try await db.from("sessions").where("user", "ada").rows()

db.from("sessions")
    .insert(mapOf("user" to "ada", "seen" to Computed.expr("now()")))

db.from("sessions")
    .where("user", "ada")
    .update(mapOf("seen" to Computed.expr("now()")))

db.from("sessions").where("user", "ada").rows()

await db.from('sessions')
    .insert({'user': 'ada', 'seen': Computed.expr('now()')});

await db.from('sessions')
    .where('user', 'ada')
    .update({'seen': Computed.expr('now()')});

await db.from('sessions').where('user', 'ada').rows();

put sessions {user: $1, seen: now()}
-- ["ada"]
set sessions {seen: now()} where user = $1
-- ["ada"]
get sessions where user = $1
-- ["ada"]

create index on events (at) @ttl(7d)                  -- an existing timestamp field
alter collection sessions alter field seen @ttl(1h)   -- another expiry
alter collection sessions alter field seen @sorted    -- none: the rows live

A read of the collection — get, count, an aggregate, near, match, a lookup level, an inner get, a set or del by filter, and a subscription's shape — has not (seen <= now - ttl) added to its filter, the way a token's rule is added: a row is gone at the moment its time plus the ttl is reached. A row whose field is null has no time to expire from, and lives. In the browser the module has no clock: now is the page's, Date.now() unless db.now is set, as every time is handed to it; the page deletes nothing, and a row past its time stays in its file, out of every read. A live query in the page is woken by writes, not by time passing.

@ttl is an ordered index whose rows expire, so the field also does everything @sorted does, and a collection's rows expire by one such field. The sweeper deletes a range of it at a time rather than scan for the rows past their time: found under the read lock, deleted under the write lock 1 000 at a time, each batch a block of ordinary deletes, its fsync run once the lock is let go. A replica never sweeps — its writes are its primary's, the sweep's among them — and neither does a tenant that follows another node. The server's Idempotency-Keys are kept this way.

The ttl is a kind of index in the file (kind 9, the milliseconds behind it), so a binary from before it refuses the file rather than hand out the rows past their time. A write takes the place of a row past its time: an insert naming its id writes over it, and a @unique value it holds is let go of with it, the row deleted in the same statement — so a lock taken by insert and left to expire can be taken again at once (Instead of Redis). BM25's statistics count the row until the sweep.

Reading what expired: the reaper

A row past its time is out of every read, so a hold that lapsed cannot be found to give its money back, nor a reservation its stock. expired() in a filter reads those rows — and only those, where the filter says so — wherever a filter goes: get, count, a lookup level, an inner get, set and del. It is until <= now - ttl, a range of the field's ordered index; not expired() is what a read finds without it.

create collection holds (account text @hash, amount int, until timestamp @ttl(1ms))

get holds select id, account, amount where expired() limit 100   -- what lapsed

-- each given back once, in one block (a /batch): the delete is the guard
del holds where expired() and id = $1 require 1
set accounts {held: held - $2} where ext = $3 and held >= $2 require 1

A capture needs no guard of its own against the lapse: get holds where id = $1 limit 1 require 1 finds no hold past its time. Two reapers, or a reaper and a capture, racing for one hold: the del ... require 1 lets one through, and the other's block is put back whole. The sweeper leaves a row a minute past its time before it deletes it, so a reaper that comes by once a minute sees every one; a row it misses for longer is deleted unseen, and what it held is the reconciliation's to find. A scoped token reads them only by the policy's expired grant (Scoped access). Without @ttl on the collection, expired() is an error.