perf: optimize SQLite PRAGMAs and add missing indexes by 100yenadmin · Pull Request #294 · Martian-Engineering/lossless-claw

100yenadmin · 2026-04-06T16:00:28Z

Summary

Zero-logic-change SQLite tuning for multi-GB databases with concurrent agent sessions. 16 lines changed, no behavioral differences.

Partial fix for #291

Changes

PRAGMAs (connection.ts)

PRAGMA	Before	After	Impact
`cache_size`	-2000 (2MB)	-65536 (64MB)	32x larger page cache. Demand-allocated, released on close.
`synchronous`	FULL (default)	NORMAL	~50% write latency reduction. Officially recommended for WAL mode.
`temp_store`	DEFAULT (disk)	MEMORY	Temp B-trees in RAM (helps ordinal resequencing)

Added PRAGMA optimize on connection close — updates query planner statistics for tables that changed during the session.

Missing Indexes (migration.ts)

CREATE INDEX IF NOT EXISTS summary_messages_message_idx ON summary_messages (message_id);
CREATE INDEX IF NOT EXISTS summaries_conv_kind_depth_idx ON summaries (conversation_id, kind, depth);

These eliminate full table scans on cascade deletes and condensation depth filtering.

Safety

synchronous = NORMAL in WAL mode is crash-safe for application crashes. Only risks data loss on power failure (OS/kernel crash). LCM's bootstrap process re-ingests any lost transactions from session files (the source of truth).
cache_size = -65536 is 64MB per connection, demand-allocated. 5 connections = 320MB worst case — trivial on machines running Opus 4.6.
Indexes are additive (CREATE INDEX IF NOT EXISTS) — safe for existing databases.

Test plan

197/197 tests pass locally
CI should pass (no logic changes)

Zero-logic-change performance improvements for multi-GB databases with concurrent agent sessions. PRAGMAs added to configureConnection(): - cache_size = -65536 (64MB page cache, up from 2MB default) Demand-allocated, released on close. 5 connections = 320MB max. - synchronous = NORMAL (officially recommended for WAL mode) Crash-safe for app crashes; only risks power-failure data loss. Bootstrap re-ingests any lost transactions from session files. - temp_store = MEMORY (keeps temp B-trees in RAM) Added PRAGMA optimize on connection close to update query planner statistics for tables that changed during the session. Missing indexes (cause full table scans on large databases): - summary_messages(message_id) — needed for cascade delete lookups - summaries(conversation_id, kind, depth) — needed for condensation depth filtering queries Fixes Martian-Engineering#291 (partial — PRAGMA + index portion) Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>

The summaries(conversation_id, kind, depth) index references the depth column which is added by ensureSummaryDepthColumn(). The index was in the initial schema creation (too early). Moved it to run right after the depth column migration. Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>

Copilot

Pull request overview

This PR tunes SQLite connection settings and adds a couple of indexes to improve performance on large (multi-GB) LCM databases, targeting reduced latency and fewer table scans under concurrent agent sessions (partial fix for #291).

Changes:

Adjust SQLite connection PRAGMAs (cache size, synchronous mode, temp storage) and run PRAGMA optimize on connection close.
Add missing indexes on summary_messages(message_id) and summaries(conversation_id, kind, depth).

Reviewed changes

Copilot reviewed 2 out of 2 changed files in this pull request and generated 5 comments.

File	Description
`src/db/connection.ts`	Sets additional SQLite PRAGMAs for performance and runs `PRAGMA optimize` during connection close.
`src/db/migration.ts`	Adds two new indexes intended to speed FK checks/deletes and depth-related summary queries.

💡 Add Copilot custom instructions for smarter, more guided reviews. Learn how to get started.

100yenadmin · 2026-04-06T16:18:06Z

@jalehman Ready for merge — CI green, zero logic changes (PRAGMAs + indexes only).

…ing, index order, comments 1. PRAGMA optimize in separate try block so SQLITE_BUSY doesn't skip db.close() (handle leak prevention). 2. Index column order: (conversation_id, depth, kind) instead of (conversation_id, kind, depth) — matches getDistinctDepthsInContext query pattern which filters by conversation_id + depth. 3. Fixed misleading comment on summary_messages index. Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>

100yenadmin · 2026-04-06T16:39:46Z

Part of the LCM Performance & Cache Optimization Sprint — see #297 for the full tracking issue linking all 5 PRs.

Copilot

Pull request overview

Copilot reviewed 2 out of 2 changed files in this pull request and generated 4 comments.

💡 Add Copilot custom instructions for smarter, more guided reviews. Learn how to get started.

…overhead Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>

Copilot

Pull request overview

Copilot reviewed 2 out of 2 changed files in this pull request and generated 2 comments.

💡 Add Copilot custom instructions for smarter, more guided reviews. Learn how to get started.

) Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>

Copilot

Pull request overview

Copilot reviewed 3 out of 3 changed files in this pull request and generated no new comments.

💡 Add Copilot custom instructions for smarter, more guided reviews. Learn how to get started.

100yenadmin · 2026-04-06T17:54:06Z

Merge order: 2nd — No dependencies. PRAGMAs + indexes — independent of all other PRs.

See #297 for the full sprint tracking issue with all 5 PRs.

Recommended merge sequence: #288 → #294 → #289 → #295 → #296

jalehman · 2026-04-06T22:27:04Z

Thank you!

Copilot AI review requested due to automatic review settings April 6, 2026 16:00

Copilot started reviewing on behalf of 100yenadmin April 6, 2026 16:01 View session

Copilot AI reviewed Apr 6, 2026

View reviewed changes

Comment thread src/db/connection.ts

Comment thread src/db/connection.ts

Comment thread src/db/connection.ts

Comment thread src/db/migration.ts Outdated

Comment thread src/db/migration.ts Outdated

100yenadmin mentioned this pull request Apr 6, 2026

meta: LCM Performance & Cache Optimization Sprint — All PRs #297

Open

100yenadmin requested a review from Copilot April 6, 2026 16:50

Copilot started reviewing on behalf of 100yenadmin April 6, 2026 16:51 View session

Copilot AI reviewed Apr 6, 2026

View reviewed changes

Comment thread src/db/connection.ts

Comment thread src/db/migration.ts Outdated

Comment thread src/db/migration.ts Outdated

Comment thread src/db/migration.ts Outdated

fix: move depth index after backfillSummaryDepths to avoid migration …

c9e3bf4

…overhead Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>

100yenadmin requested a review from Copilot April 6, 2026 17:03

Copilot started reviewing on behalf of 100yenadmin April 6, 2026 17:03 View session

Copilot AI reviewed Apr 6, 2026

View reviewed changes

Comment thread src/db/migration.ts

Comment thread src/db/migration.ts

test: assert perf indexes exist after migration (Martian-Engineering#291

a4b8f6a

) Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>

100yenadmin requested a review from Copilot April 6, 2026 17:15

Copilot started reviewing on behalf of 100yenadmin April 6, 2026 17:16 View session

Copilot AI reviewed Apr 6, 2026

View reviewed changes

This was referenced Apr 6, 2026

fix: defer DB init to gateway_start hook to prevent database lock race #288

Merged

feat: cache-aware compaction guards with budget-pressure priority and per-tier tuning #289

Closed

This was referenced Apr 6, 2026

perf: context cache, delta tracking, and bulk ordinal resequencing #295

Merged

feat: configurable search sort (recency/relevance/hybrid) + phrase matching fix #296

Merged

fix: route manual/overflow compaction through compact() wrapper #298

Merged

docs: add changeset for sqlite tuning PR

5279421

jalehman merged commit 43342d9 into Martian-Engineering:main Apr 6, 2026
1 check passed

github-actions Bot mentioned this pull request Apr 7, 2026

chore: version packages #301

Merged

github-actions Bot mentioned this pull request Apr 8, 2026

chore: version packages majestic7media/lossless-claw#1

Open

rmarr mentioned this pull request Apr 11, 2026

[Bug]: Migration backfill steps run unconditionally on every startup, causing gateway health check timeout on large databases #383

Closed

Conversation

100yenadmin commented Apr 6, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Summary

Changes

PRAGMAs (connection.ts)

Missing Indexes (migration.ts)

Safety

Test plan

Uh oh!

Copilot AI left a comment

Choose a reason for hiding this comment

Pull request overview

Reviewed changes

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

100yenadmin commented Apr 6, 2026

Uh oh!

100yenadmin commented Apr 6, 2026

Uh oh!

Copilot AI left a comment

Choose a reason for hiding this comment

Pull request overview

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Copilot AI left a comment

Choose a reason for hiding this comment

Pull request overview

Uh oh!

Uh oh!

Uh oh!

Copilot AI left a comment

Choose a reason for hiding this comment

Pull request overview

Uh oh!

100yenadmin commented Apr 6, 2026

Uh oh!

Uh oh!

jalehman commented Apr 6, 2026

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

3 participants

100yenadmin commented Apr 6, 2026 •

edited

Loading