Skip to content

Latest commit

 

History

History
247 lines (202 loc) · 12.7 KB

results.md

File metadata and controls

247 lines (202 loc) · 12.7 KB

Result cache

Back to README

The notebook keeps every statement's complete result so it can show the first rows immediately and later page through, aggregate or export the rest without running the statement again. These functions exist on DuckDB 2.0 builds only.

Storing a result

select * from sqlnb_result_tee(
    (select * from orders),
    id := '5f0c2a3d9b8e4f61a7d2c0e9b4a1f387',
    meta := '{"cell":"c1"}'
);
Parameter Default Meaning
first argument The statement whose result is kept, as a table.
id required 32 lowercase hexadecimal characters, unique among the process's results.
head_rows 10240 Rows returned to the caller.
head_bytes 4 MiB Arrow buffer bytes returned to the caller.
meta '' Caller data returned by sqlnb_results(), at most 64 KiB.
max_result_bytes 4 GiB Bytes stored for this result.
max_cache_bytes 8 GiB Bytes stored for all results of the process.
ttl_seconds 86400 Seconds the result is kept after its last use. NULL keeps it until DuckDB exits.
stop_at_limit false Stop reading the input once storing stops.

The tee marks the statement whose result is kept. That statement has to return the tee's columns unchanged, as select * from sqlnb_result_tee(...) does; anything else, such as an aggregate over the tee, CREATE TABLE AS, EXPLAIN or a second tee, is an error before the statement runs.

The statement returns only its first rows: whole chunks, as DuckDB produces them, until head_rows rows or head_bytes bytes have been returned (the chunk that crosses either is still returned whole). Every row is stored, unless a limit or a failure stops storing (see Limits).

The rows returned and the rows stored are both the first rows of the statement's result. When everything is stored, the rows returned are exactly the first rows stored. When storing stopped early, the result can keep fewer rows than the statement returned, down to none: stored_rows is then at most the number of rows returned, and the cache adds nothing to them. A cache that cannot store never shortens or reorders what the statement returns.

The statement runs as it would without the tee, on all its threads, and each thread stores the rows it produces. They are stored in the statement's order: the order of an ORDER BY, of a table, or of the files and row groups of a Parquet scan. Where DuckDB gives a statement's rows no order, such as the output of a join or an aggregate, or everything when preserve_insertion_order is false, they are stored in the order they arrive.

A result becomes readable when its statement finishes. A statement that fails or is interrupted leaves nothing behind and frees its id.

How the order is kept

DuckDB numbers the batches a parallel scan hands to its threads, and delivers a statement's rows in batch order. Each thread stores Arrow batches labelled with the executor batch they come from and writes them to the file wherever there is room, without waiting for earlier rows. When the statement ends, the file's footer lists the batches in executor order, and readers follow the footer.

Only the first rows need their order while the statement still runs. The thread on the lowest executor batch returns its rows at once. Every other thread keeps a copy of the first rows of its batch, at most head_rows rows or head_bytes bytes, until the batches before it have ended or enough rows were returned. A batch that ends before the batches below it leaves its copy waiting for them. Only the lowest such batches wait, up to the one with which they hold enough rows to be the first rows by themselves; batches after that one keep no copy. The memory this takes therefore does not grow with the number of batches: at most about twice head_rows rows or head_bytes bytes wait, besides one copy per thread.

Results with at most head_rows rows and 1 MiB of Arrow buffers stay in memory. Larger results are written to <id>.arrow.tmp and renamed to <id>.arrow once complete, as an Arrow IPC file whose footer lets a read seek directly to the batches it needs.

Limits

max_cache_bytes is the caller's limit for the whole cache, not a property of one result: each tee applies the value it was given. When a tee begins, before it stores anything, the oldest results are evicted until the cache holds no more than that, so a caller that lowers its limit frees the space with its next statement, not before.

When a result would exceed max_result_bytes, or the process's results would exceed max_cache_bytes even after evicting the oldest other results, the tee stops storing. The result keeps the rows before the first batch that was not stored, a prefix of the statement's rows, and limit_reason says why. Batches are stored whole, and threads store later parts of the result while earlier parts are still being produced, so the prefix can be shorter than the limit by what the threads had in progress. The statement still runs to completion, and rows counts every row it produced. Failures to store, such as an unwritable cache directory, likewise stop storing with limit_reason = 'io'; they never fail the statement. Limits count Arrow buffer bytes; a file can exceed its limit by its footer and the framing of its batches.

The cache also leaves room on its disk. Nothing more is written to a result file once the volume that holds the cache would have less than 5 GiB free, or less than a tenth of its size on a volume smaller than 50 GiB. The result keeps what it has, with limit_reason = 'disk-space', and results that fit in memory are still kept. The free space is read when a result's file is created and again after every 64 MiB written to the cache, so several threads can take the volume a little below the floor before storing stops; once it is below, the space is read before every write until there is room again. Other results are not evicted to make room, and a volume that does not report its free space has no floor.

With stop_at_limit := true the tee also stops reading its input once storing stops, as a LIMIT would, and publishes the stored prefix. It returns its first rows in full before it stops, also when storing stopped within them. rows is then the number of rows read, a lower bound, and rows_exact is false. The option accepts any constant expression, such as Instant SQL's verdict that a statement is safe to stop early:

select * from sqlnb_result_tee(
    (select * from big_table),
    id := '5f0c2a3d9b8e4f61a7d2c0e9b4a1f387',
    stop_at_limit := system.main.sqlnb_is_read_only('select * from big_table')
);

Reading results

sqlnb_results() lists the readable results, newest first:

Column Type Meaning
id VARCHAR The result's id.
rows BIGINT Rows the statement produced (or read, when rows_exact is false).
stored_rows BIGINT Rows that can be read back. Less than rows after a limit.
stored_bytes BIGINT File size, or Arrow buffer bytes for results in memory.
in_memory BOOLEAN Whether the result is held in memory rather than a file.
limit_reason VARCHAR result-bytes, cache-bytes, disk-space, io, or NULL when every row was stored.
meta VARCHAR The tee's meta.
created_ms BIGINT Unix time in milliseconds when the statement started.
elapsed_ms BIGINT Milliseconds from the start of the statement to the end of its input.
rows_exact BOOLEAN False when stop_at_limit stopped the statement early.
last_used_ms BIGINT Unix time in milliseconds when the statement finished or the result was last read.
expires_ms BIGINT Unix time in milliseconds from which the result is no longer kept, unless it is read before. NULL when it is kept until DuckDB exits.

sqlnb_result(id, first_row := 0, row_count := NULL, row_number := NULL) reads a stored result back with its original column names, types and row order. first_row and row_count select a window, NULL row_count meaning every row from first_row on. row_number names an extra BIGINT column with each row's zero-based position in the whole result; it must differ from the result's columns. Only the batches and columns a query needs are read:

select * from sqlnb_result('5f0c2a3d9b8e4f61a7d2c0e9b4a1f387',
    first_row := 45000, row_count := 200, row_number := 'position');
select sum(amount) from sqlnb_result('5f0c2a3d9b8e4f61a7d2c0e9b4a1f387');

Columns with the same name come back with DuckDB's usual suffixes (a, a_1). TIMESTAMP WITH TIME ZONE values are stored in UTC.

sqlnb_result_drop(id) removes a readable result and returns whether it existed. Scans that already started, including scans of evicted and expired results, finish reading their rows.

Expiry

A result is kept for ttl_seconds after its last use and then expires. Its last use is the end of its statement or, later, the last time sqlnb_result read it: binding a read and starting its scan both count, so a result that is being paged through does not expire. Listing results with sqlnb_results() is not a use. The period belongs to the result: it is the ttl_seconds its tee was given, and later tees with another period do not change it.

An expired result is gone exactly as an evicted one is: it is no longer listed, reading it raises SQLNB_RESULT_MISSING:, its file is removed and its id is free. A statement that runs longer than its period is not affected; the period starts when it finishes.

There is no timer. Expired results are removed whenever the cache is used: when a tee is bound or begins, and by sqlnb_results(), sqlnb_result and sqlnb_result_drop. Until then an expired result's file stays on disk, so a DuckDB process that nobody queries keeps its last results until it exits. Times are the system clock's: setting the clock forward expires results early, setting it back keeps them longer.

Errors

DuckDB prefixes these messages with the error type, such as Invalid Input Error:.

Prefix Raised by
SQLNB_RESULT_EXISTS: A tee whose id is already used by a running or readable result.
SQLNB_RESULT_MISSING: Reading a result that never existed, was dropped, expired or was evicted.
SQLNB_RESULT_UNSUPPORTED: A tee over a column the cache cannot read back unchanged.
POLICY_BLOCKED: Any of these functions on the MCP agent connection.

Every DuckDB type is stored and read back unchanged, including nested types, UNION, UUID, JSON, HUGEINT, BIGNUM, VARIANT and GEOMETRY, except ENUM (anywhere in a column's type) and aliased types without an Arrow extension type. Cast those columns, for example to VARCHAR, before the tee.

Files and lifetime

Results belong to the process: every connection and database in it shares them, including the notebook's Quack sessions. The MCP agent connection cannot use them. Results are removed when the last database that loaded SQLNB closes, and earlier when they expire or are evicted.

Files live in a directory of their own per process, created when the first result is written to disk, under:

  • macOS: ~/Library/Caches/sidequery/sqlnb-extension/results/
  • Linux: ${XDG_CACHE_HOME:-~/.cache}/sidequery/sqlnb-extension/results/
  • Windows: %LOCALAPPDATA%\Cache\sidequery\sqlnb-extension\results\

The process holds a lock on its directory's session.lock file while it runs, and on Unix the directory and its files are readable by their owner only. A directory whose lock is free and more than a minute old belonged to a process that exited without cleaning up; it is removed when another process creates its own directory.

Storage format

Files are uncompressed Arrow IPC with batches of at most 8192 rows; a batch ends earlier when it reaches 64 MiB or its thread's executor batch ends. Batches are in the file in the order they were written, which is not the result's order: read the file through its footer, as sqlnb_result and any Arrow file reader do, not as a stream.

The first rows reach the caller before anything is written to disk, so compression would not change how quickly they appear. Measured on an Apple Silicon Mac with a warm page cache, with one thread storing the result, for 5,000,000 rows of BIGINT, DECIMAL, VARCHAR and TIMESTAMP WITH TIME ZONE (medians of three runs):

Uncompressed LZ4 frames
File size 239 MB 92 MB
Tee statement (create temp table: 308 ms) 283 ms 387 ms
Window of 200 rows at row 4,500,000 1 ms 1 ms
sum of one column 16 ms 23 ms
count(*) 12 ms 18 ms

The tee adds about 60 µs to a one-row statement, 80 µs at 1000 rows and 0.2 ms at 10,000 rows, which stay in memory.