perf(state): reduce FTS storage with external-content indexes (schema v15) by JabberELF · Pull Request #20239 · NousResearch/hermes-agent

JabberELF · 2026-05-05T13:20:24Z

Summary

Switch messages_fts and messages_fts_trigram from inline FTS5 storage to external-content FTS5 (schema v14 → v15 on current main). This removes duplicated message text from FTS shadow storage while keeping messages as the source of truth.

Changes

hermes_state.py
- SCHEMA_VERSION 14 → 15.
- Define messages_fts and messages_fts_trigram as external-content tables with content='messages' and content_rowid='id'.
- Keep content, tool_name, and tool_calls indexed.
- Update insert/update/delete triggers to use the external-content FTS5 pattern.
- Add a v15 migration that drops/recreates derived FTS tables and rebuilds them from messages.
- Use snippet(..., -1, ...) so matches in non-content columns can produce useful snippets.
tests/test_hermes_state.py
- Add external-content trigger lifecycle coverage for insert/update/delete.
- Add v12-style inline FTS → v15 external-content migration coverage.
- Cover search behavior for content, tool_name, tool_calls, and CJK trigram text.

Compatibility

messages remains the source of truth and is not mutated by the migration.
Search behavior is preserved for normal FTS and CJK trigram queries.
Existing v10/v11/v14 databases upgrade through the version-gated migration path.
The FTS tables are derived indexes. Users who need rollback should restore a pre-upgrade state.db backup.
Disk pages become reusable in SQLite after the rebuild; returning space to the filesystem still requires VACUUM / the existing maintenance path.

Validation

python -m pytest -o addopts='' tests/test_hermes_state.py -q --tb=short
- 231 passed
python -m ruff check hermes_state.py tests/test_hermes_state.py
- All checks passed!
python -m compileall -q hermes_state.py tests/test_hermes_state.py
git diff --check

Independent read-only review of the rebased diff found no Critical/Must Fix issues.

JabberELF · 2026-05-22T11:01:41Z

Refreshed this PR to make it easier to review.

What changed:

Rebased onto current main.
Removed the unrelated fork workflow cleanup from the PR branch.
Updated the schema bump to v13, since upstream main already uses v12.
Kept this focused as an FTS storage reduction: external-content FTS tables now reference messages instead of duplicating message text.
Kept the positioning complementary to feat(state): make trigram FTS5 index optional #27770: if trigram / FTS search remains enabled, this reduces the storage overhead of the enabled indexes.

Verification added/updated:

v12 inline FTS → v13 external-content migration coverage.
External-content trigger lifecycle coverage for insert/update/delete.
Search coverage for content, tool_name, tool_calls, and CJK trigram text.
Snippet behavior now uses snippet(..., -1, ...) so non-content column matches can produce useful snippets too.

Local verification:

python -m pytest -o addopts='' tests/test_hermes_state.py -q --tb=short
Result: 217 passed.
git diff --check: clean.

Synthetic SQLite benchmark on 2,500 generated messages:

inline FTS DB: 26.80 MiB
external-content FTS DB: 17.22 MiB
size reduction: 35.7%
normal FTS median query time: 0.008 ms vs 0.008 ms
trigram CJK median query time: 0.022 ms vs 0.022 ms

Also adjusted the rollback wording: since the FTS tables are derived indexes over messages, rollback should restore a pre-upgrade state.db backup rather than rely on the migration being reversible in-place.

Happy to rebase again or adjust around #27770 if that lands first.

…imize' The FTS5 indexes (messages_fts, messages_fts_trigram) grow as a series of incremental b-tree segments — one per trigger-driven insert batch. SQLite's automerge caps at ~16 segments, so a long-lived store keeps scanning many segments per MATCH and never collapses them unless the special 'optimize' command runs. Nothing in the codebase ever ran it: vacuum() only fired after a prune that deleted rows, and even then never merged FTS segments. Changes: - SessionDB.optimize_fts(): merges each FTS5 index to a single segment, probing for the (optional/lazy) trigram table first so it is safe to call unconditionally. Layout-only — search results and snippet() are unchanged. - vacuum() now calls optimize_fts() before VACUUM so freed index pages are returned to the OS in the same pass. - 'hermes sessions optimize' CLI subcommand for on-demand reclamation + segment compaction (previously there was no way to compact the store without a prune deleting rows), with before/after size reporting. Benchmark (8000 msgs, fragmented to 8 segments/index): - segments 8 -> 1 on both indexes - porter MATCH 5.5x faster (0.449 -> 0.081 ms/q) - trigram MATCH 3.0x faster (0.632 -> 0.207 ms/q) - 8000 matches before == 8000 after, identical row ids (no functional change) Orthogonal to the structural FTS-size PRs (#20239 external-content, #27770 optional trigram) — segment merge helps regardless of those. Tests: TestOptimizeFts covers index count, search+snippet preservation, missing-trigram path, and idempotency. Full test_hermes_state.py green (227).

…imize' The FTS5 indexes (messages_fts, messages_fts_trigram) grow as a series of incremental b-tree segments — one per trigger-driven insert batch. SQLite's automerge caps at ~16 segments, so a long-lived store keeps scanning many segments per MATCH and never collapses them unless the special 'optimize' command runs. Nothing in the codebase ever ran it: vacuum() only fired after a prune that deleted rows, and even then never merged FTS segments. Changes: - SessionDB.optimize_fts(): merges each FTS5 index to a single segment, probing for the (optional/lazy) trigram table first so it is safe to call unconditionally. Layout-only — search results and snippet() are unchanged. - vacuum() now calls optimize_fts() before VACUUM so freed index pages are returned to the OS in the same pass. - 'hermes sessions optimize' CLI subcommand for on-demand reclamation + segment compaction (previously there was no way to compact the store without a prune deleting rows), with before/after size reporting. Benchmark (8000 msgs, fragmented to 8 segments/index): - segments 8 -> 1 on both indexes - porter MATCH 5.5x faster (0.449 -> 0.081 ms/q) - trigram MATCH 3.0x faster (0.632 -> 0.207 ms/q) - 8000 matches before == 8000 after, identical row ids (no functional change) Orthogonal to the structural FTS-size PRs (NousResearch#20239 external-content, NousResearch#27770 optional trigram) — segment merge helps regardless of those. Tests: TestOptimizeFts covers index count, search+snippet preservation, missing-trigram path, and idempotency. Full test_hermes_state.py green (227).

JabberELF · 2026-05-30T10:55:27Z

Refreshed again onto current main.

What changed since the previous refresh:

Rebased cleanly on current main.
Moved the external-content FTS migration to schema v15 because upstream main is now schema v14.
Kept the PR focused on the same storage optimization: messages_fts and messages_fts_trigram now use external-content tables backed by messages instead of duplicating message text in FTS shadow storage.
Preserved compatibility with the newer main changes, including the FTS optimize/VACUUM maintenance path.
Cleaned stale migration comments after review.

Verification:

python -m pytest -o addopts='' tests/test_hermes_state.py -q --tb=short → 231 passed
python -m ruff check hermes_state.py tests/test_hermes_state.py → All checks passed
python -m compileall -q hermes_state.py tests/test_hermes_state.py
git diff --check

I also ran an independent read-only review of the rebased diff. It found no Critical/Must Fix issues; the only suggestions were comment wording cleanups, which are included in this update.

Rebase the external-content FTS change onto current main and move the schema bump to v13, since upstream main already uses schema v12. Drop the unrelated fork workflow cleanup from the PR branch. The migration rebuilds both FTS indexes from messages using external-content tables and keeps the messages table as the source of truth. Users who need rollback should restore a pre-upgrade state.db backup.

JabberELF · 2026-05-31T07:55:37Z

Refreshed this PR onto current main and resolved the hermes_state.py conflict.

What changed in the refresh:

Kept the external-content FTS5 schema as schema v15.
Preserved the newer upstream FTS5-unavailable migration guards.
Updated the shared FTS rebuild helper to use FTS5's external-content rebuild command.
Kept the PR scoped to hermes_state.py and tests/test_hermes_state.py.

Local verification:

python -m pytest -o addopts='' tests/test_hermes_state.py -q --tb=short → 234 passed
python -m ruff check hermes_state.py tests/test_hermes_state.py → All checks passed
python -m compileall -q hermes_state.py tests/test_hermes_state.py
git diff --check origin/main...HEAD

…imize' The FTS5 indexes (messages_fts, messages_fts_trigram) grow as a series of incremental b-tree segments — one per trigger-driven insert batch. SQLite's automerge caps at ~16 segments, so a long-lived store keeps scanning many segments per MATCH and never collapses them unless the special 'optimize' command runs. Nothing in the codebase ever ran it: vacuum() only fired after a prune that deleted rows, and even then never merged FTS segments. Changes: - SessionDB.optimize_fts(): merges each FTS5 index to a single segment, probing for the (optional/lazy) trigram table first so it is safe to call unconditionally. Layout-only — search results and snippet() are unchanged. - vacuum() now calls optimize_fts() before VACUUM so freed index pages are returned to the OS in the same pass. - 'hermes sessions optimize' CLI subcommand for on-demand reclamation + segment compaction (previously there was no way to compact the store without a prune deleting rows), with before/after size reporting. Benchmark (8000 msgs, fragmented to 8 segments/index): - segments 8 -> 1 on both indexes - porter MATCH 5.5x faster (0.449 -> 0.081 ms/q) - trigram MATCH 3.0x faster (0.632 -> 0.207 ms/q) - 8000 matches before == 8000 after, identical row ids (no functional change) Orthogonal to the structural FTS-size PRs (NousResearch#20239 external-content, NousResearch#27770 optional trigram) — segment merge helps regardless of those. Tests: TestOptimizeFts covers index count, search+snippet preservation, missing-trigram path, and idempotency. Full test_hermes_state.py green (227).

…imize' The FTS5 indexes (messages_fts, messages_fts_trigram) grow as a series of incremental b-tree segments — one per trigger-driven insert batch. SQLite's automerge caps at ~16 segments, so a long-lived store keeps scanning many segments per MATCH and never collapses them unless the special 'optimize' command runs. Nothing in the codebase ever ran it: vacuum() only fired after a prune that deleted rows, and even then never merged FTS segments. Changes: - SessionDB.optimize_fts(): merges each FTS5 index to a single segment, probing for the (optional/lazy) trigram table first so it is safe to call unconditionally. Layout-only — search results and snippet() are unchanged. - vacuum() now calls optimize_fts() before VACUUM so freed index pages are returned to the OS in the same pass. - 'hermes sessions optimize' CLI subcommand for on-demand reclamation + segment compaction (previously there was no way to compact the store without a prune deleting rows), with before/after size reporting. Benchmark (8000 msgs, fragmented to 8 segments/index): - segments 8 -> 1 on both indexes - porter MATCH 5.5x faster (0.449 -> 0.081 ms/q) - trigram MATCH 3.0x faster (0.632 -> 0.207 ms/q) - 8000 matches before == 8000 after, identical row ids (no functional change) Orthogonal to the structural FTS-size PRs (#20239 external-content, #27770 optional trigram) — segment merge helps regardless of those. Tests: TestOptimizeFts covers index count, search+snippet preservation, missing-trigram path, and idempotency. Full test_hermes_state.py green (227).

alt-glitch added type/perf Performance improvement or optimization P2 Medium — degraded but workaround exists comp/agent Core agent loop, run_agent.py, prompt builder labels May 5, 2026

LeonSGP43 mentioned this pull request May 6, 2026

fix(session-search): route short CJK OR queries to LIKE #20499

Closed

alt-glitch mentioned this pull request May 18, 2026

feat(state): make trigram FTS5 index optional #27770

Open

19 tasks

JabberELF force-pushed the feat/fts-v12-external-content branch from f72b1f6 to ad232f3 Compare May 22, 2026 11:00

JabberELF changed the title ~~feat(state): switch FTS5 to external-content mode (schema v12)~~ perf(state): reduce FTS storage with external-content indexes (schema v13) May 22, 2026

plcunha mentioned this pull request May 26, 2026

fix(doctor): detect and repair state.db FTS indexes #32589

Closed

kshitijk4poor mentioned this pull request May 28, 2026

perf(state): merge FTS5 segments on VACUUM + add 'hermes sessions optimize' #34074

Closed

JabberELF force-pushed the feat/fts-v12-external-content branch from ad232f3 to 6ba4830 Compare May 30, 2026 10:54

JabberELF changed the title ~~perf(state): reduce FTS storage with external-content indexes (schema v13)~~ perf(state): reduce FTS storage with external-content indexes (schema v15) May 30, 2026

JabberELF force-pushed the feat/fts-v12-external-content branch from 6ba4830 to 903b6f9 Compare May 31, 2026 07:54

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

perf(state): reduce FTS storage with external-content indexes (schema v15)#20239

perf(state): reduce FTS storage with external-content indexes (schema v15)#20239
JabberELF wants to merge 1 commit into
NousResearch:mainfrom
JabberELF:feat/fts-v12-external-content

JabberELF commented May 5, 2026 •

edited

Loading

Uh oh!

JabberELF commented May 22, 2026

Uh oh!

JabberELF commented May 30, 2026 •

edited

Loading

Uh oh!

JabberELF commented May 31, 2026

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

2 participants

Conversation

JabberELF commented May 5, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Summary

Changes

Compatibility

Validation

Uh oh!

JabberELF commented May 22, 2026

Uh oh!

JabberELF commented May 30, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

JabberELF commented May 31, 2026

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

2 participants

JabberELF commented May 5, 2026 •

edited

Loading

JabberELF commented May 30, 2026 •

edited

Loading