Skip to content

Query execution

This page explains what happens between pressing Run and seeing results — the path a query takes through the control plane and an agent.

Lifecycle

  1. The browser submits SQL plus a chosen agent to the control plane.
  2. The API checks workspace membership, validates the SQL against the allowlist (parse-only), and confirms the agent is connected and compatible with the workspace backend.
  3. The API dispatches the query over the agent's WebSocket.
  4. The agent admits the query (sizing memory, queueing if needed), executes it, materializes the result to Parquet, and reports back.
  5. The browser polls for status, then pages result rows on demand.

The SQL allowlist

Only data statements (SELECT, INSERT, UPDATE, DELETE, MERGE) and catalog DDL (CREATE, ALTER, DROP) reach an agent. Sandbox escapes such as ATTACH, COPY, LOAD, SET, and PRAGMA are rejected at the API boundary. See SQL support.

Admission control and concurrency

Each agent runs queries under admission control so it never oversubscribes memory and gets OOM-killed. The agent's memory budget is cgroup-aware (it respects a container/pod limit). Capacity is sized one of two ways:

  • auto (default) — the agent runs EXPLAIN and estimates a query's peak memory from the optimizer's plan, then reserves a proportional "T-shirt" slice; cheap queries pack in, heavy ones queue. This applies to one-shot queries and to statements run inside a SQL session — a session grows to fit each statement and shrinks back between them, rather than holding one size for its whole life.
  • Static slot ladders — a fixed weighted split (single, equal_2, decaying_2, decaying_3). Slots are fixed, so a session under a static profile holds its whole slot for its lifetime.

The profile is switchable at runtime and applies per agent. See Runbook §6 and Scaling compute.

What a query is actually given

A reservation has two parts, and they answer different questions.

Required memory is what the query must have in order to finish — the estimate above, snapped to a bucket. It is what the admission gate blocks on, and it is never taken away once granted. The sum of every running query's required memory always stays inside the agent's budget; that is the guarantee that stops the agent being OOM-killed.

Required memory is capped (SESSION_MAX_BUCKET_FRACTION, a third of the budget by default), and the cap is about concurrency rather than safety. An estimate is a guess made before the query runs, and cardinality estimates are routinely out by an order of magnitude; letting one claim most of the budget means everything else queues behind a number that may be wrong. Capping it guarantees at least three statements can run at once. It does not cap what a query uses — elastic memory below still lends it whatever is free — so on an idle agent a heavy query gets the same memory it always did. It only stops one query's guess from serializing the agent.

Estimates are remembered across sessions, keyed by the query text together with the catalogs and schema it binds against, so the same query is planned once rather than once per session. Estimating is also bounded: if DuckDB takes too long to plan a query, the agent stops waiting and sizes it from a default instead. That costs the agent a little capacity, so it is reported as duckhaven_agent_estimates_abandoned in monitoring.

Learning from what a query actually used

The plan-based estimate is deliberately pessimistic: it assumes every blocking operator in the plan holds its full estimated row count at once. On a TPC-H run at scale factor 10 that came out five to eleven times the memory the queries really used — one of them reserved 9.8 GB to peak at 875 MB. Because the estimate picks the bucket, and the bucket decides how many queries fit at once, that overshoot is paid in concurrency by everything else on the agent.

So the agent measures. Once a query has run, its observed peak memory is remembered against the same key as its estimate, and the next query of that shape is sized from the measurement instead of the plan — the approach SQL Server calls memory grant feedback. A margin is added on top (GRANT_FEEDBACK_SAFETY_MULTIPLIER), because a shape covers every query with that text and not every run sees the same data. If a query spilled to disk, its grant was too small rather than its peak informative, so the agent remembers the grant and the margin pushes the next run into a larger bucket; that is the only way a remembered size grows.

Measurements expire on the same schedule as estimates, and any DDL or DML drops them outright — a write changes how much data the next query of that shape will hold. Only queries that return a result set are measured, so CREATE TABLE/INSERT and session commands like USE keep their existing sizing. Set GRANT_FEEDBACK_ENABLED=false to size purely from the plan.

Sessions that run many statements learn less

DuckDB reports peak memory as a high-water mark for the whole connection rather than per statement, so the agent can only trust the figure for the first query that runs on a connection. A client that opens a session per query — which is where this pathology showed up — measures every one; a long-lived session measures its first statement and then relies on what other sessions have already learned.

Elastic memory is spare budget lent to a query on top of that. DuckDB's memory limit does not only cap operator memory — it also sizes the cache DuckDB keeps of the Parquet files it has read from object storage. A query sized to its operators alone has nowhere to keep that cache, so every scan goes back to the object store and re-decompresses data it has already read. Lending it the agent's idle memory removes that cost, and costs other tenants nothing: elastic memory is revocable, so the moment another query needs those bytes the agent takes them back (the lender simply loses some cache) rather than making the newcomer queue. A query never waits on memory that is only being used to cache with. Elastic memory belongs to auto alone — a static ladder's slot is a fixed contract, which is the reason to choose one, so it neither grows nor shrinks. On a static profile a query gets exactly its slot's share, and a small slot on a small agent may be too tight to cache with.

No query can take more than a fair share of the lendable memory — the agent's budget divided by the number of live sessions. Without that bound the first query to ask takes everything free and every session behind it runs on the bare idle minimum, which is far worse for everyone than an even split. The share is recalculated every time memory is handed out, so it shrinks as sessions arrive and each holder gives the excess back when its next statement finishes.

When the agent is full

Sometimes there is simply not enough memory to go round — twenty concurrent scans over a large table do not fit in a small agent under any allocation. A query that cannot reach a workable fraction of what it asked for waits for room rather than running in a size it cannot work in, because a query squeezed into the idle minimum spills to disk so hard that it hurts everything else running beside it. It stops waiting as soon as memory frees up, when its own timeout would be exceeded, or immediately if nothing else is running (in which case no memory is going to be released and waiting could only make things slower).

A waiting query gives its memory back while it waits, keeping only the idle minimum. That matters more than it sounds: a query that slept holding a partial allocation would be holding exactly the memory it — and everyone behind it — was waiting for, and a group of them can end up holding all of it between them while all of them wait. Waiters are then served one at a time, in the order they arrived, so whoever is at the front gets the whole of what is free rather than every waiter getting a useless slice of it.

Waiting is visible rather than mysterious: every query reports how long it spent waiting for memory in its profile, so a query that was slow because the agent was busy is distinguishable from one that was slow because it was expensive, and duckhaven_agent_growth_waiting in monitoring shows how many are waiting right now. STATEMENT_ADMISSION_WAIT_S bounds the wait; setting it to 0 restores the older behaviour of always running immediately at whatever size was available.

Threads are separate from both, and work the same way under every profile: each statement is given the agent's full core count. Neither the auto estimator's buckets nor a static ladder's weights touch it — they divide memory. The container's CPU quota is the real limit on how much CPU an agent can use, and the operating system shares it out between concurrent queries, so handing one query fewer threads than the agent has cores only makes that query slower without leaving anything extra for anyone else.

Sessions keep their cache between statements

A SQL session hands its required memory back after each statement but keeps its elastic grant, so consecutive statements against the same tables do not re-read them from object storage. An idle session holding cache never blocks another query — see above.

Scheduled vs. interactive runs

Most queries are interactive: a person presses Run, picks the agent, and waits for rows. A query can also run unattended on a cron schedule. A scheduled run takes the exact same path described above — same allowlist, same agent dispatch, same admission control — with two differences: there is no waiting user (it is dispatched fire-and-forget; results stream back and are recorded), and it is tagged origin="scheduled" so History can distinguish it from a query someone ran by hand. A leader-elected loop in the control plane drives schedules, so exactly one replica dispatches a given due schedule.

Results and profiles

Results are materialized as Parquet on the executing agent; the control plane fetches and decodes pages on demand, so large results are never loaded whole. After each run the agent captures DuckDB's per-operator execution profile — see Read query profiles. Queries can be cancelled mid-flight, and a wall-clock timeout is enforced agent-side.

Column types

A finished query reports its column types alongside its rows, so a client never has to guess them from the values. The agent reads the types off the DuckDB result before writing the Parquet file, and reports them with the completion — which means they are available from the query's status as soon as it is done, without fetching a page. Both GET /api/queries/{query_id} and GET /api/queries/{query_id}/rows carry them as column_schema, a list of {"name": …, "type": …} entries.

The type is spelled exactly the way DuckDB itself prints a logical type — the same string DESCRIBE returns. That spelling is self-describing and complete, including parameterized and nested types such as DECIMAL(38,10), STRUCT(a INTEGER, b VARCHAR), ENUM('e', 'f') and INTEGER[2], so a client can map a column without a second lookup.

Capturing the types before materialization matters, because DuckDB's Parquet writer does not preserve all of them: a HUGEINT column becomes DOUBLE in the file, ENUM and BIT become VARCHAR, and a fixed-size INTEGER[2] becomes a variable-length INTEGER[]. Reading the types back out of the result file would therefore report the file's types rather than the query's.

Values are still JSON

column_schema describes the types; the row values themselves are still JSON-encoded. DECIMAL and HUGEINT values arrive as JSON numbers and lose precision at the extremes — the type tells you what the column is, but exact decimal round-tripping is not yet available. column_schema is null for statements that produce no result grid (DDL and DML) and for queries run by an agent older than this feature.