SQLite WAL Bloat in Hermes: What It Is and How I Vacuumed It Safely

Hermes session storage ballooned to 574MB after 4,000+ sessions. The WAL file was one problem, but the real culprit was a redundant FTS trigram index eating half the database. Here is what I found, how I diagnosed it, and the safe cleanup that shaved 267MB with zero data loss.

2026-04-29 · 4 min read

Something felt bloated, and it was not subtle

You know that moment where your system still works, but everything feels heavier than it should? Slower lookups, clunkier restarts, that general "why is this dragging?" feeling.

That was me in Hermes land.

Hermes Agent stores session state in SQLite at ~/.hermes/state.db. After 4,000+ sessions with 53,000+ messages, I found:

• state.db: 574MB
• state.db-wal: 5MB
• state.db-shm: 32KB

Nothing was "broken", but the database footprint had clearly drifted into maintenance territory.

What a WAL file is, in plain English

WAL means Write-Ahead Logging.

In SQLite, instead of writing changes directly into the main database file every single time, writes are first appended to a separate WAL file (.db-wal). Think of it as a staging lane for changes.

Why do this? Concurrency.

It allows reads and writes to coexist more smoothly, which is great for active systems where I want responsiveness and fewer lock headaches.

So the WAL file is not junk and it is not corruption. It is a normal part of how SQLite works. But it does need periodic checkpointing, and the main database itself can accumulate structure that no longer earns its keep.

Step one: checkpoint and vacuum

First I flushed the WAL and compacted the main database:

sqlite3 ~/.hermes/state.db "PRAGMA wal_checkpoint(TRUNCATE); VACUUM;"

This recovered a few megabytes from the WAL, but the main database was still enormous. Something else was going on.

Step two: finding the real bloat

I broke down where the bytes actually live using SQLite's built-in dbstat virtual table:

sqlite3 ~/.hermes/state.db "
SELECT name, ROUND(SUM(pgsize)/1024.0/1024, 1) as MB
FROM dbstat GROUP BY name ORDER BY SUM(pgsize) DESC LIMIT 10;"

The results were surprising:

The trigram full-text search index (messages_fts_trigram_data + messages_fts_trigram_content) was 262 MB, nearly half the entire database.

What a trigram index is, and why I did not need it

Hermes uses SQLite FTS5 for session search, which lets you find posts by keywords. It actually had two FTS indexes:

1. messages_fts — a standard word-based FTS index (26 MB). Finds "vacuum" in "how I vacuumed the database". This is what session search actually uses.

2. messages_fts_trigram — a trigram index (262 MB). Enables substring search, like finding "acuu" inside "vacuumed". Impressive on paper, but nobody searches session history by three-letter substrings.

The trigram index was eating 262 MB to power a feature I never use. The standard FTS index handles everything I need at one-tenth the cost.

The fix: drop the trigram index

I stopped the gateway first (you should never modify a database while it is being written to), then removed the trigram table and its triggers:

# 1. Stop the gateway to prevent write conflicts
systemctl --user stop hermes-gateway

# 2. Back up the database
cp ~/.hermes/state.db ~/.hermes/state.db.bak.$(date +%Y%m%d)

# 3. Drop the redundant trigram index
sqlite3 ~/.hermes/state.db "
DROP TRIGGER IF EXISTS messages_fts_trigram_insert;
DROP TRIGGER IF EXISTS messages_fts_trigram_delete;
DROP TRIGGER IF EXISTS messages_fts_trigram_update;
DROP TABLE IF EXISTS messages_fts_trigram;
"

# 4. Checkpoint and vacuum
sqlite3 ~/.hermes/state.db "
PRAGMA wal_checkpoint(TRUNCATE);
VACUUM;
"

# 5. Restart the gateway
systemctl --user start hermes-gateway

The result

267 MB reclaimed with zero data loss. Session search still works perfectly via the standard FTS index.

Should you do this?

Short answer: yes, but with care.

The WAL checkpoint and vacuum is safe, routine maintenance. Run it periodically if you run Hermes with heavy session volume.

Dropping the trigram index is safe if you do not rely on substring search — and honestly, almost nobody does for session history. But always:

1. Back up first — cp state.db state.db.bak.$(date +%Y%m%d)
2. Stop the gateway — never modify the DB while it is live
3. Verify after — check your session count and test search before moving on

Practical signs to investigate your own database size:

• ~/.hermes/state.db is above 300 MB
• session lookups feel slower than expected
• restarts feel rougher
• you are running low on disk space

Performance wins are often less about new tooling and more about disciplined housekeeping. Sometimes the biggest gains come from removing what you do not need.

Found this useful?

Follow @Raf_VRS on X for more Build Journal updates.

Support the work: ko-fi.com/rafvrs

#BuildJournal #HardInterference #Raf_VRS