Automattic / Automattic/cortext

Batch the sidecar reindex after relation writes

Open
#303 0 comments 0 reactions 0 assignees View on GitHub
area: performance type: enhancement
Dominant language
JavaScript
Stars
51
Forks
2
Avg merge
3h 12m
Merged PRs (30d)
8

Description

#280 batched the heavy part of the relation write path: the forward write and the reverse pointers now go out in bulk (`fast_write_forward_meta` and `apply_relation_pointers`), which took `relation_many_targets` from ~18s to ~460ms. One bit stayed per-row though, the sidecar reindex. `reindex_targets` still loops and calls `index_row_field` once for every target document it touched, so 500 targets is 500 × (SELECT + DELETE + N INSERTs). That loop is all this issue is about.

**Proposed solution:** the pointer write already hands us the delta (which targets were added, which were removed), so build the sidecar delta from that and write it in two statements instead of a loop: one `DELETE ... WHERE (row_id, field_id, value) IN (...)` for the removals, one multi-row `INSERT` for the additions. The forward field can stay on `index_row_field`, it's a single row.

Contributor guide

Open the contributing guide

Research direction

Start at reindex_targets and trace the pointer-write path, especially the added and removed target deltas from fast_write_forward_meta and apply_relation_pointers. Confirm that sidecar removals use one DELETE and additions use one multi-row INSERT, while the single forward field still uses index_row_field.

Written by the indexing model from the issue text.

Assessment

Tech stack
javascript, sql
Domain
database, performance
Issue type
Refactor
Difficulty
3/5
Estimated time
1-2 days
Activity status
Quiet
Clarity
Clearly specified
Newbie friendliness
68/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.