upsert_entity takes 1.8s avg for single entity writes on cloud

[phernandez]

Problem

entity_service.edit.upsert_entity averages 1.8s (P50: 1.89s, P95: 2.84s) for a single entity upsert. In the worst case (trace 019d49a39cffc28f742bca0fd6a8793f), it took 2.6s. This is the single largest span in the write path.

For create, upsert_entity averages 1.13s (P50: 0.78s, P95: 1.97s).

This is likely multiple sequential DB queries to Neon PostgreSQL: upserting the entity row, then diffing and upserting observations, then diffing and upserting relations — each paying ~200-300ms network latency.

Evidence (last 12h)

Span	Avg	P50	P95	Calls
entity_service.edit.upsert_entity	1.79s	1.89s	2.84s	81
entity_service.create.upsert_entity	1.13s	0.78s	1.97s	49
entity_service.update.upsert_entity	1.07s	1.08s	1.24s	3
entity_service.edit.update_checksum	0.26s	0.24s	0.38s	81
entity_service.create.update_checksum	0.26s	0.25s	0.33s	49

Full edit_note write path breakdown (worst case trace):

write_entity                          4.201s
├── resolve_entity                    0.330s  (DB lookup)
├── read_file                         0.406s  (S3 get)
├── apply_operation                   0.002s  (in-memory edit)
├── write_file                        0.428s  (S3 put)
├── parse_markdown                    0.084s  (CPU)
├── upsert_entity                     2.612s  ← biggest span
└── update_checksum                   0.334s  (DB update)

Investigation Areas

1. Profile the queries inside upsert_entity — add telemetry sub-spans for entity upsert, observation diff/upsert, relation diff/upsert to identify which DB operations are slowest
2. Batch DB writes — if upsert_entity does multiple sequential INSERT/UPDATE statements, consider combining them into fewer round-trips (e.g., bulk upsert observations in one query)
3. update_checksum as separate query — the 0.26s checksum update could potentially be folded into the upsert transaction to eliminate a round-trip

Impact

upsert_entity + update_checksum account for ~2s of the ~3.3s avg write_entity duration. Reducing DB round-trips here would directly improve edit_note (P50: 6.7s) and write_note (P50: 6.2s) — the two slowest MCP tools.

[claude]

Claude finished @phernandez's task in 1m 41s —— View job

---

Triage Analysis

• Gather context and read issue
• Investigate upsert_entity implementation
• Check for duplicate issues (none found)
• Apply labels and complete triage

---

Classification

Field	Value
Type	Enhancement (performance improvement)
Priority	High — directly impacts P50 latency of edit_note (6.7s) and write_note (6.2s), the two core MCP write tools
Component	Database / Cloud (Neon PostgreSQL)
Complexity	Complex — requires batching/combining sequential DB operations

Labels applied: enhancement, cloud

---

Root Cause Analysis

After reading entity_service.py, the upsert_entity_from_markdown (edit path) issues 6–8+ sequential DB round trips to Neon, each paying ~200–300ms network latency:

update_entity_and_observations (4 queries):

1. get_by_file_path — fetch existing entity
2. delete_by_fields (observations) — clear old observations
3. add_all (observations) — insert new observations
4. update (entity) — update entity fields

update_entity_relations (3–5 queries):
5. get_by_file_path — duplicate fetch (entity was already loaded in step 1)
6. delete_outgoing_relations — clear old relations
7. N × parallel get_by_permalink calls to resolve relation targets
8. add_all (relations) — insert new relations

update_checksum (1 query):
9. Separate UPDATE to set checksum

At 200–300ms per round trip, 6–9 sequential queries = 1.2–2.7s, which matches the observed P50 of 1.89s exactly.

---

Recommended Fix Areas

1. Eliminate the duplicate get_by_file_path in update_entity_relations — pass the already-loaded entity instead of re-fetching by path (entity_service.py:901)

2. Merge update_checksum into the final entity update — fold the checksum write into the last repository.update() call to save one round-trip

3. Combine delete + insert for observations/relations into single transactions — use bulk upsert patterns instead of separate delete-then-insert

4. Add telemetry sub-spans inside upsert_entity_from_markdown to pinpoint which query is slowest (as suggested in the issue)

The quick wins (items 1 and 2) could reduce round trips by 2, saving ~400–600ms with minimal risk.