PostgreSQL ILIKE/tsvector Full-Text Search→Elasticsearch or OpenSearch Dedicated Index
Duration Estimate
2–6 weeks
Migration Stages
6
Blocking Prerequisites
2
Confidence
StrongReadiness Checklist(2 blocking, 2 advisory)
Team at senior maturity level
This migration requires senior engineering maturity. The team should have hands-on experience with the relevant technologies and proven ability to execute complex system changes safely.
Guidance: Build team skills through learning modules before attempting this migration.
Production observability baseline established
Comprehensive metrics, logs, and traces must be in place before the migration starts. You need a clear baseline to detect regressions during and after each migration stage.
Guidance: Instrument key metrics (latency p99, error rate, throughput, resource utilization) on all affected components.
Rollback plan documented and tested
Rollback feasibility for this migration is 'complex'. Rollback is possible but may be complex, document the exact rollback steps and test them in a non-production environment.
Guidance: Run a full dry-run in a staging environment that mirrors production load characteristics.
Migration trigger condition confirmed
Confirm that the actual trigger for this migration is present. Common triggers: Search query p99 latency exceeds 500ms as dataset grows: ILIKE queries cause sequential scans that bypass indexes; Product requires relevance ranking by score, freshness, or popularity: PostgreSQL tsvector ranking (ts_rank) is insufficient; Faceted search requirements (filter by category, brand, price range simultaneously) are complex in relational SQL.
Guidance: Validate the trigger with production metrics or benchmarks before committing to the migration timeline.
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.
Key tasks
- ·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.
- ·Validate rollback capability at this stage boundary before proceeding.
Exit criteria
- ✓'Deploy Elasticsearch Cluster and Build Initial Index' validated in production with no regression in key metrics
Rollback: possible at this stage
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.
Key tasks
- ·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.
- ·Validate rollback capability at this stage boundary before proceeding.
Exit criteria
- ✓'Set Up CDC Pipeline for Index Freshness' validated in production with no regression in key metrics
Rollback: possible at this stage
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).
Key tasks
- ·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).
- ·Validate rollback capability at this stage boundary before proceeding.
Exit criteria
- ✓'Build Elasticsearch Search API and Shadow Test' validated in production with no regression in key metrics
Rollback: possible at this stage
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.
Key tasks
- ·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.
- ·Validate rollback capability at this stage boundary before proceeding.
Exit criteria
- ✓'A/B Traffic Split' validated in production with no regression in key metrics
Rollback: possible at this stage
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.
Key tasks
- ·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.
- ·Validate rollback capability at this stage boundary before proceeding.
Exit criteria
- ✓'Full Cutover' validated in production with no regression in key metrics
Rollback: possible at this stage
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.
Key tasks
- ·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.
- ·No rollback available after this stage, confirm all exit criteria before proceeding.
Exit criteria
- ✓'Remove PostgreSQL Search Code' validated in production with no regression in key metrics
Rollback: not available after this stage
Rollback Decision Points
After stage 5
Trigger: Any of the following in the 30 minutes after completing stage 5: p99 latency increases >50% from baseline, error rate exceeds 1%, or data consistency checks fail.
Procedure: Revert stage 5 changes. Rollback is complex, follow the documented rollback procedure for 'search-to-dedicated-index'. Engage senior engineering support.
Recovery: 1–4 hours (complex rollback)
After stage 6
Trigger: Final validation: confirm all success criteria are met within 24 hours of migration completion. If any success criterion fails, trigger rollback or remediation.
Procedure: If success criteria are not met within 24 hours, escalate to the architecture team. Decide between full rollback, partial rollback, or targeted remediation based on scope.
Recovery: 24–72 hours for full assessment and remediation
Success Criteria
- ✓Target state 'Elasticsearch or OpenSearch Dedicated Index' is stable in production for 72+ hours with no regressions.
- ✓p99 latency on all affected services is within acceptable range (≤110% of pre-migration baseline).
- ✓Error rate is at or below pre-migration baseline.
This migration playbook is derived from structured architecture knowledge. Duration estimates and risk levels are structural approximations based on documented migration complexity and operational risk, not measured execution data. Validate all stages against your specific system constraints before executing.