MemberJunction / MemberJunction/MJ

Integration engine: composite-key records bypass the batched fast lane (per-record writes, ~20x slower)

Open
#4,173 0 comments 0 reactions 0 assignees View on GitHub
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.