NuORDER · global filter-cache rebuild

Silent data loss in a filter-cache rebuild: three root causes

Whole seasons vanished from buyers' filter sidebar for large brands. The rebuild appeared complete, but data was missing. The RCA found three independent failures.

MY ROLESole owner: investigation, fixes, architecture proposal

independent root causes: 3
ID sequence lag behind MAX(id): ~2.19B
products in affected large-brand catalogs: 200k+
rebuild runs for 200k+ product brands: ~70 min

THE SHORT VERSION

The global filter-cache rebuild for large brands silently dropped filter values. I found three independent causes: a Mongo cursor option that stopped working after a Mongoose v5 → v8 upgrade, an auto-resume cron lost in a Marathon → Kubernetes move, and a Postgres sequence ~2.19 billion behind MAX(id). I fixed all three and proposed a chunked, queue-based rebuild.

Context

Buyers filter a brand's catalog through a sidebar backed by a precomputed filter cache. A global job rebuilds that cache. For brands with 200k+ products a run takes about 70 minutes and writes into a shared PostgreSQL table.

My role

Sole owner, from the first report to the fixes and the follow-up architecture proposal.

Problem

For large brands the rebuild silently dropped filter values. Whole seasons disappeared from buyers' filter sidebar. Nothing failed loudly, so there was no single error to follow.

SYSTEM NOTE / ENGINEERING DETAIL

Symptom: filter values dropped for large brands

whole seasons missing from buyers' filter sidebar; no error raised

Mongo cursor
changed:
cursor option silently stopped working after Mongoose v5 → v8
effect:
replica-set elections killed long cursors
fix:
resumable cursor from last seen id
Auto-resume cron
changed:
lost in the Marathon → Kubernetes migration
effect:
interrupted runs never resumed
fix:
Kubernetes CronJob, all environments
Postgres sequence
changed:
~2.19B behind MAX(id)
effect:
PK conflicts, inserts silently skipped
fix:
live-safe sequence resync

One symptom, three independent causes. Fixing any one alone would have left the data loss in place.

Constraints

Long runs (~70 minutes) on brands with 200k+ products.
MongoDB replica-set elections can happen during a run and kill long-lived cursors.
The Postgres table is written live, so the sequence repair had to account for ongoing writes.
Buyers keep using filters while a rebuild runs.

Root causes

1. Cursor after a major upgrade: A MongoDB cursor option silently stopped working after the Mongoose v5 → v8 upgrade. Replica-set elections then killed long cursors mid-run, and the rebuild ended early without saying so.
2. Lost cron: The auto-resume cron job did not survive the Marathon → Kubernetes migration, so interrupted runs were never resumed.
3. Sequence lag: A Postgres ID sequence lagged ~2.19 billion behind MAX(id). Inserts hit primary-key conflicts and were silently skipped.

Fixes

One fix per root cause
Cause	Fix
Cursor dies on election	Resumable cursor that reopens from the last seen id. Feature-flagged, with capped retries.
Auto-resume cron missing	Restored as a Kubernetes CronJob in all environments.
Sequence ~2.19B behind MAX(id)	Live-safe sequence resync migration.

Architecture proposal

The fixes close the three holes. The proposal addresses the shape that let them stay hidden: one long job with no checkpoint and no visible partial state.

SYSTEM NOTE / PROPOSED ARCHITECTURE

Rebuild writes snapshot N+1

Queue of id-range chunks

ids 0…k
ids k…2k
ids 2k…3k
…

worker per chunk: ack on success, retry on failure

Filter cache, snapshot N+1

built in the background

Buyers read snapshot N

Filter sidebar

reads the last complete snapshot

switch when N+1 completes

Snapshot N+1 becomes current

no partial state is ever visible

Proposed rebuild: id-range chunks on a queue with ack/retry, writing under a new snapshot id. Buyers read the previous snapshot until the new one completes.

Chunked, queue-based rebuild by id ranges, with ack and retry per chunk.
A snapshot id per rebuild, so buyers see the old filters until the new rebuild completes.

Results & evidence

Implemented recovery mechanisms are shown below. Snapshot-based rebuilding is a follow-up proposal, not a deployed outcome. No post-fix throughput measurement is claimed.
Metric	Before	After
Long cursor during replica-set election	dies; run ends early, silently	reopens from last seen id, capped retries
Auto-resume of interrupted runs	lost in Marathon → Kubernetes move	Kubernetes CronJob in all environments
Postgres ID sequence	~2.19B behind MAX(id); inserts skipped	resynced with a live-safe migration
Partial rebuild visible to buyers	yes	proposal: snapshot id, old filters until complete

Engineering takeaways

Silent failures stack. Three independent ones looked like a single bug until each was isolated.
Infrastructure migrations (major library upgrades, scheduler moves) need an inventory of implicit behaviour, not only of code.
Long jobs need checkpoints and a visible snapshot boundary, not just retries.