SELECT

Reads through Tables.Execute (or Tables.Query, which is the read-only twin). Send DuckDB SQL; the plane picks the plan shape from table size, partitioning and query shape. For the RPC contract, see the Execute hub.


SELECT event_type, COUNT(*) AS n
FROM events
GROUP BY event_type
ORDER BY n DESC;

psql


psql "postgresql://$USER:$TOKEN@pg.uk-lon-1.dodil.io:5432/kb-prod?sslmode=require" \
  -c "SELECT event_type, COUNT(*) AS n FROM events GROUP BY event_type ORDER BY n DESC"

Python (psycopg)


import psycopg
 
with psycopg.connect(
    "host=pg.uk-lon-1.dodil.io port=5432 dbname=kb-prod "
    f"user={user} password={token} sslmode=require"
) as conn, conn.cursor() as cur:
    cur.execute("SELECT event_type, COUNT(*) AS n FROM events GROUP BY event_type ORDER BY n DESC")
    print(cur.fetchall())

dodil data


dodil data sql -b kb-prod "SELECT event_type, COUNT(*) AS n FROM events GROUP BY event_type"

HTTP


curl -sS -X POST "https://table.uk-lon-1.dodil.io/v1/databases/kb-prod/sql/query" \
  -H "Authorization: Bearer $DODIL_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"sql":"SELECT event_type, COUNT(*) AS n FROM events GROUP BY event_type"}'


{
  "columns": [
    { "name": "event_type", "type": "string", "vectorDimension": 0 },
    { "name": "n", "type": "long", "vectorDimension": 0 }
  ],
  "rows": [
    { "event_type": "click", "n": 184729 },
    { "event_type": "purchase", "n": 2147 }
  ],
  "rowCount": 2,
  "warnings": []
}

Freshness — `min_ulid`, not an enum

Reads are read-your-writes by default. There is no Freshness request field and no QueryStrategy enum; both were removed from the wire.

The cross-process mechanism is a session watermark:

A write returns ExecuteResponse.max_wal_ulid (or WriteAck.wal_ulid).
You pass that value back as min_ulid on the next read.
The read observes at least that ULID or fails over to the S3 WAL — never a silently-stale overlay.

Setting min_ulid forces the strong path; the eventual fast paths do not thread the watermark (dodil-tables/proto/api/tables.proto:132-140,232-241). On the Postgres wire the adapter threads it for you, which is why a SELECT after an INSERT in the same session always sees the row. The CLI’s --freshness flag is a deprecated no-op (cli-shell/cli-k3/cmd/table.go:692-693).

`ServedBy` — which tier answered

ExecuteResponse.served_by / QueryResponse.served_by (proto/api/types.proto:177-185):

Value	Meaning
`SERVED_BY_HOT`	RAM slot
`SERVED_BY_WARM`	Disk slot
`SERVED_BY_MERGED`	Overlay ∪ Delta — the strong read
`SERVED_BY_WAREHOUSE`	Delta scan — the analytical read
`SERVED_BY_WAL`	Overlay only (internal freshness)
`SERVED_BY_HOT_SHARDED`	Sharded hot-set fan-out

Read strategies

The read planner emits one of two plan shapes, and you do not request either (crates/htap-planner/src/ir.rs:443-451, query.rs:1-20):

Shape	When
`Unary`	One executor call. The default: single table, and anything with window functions, `COUNT(DISTINCT)` or joins — those are never distributed.
`Federated { partitions }`	Aggregates over a partitioned table above the distribution threshold — N parallel calls by partition value, then a reduce.

There is no FEDERATED_SCAN / FEDERATED_TO_SINGLE / UNARY_MERGED_STRONG vocabulary, and no strategy_reason field — earlier drafts of this page invented all of it.

Spooled results

Above a size threshold the plane writes result parts to object storage and returns presigned GETs on ExecuteResponse.manifest. When manifest is set, rows is empty — fetch the parts (preferred) or re-issue through Query. Never treat a manifest-without-rows as a zero-row result (proto/api/types.proto:187-200).

JSON column reads

json columns work with every DuckDB JSON operator — the plane stores them as canonical JSON text tagged for DuckDB recognition.


SELECT user_id,
       payload->>'sku' AS sku,
       (payload->>'amount')::double AS amount
FROM events
WHERE event_type = 'purchase'
ORDER BY amount DESC
LIMIT 100;

->> extracts as text — cast as needed. ->, json_extract, json_extract_string, json_each and from_json all work.

Joins, CTEs and windows

Any DuckDB-supported read SQL works. The reader registers an overlay ∪ Delta view for every referenced table — joins, CTE bodies, derived tables and WHERE subqueries alike — so plain table names are all you need (crates/reader/src/service.rs:1085-1098).


-- CTE + JOIN + aggregate
WITH purchases AS (
  SELECT user_id, payload->>'sku' AS sku, (payload->>'amount')::double AS amount
  FROM events WHERE event_type = 'purchase'
)
SELECT u.email, SUM(p.amount) AS total
FROM purchases p JOIN users u ON p.user_id = u.id
WHERE p.amount >= 50
GROUP BY u.email
ORDER BY total DESC;
 
-- Window function
SELECT user_id, occurred_at,
       LAG(occurred_at) OVER (PARTITION BY user_id ORDER BY occurred_at) AS prev_event
FROM events
ORDER BY user_id, occurred_at;
 
-- Set ops
(SELECT user_id FROM events WHERE event_type = 'click')
INTERSECT
(SELECT user_id FROM events WHERE event_type = 'purchase');

`SHOW`, `DESCRIBE`, `EXPLAIN`

All three route through Execute and come back in the rows arm (statement_kind is show_tables / describe_table / explain):


SHOW TABLES;
DESCRIBE "events";
EXPLAIN SELECT * FROM events WHERE user_id = 'u-101';

On the Postgres wire, psql’s \d and \dt work too — an emulated pg_class / pg_namespace / pg_attribute shim serves them (crates/adapter/src/introspect.rs:129,148-196).