Promise-caching killed my duplicate rows (and made the merge 8x faster)

The 6-minute merge that shouldn't have been

The full-corpus merge for Statpro was crawling at about 6 minutes per season. Every single row (player bio, stint, schedule game, stat feeds) awaited its own round trip to the database, one after another. The bottleneck wasn't CPU or parsing. It was latency: thousands of sequential awaits, each burning 30-80ms of network round-trip time doing nothing.

But going concurrent naively would have created duplicate entities. If two rows reference the same player the merge hasn't seen yet, and both fire a "resolve or create" at the same time, you get two canonical rows for one person. The loser of the slug race strands an orphan. So the merge ran sequentially for correctness, and it was slow.

The trick: cache promises instead of values

Here's the move. The entity-resolution cache used to hold resolved IDs:

teamIds: Map<string, number>  // external id → canonical id
playerIds: Map<string, number>

Change it to hold promises:

teamIds: Map<string, Promise<number>>
playerIds: Map<string, Promise<number>>

Now when two concurrent merge tasks both need player X (who doesn't exist yet), the first one stores its in-flight promise in the cache. The second one sees the promise already there and awaits it. They both get the same ID back. One insert happens. No race, no orphan, no stolen slug.

// First caller: cache miss → kick off resolution, store the PROMISE
const pending = (async () => {
  const existing = await crosswalkLookup(ctx, table, column, externalId);
  if (existing !== null) return existing;

  const entityId = await insert();
  await ctx.db
    .insert(table)
    .values({ sourceSlug: ctx.sourceSlug, externalId, [column]: entityId } as never)
    .onConflictDoNothing();
  // If a concurrent writer won the crosswalk race, prefer its mapping.
  return (await crosswalkLookup(ctx, table, column, externalId)) ?? entityId;
})().catch((error: unknown) => {
  cache.delete(externalId); // don't poison the cache with a transient failure
  throw error;
});
cache.set(externalId, pending);
return pending;

The important detail is the .catch on the promise: if the insert fails, you delete the cache entry so a retry gets a fresh attempt instead of replaying a rejection forever. A poisoned cache is worse than no cache.

Bounded concurrency over a free-for-all

With entity resolution now race-proof, the merge stages can run concurrently. But unbounded Promise.all over thousands of rows would hammer the database connection limits. The fix is a tiny chunked runner:

const WRITE_CONCURRENCY = 8;

function chunks<T>(items: T[], size: number): T[][] {
  const out: T[][] = [];
  for (let i = 0; i < items.length; i += size) out.push(items.slice(i, i + size));
  return out;
}

async function eachConcurrent<T>(items: T[], run: (item: T) => Promise<void>) {
  for (const group of chunks(items, WRITE_CONCURRENCY)) {
    await Promise.all(group.map(run));
  }
}

Process 8 rows at a time, await the whole batch, move on. the database latency is the bottleneck rather than CPU, so 8 in flight keeps the pipe full without overwhelming the pool. The whole thing is ~12 lines of code and it cut the merge from ~6 min/season to under a minute.

The verification that mattered

The scary part of rewriting a working merge is silent data corruption. Same row counts don't prove anything if you've created a subtle duplicate. So I checked the thing that would actually break: every player slug was still distinct, and the counts matched the sequential version exactly (1,453 players, 2,473 games, 929 roster lines, 0 flags). Identical output, 8x faster.

The cross-process race (two separate Fargate tasks hitting the database at once) is still covered by the existing ON CONFLICT DO NOTHING on the crosswalk table with a re-read. The promise cache only deduplicates within one process. That's the right boundary: the DB handles cross-process, the cache handles in-process, and neither layer has to know about the other.

What I'd take from this

The pattern generalizes beyond merges. Any time you have a "resolve or create" path where concurrent callers might race on the same new entity (user signup by email, webhook dedup, idempotent API handlers), caching the promise instead of the value gives you deduplication for free, without locks, without a separate job queue. The two things to remember: delete on failure so the cache doesn't poison, and keep a unique constraint at the storage layer for the cross-process case the cache can't see.

The broader Statpro devlog covers the day-to-day of building a sports stats platform. This particular week was about multi-league ingestion going live (NBA and NFL joined MLB), but the promise-cache trick was the thing I was most excited to ship. It's small, it's obvious in hindsight, and it saved the merge from being unwatchably slow.