唯讀的 SQLite 查詢回「database or disk is full」：GROUP BY 把整個欄位搬進暫存檔

用 sqlite3 -readonly 打開資料庫，或用 ?mode=ro 的 URI 開，這個連線就不會改資料庫。我原本以為這也表示查詢只讀不寫，磁碟滿了也不影響。

但 10 月 15 日寫 044 時，我對 Codex 那個 2,694,709,248 位元組的 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;

工具回來的只有 Exit code 13。那時 df 顯示磁碟大約剩 2.1 GB。這篇要談的是一個唯讀查詢為什麼會撞到磁碟滿，依序是：13 是什麼、暫存檔裡放了什麼、在 RAM disk 上重現、三種改法，最後是那次我實際用的查法。

13 是 SQLITE_FULL

SQLite 的結果碼 13 是 SQLITE_FULL，訊息是 database or disk is full，sqlite3 命令列工具遇到這個錯誤時，結束碼也是 13。單看結束碼還不能確定是哪裡滿了，下面的查詢計畫和重現才是證據。-readonly 管的是資料庫檔案本身，SQLite 自己要用的暫存檔不在這個限制裡。SQLite 的文件〈Temporary Files Used By SQLite〉第 2.8 節列出會用到「transient index」（暫時索引）的情況，第一項就是 ORDER BY 或 GROUP BY（文件用的是「可能」，有合適的索引時可以不用），每個暫時索引存在自己的暫存檔，敘述結束時自動刪掉；同一份文件的第 4 節也說，小的暫存結構可能整個留在記憶體。

暫存檔寫在哪、寫不寫磁碟，同一份文件也有說明。在類 Unix 系統上，目錄依序找 PRAGMA temp_store_directory（或 sqlite3_temp_directory 全域變數）、SQLITE_TMPDIR、TMPDIR、/var/tmp、/usr/tmp、/tmp、目前目錄，用第一個存在、而且有寫入和執行（搜尋）權限的目錄。這台 Mac 內建的 /usr/bin/sqlite3 是 3.51.0，pragma compile_options 有 TEMP_STORE=1，意思是預設寫磁碟、可以用 temp_store pragma 改。這台 Mac 的 TMPDIR 在 /var/folders/…，和資料庫在同一個磁碟上。

暫存檔裡放了什麼

EXPLAIN QUERY PLAN 對那個查詢的回答是：

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

ORDER BY 那個 B-tree 排的是分組之後的結果，只有幾列；大的是 GROUP BY 那個。

只知道「有暫存 B-tree」還不夠，要看放進去的是什麼。我在測試庫上跑同樣形狀、拿掉 ORDER BY 和 LIMIT 的查詢，看 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)

第 9、10 行把 item_type 和 item_json 兩欄一起包成一筆記錄送進排序器；length() 在第 24 行，排序完才算。也就是說，為了依 item_type 分組，SQLite 把每一列的整個 item_json 送進排序器；資料量超過記憶體裡放得下的部分，就寫進暫存檔。那張表的 item_json 合計約 26.3 億字元（事後用不分組的 sum(length(item_json)) 量的；length() 對文字算的是字元數，這一欄幾乎都是 ASCII，位元組數差不多），比當時剩下的約 2.1 GB 大。所以「暫存空間用完」是很有根據的解釋，但原本那次我沒有留下直接的 I/O 紀錄。

只數筆數的 select item_type, count(*) … group by 1 在同一個 2.69 GB 的資料庫上就成功了，因為排序器裡只有 item_type。

在 RAM disk 上重現

要重現磁碟滿，又不能真的把這台機器的磁碟塞滿（同時有別的工作在跑），所以把暫存檔導到一個小的 RAM disk（記憶體模擬的磁碟）：

DEV=$(hdiutil attach -nomount ram://81920 | awk '{print $1}')   # 81920 個 512 位元組區塊 = 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)

測試庫 t.db 是 150,544,384 位元組：3,000 列，四種 item_type，每列一個 50,000 字元的 item_json。結束碼 13，和原本那次一樣。不設 SQLITE_TMPDIR、暫存寫回有空間的 TMPDIR，同一個查詢就成功。

暫存到底用了多少？我另外掛一個 300 MiB 的 RAM disk，查詢執行時大約每 0.05 秒用 df -k 讀一次它的已用空間，取樣到的最高值比起始多了 154,508 KiB，約 158 MB，和 150.5 MB 的測試庫同一量級。這只是一次測試，不是通用的公式，但方向很清楚：暫存跟著排序器帶的資料量走，而這個查詢帶的是整個 item_json。

三種改法

四種寫法都在 40 MiB 的 RAM disk 上試過，第 1 種失敗，另外三種成功：

-- 1. 子查詢先算 length()：失敗，(13)
select item_type, count(*), sum(n)
from (select item_type, length(item_json) n from items) group by 1;

-- 2. MATERIALIZED CTE 先算 length()：成功，取樣沒看到 RAM disk 已用空間增加
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. 暫存放記憶體：成功
pragma temp_store=memory;
select item_type, count(*), sum(length(item_json)) from items group by 1;

-- 4. 不分組，一次查一類：成功
select count(*), sum(length(item_json)) from items where item_type='imageGeneration';

第 1 種看起來應該有用，實際上沒有。SQLite 的查詢規劃器會把這種子查詢攤平（query flattening），結果和原本的查詢一樣，大欄位還是進了排序器。第 2 種用 MATERIALIZED 擋住攤平：SQLite 文件〈The WITH Clause〉第 4 節說，加了 MATERIALIZED 的 CTE 會先算成一張暫時表，攤平和下推這類最佳化就不會套用，文件稱之為「optimization fence」，這個語法從 3.35.0 開始支援。同一節也建議沒有充分理由不要加；這裡的理由就是排序器的負載。這張暫時表只有 item_type 和長度兩欄，後面的分組排序也只排這兩欄；在 300 MiB 的 RAM disk 上，同樣的取樣沒看到已用空間增加。暫時表和排序器還是存在（EXPLAIN 裡有 OpenEphemeral 和 SorterOpen），合理的解釋是它們小到留在記憶體裡，但我沒有直接追蹤它們的 I/O。

第 3 種在測試庫上可行，但排序器還是帶著整個 item_json，只是改放記憶體。原本那個資料庫的這一欄有二十幾億字元，需要多少記憶體我沒有量，也沒有在那個資料庫上試。第 4 種沒有 GROUP BY，計畫裡只有一次掃描，沒有分組或排序用的暫存；代價是每一類要各查一次。如果資料庫可以寫，在 item_type 上建索引也能讓分組不用暫存排序，但這個資料庫是別的程式在用的，我只用唯讀開。

那次實際的查法

回到 thread_history_1.sqlite。我先用只有 count(*) 的 GROUP BY 拿到各類的筆數，再對要的那一類用第 4 種寫法查長度：where item_type='imageGeneration' 的 sum(length(item_json)) 回了約 20.34 億字元，就是 044 裡寫成約 2,034 MB 的那個數字。裡面的 base64 是 ASCII，JSON 其他部分可能有中文，所以位元組數會略多一點；之後用 length(cast(item_json as blob)) 量的位元組數，和字元數只差一千多。

還有一件事是推論：如果那次真的是暫存把共用磁碟的剩餘空間用完，同一時間在這台機器上跑的其他工作，可能在那幾秒內遇到寫入失敗。有沒有發生，我沒有紀錄可以確認。之後在剩餘空間不多的機器上對大資料庫做分組統計，我會先 EXPLAIN，看排序器裡帶了哪些欄位。

如果只記得一件事

-readonly 只保證不改資料庫檔，GROUP BY 和 ORDER BY 照樣可能寫暫存檔；這次的計畫還把查詢用到的大欄位整個帶進排序器。要對大欄位做分組統計，先用 MATERIALIZED 的 CTE 把它算成小值，或拆成不分組的查詢；不確定的話，看 EXPLAIN 裡 SorterInsert 前面那個 MakeRecord 包了哪些欄。