SproutStackSproutStack home
SeniorSearch (Elasticsearch)

Search shows products that no longer exist

The app writes to PostgreSQL and Elasticsearch separately, and they drift.

The setup today

When a product changes, the API writes PostgreSQL and then calls Elasticsearch in the same request. Deletions and bulk imports sometimes skip the second call. The index was created with one primary shard years ago and is now 400 GB.

Current configuration

api: UPDATE products ...; COMMIT;  then  POST products/_doc/{id}
bulk import: writes PostgreSQL only
index: 1 primary shard, 400 GB

What you know

  • About 0.3% of products in search are stale or deleted.
  • Search latency has crept up as the index grew, and recovery after a node restart takes hours.

The new requirement

Search must reflect every change within a minute, whichever code path made it, and the index must scale with the catalog.

Make the call

2 questions · first-try answers earn the most XP

1. How do you keep the index in sync reliably?
2. What do you do about the 400 GB single shard?
What a strong answer also covers
  • CDC events can arrive more than once and out of order across keys: make indexing idempotent and use the row version to ignore older updates.
  • Run a periodic reconciliation job that compares counts and samples, as a check rather than the main path.
  • Plan shard counts for the catalog in two years, since resharding means reindexing.
  • Measure end-to-end freshness: time from database commit to searchable.
Variations to think through

Change one fact and decide again. There is no single right answer here.

  • Some products should disappear from search instantly when a seller is banned. Is CDC fast enough?
  • The search document combines data from five tables. How does a change in one table update it?
  • Traffic is 90% searches for the newest 5% of products. Would time-based indices help?

The background

More drills