PostgreSQL Full-Text Search: tsvector, tsquery, the Turkish Configuration, unaccent and pg_trgm

Version note: The examples in this post were run on PostgreSQL 18.7 (the official postgres:18 Docker image), in the Bookstore lab we set up in post 02, right after reloading the schema; for the collation comparison one query was also run on PostgreSQL 17.11.

Guess First

Bookstore, the online bookstore used as the shared example across this site's series, is adding Turkish classics to its catalog. 10-full-text.sql adds twelve Turkish classics next to the 40,000 English demo titles, with short descriptions we wrote for this lab. Çalıkuşu's description says that Feride goes from Istanbul to Anatolia as a teacher and spends years in small towns; the Turkish text is "Feride'nin İstanbul'dan Anadolu'ya öğretmen olarak gidişi ve kasabalarda geçen yılları." We try six search terms a user might type, using PostgreSQL's Turkish full-text search configuration:

SELECT q, to_tsvector('turkish', description) @@ websearch_to_tsquery('turkish', q) AS found
FROM books, unnest(ARRAY['istanbul', 'kasabalar', 'kasaba', 'öğretmen', 'öğretmenler', 'anadoluda']) AS q
WHERE title = 'Çalıkuşu';

ILIKE '%kasaba%' would find most of these but not "kasabalar". Which ones does full-text search, which stems words down to their root, find? Could "kasabalar", towns, be found while "kasaba", town, is not?

      q      | found
-------------+-------
 istanbul    | t
 kasabalar   | t
 kasaba      | f
 öğretmen    | t
 öğretmenler | f
 anadoluda   | f

The search term "kasabalar" matched "kasabalarda" in the text, but "kasaba" didn't. The teacher, "öğretmen", was found; teachers, "öğretmenler", wasn't. In this post we see why: how tsvector and tsquery work, the limits of the Turkish stemmer, dotted and dotless i and the collation change in 18, searching without Turkish characters with unaccent, weighted and indexed search for Bookstore, typo tolerance with pg_trgm, and when to move to Elasticsearch.

tsvector and tsquery

Full-text search is built on two types. tsvector is the processed form of a document: the text is split into words, stop words, frequent words that carry no meaning on their own such as "ve", "çok" and "için", are dropped, the remaining words go through a dictionary, become lexemes and are stored with their positions. tsquery is the search expression after the same processing. The @@ operator matches the two.

SELECT to_tsvector('turkish', description) FROM books WHERE title = 'Çalıkuşu';

                                                   to_tsvector
------------------------------------------------------------------------------------------------------------------
 'anadol':5 'dan':4 'feri':1 'geçe':12 'gidiş':9 'istanbul':3 'kasaba':11 'nin':2 'olarak':8 'yıl':13 'öğretme':7

Here's the answer to the guess. "kasabalarda" is stored as 'kasaba', while the search term "kasaba" turns into 'kasap'. "öğretmen" met at 'öğretme' because it is the same word on both sides, while "öğretmenler" became 'öğretmen'. A match depends on both sides reducing to the same lexeme, and the stemmer can't always reduce different inflections of a word to the same root.

Use websearch_to_tsquery to turn user input into a tsquery: it understands the syntax people know from search boxes and doesn't throw on malformed input. A quoted phrase becomes <->, "immediately followed by"; there is also or, and exclusion with a leading minus:

SELECT websearch_to_tsquery('turkish_unaccent', '"saatleri ayarlama" or calikusu -huzur');

            websearch_to_tsquery
--------------------------------------------
 'saat' <-> 'ayarla' | 'calikus' & !'huzur'

to_tsquery, on the other hand, expects the operators (&, |, !, :*) directly; passing it user text ends in a syntax error.

The Turkish Configuration: Stemming and Its Limits

The turkish configuration uses the Turkish stemmer from the Snowball project. Rule-based stemming is hard in an agglutinative language; looking at it word by word, the limits become clear:

      w      |     lexemes
-------------+-----------------
 kasaba      | 'kasap':1
 kasabalarda | 'kasaba':1
 öğretmen    | 'öğretme':1
 öğretmenler | 'öğretmen':1
 roman       | 'roma':1
 romanlar    | 'roman':1
 aşk         | 'aşk':1
 aşkı        | 'aşkı':1
 Efendi'nin  | 'efe':1 'nin':2

Three kinds of problems show up. The bare and inflected forms of the same word reduce to different roots (kasaba/kasabalarda, roman/romanlar). Some suffixes are not removed at all (aşkı). Proper nouns with an apostrophe are split into two tokens and the suffix is stored as a separate "word" (Efendi'nin). On top of that, since the lexeme of "roman", novel, is 'roma', a search for "Roma" can return results close to every description that mentions a novel.

Two techniques make this acceptable. The first is prefix search: with the simple configuration, which doesn't stem, a query ending in :* matches the beginning of a word. In an agglutinative language this is often more predictable than the stemmer:

SELECT to_tsquery('simple', 'kasaba:*') @@ to_tsvector('simple', description) AS kasaba,
       to_tsquery('simple', 'anadolu:*') @@ to_tsvector('simple', description) AS anadolu
FROM books WHERE title = 'Çalıkuşu';

 kasaba | anadolu
--------+---------
 t      | t

The second is combining both: keep both a turkish and a simple vector for the same row and ask both in the query. Appending :* to every word the user types returns too many results for short terms; set a limit such as at least three letters.

Dotted and Dotless i: the Collation Change in 18

Lexemes are stored in lower case, and which rules do the lowercasing matters. The lab database uses libc en_US.utf8; we ran the same sentence in a database created with the ICU tr-TR locale too:

SELECT to_tsvector('turkish', 'IŞIK İSTANBUL ışık istanbul');

/* PostgreSQL 18, libc en_US.utf8 */
 'istanbul':2,4 'işik':1 'ışık':3

/* PostgreSQL 18, ICU tr-TR */
 'istanbul':2,4 'ışık':1,3

/* PostgreSQL 17, ICU tr-TR */
 'istanbul':2,4 'işik':1 'ışık':3

Because libc's English rule turns "I" into "i", "IŞIK" written in capitals doesn't match a search for "ışık". With ICU tr-TR the two spellings reduce to the same lexeme. The important line is the third: PostgreSQL 17 always used libc rules for full-text search, even when the database was ICU. That changed in 18; full-text search and pg_trgm now use the database's default collation provider. The release notes recommend rebuilding full-text and pg_trgm indexes after moving a cluster with an ICU or builtin provider to 18 with pg_upgrade; otherwise the old lexemes in the index and the lexemes of new queries can differ. If you are setting up a new database with mostly Turkish content, choosing ICU tr-TR from the start (the collation section in post 03) avoids the problem.

unaccent: Searching Without Turkish Characters

A good share of users type without Turkish characters: "calikusu", "ogretmenler". The unaccent extension (trusted; the owner can install it) is a dictionary that strips accents from letters. We place it in a copy of the Turkish configuration so that it runs before the stemmer:

CREATE EXTENSION IF NOT EXISTS unaccent;
CREATE TEXT SEARCH CONFIGURATION turkish_unaccent (COPY = turkish);
ALTER TEXT SEARCH CONFIGURATION turkish_unaccent
    ALTER MAPPING FOR hword, hword_part, word WITH unaccent, turkish_stem;

      w      |   turkish    | turkish_unaccent
-------------+--------------+------------------
 Çalıkuşu    | 'çalıkuş':1  | 'calikus':1
 calikusu    | 'calikus':1  | 'calikus':1
 öğretmenler | 'öğretmen':1 | 'ogretmen':1
 ogretmenler | 'ogretmen':1 | 'ogretmen':1

Now both spellings meet at the same lexeme. There is a cost: the stemmer runs its rules, which rely on Turkish vowel harmony, on simplified letters and misses some suffixes (for example "Savaşı" stays 'savasi' once simplified). As long as document and query go through the same configuration it's consistent, but the matching is a bit coarser than plain turkish.

Bookstore Search: setweight, a Generated Column and GIN

The search covers the title, the author name and the description, and a match in the title is worth the most. setweight gives each part a weight (A highest, D lowest). Since a generated column can only use the row's own columns, we copied the author name into the row (author_name; keeping it current is the application's job). English and Turkish books are processed with different configurations:

ALTER TABLE books ADD COLUMN search_vector tsvector GENERATED ALWAYS AS (
    CASE language
        WHEN 'tr' THEN setweight(to_tsvector('turkish_unaccent', title), 'A')
                    || setweight(to_tsvector('turkish_unaccent', coalesce(author_name, '')), 'B')
                    || setweight(to_tsvector('turkish_unaccent', coalesce(description, '')), 'C')
        ELSE setweight(to_tsvector('english', title), 'A')
          || setweight(to_tsvector('english', coalesce(author_name, '')), 'B')
          || setweight(to_tsvector('english', coalesce(description, '')), 'C')
    END) STORED;
CREATE INDEX ix_books_search_vector ON books USING gin (search_vector);

Writing STORED explicitly is required: as we saw in post 04, generated columns are virtual by default in 18, and a virtual column can't be indexed. The two-argument form of to_tsvector, with the configuration spelled out, is immutable, so it can be used in a generated column. Since we don't know the user's language at query time, we combine the queries of both configurations with ||, which means or:

\set q 'yusuf'
WITH q AS (SELECT websearch_to_tsquery('turkish_unaccent', :'q') || websearch_to_tsquery('english', :'q') AS query)
SELECT book_id, title, author_name, round(ts_rank(search_vector, query)::numeric, 4) AS rank
FROM books, q WHERE search_vector @@ query ORDER BY rank DESC, book_id LIMIT 5;

 book_id |      title      |  author_name   |  rank
---------+-----------------+----------------+--------
   40002 | Kuyucaklı Yusuf | Sabahattin Ali | 0.6383
   40009 | Aylak Adam      | Yusuf Atılgan  | 0.2432

The book with "Yusuf" in its title and description comes before the book that has it only in the author name. To show where it matched in the result list, use ts_headline:

     title      |            snippet
----------------+--------------------------------
 Huzur          | [İstanbul]'da, İkinci
 Çalıkuşu       | [İstanbul]'dan Anadolu
 Sinekli Bakkal | [İstanbul]'un bir mahallesinde
 Aylak Adam     | [İstanbul] sokaklarında
 Kiralık Konak  | [İstanbul] konağında

ts_headline processes the original text of the document again; it's expensive, so call it only for the rows shown on the page. For paging, add a unique column to the sort (ORDER BY rank DESC, book_id); all 2,000 "hobbit" results got the same rank, and without the second column rows can move between pages. count(*) OVER () returns the total number of results in the same query. The plan shows that the GIN index is used:

 Bitmap Heap Scan on books (actual rows=1.00 loops=1)
   Recheck Cond: (search_vector @@ '''calikus'''::tsquery)
   Buffers: shared hit=4
   ->  Bitmap Index Scan on ix_books_search_vector (actual rows=1.00 loops=1)
         Index Cond: (search_vector @@ '''calikus'''::tsquery)

Typo Tolerance with pg_trgm

Full-text search doesn't forgive typos: "madona" is a lexeme and doesn't match "madonna". This is where pg_trgm, which we used for ILIKE in post 06, comes in. similarity gives the share of trigrams two texts have in common; the % operator returns true when that share passes a threshold (pg_trgm.similarity_threshold, 0.3 by default) and uses the GIN trigram index:

CREATE INDEX ix_books_title_trgm ON books USING gin (title gin_trgm_ops);
SELECT title, similarity(title, 'kurk mantolu madona') AS sim
FROM books WHERE title % 'kurk mantolu madona' ORDER BY sim DESC LIMIT 5;

        title         |    sim
----------------------+-----------
 Kürk Mantolu Madonna | 0.6086956

A similarity of 0.61 despite one wrong letter and one missing letter. To search for a single word inside a long title, word_similarity and the <% operator fit better: for "ayarlma", the similarity to the whole "Saatleri Ayarlama Enstitüsü" is 0.5 while the word similarity is 0.625. In practice a search screen searches with full-text first and, if there are no or few results, produces a "Did you mean?" suggestion with trigrams.

When Elasticsearch?

PostgreSQL's full-text search lives next to the data, is consistent with transactions and needs no extra infrastructure. For a catalog of tens of thousands or even millions of records like Bookstore's, it covers most needs. What makes you consider Elasticsearch or OpenSearch: fast facets with counts, such as category, author or price range; suggestions while typing (autocomplete) and synonym management; better analyzers for Turkish (morphological analysis plugins); the need to tune relevance in detail (BM25, per-field boosts, tuning with click data); and moving search traffic off the database. The price is keeping data in two places and synchronizing it. The details are in the Elasticsearch ile Arama series. Meaning-based (vector) search and combining it with keyword search is the topic of the hybrid search post in the RAG series.

Production Notes

Don't compute the vector with to_tsvector(...) in every query; use a generated column or an expression index. With an expression index, the expression in the query has to match the index exactly (post 06); a generated column removes that risk.

If you change the configuration (a new stop word list, adding unaccent), existing vectors stay as they were built. Recomputing the generated column with ALTER TABLE ... ALTER COLUMN search_vector SET EXPRESSION AS (...) (17+) rewrites the table and locks it for the duration; plan it as maintenance on a large table.

Writes are expensive on large GIN indexes; GIN first collects inserts in a "pending list" and moves them into the index during VACUUM. On heavily written tables, gin_pending_list_limit and the autovacuum settings affect query times.

Common Pitfalls

Processing the document with one configuration and the query with another: to_tsvector('turkish', ...) and plainto_tsquery('simple', ...) produce different lexemes, and you silently find nothing. Always pass the configuration explicitly with the two-argument functions; don't rely on the default default_text_search_config.

Expecting perfection from the Turkish stemmer: the table above is real. For critical searches use prefix search and trigram suggestions together, and test the results with real user queries.

Computing ts_rank for every result: rank is computed and sorted for all 2,000 matching rows. Limit the number of results for very common terms, or require terms of at least three letters.

A Small Experiment

★☆☆ Look at the output of ts_debug('turkish', 'İstanbul''un kitapçıları') to see which dictionary each token went through. What happens to the suffix after the apostrophe?

★★☆ Add the description processed with the simple configuration to search_vector as a fourth part with weight D, and make the "kasaba" search find Çalıkuşu with a kasaba:* prefix query. How did you combine the two queries with ||?

★★★ Write a single SQL function that searches with full-text first and, if it finds nothing, suggests the three closest titles with word_similarity, for misspelled searches like "sinekli bakal" and "tutunamayınlar". How did you choose the threshold?

In the next post we move to Block B, the .NET side: NpgsqlDataSource with Npgsql and Dapper, connection pooling, type mapping, bulk loading with binary COPY, and LISTEN/NOTIFY.