A read-only SQLite query returns "database or disk is full": GROUP BY copies a whole column into a temp file

Open a database with sqlite3 -readonly, or with a ?mode=ro URI, and the connection won't modify the database. I had assumed that also meant queries only read, and a full disk doesn't matter.

But on October 15, while writing 044, I ran this query against Codex's 2,694,709,248-byte thread_history_1.sqlite:

select item_type, count(*), round(sum(length(item_json))/1e6,1)
from thread_items group by 1 order by 3 desc limit 6;

All the tool returned was Exit code 13. df showed about 2.1 GB free at the time. This note is about how a read-only query runs into a full disk. In order: what 13 means, what goes into the temp file, a reproduction on a RAM disk, three fixes, and the query I actually used that day.

13 is SQLITE_FULL

SQLite result code 13 is SQLITE_FULL, with the message database or disk is full, and the sqlite3 command-line tool also exits with 13 on it. The exit code alone doesn't say what filled up; the query plan and the reproduction below are the evidence. -readonly governs the database file itself; SQLite's own temp files aren't covered by it. Section 2.8 of SQLite's "Temporary Files Used By SQLite" lists the cases that use transient indices, and the first is an ORDER BY or GROUP BY clause (the document says "may"; a suitable index can avoid it); each transient index is stored in its own temporary file, deleted automatically at the end of the statement, and section 4 notes that small temporary structures may stay entirely in memory.

The same document covers where temp files go and whether they hit disk. On unix-like systems the directory search order is PRAGMA temp_store_directory (or the sqlite3_temp_directory global), SQLITE_TMPDIR, TMPDIR, /var/tmp, /usr/tmp, /tmp, then the current directory, using the first that exists and has write and execute (search) permission. On this Mac, the built-in /usr/bin/sqlite3 is 3.51.0, and pragma compile_options includes TEMP_STORE=1: on disk by default, overridable with the temp_store pragma. This Mac's TMPDIR is under /var/folders/…, on the same disk as the database.

What goes into the temp file

EXPLAIN QUERY PLAN for that query says:

|--SCAN thread_items
|--USE TEMP B-TREE FOR GROUP BY
`--USE TEMP B-TREE FOR ORDER BY

The ORDER BY B-tree sorts the grouped result, a handful of rows; the big one is the GROUP BY B-tree.

Knowing there's a temp B-tree isn't enough; what matters is what goes into it. On a test database, a query of the same shape without the ORDER BY and LIMIT, through EXPLAIN:

7   Column       0  1  11   r[11]= cursor 0 column 1
8   Column       0  2  12   r[12]= cursor 0 column 2
9   MakeRecord   11 2  13   r[13]=mkrec(r[11..12])
10  SorterInsert 1  13 0    key=r[13]
…
13  SorterSort   1  45 0    GROUP BY sort
…
23  Column       2  1  15   r[15]=items.item_json
24  Function     0  15 14   r[14]=func(r[15])        length(1)

Lines 9 and 10 pack item_type and item_json into one record and hand it to the sorter; length() is at line 24, after the sort. In other words, to group by item_type, SQLite feeds every row's entire item_json to the sorter, and whatever doesn't fit in memory is written to a temp file. That table's item_json totals about 2.63 billion characters (measured afterwards with an ungrouped sum(length(item_json)); length() counts characters for text, and this column is almost all ASCII, so bytes are about the same), more than the roughly 2.1 GB free at the time. So running out of temp space is a well-supported explanation, but I kept no direct I/O record of the original failure.

A count-only select item_type, count(*) … group by 1 succeeded on the same 2.69 GB database, because the sorter then holds only item_type.

Reproducing it on a RAM disk

To reproduce a full disk without actually filling this machine's disk (other jobs were running), I pointed temp files at a small RAM disk (a disk backed by memory):

DEV=$(hdiutil attach -nomount ram://81920 | awk '{print $1}')   # 81920 512-byte blocks = 40 MiB
diskutil erasevolume HFS+ sqltmp "$DEV"
SQLITE_TMPDIR=/Volumes/sqltmp sqlite3 -readonly t.db \
  "select item_type, count(*), sum(length(item_json)) from items group by 1;"
Error: stepping, database or disk is full (13)

The test database t.db is 150,544,384 bytes: 3,000 rows, four item_type values, one 50,000-character item_json per row. Exit code 13, same as the original. Without SQLITE_TMPDIR, so that temp files go to a TMPDIR with room, the same query succeeds.

How much temp space does it actually use? I mounted a separate 300 MiB RAM disk and read its used space with df -k roughly every 0.05 seconds during the query: the highest sample was 154,508 KiB above the start, about 158 MB, the same order as the 150.5 MB test database. One test isn't a sizing formula, but the direction is clear: temp space follows the data the sorter carries, and this query carries all of item_json.

Three fixes

All four tried on the 40 MiB RAM disk; number 1 fails, the other three succeed:

-- 1. subquery computing length() first: fails, (13)
select item_type, count(*), sum(n)
from (select item_type, length(item_json) n from items) group by 1;

-- 2. MATERIALIZED CTE computing length() first: succeeds, no increase in RAM-disk usage seen in sampling
with x as materialized (select item_type, length(item_json) n from items)
select item_type, count(*), sum(n) from x group by 1;

-- 3. temp in memory: succeeds
pragma temp_store=memory;
select item_type, count(*), sum(length(item_json)) from items group by 1;

-- 4. no grouping, one type at a time: succeeds
select count(*), sum(length(item_json)) from items where item_type='imageGeneration';

Number 1 looks like it should work and doesn't. SQLite's query planner flattens this kind of subquery (query flattening), so it ends up the same as the original query and the large column still goes into the sorter. Number 2 blocks the flattening with MATERIALIZED: section 4 of SQLite's "The WITH Clause" says a CTE marked MATERIALIZED is evaluated into an ephemeral table first, so optimizations such as flattening and push-down don't apply; the document calls it an "optimization fence"; the syntax is available from 3.35.0. The same section advises against adding it without a compelling reason; here the reason is the sorter's payload. The ephemeral table holds only item_type and the length, and the grouping sort that follows sorts only those two columns; on the 300 MiB RAM disk the same sampling showed no increase in used space. The ephemeral table and sorter still exist (EXPLAIN shows OpenEphemeral and SorterOpen); the likely explanation is that they're small enough to stay in memory, but I didn't trace their I/O directly.

Number 3 works on the test database, but the sorter still carries all of item_json, just in memory. On the original database that column holds over two billion characters; I didn't measure how much memory that would take, and didn't try it there. Number 4 has no GROUP BY; the plan is a single scan with no grouping or sorting temp structures, at the cost of one query per type. If the database were writable, an index on item_type would also let the grouping skip the temp sort, but this one belongs to another program and I only open it read-only.

The query I actually used

Back to thread_history_1.sqlite. I first got per-type row counts with a count(*)-only GROUP BY, then used approach 4 for the one type I needed: sum(length(item_json)) with where item_type='imageGeneration' returned about 2.034 billion characters, the figure written as about 2,034 MB in 044. The base64 inside is ASCII, but other parts of the JSON may contain Chinese, so the byte count is slightly higher; a later byte count with length(cast(item_json as blob)) differed from the character count by a little over a thousand.

One thing here is inference: if that failure really was temp data using up the shared disk's remaining space, other jobs running on this machine at the same time may have hit write failures for those few seconds. Whether that happened, I have no records to confirm. From now on, before running grouped statistics on a large database on a machine low on space, I'll run EXPLAIN first and look at which columns the sorter carries.

If you remember one thing

-readonly only guarantees the database file isn't modified; GROUP BY and ORDER BY can still write temp files, and this plan carried the large column the query uses into the sorter in its entirety. To group statistics over a large column, reduce it to a small value first in a MATERIALIZED CTE, or split it into ungrouped queries; when in doubt, look at which columns the MakeRecord before SorterInsert packs in EXPLAIN.