Search is slow, so the team adds Elasticsearch. Six weeks later the catalog shows products that were delisted yesterday.
That is not an Elasticsearch problem. It is what happens when a search index becomes a second source of truth without anyone owning the sync.
The pattern we keep seeing: a product query gets heavy, someone puts the data into an index, and the write path grows a fork. One branch goes to PostgreSQL, the other to the index. They agree on the happy path. They disagree whenever a write fails halfway, a background job dies quietly, or a bulk import runs outside the normal flow. Nobody notices, because a stale index does not throw errors. It just answers wrong.
We run Postgres, MongoDB, Redis and Elasticsearch in production, and our rule is simple: the index is disposable, the database is not. Reindexing must be a routine operation you can trigger at any time, not a migration nobody dares to touch. Sync goes through a queue with retries, and there is a reconciliation job that compares counts and flags drift before a user does.
Most catalogs we have worked on could have gone further on Postgres than the team assumed — full-text search and the right indexes carry more load than people expect.
What finally forced you off your primary database for search — query latency, ranking quality, or something else?