Migrate multilingual full-text search from SQL Server to PostgreSQL

After migrating full-text search from SQL Server to PostgreSQL, equivalent searches can silently return different results. For example, cafe may no longer match café, and stemming behavior can diverge. This happens because the two engines handle text comparison, linguistic processing, and accents differently. A previous AWS Database Blog post demonstrated the core steps for migrating full-text search (FTS) from Microsoft SQL Server to Amazon Aurora PostgreSQL-Compatible Edition or Amazon Relational Database Service (Amazon RDS) for PostgreSQL.

SQL Server uses collation for ordinary comparison and ordering, the indexed column’s language for word breaking and stemming, and the full-text catalog’s ACCENT_SENSITIVITY setting for FTS accent handling. PostgreSQL uses collation for ordinary comparisons and text search configurations for tokenization and normalization. In PostgreSQL, use an unaccent dictionary to implement accent-insensitive FTS.

Native PostgreSQL FTS keeps search close to transactional data and provides transactional consistency. Consider Amazon OpenSearch Service for features such as fuzzy matching, custom analyzers, faceting, highlighting, independent scaling, or workload isolation. In this post, we show how to reproduce SQL Server full-text search behavior on Aurora PostgreSQL and RDS for PostgreSQL. We walk through collation compared to text search configuration, language-specific tokenization for eight languages, accent-insensitive search, and synonym expansion. We also outline what to test to confirm behavior matches.

Prerequisites

To run the sample queries, you need:

1. An Aurora PostgreSQL DB cluster or an RDS for PostgreSQL DB instance. We validated the examples with SQL Server 2022 CU16 (build 16.0.4165.4) and Aurora PostgreSQL 16.8.
2. AWS CloudShell, which includes the psql client, or another PostgreSQL client with the necessary database credentials and network connectivity to the DB cluster or instance.
3. Support for the required International Components for Unicode (ICU) functionality and extensions, including unaccent and pg_bigm. Verify availability for your target engine version in the Aurora PostgreSQL extension-version documentation or RDS for PostgreSQL extension-version documentation, and test the examples on your selected release before implementation.

Solution overview

Migrating full-text search (FTS) from SQL Server to Aurora PostgreSQL or Amazon RDS for PostgreSQL involves migrating the schema and data. You then reimplement the search functionality on the target database. The workflow consists of the following stages:

1. Schema conversion – AWS Database Migration Service (AWS DMS) Schema Conversion (DMS SC) or the AWS Schema Conversion Tool (AWS SCT) reads the source SQL Server metadata and converts tables to PostgreSQL-compatible formats.
2. Data migration – AWS DMS copies and applies the data from SQL Server to PostgreSQL.
3. FTS adaptation – SQL Server full-text catalogs and indexes require reimplementation in PostgreSQL. Identify the columns that require full-text search, and convert their content to tsvector values by using the appropriate PostgreSQL text search configuration. Create GIN indexes on the resulting tsvector expressions or columns.
4. Query rewriting – Applications that use SQL Server FTS predicates (such as CONTAINS and FREETEXT) must be updated to PostgreSQL equivalents (such as to_tsvector, to_tsquery, and plainto_tsquery).

The following architecture diagram shows these stages, from the source SQL Server database through schema conversion and data migration to FTS adaptation and query rewriting for Aurora PostgreSQL or Amazon RDS for PostgreSQL.

Figure 1: Full-text search migration workflow from SQL Server to Aurora PostgreSQL or Amazon RDS for PostgreSQL

Language and collation in PostgreSQL

In PostgreSQL, language processing and collation are handled in two separate layers:

• Collation (libc or ICU-based): PostgreSQL collations control string comparison and ordering, character classification and case conversion, and supported pattern-matching operations. Aurora PostgreSQL supports the libc and ICU collation providers. ICU collations provide customizable, locale-aware behavior. Collations don’t control full-text search tokenization or normalization. PostgreSQL text search configurations control these operations by selecting the parser and dictionaries.
• Text search configuration: Defines the parser and dictionaries that PostgreSQL uses to tokenize and normalize text, including stemming and stop-word removal. PostgreSQL provides built-in configurations such as english, dutch, and simple. Although PostgreSQL supports custom file-based dictionaries such as ispell, you can’t generally upload the required dictionary files to the server file system in Aurora PostgreSQL or RDS for PostgreSQL. The pg_bigm extension is separate from PostgreSQL text search configurations and to_tsvector. It provides bigram-based GIN indexing for LIKE, regular-expression, and similarity searches, which can be useful for languages such as Japanese, Chinese, and Korean.

Version compatibility: Extension and ICU availability varies by Aurora PostgreSQL and RDS for PostgreSQL engine version. The examples in this post were validated on Aurora PostgreSQL 16.8, which supports ICU 60.2, pg_bigm 1.2, and unaccent 1.1. Verify availability for your selected engine release in the Aurora PostgreSQL or RDS for PostgreSQL extension-version documentation.

The following diagram shows the three separate paths that PostgreSQL uses for ordinary text operations, native full-text search, and bigram-based search.

Figure 2: The three separate paths PostgreSQL uses for ordinary text operations, native full-text search, and bigram-based search

For ordinary text operations, PostgreSQL uses a libc or ICU collation to control comparison, ordering, case conversion, and supported pattern matching. Native full-text search uses the same text search configuration to process document text and user queries. The configuration applies a parser and dictionaries to produce tsvector values for documents and tsquery values for queries. PostgreSQL can use a GIN or GiST index to accelerate document searches and uses the @@ operator to match the resulting values. Separately, the pg_bigm extension provides bigram-based indexing for LIKE, regular-expression, and similarity searches. Collation is independent of these full-text and bigram search paths and doesn’t preprocess text for full-text search.

Differences in full-text search architecture

The following table summarizes the architectural differences:

-- list text search dictionaries
SELECT dictname, dictinitoption FROM pg_ts_dict
where dictname ilike 'english%';

dictname | dictinitoption
--------------+---------------------------------------------
english_stem | language = 'english', stopwords = 'english'

-- Stemming example in English
-- running → stemmed to run (position 1)
-- ran → stays as ran (position 2)
-- runs → stemmed to run (position 3)
SELECT to_tsvector('english', 'running ran runs') as position;

position
-------------------
'ran':2 'run':1,3

Collation and ICU in PostgreSQL

SQL Server configures FTS through the indexed column’s language, stoplists, and full-text catalog accent sensitivity. PostgreSQL uses text search configurations to select parsers and dictionaries that produce tsvector and tsquery values. In both engines, ordinary collation controls string comparison and ordering separately from FTS. The following diagram compares these models.

Figure 3: Full-text search controls compared between SQL Server and PostgreSQL

In SQL Server, the indexed column’s language controls word breaking and stemming, the stoplist excludes specified words, and the full-text catalog’s ACCENT_SENSITIVITY setting controls accent handling. Applications query the full-text index by using predicates such as CONTAINS and FREETEXT. In PostgreSQL, the same text search configuration processes documents and queries to produce tsvector and tsquery values. PostgreSQL can use a GIN or GiST index to accelerate matching with the @@ operator. In both engines, collation remains separate from the full-text search pipeline.

PostgreSQL supports both libc and ICU collations. libc is the default collation.

SELECT datname, datlocprovider, datcollate, datctype
FROM pg_database
WHERE datname = current_database();

datname  | datlocprovider | datcollate  | datctype
---------+----------------+-------------+-------------
postgres | c              | en_US.UTF-8 | en_US.UTF-8

With ICU collations, you can define locale-aware sort orders for a wide range of languages (for example, en-US, nl-NL, fr-FR). This provides fine-grained control over case and accent sensitivity independent of FTS. For example, you can define one collation for accent-sensitive comparisons (café ≠ cafe) and another for accent-insensitive searches, while still using the same text search configuration.

-- ICU French (accent-insensitive)
CREATE COLLATION fr_icu_ai (provider = icu, locale = 'fr-FR-u-ks-level1', deterministic = false);

-- check provider of the collation
select collname, collprovider from pg_collation where collname in ('fr_FR.utf8', 'fr_icu_ai');

collname | collprovider
------------+--------------
fr_FR.utf8 | c
fr_icu_ai | i

-- FALSE under libc
SELECT 'café' = 'cafe' COLLATE "fr_FR.utf8";

-- TRUE under icu accent-insensitive
SELECT 'café' = 'cafe' COLLATE "fr_icu_ai";

Language-specific tokenization

PostgreSQL text search configurations select parsers and dictionaries for tokenization, stemming, and stop-word removal. The following examples demonstrate language-specific behavior. For Chinese, Japanese, and Korean (CJK) text, pg_bigm provides separate bigram indexing for LIKE, regular-expression, and similarity searches.

For example:

CREATE TABLE docs (
    id serial PRIMARY KEY,
    lang text,
    body text
);

INSERT INTO docs(lang, body) VALUES
('english', 'I bought a car yesterday.'),
('english', 'This automobile is expensive.'),
('english', 'An auto show will be held tomorrow.'),
('english', 'The athlete was running and then runs again.'),
('dutch', 'We werken in de werkplaats aan de fiets.'),
('french', 'J''ai mangé au café et j''ai acheté un livre.'),
('german', 'Der Jäger geht durch den schönen Wald.'),
('spanish', 'El niño está jugando con el teléfono móvil.'),
('arabic', '.مرحبا بكم في مدينة دبي الجميلة'),
('hebrew', '.ברוך הבא לירושלים, עיר הקודש'),
('japanese','東京駅に着きました。');

• An English configuration reduces running to the lexeme run.

-- Index using English text search configuration
CREATE INDEX idx_docs_english_fts
ON docs USING gin (to_tsvector('english', body))
WHERE lang = 'english';

-- Query: searching for run matches `running` / `runs`
SELECT id, body
FROM docs
WHERE lang = 'english'
AND to_tsvector('english', body) @@ plainto_tsquery('english', 'run');

id | body
----+-----------------------------------------------
4 | The athlete was running and then runs again.

• The Dutch configuration stems Dutch words. It does not split compound words. However, prefix matching can match the beginning of an indexed compound lexeme such as werkplaats.

-- Index with Dutch config
CREATE INDEX idx_docs_dutch_fts
ON docs USING gin (to_tsvector('dutch', body))
WHERE lang = 'dutch';

-- Prefix matching against the indexed lexeme "werkplaats"
SELECT id, body
FROM docs
WHERE lang = 'dutch'
AND to_tsvector('dutch', body) @@ to_tsquery('dutch', 'werkpl:*');

id | body
----+------------------------------------------
5 | We werken in de werkplaats aan de fiets.

-- Query: exact word search for werkplaats
SELECT id, body
FROM docs
WHERE lang = 'dutch'
AND to_tsvector('dutch', body) @@ plainto_tsquery('dutch', 'werkplaats');

id | body
----+------------------------------------------
5 | We werken in de werkplaats aan de fiets.

• A custom French text search configuration combines accent removal with French stemming.

-- Enable the unaccent extension
CREATE EXTENSION IF NOT EXISTS unaccent;

-- Copy the built-in French configuration
CREATE TEXT SEARCH CONFIGURATION public.french_unaccent
(COPY = pg_catalog.french);

-- Remove accents before applying the French stemmer
ALTER TEXT SEARCH CONFIGURATION public.french_unaccent
ALTER MAPPING FOR hword, hword_part, word
WITH unaccent, french_stem;

-- Create a language-specific FTS index
CREATE INDEX idx_docs_french_fts
ON docs USING GIN (
    to_tsvector('public.french_unaccent', body)
)
WHERE lang = 'french';

-- Searching for "manger" matches inflected forms such as "mangé"
SELECT id, body
FROM docs
WHERE lang = 'french'
AND to_tsvector('public.french_unaccent', body)
@@ plainto_tsquery('public.french_unaccent', 'manger');

id | body
----+---------------------------------------------
6 | J'ai mangé au café et j'ai acheté un livre.

-- Searching for "cafe" matches accented text such as "café"
SELECT id, body
FROM docs
WHERE lang = 'french'
AND to_tsvector('public.french_unaccent', body)
@@ plainto_tsquery('public.french_unaccent', 'cafe');

id | body
----+---------------------------------------------
6 | J'ai mangé au café et j'ai acheté un livre.

• The German configuration performs German stemming and normalizes umlauts. It does not split compound words.

-- Index with German config
CREATE INDEX idx_docs_german_fts
ON docs USING gin (to_tsvector('german', body))
WHERE lang = 'german';

-- Query: searching for "Wald" (forest)
SELECT id, body
FROM docs
WHERE lang = 'german'
AND to_tsvector('german', body) @@ plainto_tsquery('german', 'Wald');

id | body
----+----------------------------------------
7 | Der Jäger geht durch den schönen Wald.

-- Handling umlauts with normalization
-- Will match "schönen" after stemming and umlaut normalization
SELECT id, body
FROM docs
WHERE lang = 'german'
AND to_tsvector('german', body) @@ plainto_tsquery('german', 'schon');

id | body
----+----------------------------------------
7 | Der Jäger geht durch den schönen Wald.

• Spanish configuration handles accented characters, and gender and number variations:

-- Index with Spanish config
CREATE INDEX idx_docs_spanish_fts
ON docs USING gin (to_tsvector('spanish', body))
WHERE lang = 'spanish';

-- Query: searching for "jugar" matches "jugando" (verb stemming)
SELECT id, body
FROM docs
WHERE lang = 'spanish'
AND to_tsvector('spanish', body) @@ plainto_tsquery('spanish', 'jugar');

id | body
----+---------------------------------------------
8 | El niño está jugando con el teléfono móvil.

-- Query: searching for "telefono" matches "teléfono" (accent insensitive)
SELECT id, body
FROM docs
WHERE lang = 'spanish'
AND to_tsvector('spanish', body) @@ plainto_tsquery('spanish', 'telefono');

id | body
----+---------------------------------------------
8 | El niño está jugando con el teléfono móvil.

• With pg_bigm, Japanese text such as “東京駅” (Tokyo Station) is divided into overlapping bigrams. You can use a bigram index with LIKE and regular-expression searches to perform partial matching without predefined dictionaries. This approach is useful for Chinese, Japanese, and Korean (CJK) text.

-- Enable extension
CREATE EXTENSION IF NOT EXISTS pg_bigm;

-- Create a GIN index using gin_bigm_ops
CREATE INDEX idx_docs_bigm_fts
ON docs USING gin (body gin_bigm_ops)
WHERE lang = 'japanese';

-- Query with LIKE - index will accelerate bigram search
SELECT id, body
FROM docs
WHERE lang = 'japanese'
AND body LIKE '%東京%';

id | body
----+----------------------
11 | 東京駅に着きました。

-- Show the bigram vector representation
SELECT show_bigm('東京駅');

show_bigm
-------------------------
{京駅,東京,"駅 "," 東"}

Both Arabic and Hebrew are right-to-left (RTL) Semitic languages, and PostgreSQL fully supports storing and indexing RTL text. However, their full-text search behavior differs:

• PostgreSQL includes an Arabic Snowball stemmer that can conflate some inflected forms. The stemmer does not provide general root-based morphological analysis, so validate the required word forms with representative Arabic text and queries.

-- Create an arabic configuration
CREATE TEXT SEARCH CONFIGURATION arabic_search ( COPY = arabic );

-- Create an index
CREATE INDEX docs_body_fts_ar ON docs
USING GIN (to_tsvector('arabic_search', body))
WHERE lang = 'arabic';

-- Basic search query with to_tsquery
SELECT id, lang, body
FROM docs
WHERE lang = 'arabic'
AND to_tsvector('arabic_search', body)
@@ to_tsquery('arabic_search', 'مدينة');

id | lang | body
----+--------+---------------------------------
9 | arabic | .مرحبا بكم في مدينة دبي الجميلة

• Hebrew does not have a built-in stemmer in PostgreSQL, so search is token-based. To achieve root-level or prefix-insensitive matching, Hebrew typically requires manual normalization or custom processing.

-- Create a simple Hebrew configuration
CREATE TEXT SEARCH CONFIGURATION hebrew_simple (COPY = simple);

-- Create an index
CREATE INDEX docs_body_fts_he ON docs
USING GIN (to_tsvector('hebrew_simple', body))
WHERE lang = 'hebrew';

-- Basic search query with to_tsquery
SELECT id, lang, body
FROM docs
WHERE lang = 'hebrew'
AND to_tsvector('hebrew_simple', body)
@@ to_tsquery('hebrew_simple', 'לירושלים');

id | lang | body
----+--------+-------------------------------
10 | hebrew | .ברוך הבא לירושלים, עיר הקודש

Stopwords, thesaurus, and synonyms

SQL Server uses stoplists for excluded words and thesaurus XML files for synonyms (for example, car = automobile). PostgreSQL ships built-in dictionaries (Snowball stemmers for English, French, Dutch, and others), but file-based dictionaries like ispell and thesaurus XML equivalents are not available on Aurora PostgreSQL or RDS for PostgreSQL. Implement custom stopwords and synonyms using SQL tables and functions instead.

The following example uses a user-defined make_syn_tsquery function (full source in the sample GitHub repository) so a search for ‘automobile’ also returns documents containing ‘car’ or ‘auto’:

SELECT id, body
FROM docs
WHERE lang = 'english'
AND to_tsvector('english', body) @@ make_syn_tsquery('english', 'automobile');

id | body
----+-------------------------------------
1 | I bought a car yesterday.
2 | This automobile is expensive.
3 | An auto show will be held tomorrow.

Accent sensitivity and Unicode

As discussed earlier, ordinary collation and full-text accent handling are separate concerns in both engines. In PostgreSQL, use an ICU collation for accent-insensitive ordinary string comparisons. For accent-insensitive full-text search, use a custom text search configuration such as public.french_unaccent, and apply the same configuration when indexing documents and processing queries.

For French, accents often change word meaning. With ICU collations, you can define both accent-sensitive and accent-insensitive behavior side by side:

-- Accent-sensitive collation (French)
CREATE COLLATION fr_sensitive (provider = icu, locale = 'fr-FR', deterministic = true);

-- Accent-insensitive collation (French)
CREATE COLLATION fr_insensitive (provider = icu, locale = 'fr-FR-u-ks-level1', deterministic = false);

With these collations:

• A query using fr_sensitive will not match “café” and “cafe” as equal.
• A comparison using fr_insensitive will treat “café” and “cafe” as equal. This collation affects ordinary string comparison and ordering, not full-text tokenization.

Performance and indexing considerations

PostgreSQL supports GIN and GiST indexes for tsvector values. Query plans and maintenance behavior depend on the data, workload, statistics, and configuration. Consider the following:

• Index type – GIN indexes store lexemes and are commonly used for full-text search. GiST indexes use signatures and can require rechecking candidate matches.
• GIN pending list – With fastupdate enabled, inserts and updates can accumulate in a pending list before entries are moved into the main index. PostgreSQL cleans this list during autovacuum or when it exceeds gin_pending_list_limit.
• Vacuum and statistics – Autovacuum maintains table and index storage and refreshes planner statistics. Monitor and tune autovacuum for write-intensive tables.
• Parallel execution – PostgreSQL can use parallel plans when the query and planner cost estimates make them eligible. Parallel execution is not guaranteed.
• Partitioning – Partition pruning can limit a query to relevant partitions when predicates include the partition key. Partitioning does not automatically improve every FTS query.
• Plan validation – Use EXPLAIN (ANALYZE, BUFFERS) with representative data to verify whether PostgreSQL uses the intended index and to measure execution time and buffer activity.

For more information, see:

• PostgreSQL preferred index types for text search.
• PostgreSQL GIN fast-update technique.
• Using EXPLAIN.

Considerations and limitations

Before deploying the migrated full-text search implementation to production, consider the following limitations:

• Custom dictionary files – Aurora PostgreSQL and RDS for PostgreSQL don’t provide access to install custom dictionary files under $SHAREDIR. This prevents direct use of file-backed dictionaries such as ispell, synonym, thesaurus, and custom stop-word dictionaries. Where built-in dictionaries aren’t sufficient, implement stop-word and synonym handling with database tables and functions or application-side query expansion.
• Compound words – The built-in Dutch and German text search configurations stem words but don’t split compound words into their components. Prefix matching can address some use cases, but validate compound-word searches against representative application data.
• Arabic morphology – The Arabic Snowball stemmer in PostgreSQL can normalize some inflected forms, but it doesn’t provide general root-based morphological analysis. Applications that require root-level Arabic matching might need additional normalization or external language-processing capabilities.
• Hebrew stemming – PostgreSQL doesn’t include a built-in Hebrew stemmer. The simple configuration supports token-based matching, but matching across prefixed forms or other morphology-aware behavior requires custom normalization or processing.

These behaviors can produce different results from SQL Server even when the schema, data, and queries have been migrated correctly. Test the target implementation with representative multilingual content, query terms, expected matches, and non-matches before production use.

Migration checklist

Use this checklist when planning the migration:

• Review source collations – Identify which SQL Server collations are in use (case sensitivity, accent sensitivity) and plan equivalent glibc or ICU collations in PostgreSQL.
• Inventory FTS objects – Document catalogs, stoplists, and thesaurus XML files from SQL Server.
• Map to PostgreSQL dictionaries – Select suitable built-in dictionaries and define the necessary database or application-level normalization. Apply the chosen normalization consistently when indexing documents and processing queries.
• Choose text search configurations – Select appropriate built-in configurations, such as english, french, or dutch. For CJK text, plan for separate pg_bigm bigram indexing.
• Recreate indexes – Define tsvector columns or expressions and create GIN indexes for search efficiency.
• Rewrite queries – Update SQL Server FTS predicates (CONTAINS, FREETEXT) into PostgreSQL equivalents (to_tsvector, to_tsquery, plainto_tsquery, websearch_to_tsquery).
• Validate accent and case sensitivity – Test queries with and without accents (for example, café compared to cafe) and confirm that behavior matches business requirements.
• Benchmark performance – Measure query response times and index sizes. Tune autovacuum, gin_pending_list_limit, and partitioning as needed.
• Consider OpenSearch integration – For advanced features such as fuzzy matching, faceting, or large-scale log and text analytics, evaluate Amazon OpenSearch Service alongside PostgreSQL.

Clean up

To remove the sample objects created in this walkthrough, run the following commands:

DROP TABLE IF EXISTS docs CASCADE;
DROP FUNCTION IF EXISTS make_syn_tsquery(regconfig, text);
DROP TABLE IF EXISTS synonym_group;
DROP TEXT SEARCH CONFIGURATION IF EXISTS public.french_unaccent;
DROP TEXT SEARCH CONFIGURATION IF EXISTS arabic_search;
DROP TEXT SEARCH CONFIGURATION IF EXISTS hebrew_simple;
DROP COLLATION IF EXISTS fr_icu_ai;
DROP COLLATION IF EXISTS fr_sensitive;
DROP COLLATION IF EXISTS fr_insensitive;

Dropping the docs table also removes its indexes and the sequence owned by its serial column.

If you installed the extensions specifically for this walkthrough in a disposable database, and no other objects use them, you can also remove them:

DROP EXTENSION IF EXISTS pg_bigm;
DROP EXTENSION IF EXISTS unaccent;

Conclusion

PostgreSQL separates collation from full-text linguistic processing, including tokenization, stemming, and accent handling. When migrating multilingual workloads, test language-specific behavior, synonyms, query results, and index performance with representative data.

To get started, use the sample GitHub repository to run these examples in a non-production Aurora PostgreSQL or RDS for PostgreSQL environment. For further reading, see the PostgreSQL full-text search documentation.

About the authors