[SOLVED] Database performance on large (50k notes, 12GByte) database

[oschettler]

I like Trilium very much.

However, with the size of my database, it has recently become barely usable: Unresponsive UI on both the server web as well as in apps synced and connected to it.

For context: I run the server on a Linux machine with 32GByte, 8 cores, big SSD, so, hopfully beefy enough. Both the server and clients are version 0.102.1.

I had Claude take a look at my backend log and it came up with some additional indices on the SQlite database:

sqlite3 "./document.db" <<'EOF'
CREATE INDEX IF NOT EXISTS IDX_entity_changes_entityName_isErased_entityId ON entity_changes (entityName, isErased, entityId);
CREATE INDEX IF NOT EXISTS IDX_entity_changes_entityName_isErased_entityId_id ON entity_changes (entityName, isErased, entityId, id);
CREATE INDEX IF NOT EXISTS IDX_notes_isDeleted_utcDateModified ON notes (isDeleted, utcDateModified);
CREATE INDEX IF NOT EXISTS IDX_attributes_isDeleted_utcDateModified ON attributes (isDeleted, utcDateModified);
CREATE INDEX IF NOT EXISTS IDX_attachments_isDeleted_utcDateModified ON attachments (isDeleted, utcDateModified);
CREATE INDEX IF NOT EXISTS IDX_branches_isDeleted ON branches (isDeleted);
CREATE INDEX IF NOT EXISTS IDX_notes_isDeleted ON notes (isDeleted);
ANALYZE;
EOF

I have applied these, after backing up my databases, both on the client machine and on the server. No changes to the application code were required. This has significantly improved the performance in both cases.

---

Here is the full analysis:

Trilium Notes Database Optimization

Problem

Analysis of log.txt (19MB, ~75,000 slow query entries) reveals severe performance
issues during Trilium's sync consistency checks. These checks run every ~10 seconds
and execute ~15 queries each, joining the entity_changes table (1,010,077 rows)
against various entity tables.

Worst Offenders

Query Pattern	Max Time	Occurrences	Root Cause
entity_changes JOIN attributes (isErased=1)	1,047,403ms (17 min!)	4,998	No index on (entityName, isErased)
entity_changes LEFT JOIN attributes (isErased=0)	901,758ms	4,998	Same
entity_changes LEFT JOIN revisions	773,839ms	4,998	Same
attributes LEFT JOIN entity_changes	346,133ms	4,998	Reverse join direction
Orphan blob detection (triple LEFT JOIN)	128ms	5,002	Already indexed, inherently expensive
All other entity_changes consistency queries	20-40ms	~40,000	Same missing index

Root Cause Analysis

The entity_changes table has only two indices:

• IDX_entityChanges_entityName_entityId — UNIQUE on (entityName, entityId)
• IDX_entity_changes_changeId — on (changeId)

The sync consistency queries filter by (entityName, isErased) then join on entityId,
but no index covers this combination. The existing UNIQUE index starts with entityName
but doesn't include isErased, forcing SQLite to scan all rows for a given entityName
and then filter by isErased in a second pass.

With 640K+ attribute-related rows in entity_changes, this means scanning hundreds of
thousands of rows every 10 seconds.

Solution: 7 New Indices

Critical (sync performance)

1. IDX_entity_changes_entityName_isErased_entityId on entity_changes(entityName, isErased, entityId)

   • Allows direct seek to the right (entityName, isErased) partition
   • Provides entityId for the join without touching the main table

2. IDX_entity_changes_entityName_isErased_entityId_id on entity_changes(entityName, isErased, entityId, id)

   • Fully covering index: queries that SELECT both id and entityId never need to access the main table at all

Important (cleanup queries)

3. IDX_notes_isDeleted_utcDateModified on notes(isDeleted, utcDateModified)
4. IDX_attributes_isDeleted_utcDateModified on attributes(isDeleted, utcDateModified)
5. IDX_attachments_isDeleted_utcDateModified on attachments(isDeleted, utcDateModified)

Minor (general filtering)

6. IDX_branches_isDeleted on branches(isDeleted)
7. IDX_notes_isDeleted on notes(isDeleted)

Expected Impact

• Sync consistency checks: 700ms+ → <10ms for attribute queries (the worst offender)
• Overall sync cycle: ~2-3 seconds of slow queries per cycle → <100ms
• Cleanup queries: Occasional multi-second queries → <10ms
• No code changes required: Pure database-level optimization

Usage

sqlite3 "./document.db" <<'EOF'
CREATE INDEX IF NOT EXISTS IDX_entity_changes_entityName_isErased_entityId ON entity_changes (entityName, isErased, entityId);
CREATE INDEX IF NOT EXISTS IDX_entity_changes_entityName_isErased_entityId_id ON entity_changes (entityName, isErased, entityId, id);
CREATE INDEX IF NOT EXISTS IDX_notes_isDeleted_utcDateModified ON notes (isDeleted, utcDateModified);
CREATE INDEX IF NOT EXISTS IDX_attributes_isDeleted_utcDateModified ON attributes (isDeleted, utcDateModified);
CREATE INDEX IF NOT EXISTS IDX_attachments_isDeleted_utcDateModified ON attachments (isDeleted, utcDateModified);
CREATE INDEX IF NOT EXISTS IDX_branches_isDeleted ON branches (isDeleted);
CREATE INDEX IF NOT EXISTS IDX_notes_isDeleted ON notes (isDeleted);
ANALYZE;
EOF

[openapphub]

@eliandoran Is it feasible? Can it be used directly? It looks very impressive.

[eliandoran]

Yes, I believe @perfectra1n already made a PR inspired by this: #9141

[openapphub]

Yes, I believe @perfectra1n already made a PR inspired by this: #9141

I am very much looking forward to the next version. Based on my personal experience, the current version is already quite comprehensive. Considering some optimizations around performance and resource usage would make it even better.

[eliandoran]

This is a genuine good use case of LLMs, because the change is relatively small and it can bring a pretty good performance improvement. Personally I haven't noticed these performance issues, but I am glad you found a solution.

If we merge #9141 you might want to be careful when upgrading since you've altered the database externally, which Trilium probably won't expect.