Streaming huge result sets without eating your RAM

Sooner or later someone runs SELECT * FROM events against a table that has been collecting rows since the company was founded. In a lot of database tools, what happens next is a spinner, a fan that sounds like it is preparing for takeoff, and eventually either a grid or a crash. Neither the query nor the person who ran it did anything wrong. The tool simply tried to hold the whole answer in memory before showing you any of it.

SQLly is built so that a very large result set does not turn into a very large memory problem. The grid behaves the same way whether a query returns 50 rows or 50 million. This post follows a row from the wire to your screen and explains each decision along the way, including the trade-offs, because there are some.

Step one: the driver reads in batches

SQLly speaks each database's wire protocol itself: TDS for SQL Server, the PostgreSQL and MySQL protocols, and SQLite directly, all inside a dedicated Rust engine that runs off the UI thread. There is no ODBC or JDBC layer in between, which matters here because the engine controls exactly when rows are read and handed on.

Rows come off the wire in batches with a flush timer. The batch keeps overhead low when rows arrive quickly, and the timer makes sure that a slow query still shows you what it has rather than holding rows back until a batch happens to fill. PostgreSQL used to be the exception, buffering the whole response before the first row reached the grid; that was fixed, and PostgreSQL results now stream in as the server produces them.

Step two: a channel that pushes back

Between the driver and the interface sits a bounded queue. This is the piece that does the most work while getting the least attention. If the grid cannot keep up, the queue fills and the driver waits. That backpressure is what stops a fast server on a fast network from filling your memory faster than the app can drain it.

An unbounded queue feels faster in a demo, right up to the result set that is larger than your free RAM. A bounded one means memory use is set by the size of the queue, not the size of the answer.

Step three: past a threshold, spill to disk

Small results stay in memory, where they are cheapest to work with. Once a result set grows past 50,000 rows or a 64 MiB per-set memory budget, whichever comes first, SQLly writes it to a typed binary spill file with its own row-offset index and releases the in-memory copy.

Both thresholds are yours to change in Settings › Performance › Query Result Optimizations: the row threshold, and the per-set memory budget anywhere from 1 MiB to 2 GiB. Raise them if you have memory to spare and want in-memory features on bigger sets; lower them on a modest machine.

Spill files are treated as the data they are. They are written owner-only, in a spill directory that is locked down the same way. They belong to the run that made them: closing the tab or starting a new query deletes them, and a superseded query cleans up after itself even if its results arrive late. Files orphaned by a crashed or killed session are swept on the next launch once they are an hour old.

Step four: the grid pages from the file

A spill-backed result set keeps a window of rows resident around your viewport and re-centres it on a background task as you scroll. The row-offset index is paged too, so the cost of scrolling does not grow with the size of the set. Reading a page seeks once and then streams forward, which keeps the read buffer warm across the whole page. And if a page cannot be read, because the file was removed or became unreadable, the Messages pane says so instead of leaving you staring at a blank grid.

Drawing is the other half. The interface is GPU-rendered through each platform's native graphics path rather than a webview, and the grid renders only the slice currently on screen. In the Tabbed layout, only the visible grid is rendered at all, so a batch that returns several large sets costs what the one you are looking at costs.

You keep working while it streams

None of this waits for the query to finish. The grid paints as rows arrive, the live row count updates as it goes, and repaints are coalesced so that streaming a large set does not spend its time redrawing the same screen. Completion and a running query do not take turns either, because the engine runs them on separate background work.

If you change your mind, cancel actually stops the statement on the server: PostgreSQL's cancel protocol, SQLite's interrupt, MySQL's KILL QUERY, and SQL Server's best-effort KILL. A cancelled query stops burning server resources instead of continuing invisibly, and the status bar says "Query cancelled" rather than something vaguer. For queries that should never run long, connections have an optional Query timeout that stops the statement on the server and reports a clear timeout.

The honest trade-offs

A spill-backed set lives on disk, not in memory, and some features need the whole set at once. So for spilled results:

• Sort, filter and diff are disabled. Quietly sorting only the visible window would be a lie.
• Pivot and Chart lock with a "Result too large" status, the same way they lock while a query is still streaming. An earlier version let the pivot aggregate only the resident rows; that was worse than no pivot, so it was changed.

Even below the spill threshold, the Chart tab caps very large sets with a visible note rather than stalling while it tries to plot everything.

If you need those features on a bigger set, raise the thresholds, or better, push the work to the database: an aggregate or a WHERE is almost always cheaper than moving millions of rows to your laptop to look at a few of them.

-- instead of pulling the whole table into the grid...
SELECT * FROM events;

-- ...let the server do the grouping
SELECT event_type, COUNT(*) AS total
FROM events
GROUP BY event_type;

Why build it this way

Each step removes a place where a database tool normally makes you wait or runs out of room: batching removes the wait for the first row, backpressure caps memory, spilling moves the bulk to disk, and windowed paging keeps scrolling cheap. None of it is exotic on its own. What matters is that all four are in place at once, so the unexpected 50-million-row result becomes a scroll rather than a stall.

The reference details are in the docs under Unbounded, spill-to-disk results, and the broader engineering story is in Speed & access and the engineering deep dive.