Big results in the browser no longer eat the tab
SQLly's browser version now keeps big query results in browser storage (OPFS) instead of memory, so a million-row result scrolls, pivots and stays light.
The browser version of SQLly used to keep every row of every result in the tab's memory. Run a query that returns a few million rows and you found out exactly how much memory your tab had. That's fixed: big results now go to the browser's own storage, the same way the desktop app writes them to disk.
What was wrong
On the desktop, once a result passes 50,000 rows (or 64 MB), SQLly stops holding it in memory. The rest goes to a spill file and the grid pages from it as you scroll, so a huge result barely moves the app's memory. That's been true for a while; the streaming docs have the details.
The browser build never had any of that. The live preview and SQLly Embedded pages run the same app compiled to WebAssembly, but a browser tab has no disk to write to, so every row just stayed in memory. A 32-bit WebAssembly app can't use more than 4 GB, and in my test, 10 million rows of a pretty ordinary 7-column result took about 2.5 GB just to hold, plus nearly half a minute of the page being busy building them. So yeah, it sucked.
Where the rows go now
Browsers give every site a private bit of storage called the Origin Private File System (OPFS). The fast way to use it, synchronous file handles, only exists inside a Web Worker, not on the page where the app runs.
So there's now a small worker, about 190 lines of plain JavaScript, whose only job is appending bytes to files and reading byte ranges back. The app does the real work: it encodes rows in exactly the same spill format the desktop app writes, sends the bytes to the worker a megabyte at a time, and asks for the rows that are on screen when you scroll. The worker doesn't even know what a row is.
Once a result spills in the browser, these all work on every row, not just the ones in memory:
- Scrolling anywhere in the result, including dragging the scrollbar to the end of a million rows.
- View Row as Form, copying cells, and selection stats for what's on screen.
- Top Values and column profiling.
- The Pivot and Chart tabs, and drilling from a pivot cell into the rows behind it.
- Export and copy, up to the first 100,000 rows (more on that below).
The stored rows are deleted when the result is replaced or closed. If a tab dies before it can clean up, the next SQLly page you open in that browser removes the leftovers on startup, while leaving alone files that another open tab is still using.
The numbers
Before building it for real I measured the idea in a stripped-down test page (headless Chrome, Apple silicon):
| 1M rows | 10M rows | |
|---|---|---|
| Stored in OPFS | 138 MB | 1,391 MB |
| Time to write it | 0.34 s | 2.8 s |
| Reading a random 200-row page (median) | 0.1 ms | 0.1 ms |
| Reading every row for a pivot | 0.29 s | 2.9 s |
| Page memory growth, stored | 3.3 MB | 3.3 MB |
| Page memory growth, all in memory (the old way) | 255 MB | 2,555 MB |
Then I ran the real app in Chrome on my macOS test VM. A million-row query against the SQLite sample finished in about a third of a second, the spill took 75 MB of browser storage, and the page's JavaScript heap sat at around 62 MB the whole time. Dragging to row 967,619 came back with the right values, and a pivot summing all million rows by category matched a separate calculation to the cent.
The honest bits
- Only tested in Chrome so far. Safari 17+ and Firefox 111+ have the APIs, but I haven't measured them yet. A browser without OPFS, or a private window that refuses it, keeps results in memory like before and says so in Messages.
- Pivot and Chart aren't flat. To read every row they load the stored result back into the browser's memory (outside the app's own) the first time you open them, and keep it until the result goes away. Still way better than holding the whole thing in the app, but not free.
- Export is still capped at 100,000 rows in the browser. Streaming a full export straight to a file is a desktop thing for now.
- Sort and filter are off for spilled results, same as on the desktop. Sorting just the rows on screen and calling it sorted would be a lie.
- The Postgres sample hands back everything at once. PGlite returns a whole result in one go, so a giant query against the Chinook sample still peaks in memory before it gets stored. SQLite results are read in batches and stored as they come.
- If storage fills up partway, SQLly keeps the rows it managed to store and tells you how many.
Try it
Open the live preview, connect to the Northwind sample, and run something silly:
WITH RECURSIVE n(x) AS (SELECT 1 UNION ALL SELECT x + 1 FROM n WHERE x < 1000000)
SELECT x AS id, 'customer-' || x AS name, x % 37 AS category FROM n;
Then drag the scrollbar to the bottom, or open the Pivot tab. If something breaks, and in the browser build things still do, send me feedback from the app. I read every report.