MemberJunction / MemberJunction/MJ
Integration engine: composite-key records bypass the batched fast lane (per-record writes, ~20x slower)
- Dominant language
- TSQL
- Stars
- 29
- Forks
- 6
- Avg merge
- 2d 1h
- Merged PRs (30d)
- 323
Description
## Problem
The integration engine's batched write path (prefetch-proves-absence + set-based existence checks) only engages for single-column external keys. Objects whose identity is a composite key (e.g. a 3-column business key on a table with no natural single id) fall back to per-record existence checks and individual saves.
Observed on a live PG instance: single-key objects sustain ~15,000 records/min through the batched lane, while a 3-column composite-key object on the same connection and hardware sustains ~800 records/min — roughly 20x slower. A ~70k-row object that would take ~5 minutes as single-key takes ~90 minutes as composite.
This is not just a first-sync cost: subsequent incremental/full syncs of composite-key objects also ride the per-record lane for their skip/compare work, so steady-state scheduled syncs pay it every run.
## Expected
Composite keys should ride the same batched lane: prefetch by key tuple (joined key or multi-column IN/VALUES probe), set-based absence proof, and batched submits — identical semantics to the single-key path, keyed on the ordered tuple of key fields.
## Notes
- The engine already carries the tuple (the record's external identity is the joined key string in run events), so the join/format convention exists; the prefetch/probe layer just never learned to use it for >1 column.
- Related: the composite tuple should also drive the in-batch duplicate-identity collapse consistently with single keys (it appears to, but worth pinning in the same change's tests).
🤖 Generated with [Claude Code](https://claude.com/claude-code)
Contributor guide
Research direction
No file or test is named in the issue; start by locating the integration engine's batched write path and its prefetch/probe layer, then trace how joined external-key strings become lookup keys. Done means composite-key records use tuple-based prefetch, set-based absence checks, batched submits, and consistent duplicate-identity handling, with coverage for initial and incremental syncs.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- postgresql, sql
- Domain
- backend, databases, performance
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Active
- Clarity
- Mostly clear
- Newbie friendliness
- 52/100