Runtime behavior

This page documents how the ai extension executes provider calls at runtime. It is the operational reference for the retry, caching, concurrency and egress controls used by the SQL functions.

Function stability

Model-backed scalar and aggregate functions are registered as VOLATILE in DuckDB. The planner must not constant-fold or reuse their results as if provider calls were deterministic.

Local helper functions such as ai_count_tokens() and ai_is_read_only_sql() remain deterministic and do not call providers.

Prompt shape

Completion functions accept one user prompt plus an optional system prompt. Task wrappers and SQL assistant functions build provider messages from their SQL arguments: static instructions and schema context are placed in the system message when the selected provider protocol supports it, while row-specific text stays in the user message.

Multi-turn chat transcripts are deliberately outside the scalar function surface. Store or serialize the conversation in your own table and pass the conversation summary or transcript as the prompt when you need row-wise SQL enrichment.

Runtime state scope

Usage events, response-cache entries, and provider pacing state are scoped to the current DuckDB database instance. Separate embedded DuckDB databases in the same process do not share these buffers.

The affected state includes:

• ai_usage() and ai_clear_usage(),
• opt-in response cache entries used by cache := true or duckdb_ai_cache,
• bounded query-local embeddings used to deduplicate ai_similarity() inputs,
• generated SQL cache entries for successful ai_query_data() binds,
• duckdb_ai_max_concurrent_requests,
• duckdb_ai_min_request_interval_ms, and
• duckdb_ai_token_limit_per_minute.

Intra-chunk provider concurrency

Row-wise provider scalar functions prepare row inputs, options, and secrets on the DuckDB execution thread, then submit provider work to a bounded executor owned by the current DuckDB database instance. Worker threads persist across vector chunks instead of being created once per vector. The DuckDB result vectors are filled after worker completion on the execution thread.

This applies to:

• ai_complete(), ai_try_complete(), ai_complete_json(), and ai_completion_request_json(),
• task wrappers such as ai_summarize(), ai_classify(), ai_redact(), ai_translate(), and ai_filter(),
• ai_embed(), ai_embedding_request_json(), and ai_similarity(), and
• ai_sql().

The worker count is bounded by duckdb_ai_max_concurrent_requests or max_concurrent_requests := ... when configured. Without an explicit cap, the extension uses a conservative local worker count for the chunk while the provider rate limiter still controls outbound request pacing. Configured worker caps must be between 0 and 64; larger values are rejected instead of being silently clamped.

SQL-assistant table functions perform one provider call per invocation, plus up to fix_attempts correction calls when the generated SQL does not bind. Aggregate functions use one request for groups that fit max_context_chars and a bounded parallel map/reduce request tree for larger groups.

Embedding request packing

ai_embed(), ai_similarity(), classifier training, and optimized classification pack embedding inputs by provider/model option group. A request is closed when the configured input count, estimated token count, or request byte limit would be exceeded. External model options can override these limits; conservative built-in defaults apply otherwise.

ai_similarity() deduplicates both sides of every row, keeps a bounded query-local embedding cache across DuckDB vector chunks, embeds each distinct value once, and computes cosine similarity locally. Its request count therefore scales with packed distinct values rather than two HTTP requests per row. The query cache is capped at 8 MiB and the database keeps at most eight recent query caches; ai_clear_cache() clears it explicitly. Query-cache hits appear in ai_usage_summary() without increasing batch_count.

When a provider rejects a multi-input embedding request with HTTP 413 or a recognized payload/context-size error, the extension recursively bisects that request. Recoverable splits do not count as terminal failures in usage summaries. Input packing measures the encoded JSON payload, so quotes, control characters, and other escaped content are included in the byte limit. A single input that exceeds an external model's declared context or byte limit fails explicitly.

Context-safe aggregate reduction

ai_agg() and ai_summarize_agg() never truncate grouped input. Groups within max_context_chars use one final request. Larger groups are split on UTF-8-safe boundaries, mapped concurrently into compact evidence, packed, and recursively reduced until one final request fits. Every child uses the parent query's operation tree for usage attribution. Set overflow_policy := 'error' to reject an oversized group before any provider call. Hierarchical reduction also stops immediately with an explicit error when a provider's intermediate responses do not reduce either the chunk count or total byte size.

Estimated token pacing

Completion reservations include the user prompt, system prompt, response schema, and requested output limit (512 tokens when no output limit is supplied). Schema text can appear in both the system instructions and the provider's structured output field; both transmitted copies count. External model input limits use the same input estimate. max_input_tokens limits only input; context_size limits estimated input plus the requested output maximum (512 estimated tokens when unspecified). Both limits apply when a profile supplies both. Native output limits are read from the request JSON body. For embedding models, both context limits constrain input. Profiles may set token_estimate_multiplier to a finite value from 1 through 16 to scale input estimates conservatively for that provider/model; completion pacing, context checks, and embedding batching share the multiplier. Choose it from measured usage on representative prompts. It does not provide exact provider tokenization, and ai_count_tokens() retains its unscaled local estimate. Estimates still use roughly one token per four UTF-8 bytes, so they do not guarantee exact enforcement of a provider's token quota.

Generated SQL caching for ai_query_data() also uses the resolved provider, credential fingerprint, and profile options. Rotating credentials or replacing a profile therefore creates a new cache key. Recency updates take constant time; retained key and SQL text is limited to 64 MiB and 1,024 entries per database. Container overhead is additional. Oversized entries are not retained.

Cancellation and retries

Provider HTTP calls use libcurl with interrupt-aware progress callbacks. When a DuckDB query is interrupted, in-flight provider calls abort and retry sleeps are also interruptible.

Retries are disabled by default. When enabled with retry_count or duckdb_ai_retry_count, retryable HTTP failures use exponential backoff with jitter. HTTP Retry-After headers on provider responses take precedence over the configured backoff. Every retry wait, including one requested by Retry-After, is capped at 60 seconds.

Usage events keep one operation_id for all input events produced by a packed request. Hierarchical aggregate children additionally carry a parent_operation_id; retries retain the same operation ID and increment the event's retry count. ai_usage_summary() exposes both row-event calls and distinct request batches, plus bounded-buffer drop counters.

HTTP connection behavior

The extension initializes libcurl once per process, reuses a thread-local easy handle for provider requests, and attaches those handles to a shared libcurl connection cache. This allows libcurl to reuse connections across provider worker threads where the provider and libcurl build support it.

Provider calls use the configured timeout_seconds for the total request (default 120 seconds, or DUCKDB_AI_TIMEOUT_SECONDS). The connect timeout defaults to the smaller of 10 seconds and the total timeout. Set connect_timeout_seconds := ..., duckdb_ai_connect_timeout_seconds, or DUCKDB_AI_CONNECT_TIMEOUT_SECONDS to override it for provider requests.

Provider redirects are not followed. This avoids forwarding authorization or API-key headers to an unexpected redirect target.

Provider and usage-log destinations must use http or https. URLs containing embedded credentials or raw control characters are rejected before libcurl is called. The same control-character check applies to generated HTTP headers.

Provider and usage-log response bodies are limited to 64 MiB by default to prevent an untrusted endpoint from growing an unbounded in-memory buffer. Set DUCKDB_AI_MAX_RESPONSE_BYTES to a positive byte count, up to 1 GiB, when a large batched embedding response requires a different bound.

CURLOPT_NOSIGNAL is enabled so DuckDB host processes are not exposed to libcurl signal behavior during DNS or timeout handling.

Response caching

Response caching is opt-in:

SET duckdb_ai_cache = true;

SELECT ai_complete('Summarize this repeated prompt.');
SELECT ai_complete('Summarize this repeated prompt.');

SELECT * FROM ai_clear_cache();

The response cache is in-memory and scoped to the current DuckDB database instance. It is keyed by provider, model, endpoint, request payload, and response-relevant options. API keys are not stored directly in cache keys.

Cached responses still record usage events, but their elapsed time is reported as 0 because no provider HTTP request was made.

The maximum number of cached entries defaults to 1024. Set cache_max_entries := ..., duckdb_ai_cache_max_entries, or DUCKDB_AI_CACHE_MAX_ENTRIES to change the bound. Use 0 to disable response-cache storage. Cached response bodies and both retained key copies also have a hard 64 MiB per-database bound; entries exceeding that bound are not cached. Container overhead is additional.

ai_query_data() also keeps a small in-memory generated-SQL cache for successful binds. ai_clear_cache() clears the response, generated-SQL, and similarity query caches.

Typed completion functions validate their output before recording a successful usage event. Invalid JSON, schema violations, unknown classification labels, invalid booleans or scores, and record projection failures produce error events and evict the rejected response from the cache. A later call can request a fresh response. Validation failures do not automatically retry the model call.

Deterministic performance benchmark

The local benchmark uses a threaded mock embedding endpoint and reports request count, wall time, peak DuckDB thread count, peak resident memory, and dropped usage events for 1,000 and 10,000 rows. It also derives a provider cost estimate from mock token usage at a fixed price:

python3 test/benchmarks/ai_runtime_benchmark.py

It covers unique ai_embed() inputs and repeated ai_similarity() inputs. The latter uses 20 distinct values at both row counts, making a regression from query-level distinct-value scaling directly visible in http_requests.

Egress allowlisting

Use duckdb_ai_allowed_hosts or per-call allowed_hosts := ... to restrict provider and usage-log destinations:

SET duckdb_ai_allowed_hosts = 'ai-gateway.internal,api.openai.com';

SELECT ai_complete(
    'Summarize this.',
    allowed_hosts := 'api.openai.com'
);

Entries can be hostnames, host:port, full URLs, wildcard subdomains such as *.example.com, or *.

The allowlist check runs before the HTTP request, so disallowed hosts fail without sending a provider request. When the DUCKDB_AI_ALLOWED_HOSTS environment variable is set, a request must match it as well, so session and per-call allowlists can only narrow it. Stored API keys are only sent to the origin they are configured for; see security and data flow.

JSON parsing

Provider responses and JSON Schema inputs are parsed with DuckDB's vendored yyjson parser. This covers completion text extraction, token usage fields, embedding arrays, ai_complete_json() validation, and ai_complete_record() projection.

ai_complete_json() and ai_complete_record() intentionally implement a documented JSON Schema subset rather than full JSON Schema draft parity. Schema pattern checks use RE2-compatible regular expressions so validation has bounded-time matching behavior; constructs unsupported by RE2 are rejected.

Type unions accept only their listed types. Nested boolean schemas are supported: true allows any value and false rejects every value. String minLength and maxLength count Unicode code points, so an emoji counts as one and a combining mark counts separately from its base character. Numeric equality and bounds compare the original decimal values, including equivalent exponent spellings. Integer multipleOf checks use decimal arithmetic without narrowing to 64 bits. Numbers whose double conversion is non-finite are rejected. Typed integer projection preserves exact values within the signed BIGINT range; projected number fields use DuckDB DOUBLE precision. Non-integer multipleOf checks still use floating-point tolerance.

Lifetime and persisted diagnostics

ai_query_cache_stats() exposes generated SQL cache retention, bounds and lookup/eviction counters. ai_usage_totals() keeps provider row events, transport attempts, failures, cache hits and partial known-token totals independently of usage-event eviction. Counters are per database, protected by the existing state mutexes, and reset by their corresponding clear function. They are in memory; examples/resumable_enrichment.py (see the resumable enrichment cookbook) persists each batch's usage snapshot in the same transaction as its results, so totals survive process reopening and uncommitted snapshots roll back with a crashed batch.