Accelerating osquery with Vector Gateway Interface

Want to have realtime access to system metrics through a convenient metadata layer with low latency? Let's mash up Trilogy and Query.Farm's Vector Gateway Interface (VGI) and see what we can do.

Tips

Query.Farm builds open-source connectivity for the modern query stack, centered around DuckDB today; Trilogy is a typed SQL that operates on concepts your users understand, not tables, enabling frictionless SQL-centric data work.

Trilogy's "concept first" semantic modeling still needs places to get data from; these can be warehouse tables or scripts written in Rust or Python that return Arrow batches.

These are very common edge assets—for example, we might fetch a remote CSV or API, and emit that to merge with local data.

A fun example of this is the osquery model in our public model repository. osquery returns system-level metrics as SQL tables; we've wrapped this in Trilogy to make certain analysis easier.

What is osquery?

osquery is an open-source tool that exposes operating-system state—running processes, network connections, installed software—as SQL tables. It began at Facebook, which open-sourced it in October 2014 for infrastructure monitoring and security analysis. Its interactive shell, osqueryi, lets you explore your own machine with SQLite SQL; its daemon, osqueryd, runs scheduled queries for ongoing monitoring.

For a taste, this small slice of our osquery model maps a Python script's Arrow output to typed process concepts, then queries them to list five running processes:

key pid int;
property pid.name string;

datasource processes (
    pid: pid,
    name: name,
)
grain (pid)
file `./ingest/osquery.py#process`;

select pid, name
order by pid asc
limit 5;

The file binding selects the script's process source; the query uses the concepts mapped by the datasource. This example assumes you're in the osquery model directory with Python datasources enabled.

Asking about the machine

Of course, the point of osquery is to expose system metrics in SQL. What does Trilogy add beyond making those metrics available in DuckDB? Let's try a more complicated question:

Which five applications have the highest combined memory usage across all their child processes?

import process as p;

select
  p.app.pid,
  p.app.name,
  p.process_count,
  p.total_resident_mb
order by p.total_resident_mb desc nulls last, p.app.pid asc
limit 5;

The process model uses self import to expose parent and application roles alongside each process's own metrics. It defines an application as the root of a launch tree below a system launcher, so its totals include child processes with other executable names. Two independently launched copies of the same program are two applications.

The same application-root and aggregation rules in raw osquery SQL look like this. You can paste this query directly into osqueryi:

Application memory rollup in osquery SQL

Run directly in osqueryi against the processes table

WITH RECURSIVE
snapshot AS MATERIALIZED (
    SELECT
        pid,
        NULLIF(parent, -1) AS parent_pid,
        name,
        CASE WHEN start_time > 0 THEN start_time END AS started_at,
        NULLIF(resident_size, -1) AS resident_bytes
    FROM processes
),
edges AS (
    SELECT
        child.pid,
        CASE
            WHEN parent.pid IS NULL
              OR parent.pid = child.pid
              OR (parent.started_at IS NOT NULL
                  AND child.started_at IS NOT NULL
                  AND parent.started_at > child.started_at)
              OR lower(coalesce(parent.name, '')) IN (
                  '[system process]', 'system', 'smss.exe', 'csrss.exe',
                  'wininit.exe', 'winlogon.exe', 'services.exe',
                  'svchost.exe', 'userinit.exe', 'explorer.exe',
                  'sihost.exe', 'launchd', 'systemd', 'init', 'kthreadd'
              )
            THEN NULL
            ELSE parent.pid
        END AS app_parent
    FROM snapshot AS child
    LEFT JOIN snapshot AS parent ON child.parent_pid = parent.pid
),
tree AS (
    SELECT pid, pid AS app_pid
    FROM edges
    WHERE app_parent IS NULL

    UNION ALL

    SELECT child.pid, tree.app_pid
    FROM edges AS child
    JOIN tree ON child.app_parent = tree.pid
)
SELECT
    app.pid,
    app.name,
    COUNT(*) AS process_count,
    ROUND(SUM(child.resident_bytes) / 1048576.0, 1)
        AS total_resident_mb
FROM tree
JOIN snapshot AS child ON child.pid = tree.pid
JOIN snapshot AS app ON app.pid = tree.app_pid
GROUP BY app.pid, app.name
ORDER BY total_resident_mb DESC NULLS LAST, app.pid ASC
LIMIT 5;

Both queries count the application root and its descendants, sum bytes before rounding to MiB, and use PID to break ties. The SQL materializes the process list once and treats unavailable counters as null.

One difference remains: on Windows, the Trilogy model supplements some unreadable process counters and start times with psutil. Raw osquery SQL cannot reproduce that extra data, so protected processes can have different totals or application assignments. Separate live queries can also see different process snapshots.

We can spin up a local MCP server to serve our model, and then start asking agents these questions. From a checkout of the public models, with osquery and uv installed:

cd trilogy_public_models/duckdb/osquery
trilogy serve . --mcp --host 127.0.0.1

This exposes the model at http://localhost:8100/mcp. The MCP endpoint requires the pytrilogy[mcp] extra; the model's README covers its other prerequisites.

However, answering a full suite of questions might take a few minutes. How can we speed this up?

Where the time goes

To understand bottlenecks, we need to understand the Trilogy resolution path for script sources.

Script sources are called inline in DuckDB and have their results piped in through stdio—or, on Windows, materialized into a temporary Arrow file and read from there. Each scan requires calling the binary or, in the case of a Python source like osquery, a uv script.

That invocation can cost several seconds on this Windows machine. We can pass local predicates down to the script call, but there's a startup overhead paid on every read.

For an interactive analysis, this might add up!

We could make this faster if we didn't have to regenerate the runtime every time. We could, perhaps, spin up a tiny server lazily and keep it around.

Rather than writing that ourselves, we can use a fantastic existing tool: Vector Gateway Interface.

An optional transport

Using VGI-wrapped sources is an optional feature; it requires opting in and using Haybarn, Query.Farm's DuckDB distribution. Trilogy's pytrilogy[vgi] extra supplies the integration and requires Python 3.13 or later. The VGI components use the Query Farm Source-Available License; they are not a default dependency.

VGI provides a standard interface for exposing APIs, services, databases, and custom code as tables and functions—exactly what we're already doing, but with an Arrow IPC protocol natively integrated with the database through an extension.

Instead of spawning each script every time we call it, we can map all our sources to a single program and spawn that as a persistent VGI worker. Lazily, of course: if you never need to read from a script, there's no reason to start it.

Before · shell

Repeat startup for every source scan

1. Trilogy query → DuckDB
2. Start uv + Python + imports
3. Run osqueryi → fresh system snapshot
4. Arrow stream → temp file on Windows
5. DuckDB reads the result; script exits

After · resident VGI

Start lazily, reuse across queries

1. Trilogy query → DuckDB / Haybarn
2. Call the resident Python worker
3. Run osqueryi → fresh system snapshot
4. Arrow IPC → DuckDB through VGI
5. Return the result; worker stays ready

The Python runtime stays warm; osquery still runs on each read. The first VGI read pays startup, and concurrent scans can use additional pooled workers.

Our model now uses one ingest/osquery.py program, with addresses such as file `./ingest/osquery.py#process` selecting a source. That means one uv environment and a shared worker pool for the model, rather than a separate environment and resident for every table. A query scanning a source twice can still need a second worker.

To opt in, the model includes trilogy.vgi.toml:

[engine]
dialect = "duck_db"

[engine.config]
enable_python_datasources = true
python_datasource_transport = "vgi"

trilogy serve . --mcp --host 127.0.0.1 --config trilogy.vgi.toml

The queries stay the same. And the data is still live: keeping Python resident doesn't cache an old process list. Each read still runs osqueryi.

About 70% less time querying

If we run a benchmark on this—replaying the queries behind an interactive agentic analysis answering ten questions about the system—we see a 70% reduction in total query runtime: 91.0 seconds down to 27.2 seconds for a warm pass through all 25 queries, or about 3.3× faster.

Those are mean pass totals from the controlled replay, not end-to-end agent response times. The final benchmark, run 5, used five fresh sessions per transport and three passes per session, alternating shell and VGI sessions. That's 750 queries with zero errors. Pass 1 starts with a cold session; passes 2 and 3 are warm.

RUN 5 · WARM QUERIES70% less time for the same workload

25 queries, alternating shell and VGI sessions. Lower is better.

Time for all 25 queries

Mean warm-pass total · seconds

Shell

91 s

VGI

27.2 s

0100 s

Typical query latency

Median of warm queries · seconds

Shell

2.23 s

VGI

1.16 s

02.5 s

Queries taking over 3 seconds

Out of 250 warm queries per transport

Shell

101 / 250

VGI

1 / 250

0250 queries

Measured on a Windows machine with constrained memory. Warm results combine passes 2 and 3: 250 queries per transport. Each pair of bars shares a zero-based scale; scales differ between panels.

The headline is the tail. Typical warm queries are about twice as fast, but shell spends much of its time in multi-second spikes that VGI doesn't have.

Pass	Shell median	VGI median	Shell p90	VGI p90	Over 3 s, shell / VGI
1 · cold session	1.76 s	1.46 s	10.7 s	2.6 s	47 / 8
2 · warm	2.22 s	1.17 s	9.2 s	1.7 s	50 / 0
3 · warm	2.24 s	1.15 s	9.0 s	1.9 s	51 / 1

Each row contains 125 queries per transport. The p90 is the latency at the 90th percentile: nine out of ten queries finish at or below it. Across both warm passes, only one of 250 VGI queries exceeded three seconds; shell had 101 of 250.

Getting the worker to stay useful

Integration was not completely smooth; we discovered a few issues.

Read once before recursing

First up, we noticed that recursive CTEs used in this model would hit the worker for every recursion. Inlining a script-backed CTE could rerun the process script on each step. That was slow, and every run took a fresh snapshot, so one query could mix several moments in time.

We optimized Trilogy to render a script-reading CTE as MATERIALIZED on DuckDB when it has multiple consumers or feeds a recursive CTE. This keeps the recursion working from a materialized result instead of repeatedly reading the machine.

Keep workers around between questions

Idle workers died after five seconds with the VGI extension's default vgi_worker_pool_idle_limit_seconds. A restart cost 3–4 seconds on Windows: uv, interpreter, imports. In real agent use, with think time between queries, nearly every query could hit this. The back-to-back replay initially hid it.

We bumped the resident worker idle timeout to an hour (POOL_TIMEOUT 3600) to keep a local instance responsive. A resident worker only helps if it stays resident!

Don't run the source just to discover its schema

Tracing the process-tree query showed another surprise: the source ran during bind to discover its schema, before the actual scan. With two scans and a second pooled worker, we could end up doing extra reads serially.

When the source declares its schema and the request cannot change it, bind can answer from that declaration. The Python and Rust libraries now do this, so the scans can run concurrently without first fetching data just to learn the column types.

Share the runtime, and start only what you need

Persistent workers cost memory. Our initial nine-script model meant nine Python residents, each holding PyArrow and its dependencies—roughly 75–95 MB apiece. Combining the sources into one program cut that duplication.

We also made the fallback bridge lazy. It had been starting at connection time even when every source could run resident, adding roughly seven seconds and 170 MB in this test. Now it starts only if a query needs a source that cannot serve itself.

One final trap: a resident process can keep serving old code after an edit. Trilogy now checks the entry script and neighboring Python modules before each query, detaches the superseded worker, and includes a version digest in the launch command. Changing the command matters because VGI pools workers by command line.

Back to the agent

In our first tests, the agent did a bulk fetch and worked through the analysis in Python and PowerShell. Transport latency can't matter much if the agent leaves!

So for our evaluations, we instructed it to work through the MCP server and show its work: answer each question using the model, and include the Trilogy query behind each answer.

The ten-question agent prompt

You're connected to a Trilogy MCP server at http://localhost:8100/mcp that models this Windows machine through osquery. Answer each question using only the Trilogy MCP tools (list_models, describe_model, get_syntax, run_query). No Python, PowerShell, or other shell commands. Start by listing the models and reading the syntax guide. For each answer, give the Trilogy query you used. If the model can't answer something, say what's missing rather than working around it.

1. Which five running processes are consuming the most physical memory?
2. Which five applications have the highest combined memory usage across all their child processes?
3. Which five processes have accumulated the most CPU execution time?
4. Which five processes currently have the most open network connections?
5. Which running processes are listening for incoming TCP connections, and on which ports?
6. Which five processes have written the most bytes to disk since they started?
7. Which five running processes have been alive the longest?
8. Which running processes were launched from temporary directories?
9. Which installed browser extensions have access to the most permissions?
10. Which applications or services are configured to start automatically when Windows boots or a user signs in?

At the end, list anything that was slow, confusing, or missing from the model. Try to do as much analysis as possible through the provided model and Trilogy queries.

Our first test with the agent, working primarily in PowerShell, took about ten minutes. The last run, with optimized VGI, was close to interactive.

With every fix, VGI is faster on every query

The final run's per-query medians break down like this. Ranges cover different queries in each group, not confidence intervals; speedups compare each query with itself.

Source / query	Shell median	VGI median	Speedup
Host, user, interfaces, connection counts	0.66–1.9 s	0.25–0.31 s	2.6–7.6×
Browser extensions, autostart	0.77–0.94 s	0.34–0.52 s	1.8–2.3×
Listening ports: count / with processes	0.68 / 4.8 s	0.26 / 1.4 s	2.6 / 3.4×
Process: 12 queries	1.6–4.7 s	1.16–1.42 s	1.4–3.7×
Connections joined with processes	2.3–2.4 s	1.3–1.4 s	1.7–1.8×
Process tree: 3 queries	5.3–6.0 s	1.55–1.73 s	3.3–3.8×

VGI removes runtime startup, not the script's own work. Light sources still take about 0.25–0.3 seconds resident; process reads cost about 1.2 seconds, including osquery and filling otherwise unreadable counters with psutil.

Benchmark notes and sources

Benchmarks are hard; absolute timings were noisy. We alternated shell and VGI runs and repeated trials to minimize background effects on a Windows machine with constrained memory.

The uv package cache was warm. A first-ever run also builds the script environment, which took about 11 seconds here. The benchmark used Haybarn 1.5.5rc1. These results describe the integration tested on October 16, 2026, with the changes in the linked PRs; an older installed release may not include them.

• Trilogy VGI implementation and transport work (#741), including the replay harness in local_scripts/vgi_benchmark/ and the transport contract in docs/script_io.md.
• osquery model changes (#125).
• Vector Gateway Interface and Haybarn getting started.