Database Info Query Performance#
decant db info reports archive metadata using a mix of SQLite pragma reads and table scans. These operations have wildly different costs at scale, which drives the split between default and --full output.
All measurements below are from a 2.52 GB synthetic archive (200k messages, 200k blocks, 616k 4 KB pages) on a warm cache using bun:sqlite.
Free by default#
| Query | Typical latency | Why it's cheap |
|---|---|---|
COUNT(*) on session, message, block, tool_call, file_ref | 0–40 ms combined | SQLite table b-tree metadata; no full page scan |
pragma_freelist_count() + pragma_page_size() | ~0 ms | Read directly from the database header — no page walk |
The row count query and the freelist pragma query both finish in under 10 ms on a multi-GB archive . This makes freelist_bytes — the field that signals deleted transcript bytes are still occupying disk space and a decant db vacuum is owed — essentially free to include unconditionally .
Expensive — opt-in behind --full#
| Query | Typical latency | Why it's slow |
|---|---|---|
COUNT(*) FROM block_fts | 150 ms – ~3 s | Scans the FTS5 index; not a header lookup even though block_fts is an external-content table |
SUM(OCTET_LENGTH(...)) over message.raw + block text columns | ~1 s – 13 s | Full table scans of the two largest tables |
These are gated behind --full . The --full flag description in the CLI is explicit: "add full-scan totals (fts_rows, text_bytes); slow on a large archive".
Why OCTET_LENGTH instead of LENGTH? The byte sum uses OCTET_LENGTH rather than LENGTH because LENGTH counts Unicode characters on a TEXT column, which understates non-ASCII archives. text_bytes is printed next to size_bytes (a real filesystem byte count), so both must use the same unit .
Why not block_fts_docsize? The FTS5 shadow table block_fts_docsize can return a row count in ~2 ms. It was deliberately not used because it is an FTS5 internal that disappears if the virtual table is ever declared with columnsize=0 — too fragile for a user-facing field.
Why not dbstat? SUM(pgsize) FROM dbstat for the message and block tables costs 2–5 s — comparable to the byte sum — and is not a cheap substitute. pragma_freelist_count() is the right tool for the "wasted space" signal.
DbInfo fields at a glance#
The DbInfo interface reflects this split:
- Always present:
path,size_bytes,schema_version,sessions,messages,blocks,tool_calls,file_refs,freelist_bytes --fullonly:fts_rows?,text_bytes?
The output handler conditionally appends the --full fields only when they are non-null .
Key source references#
| File | Lines | What's there |
|---|---|---|
src/cli.ts | 1434–1491 | dbInfo() implementation with full SQL and performance comment |
src/cli.ts | 99–117 | DbInfo interface |
src/cli.ts | 671–704 | CLI command definition and --full flag |