Columnar collections now take FILTER, SORT and joins in SDBQL

What a columnar collection is

A document collection stores each document whole, under a doc: key. To average one field over a million documents, the server reads a million documents.

A columnar collection has a fixed schema — INT64, FLOAT64, STRING, BOOL, TIMESTAMP, JSON — declared at creation, and lives in its own column family. Each value is stored under a key made of the column name and a time-ordered row id, col:{column}:{row_id}. RocksDB keeps keys sorted, so all the values of one column sit next to each other, and an aggregate over cpu reads the cpu range and nothing else. Each row is also written once more, whole, under col_row:{row_id}; that copy is what lists the rows.

That is the case it is built for: many rows, few columns per question — metrics, events, trades, anything you sum and average by host or by hour. For fetching whole records by key, a document collection is still the right tool.

One thing the layout implies: compression is per value, not per block. On the six rows used below, the collection's stats report 644 bytes of column values stored as 1279 bytes — a small number or a short string does not shrink, and the size prefix is added to each.

Creating one

Columnar collections are created through their own endpoint, with the schema up front, and loaded in batches:

# create the collection
curl -u admin:admin -X POST localhost:6745/_api/database/blog/columnar \
  -H 'content-type: application/json' -d '{
  "name": "metrics", "compression": "lz4",
  "columns": [
    {"name": "ts",       "type": "TIMESTAMP"},
    {"name": "host",     "type": "STRING"},
    {"name": "cpu",      "type": "FLOAT64"},
    {"name": "requests", "type": "INT64"}
  ]}'
# {"status":"created","name":"metrics","columns":4}

# insert rows in one batch
curl -u admin:admin -X POST localhost:6745/_api/database/blog/columnar/metrics/insert \
  -H 'content-type: application/json' -d '{"rows": [
  {"ts": "2026-07-27T10:00:00Z", "host": "web-1", "cpu": 41.5, "requests": 1200},
  {"ts": "2026-07-27T10:20:00Z", "host": "web-1", "cpu": 58.0, "requests": 1530},
  {"ts": "2026-07-27T11:05:00Z", "host": "web-1", "cpu": 72.5, "requests": 2010},
  {"ts": "2026-07-27T10:10:00Z", "host": "web-2", "cpu": 22.0, "requests": 640},
  {"ts": "2026-07-27T10:40:00Z", "host": "web-2", "cpu": 35.5, "requests": 820},
  {"ts": "2026-07-27T11:30:00Z", "host": "web-2", "cpu": 90.0, "requests": 2400}
]}'
# {"status":"ok","inserted":6,"ids":[…]}

The ids returned are UUID v7, so they sort by insertion time.

One query shape, then all of them

Before 0.33.0, SDBQL reached a columnar collection through a single hard-coded pattern: a FOR, a COLLECT with AGGREGATE, a RETURN, and nothing else. The generic scanner walks the doc: prefix, and columnar rows are not there, so any other query — a FILTER, a SORT, a join, even FOR x IN c RETURN x — failed with CollectionNotFound on a collection that plainly existed.

From 0.33.0, FOR checks whether its source is a columnar collection and, if so, reads the rows from the columns. Everything downstream is the ordinary executor, so every clause works:

FOR m IN metrics
  FILTER m.cpu > 50
  SORT m.cpu DESC
  LIMIT 2
  RETURN {host: m.host, ts: m.ts, cpu: m.cpu}

[
  {"cpu": 90.0, "host": "web-2", "ts": "2026-07-27T11:30:00Z"},
  {"cpu": 72.5, "host": "web-1", "ts": "2026-07-27T11:05:00Z"}
]

Columnar rows can be joined with documents. Here hosts is an ordinary document collection holding each host's region:

FOR m IN metrics
  FILTER m.cpu > 50
  FOR h IN hosts
    FILTER h._key == m.host
    RETURN {region: h.region, cpu: m.cpu}

[
  {"cpu": 58.0, "region": "eu-west"},
  {"cpu": 72.5, "region": "eu-west"},
  {"cpu": 90.0, "region": "us-east"}
]

And they work inside subqueries, the other way round:

FOR h IN hosts
  RETURN {host: h._key, busiest: MAX(FOR m IN metrics FILTER m.host == h._key RETURN m.cpu)}

[{"busiest": 72.5, "host": "web-1"}, {"busiest": 90.0, "host": "web-2"}]

A filter before a COLLECT is now just another query, too:

FOR m IN metrics
  FILTER m.ts >= "2026-07-27T11:00:00Z"
  COLLECT h = m.host AGGREGATE req = SUM(m.requests)
  RETURN {host: h, req}

[{"host": "web-1", "req": 2010.0}, {"host": "web-2", "req": 2400.0}]

The catch, stated in the changelog: there is no filter or projection pushdown yet. The FOR source reads every column of every row and hands the rows to the executor, which then filters. A LIMIT shortens the list after it has been read, not before. On a large collection, a selective FILTER in SDBQL reads the whole collection.

How aggregates run

The original shape still has its own path, and it is the one that uses the layout. When a query is exactly FOR over a columnar collection, then COLLECT … AGGREGATE, then RETURN, and every group expression is a plain column or TIME_BUCKET(column, "interval"), the executor hands each aggregate to the storage layer. SUM, AVG, COUNT, MIN, MAX and COUNT_DISTINCT are recognised; any other function sends the query down the generic path.

• Without grouping, each aggregate is one prefix scan of col:{column}:, folded as it goes — nothing is collected into memory except, for COUNT_DISTINCT, the set of distinct values.
• With grouping, storage lists the rows, reads each row's group columns by point lookup, then reads the aggregated column for each group. Each aggregate is a separate pass, and the executor merges the passes on the group key.

FOR m IN metrics
  COLLECT AGGREGATE avg_cpu = AVG(m.cpu), peak = MAX(m.cpu), n = COUNT(m.host)
  RETURN {avg_cpu, peak, n}

[{"avg_cpu": 53.25, "n": 6, "peak": 90.0}]

Three scans — col:cpu: twice and col:host: once. ts and requests are never touched.

Four aggregates that were quietly wrong

The aggregate path existed before 0.33.0, but four bugs made it return wrong results rather than errors — the kind that reach a dashboard before anyone notices.

The same queries on 0.33.0:

FOR m IN metrics COLLECT AGGREGATE total = SUM(m.cpu) RETURN {sum: total}

[{"sum": 319.5}]

FOR m IN metrics COLLECT AGGREGATE total = SUM(m.cpu) RETURN total

[319.5]

FOR m IN metrics COLLECT h = m.host AGGREGATE lo = MIN(m.cpu), hi = MAX(m.cpu) RETURN {host: h, lo, hi}

[{"hi": 72.5, "host": "web-1", "lo": 41.5},
 {"hi": 90.0, "host": "web-2", "lo": 22.0}]

The last query exercises three of the four fixes at once: both aggregates are there, h is bound from the COLLECT variable rather than the column name, and "web-1" has no extra quotes. The double-encoding was fixed in the storage layer, so the /columnar/…/aggregate REST endpoint returns clean keys too.

SDBQL or the REST endpoint

The REST endpoints did not go away, and for one case they are still the better choice. POST /_api/database/:db/columnar/:collection/query takes a single-column filter (EQ, NE, GT, GTE, LT, LTE, IN) and a column list. It evaluates the filter in storage — with a bitmap, hash, sorted, bloom or min/max index if the column has one — and reads only the listed columns of the rows that match:

curl -u admin:admin -X POST localhost:6745/_api/database/blog/columnar/metrics/query \
  -H 'content-type: application/json' \
  -d '{"columns": ["ts", "cpu"], "filter": {"column": "host", "op": "EQ", "value": "web-2"}, "limit": 10}'

{"result": [{"cpu": 22.0, "ts": "2026-07-27T10:10:00Z"},
            {"cpu": 35.5, "ts": "2026-07-27T10:40:00Z"},
            {"cpu": 90.0, "ts": "2026-07-27T11:30:00Z"}], "count": 3}

Pushdown is planned, and the source code says what it is waiting on: the projection and the filter live in clauses the FOR source cannot see yet. Until then, the rule is short: aggregates and single-column lookups are where the columnar layout pays; everything else is correct, and costs a full read.

The endpoints, index types and more examples are on the columnar storage page; the aggregate syntax is in SDBQL aggregations, and every change in the release is in the changelog.