The Search Index That Drifted From the Database

A client's product search showed deleted items, missed new ones, and displayed yesterday's prices. The Elasticsearch index had been quietly diverging from PostgreSQL for months.

The bug report was vague in that way that means something is deeply wrong. "Search results seem off." No stack trace. No error code. Just a product manager who'd noticed that searching for a product by its exact name sometimes returned nothing, while a product they'd discontinued two weeks ago still showed up with its old price.

I was six days into a consulting engagement with an e-commerce company — mid-size, about 200k SKUs, a Rails backend with PostgreSQL and Elasticsearch powering their product search. They'd hired me to help with performance work, but this search issue had been nagging the team for months. Nobody had made it a priority because it wasn't broken. It was just... wrong, sometimes.

That "sometimes" should have been the red flag.

The architecture that looked fine on paper

The setup was standard. Products lived in PostgreSQL as the source of truth. When a product was created, updated, or deleted, a background job would sync the change to an Elasticsearch index. The API hit Elasticsearch for search queries and PostgreSQL for individual product pages.

class Product < ApplicationRecord
  after_commit :sync_to_search_index
 
  def sync_to_search_index
    SearchIndexJob.perform_async(id, destroyed? ? :delete : :upsert)
  end
end

Clean, reasonable. The kind of code you'd write in a tutorial.

The team also had a full reindex script that would dump all products from PostgreSQL and rebuild the Elasticsearch index from scratch. They ran it during the initial setup and... never again.

Counting the drift

Before diving into the sync code, I wanted to quantify how bad things actually were. I wrote a quick script to compare the two data sources.

pg_ids = Product.where(active: true).pluck(:id).to_set
es_ids = search_client.scroll_all_ids("products").to_set
 
ghost_ids = es_ids - pg_ids      # In ES but not in PG
missing_ids = pg_ids - es_ids    # In PG but not in ES
 
puts "Ghost products in search: #{ghost_ids.size}"
puts "Missing from search: #{missing_ids.size}"

The numbers were worse than anyone expected. 1,847 ghost products — items that had been deleted or deactivated in PostgreSQL but still appeared in search results. 312 missing products — items that existed in the database but had never made it into the index. And I hadn't even started checking field-level drift, like prices and descriptions that had been updated in PostgreSQL but were stale in Elasticsearch.

When I ran a price comparison on matching records, 6% of indexed products had a price that didn't match the database. Some of these price mismatches were weeks old.

Three ways the sync was failing

The root cause wasn't one thing. It was three things, each individually minor, combining into a slow data rot.

First: swallowed errors in the background job. The Sidekiq job that synced products to Elasticsearch had a broad rescue clause that caught exceptions, logged them at warn level, and moved on. The Elasticsearch cluster would occasionally reject writes during high-load periods — bulk indexing requests timing out, the cluster hitting its thread pool limit. These rejections were logged, but nobody was watching warn-level logs from background jobs. Each rejected write was a product that silently fell out of sync.

Second: the after_commit callback didn't fire on bulk updates. The merchandising team had an admin tool that let them update prices for an entire category at once. This tool used update_all for performance, which is the right call when you're touching 5,000 rows. But update_all skips ActiveRecord callbacks. No callback, no sync job, no Elasticsearch update. Every bulk price change created a batch of stale search data.

Third: deletes were racing against the queue. When a product was deleted, the after_commit callback fired and enqueued a job to remove it from Elasticsearch. But if the Sidekiq queue was backed up (which happened during bulk imports), the delete job might not run for several minutes. In the meantime, other jobs that referenced the deleted product's associations would fail, and some of those failures cascaded in ways that left the Elasticsearch document orphaned.

The fix was boring, and that's the point

There's no clever architectural insight here. The fix was three straightforward changes.

For the swallowed errors, I added retry logic with exponential backoff and a dead letter queue. Jobs that failed after three retries got flagged for manual review. More importantly, I added a metric that tracked the sync error rate. Making failures visible made them fixable.

For the bulk update problem, I replaced the after_commit approach with a Change Data Capture pattern. Instead of relying on application-level callbacks, we tailed the PostgreSQL write-ahead log using Debezium. Every write to the products table — whether it came from ActiveRecord, raw SQL, or an admin console — generated a change event that fed into the Elasticsearch sync pipeline.

# Simplified Debezium connector config
connector.class: io.debezium.connector.postgresql.PostgresConnector
database.hostname: primary-db.internal
database.dbname: shop_production
table.include.list: public.products
plugin.name: pgoutput
publication.name: search_sync

For the delete race condition, the CDC approach solved it inherently — deletes in the WAL are ordered and guaranteed, so they can't get lost in an application-level queue.

The last piece was a scheduled consistency check. Every night, a job compared a random 5% sample of products between PostgreSQL and Elasticsearch, flagged any drift, and auto-repaired it. Think of it as a background reconciliation loop.

What I keep seeing

This is the fourth time I've encountered some version of this problem at different clients. The details change — sometimes it's Algolia instead of Elasticsearch, sometimes it's a search index and a cache, sometimes it's two databases that are supposed to stay in sync. The pattern is the same: a derived data store that starts accurate, drifts slowly, and breaks trust so gradually that nobody can point to the moment it went wrong.

The dangerous thing about index drift is that it doesn't throw errors. Your application keeps serving pages. Your monitoring stays green. Users notice before your systems do, and users don't file bugs that say "your search index has diverged from your primary database." They say "search seems off."

If you run a search index alongside a primary database, two questions worth asking: when did you last verify they agree? And what happens to sync when your application doesn't use the ORM's happy path?