PostgreSQL Full-Text Search → Dedicated Search Engine
MediumMigrating from PostgreSQL ILIKE or tsvector full-text search to a dedicated search engine (Elasticsearch or OpenSearch) to enable relevance ranking, fuzzy matching, faceted filters, and sub-100ms query latency at scale: at the cost of index synchronization lag, Elasticsearch cluster operational burden, and eventual consistency between source and search index.
Topology Changes
From
PostgreSQL ILIKE/tsvector Full-Text Search
To
Elasticsearch or OpenSearch Dedicated Index
Topology Mutations
A dedicated search cluster: minimum 3 nodes for production (1 master-eligible, 2 data nodes minimum, or 3 nodes with all roles). Index stores denormalized documents shaped for search queries. Each document is the result of joining source tables to produce a search-optimized representation.
Operational Impact
Elasticsearch requires active JVM heap monitoring, index shard sizing, and periodic force-merge of segments. Split-brain risk with 2-node clusters makes 3 nodes the minimum viable production topology.
A service responsible for translating database change events (via CDC or dual-write) into Elasticsearch documents and issuing bulk index API calls. May be a standalone service or a Kafka consumer application.
Operational Impact
Indexer failure silently pauses index updates. Index lag monitoring and dead-letter handling for failed index operations are required operational instrumentation.
Debezium (or equivalent CDC tool) reads the PostgreSQL WAL and publishes row change events to Kafka. The indexer consumes these events and updates the Elasticsearch index. This provides near-real-time index updates (typically 1–30 seconds lag).
Operational Impact
CDC replication slot on PostgreSQL accumulates WAL if the pipeline falls behind: this can fill disk on the source database. The replication slot must be monitored as a critical metric.
Search results reflect the state of the source database with a lag determined by the CDC pipeline and indexer throughput. A document created 5 seconds ago may not yet appear in search results.
Operational Impact
Application UI must communicate search latency expectations to users. Real-time search (showing a document immediately after creation) requires routing to the source database for post-write queries rather than the search index.
Migration Stages
Provision 3-node Elasticsearch cluster. Design index mapping: field types, analyzers, and tokenization strategies. Run a full table scan to bulk-index all existing documents. Validate document count and spot-check field values. Measure bulk indexing throughput to estimate time for future re-indexes.
Deploy Debezium connector reading PostgreSQL WAL. Publish change events to Kafka. Build indexer service consuming events and issuing Elasticsearch bulk updates. Validate that index lag stays below 30 seconds under production write load. Monitor replication slot WAL accumulation on the source database.
Implement the search API using Elasticsearch. Run shadow testing: every search query hits both PostgreSQL and Elasticsearch; compare result sets for coverage and ranking differences. Document acceptable divergence (freshness delta, ranking changes).
Route 10% of search traffic to Elasticsearch backend. Measure latency, error rate, and user-visible relevance (click-through rate if measurable). Compare against PostgreSQL-backed search for the same query population. Expand to 50% once validated.
Route 100% of search traffic to Elasticsearch. Keep PostgreSQL search code path intact but inactive for 2–4 weeks as fallback. Monitor Elasticsearch cluster health, query latency, and index lag continuously.
Once Elasticsearch has been stable for 4 weeks without requiring rollback, remove the PostgreSQL ILIKE/tsvector code paths. Drop the GIN indexes on the search columns to recover write throughput on those tables.
Migration Risks
A 2-node Elasticsearch cluster is vulnerable to split-brain: both nodes can elect themselves master independently during a network partition, resulting in two clusters with diverging indexes. Recovering from split-brain requires manual intervention and potential data loss in the index.
Mitigation
Always run a minimum of 3 nodes in production. Set discovery.zen.minimum_master_nodes to (N/2)+1 (or use the modern cluster.initial_master_nodes setting). Never deploy Elasticsearch with 2 nodes, regardless of cost pressure.
If the CDC pipeline falls behind: due to high write load, indexer failure, or Kafka consumer lag : the search index diverges from the source database. Search results silently omit recent documents or show stale field values. The divergence is invisible without lag monitoring.
Mitigation
Alert on CDC pipeline lag > 60 seconds and indexer consumer lag > 1000 events. Implement a periodic reconciliation job that compares document counts between PostgreSQL and Elasticsearch and alerts on divergence beyond a threshold.
Elasticsearch relevance ranking (BM25) produces different result orderings than PostgreSQL tsvector ts_rank. Users may perceive a quality regression or improvement: either can trigger user complaints if the change is abrupt.
Mitigation
Run shadow comparison for at least 2 weeks before cutover. If possible, instrument click-through rate on search results to measure relevance quality objectively. Tune Elasticsearch field boosting to match user expectations from PostgreSQL before cutover.
Coupling Changes
Search index is a derived projection of PostgreSQL data: index schema is tightly coupled to source table structure
Consequence
Source table schema changes require corresponding index mapping updates and often a full re-index
Search availability depends on both Elasticsearch cluster health and CDC pipeline continuity
Consequence
Two new failure modes (cluster unavailability, pipeline lag) that did not exist when search ran in-database
Consistency Model Changes
- ·Search results are eventually consistent with source data: lag typically 1–30 seconds under normal load
- ·Full-text index is an approximation of the source: schema mismatches or indexer bugs can cause silent divergence
- ·There is no read-after-write guarantee from the search index: a document just created may not appear in search for 5–30 seconds
Rollback Risks
- ·Reverting to PostgreSQL search requires the GIN indexes to be rebuilt: CREATE INDEX CONCURRENTLY on a large table can take hours
- ·If GIN indexes were dropped as part of cleanup, re-creation must complete before search is functional again
- ·User-facing search behavior may have been tuned to Elasticsearch relevance: reverting to tsvector ranking may feel like a regression