--- title: Migrate multilingual full-text search from SQL Server to... description: Migrating full-text search from SQL Server to Aurora PostgreSQL or Amazon RDS for PostgreSQL can silently change search results because the two engines handle... canonical: https://daily.dev/posts/migrate-multilingual-full-text-search-from-sql-server-to-postgresql-ugg1dxlwv twitter:card: summary_large_image twitter:site: @dailydotdev og:type: website og:site_name: daily.dev og:title: Migrate multilingual full-text search from SQL Server to PostgreSQL | daily.dev og:description: Migrating full-text search from SQL Server to Aurora PostgreSQL or Amazon RDS for PostgreSQL can silently change search results because the two engines handle... og:url: https://daily.dev/posts/migrate-multilingual-full-text-search-from-sql-server-to-postgresql-ugg1dxlwv og:image: https://api.daily.dev/og/posts/ugg1DxLwv.png og:image:alt: Migrate multilingual full-text search from SQL Server to PostgreSQL og:image:width: 1200 og:image:height: 630 og:locale: en --- > ## Documentation Index > Fetch the complete documentation index at: https://daily.dev/llms.txt > Use this file to discover all available pages before exploring further. # Migrate multilingual full-text search from SQL Server to PostgreSQL **[AWS Database Blog](https://daily.dev/sources/aws-database-blog)** · 12 min read · 0 upvotes · 0 comments ## Summary Migrating full-text search from SQL Server to Aurora PostgreSQL or Amazon RDS for PostgreSQL can silently change search results because the two engines handle collation, tokenization, and accents differently. The guide compares SQL Server's collation, stoplists, and thesaurus XML approach with PostgreSQL's text search configurations, ICU collations, and the unaccent and pg_bigm extensions. It walks through language-specific tokenization for eight languages, accent-insensitive search setup, synonym expansion via custom functions, indexing and performance considerations (GIN vs GiST, autovacuum, partitioning), known limitations (no file-based dictionaries, compound-word handling, Arabic/Hebrew stemming gaps), and a migration checklist covering collation review, dictionary mapping, query rewriting, and validation. ## Full article daily.dev links to this article rather than hosting it. Read it at the original source: ## Questions this post answers ### Why does searching for 'cafe' stop matching 'café' after migrating full-text search from SQL Server to PostgreSQL? Accent handling is controlled differently in each engine: SQL Server uses the full-text catalog's ACCENT_SENSITIVITY setting, while PostgreSQL separates ordinary collation from full-text tokenization entirely. To get accent-insensitive full-text search in PostgreSQL, apply a custom text search configuration built with the unaccent dictionary consistently when indexing documents and processing queries. _Track these engine-specific quirks on daily.dev when validating a SQL Server to PostgreSQL search migration._ ### How do I implement synonym search like SQL Server's thesaurus XML in PostgreSQL when using Aurora or RDS? Aurora PostgreSQL and RDS for PostgreSQL don't allow installing file-based thesaurus or ispell dictionaries under $SHAREDIR, so thesaurus-style synonym expansion (car = automobile) must be implemented with database tables and a custom function, such as a user-defined make_syn_tsquery function that expands a search term into an OR'd tsquery of its synonyms before matching against tsvector documents. _Developers rebuilding synonym search on managed PostgreSQL can compare approaches on daily.dev before committing to one._ ### Which PostgreSQL extension should I use for full-text search on Japanese, Chinese, or Korean text? Use pg_bigm, which provides bigram-based GIN indexing for LIKE, regular-expression, and similarity searches rather than relying on to_tsvector dictionaries. It splits CJK text like '東京駅' into overlapping bigrams, enabling partial matching without predefined language dictionaries; Aurora PostgreSQL 16.8 supports pg_bigm 1.2 alongside ICU 60.2 and unaccent 1.1. _Engineers picking CJK search strategies on PostgreSQL can weigh options like this on daily.dev._ ## Similar posts on daily.dev - [Postgres Full-Text Search Instead of Elasticsearch](https://daily.dev/posts/postgres-full-text-search-instead-of-elasticsearch-85slaibvz) · Prisma Blog · 0 upvotes · 0 comments - [PGroonga 4.0.4 - Multilingual fast full text search](https://daily.dev/posts/pgroonga-4-0-4---multilingual-fast-full-text-search-vjjiph3vb) · PostgreSQL · 10 upvotes · 0 comments - [Babelfish for Aurora PostgreSQL performance tuning](https://daily.dev/posts/babelfish-for-aurora-postgresql-performance-tuning-ugkvx7bx6) · AWS Database Blog · 0 upvotes · 0 comments - [Polish configuration for TSearch – select \* from depesz;](https://daily.dev/posts/polish-configuration-for-tsearch-select-from-depesz--stzeofzu6) · depesz · 0 upvotes · 0 comments --- Tags: [#aws](https://daily.dev/tags/aws), [#database](https://daily.dev/tags/database), [#postgresql](https://daily.dev/tags/postgresql), [#microsoft-sql-server](https://daily.dev/tags/microsoft-sql-server), [#full-text-search](https://daily.dev/tags/full-text-search) [View this post on daily.dev](https://daily.dev/posts/migrate-multilingual-full-text-search-from-sql-server-to-postgresql-ugg1dxlwv) ```json {"@context":"https://schema.org","@graph":[{"@type":"Organization","@id":"https://daily.dev/#organization","name":"daily.dev","url":"https://daily.dev","logo":{"@type":"ImageObject","url":"https://daily.dev/apple-touch-icon.png","width":180,"height":180},"sameAs":["https://twitter.com/dailydotdev","https://github.com/dailydotdev","https://www.linkedin.com/company/daily-dev-ltd"]},{"@type":"WebSite","@id":"https://daily.dev/#website","url":"https://daily.dev","name":"daily.dev","publisher":{"@id":"https://daily.dev/#organization"},"potentialAction":{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https://daily.dev/search?q={search_term_string}"},"query-input":"required name=search_term_string"}}]} {"@context":"https://schema.org","@type":"TechArticle","headline":"Migrate multilingual full-text search from SQL Server to PostgreSQL","url":"https://daily.dev/posts/migrate-multilingual-full-text-search-from-sql-server-to-postgresql-ugg1dxlwv","mainEntityOfPage":{"@type":"WebPage","@id":"https://daily.dev/posts/migrate-multilingual-full-text-search-from-sql-server-to-postgresql-ugg1dxlwv"},"datePublished":"2026-08-20T20:35:42.483Z","dateModified":"2026-09-14T08:07:07.256Z","description":"Migrating full-text search from SQL Server to Aurora PostgreSQL or Amazon RDS for PostgreSQL can silently change search results because the two engines handle...","image":"https://media.daily.dev/image/upload/f_auto,q_auto/v1/posts/b764617ee32b73fc3bc7b7d155f0cbfd?_a=AQAEuop","thumbnailUrl":"https://media.daily.dev/image/upload/f_auto,q_auto/v1/posts/b764617ee32b73fc3bc7b7d155f0cbfd?_a=AQAEuop","isAccessibleForFree":true,"articleSection":"AWS Database Blog","inLanguage":"en","publisher":{"@type":"Organization","name":"daily.dev","url":"https://daily.dev","logo":{"@type":"ImageObject","url":"https://daily.dev/apple-touch-icon.png","width":180,"height":180}},"author":{"@type":"Organization","name":"AWS Database Blog","logo":"https://media.daily.dev/image/upload/s--En-avd2a--/f_auto,q_auto/v1780213446/logos/aws-database-blog?_a=BAMAMiWQ0","url":"https://daily.dev/sources/aws-database-blog"},"commentCount":0,"discussionUrl":"https://daily.dev/posts/migrate-multilingual-full-text-search-from-sql-server-to-postgresql-ugg1dxlwv","interactionStatistic":[{"@type":"InteractionCounter","interactionType":{"@type":"LikeAction"},"userInteractionCount":0},{"@type":"InteractionCounter","interactionType":{"@type":"CommentAction"},"userInteractionCount":0}],"keywords":"aws,database,postgresql,microsoft-sql-server,full-text-search","timeRequired":"PT12M"} {"@context":"https://schema.org","@type":"BreadcrumbList","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https://daily.dev"},{"@type":"ListItem","position":2,"name":"AWS Database Blog","item":"https://daily.dev/sources/aws-database-blog"},{"@type":"ListItem","position":3,"name":"Migrate multilingual full-text search from SQL Server to PostgreSQL"}]} {"@context":"https://schema.org","@type":"FAQPage","@id":"https://daily.dev/posts/migrate-multilingual-full-text-search-from-sql-server-to-postgresql-ugg1dxlwv#faq","mainEntity":[{"@type":"Question","name":"Why does searching for 'cafe' stop matching 'café' after migrating full-text search from SQL Server to PostgreSQL?","acceptedAnswer":{"@type":"Answer","text":"Accent handling is controlled differently in each engine: SQL Server uses the full-text catalog's ACCENT_SENSITIVITY setting, while PostgreSQL separates ordinary collation from full-text tokenization entirely. To get accent-insensitive full-text search in PostgreSQL, apply a custom text search configuration built with the unaccent dictionary consistently when indexing documents and processing queries. Track these engine-specific quirks on daily.dev when validating a SQL Server to PostgreSQL search migration."}},{"@type":"Question","name":"How do I implement synonym search like SQL Server's thesaurus XML in PostgreSQL when using Aurora or RDS?","acceptedAnswer":{"@type":"Answer","text":"Aurora PostgreSQL and RDS for PostgreSQL don't allow installing file-based thesaurus or ispell dictionaries under $SHAREDIR, so thesaurus-style synonym expansion (car = automobile) must be implemented with database tables and a custom function, such as a user-defined make_syn_tsquery function that expands a search term into an OR'd tsquery of its synonyms before matching against tsvector documents. Developers rebuilding synonym search on managed PostgreSQL can compare approaches on daily.dev before committing to one."}},{"@type":"Question","name":"Which PostgreSQL extension should I use for full-text search on Japanese, Chinese, or Korean text?","acceptedAnswer":{"@type":"Answer","text":"Use pg_bigm, which provides bigram-based GIN indexing for LIKE, regular-expression, and similarity searches rather than relying on to_tsvector dictionaries. It splits CJK text like '東京駅' into overlapping bigrams, enabling partial matching without predefined language dictionaries; Aurora PostgreSQL 16.8 supports pg_bigm 1.2 alongside ICU 60.2 and unaccent 1.1. Engineers picking CJK search strategies on PostgreSQL can weigh options like this on daily.dev."}}]} ```