Assess and migrate SQL Server Full-Text Search to Babelfish for Aurora PostgreSQL

This article provides a six-checkpoint decision tree framework for assessing whether SQL Server Full-Text Search workloads can migrate to Babelfish for Aurora PostgreSQL and what workarounds are needed.

• Babelfish provides only partial FTS support: CONTAINS works, but FREETEXT, CONTAINSTABLE, and FREETEXTTABLE are unsupported
• Checkpoint 1: Evaluate whether native PostgreSQL migration is more suitable than Babelfish for your FTS workload
• Checkpoint 2: Identify hard blockers—Semantic Search and TYPE COLUMN features cannot migrate to Babelfish
• Checkpoint 3: Validate language support; Babelfish FTS only supports English (LCID 1033)
• Checkpoint 4: Inventory custom stoplists; Babelfish doesn't support custom stoplist creation or modification
• Checkpoint 5: Categorize FTS queries as directly supported, needing custom PL/pgSQL functions, or requiring alternatives
• Checkpoint 6: Remove full-text catalog references from DDL; Babelfish doesn't support catalogs
• Use Babelfish Compass tool to analyze SQL Server DDL and identify FTS constructs before migration
• Custom PL/pgSQL functions can replicate CONTAINSTABLE behavior using PostgreSQL tsvector/tsquery engine
• Performance, ranking scores, and index population timing may differ between SQL Server and Babelfish

This framework helps classify FTS features into three categories: migrates directly, needs custom functions, or requires alternative approaches like native PostgreSQL full-text search or Amazon OpenSearch Service.