Full Text Search: SQL Standard vs. Proprietary Features

1. Why full text search is barely standardized

Full text search belongs to the areas of SQL where the ANSI SQL standard offers only a very thin foundation. The standard does define theoretical constructs for text based search with CONTAINS and MATCH, but the actual implementation, especially relevance ranking, language analysis, and indexing strategy, is left entirely to individual database vendors. That leads to full text search in PostgreSQL, MySQL, and SQL Server being based on fundamentally different internal architectures, even though all three systems aim to solve the same underlying problem: finding relevant text passages quickly in large volumes of data.

This lack of standardization in full text search is no accident, it reflects the complexity of the problem. Text search requires linguistic preprocessing such as stemming and stopword removal, specialized index structures such as inverted indexes, and a scoring function for relevance that depends heavily on the use case and language. Unlike SELECT or JOIN, full text search has no simple, universally accepted mathematical model that could easily be standardized. The following sections show how the three major database systems each solve the problem.

2. PostgreSQL: tsvector, tsquery, and GIN indexes

PostgreSQL offers the technically most mature native full text search implementation among relational databases with the types tsvector and tsquery. A tsvector is a preprocessed, normalized representation of a text where words are reduced to their stems and annotated with position information. The function to_tsvector('german', text) creates this representation while respecting language specific rules, while to_tsquery('german', search_term) converts the search query into the same format.

For performant full text search, tsvector columns are combined with a GIN index (Generalized Inverted Index), which answers search queries in milliseconds even with millions of rows. The @@ operator checks whether a tsvector matches a tsquery, and ts_rank() returns a numeric relevance score used for sorting search results. This combination makes PostgreSQL the technically most complete full text search solution among the systems compared in this article, though at the cost that correct usage requires a deeper understanding of the underlying concepts.

-- PostgreSQL: tsvector column with GIN index for fast full text search
ALTER TABLE articles ADD COLUMN search_vector TSVECTOR
    GENERATED ALWAYS AS (to_tsvector('german', title || ' ' || body)) STORED;

CREATE INDEX idx_articles_search ON articles USING GIN (search_vector);

-- Query with relevance ranking
SELECT id, title, ts_rank(search_vector, query) AS relevance
FROM articles, to_tsquery('german', 'database & performance') AS query
WHERE search_vector @@ query
ORDER BY relevance DESC
LIMIT 20;

3. MySQL: FULLTEXT index and MATCH AGAINST

MySQL solves full text search with its own index type called FULLTEXT, created directly on one or more TEXT or VARCHAR columns. Querying happens through the function MATCH(columns) AGAINST(search_term), which works in two modes: the default mode with natural language search, and boolean mode, which supports explicit operators such as + for required words and - for excluded words. Natural language mode automatically returns a relevance score that can be output directly in the SELECT, without calling a separate ranking function.

An important difference from PostgreSQL: MySQL does not offer true linguistic stemming by default, but primarily works with exact word and prefix matches, supplemented by a configurable stopword list. For InnoDB tables, the default storage engine since MySQL 5.6, FULLTEXT works reliably, but was for a long time a MyISAM exclusive, which occasionally still causes confusion in older migrations when full text search features from an earlier MySQL version are expected.

-- MySQL: FULLTEXT index on title and body columns
ALTER TABLE articles ADD FULLTEXT INDEX idx_articles_fulltext (title, body);

-- Natural language mode, returns relevance score automatically
SELECT id, title,
       MATCH(title, body) AGAINST('database performance' IN NATURAL LANGUAGE MODE) AS relevance
FROM articles
WHERE MATCH(title, body) AGAINST('database performance' IN NATURAL LANGUAGE MODE)
ORDER BY relevance DESC
LIMIT 20;

-- Boolean mode: required and excluded words
SELECT id, title
FROM articles
WHERE MATCH(title, body) AGAINST('+database -mongodb' IN BOOLEAN MODE);

4. SQL Server: Full-Text Index and CONTAINSTABLE

SQL Server implements full text search via a separate full text index managed through its own full text catalog, an architectural detail that requires more administrative effort than the integrated solutions of PostgreSQL and MySQL. Querying happens through the functions CONTAINS() for simple boolean conditions in the WHERE clause, or CONTAINSTABLE(), which as a table valued function returns a RANK column, joined to the base table via JOIN to get a relevance sorted result list.

SQL Server supports complex search expressions via FORMSOF(INFLECTIONAL, word) for inflected word forms and NEAR(word1, word2, distance) for proximity search, where two terms must occur within a certain word distance. These functions offer a more expressive query language than MySQL's simple MATCH AGAINST syntax, but also require more familiarization. The need for a separate catalog and the comparatively slower index updates make SQL Server full text search less suitable for applications with very frequent writes than the more tightly integrated solutions of the other systems.

-- SQL Server: Full-Text Catalog and Index required first
CREATE FULLTEXT CATALOG ArticlesCatalog AS DEFAULT;

CREATE FULLTEXT INDEX ON Articles (Title, Body)
KEY INDEX PK_Articles
ON ArticlesCatalog;

-- CONTAINSTABLE returns a RANK column, joined back to the base table
SELECT a.Id, a.Title, ft.RANK AS Relevance
FROM Articles a
INNER JOIN CONTAINSTABLE(Articles, (Title, Body), 'database AND performance') ft
    ON a.Id = ft.[KEY]
ORDER BY ft.RANK DESC;

5. Relevance ranking compared

Relevance ranking is the area where full text search implementations differ the most substantively, not just syntactically. PostgreSQL offers two different ranking functions, ts_rank() and ts_rank_cd(), which take word frequency, document length, and optionally the proximity of search terms to each other into account, configurable via weighting parameters that allow four priority levels (A through D) for different text sections such as title and body.

MySQL calculates relevance automatically for natural language MATCH AGAINST using an internal, not directly configurable algorithm roughly oriented on classic TF-IDF principles, but offering considerably fewer configuration options than PostgreSQL. SQL Server also calculates the RANK column in CONTAINSTABLE() automatically, using a proprietary, not fully documented algorithm. Anyone needing fine grained control over relevance ranking, for example for an e-commerce product search with business priorities, will find PostgreSQL the most flexible but also the most complex solution among the three compared systems.

6. Language analysis: stemming, stopwords, and dictionaries

The quality of full text search depends decisively on the underlying language analysis. PostgreSQL offers the most flexible solution with its dictionary system: to_tsvector('german', text) uses built in language rules for German that support stemming (reduction to word stems), stopword filtering, and even synonym dictionaries that can be individually configured. This flexibility allows industry specific terminology or abbreviations to be stored as synonyms, valuable for specialized search applications.

MySQL, from version 5.7, also offers configurable stopword lists and, via the ngram or MeCab parsers, support for languages without word boundaries such as Japanese or Chinese, but does not offer true stemming by default. SQL Server uses language specific word breakers and stemmers, installed as separate components and applied automatically based on the configured language of the column, with solid but less customizable quality than PostgreSQL's dictionary system. Anyone using full text search for multilingual applications should explicitly set the language configuration per column or document rather than relying on a global default.

7. Limits of full text search in the database

As powerful as full text search has become in modern databases, there are clear limits. Faceted search with multiple simultaneous filters, fuzzy matching for typo tolerance, real time autocomplete with prefix matching, and especially horizontal scaling across multiple servers are areas where dedicated search engines like Elasticsearch or OpenSearch are clearly superior to database integrated full text search. PostgreSQL, MySQL, and SQL Server were primarily designed as transactional databases, full text search is a valuable but secondary feature there.

Another practical downside: full text search indexes must be updated on every change to the underlying data, which can cause noticeable latency in very write heavy tables, especially with SQL Server's separate catalog architecture. Anyone needing full text search for a small to medium data volume and moderate search requirements, for example an internal knowledge base or a blog, is usually well served by the built in full text search. For complex search requirements with facets, high search load, and the need for horizontal scaling, the additional operational overhead of a dedicated search engine pays off.

8. When a dedicated search engine is the better choice

The decision between database integrated full text search and a dedicated search engine depends on several factors. First: search volume. A few hundred search queries per minute are handled without issue by any of the three databases discussed, at several thousand queries per second a horizontally scalable solution like Elasticsearch becomes relevant. Second: complexity of the search logic. Simple keyword search with relevance sorting is well covered by full text search in the database, faceted navigation with aggregations across multiple dimensions simultaneously is the domain of dedicated search engines.

Third: operational complexity. An additional search engine means another system that must be operated, monitored, and kept in sync with the primary database, usually via change data capture or periodic batch synchronization. For many mid sized applications, the database's built in full text search is the more pragmatic starting point, with the option to switch to a dedicated solution later once demand is proven. This migration is considerably easier if the search logic was implemented clearly separated from the rest of the application logic from the start.

9. Full text search implementations side by side

The following table compares the key characteristics of the three full text search implementations.

This comparison shows that PostgreSQL offers the technically most flexible but also most complex solution, MySQL the simplest with the fewest configuration options, and SQL Server a middle ground with a powerful query language but higher administrative overhead due to the separate catalog.

10. Summary

Full text search is one of the areas of SQL that the ANSI SQL standard deliberately leaves open, which is why PostgreSQL, MySQL, and SQL Server have developed three fundamentally different solutions. PostgreSQL offers the technically most flexible but also most complex implementation with tsvector, tsquery, and GIN indexes. MySQL scores with simple handling via MATCH AGAINST, but offers less control over relevance and language analysis. SQL Server sits in between with its powerful query language and separate full text catalog, but requires more administrative effort.

The most important strategic decision, however, is not between the three database systems, but between database integrated full text search and a dedicated search engine like Elasticsearch. For moderate search requirements, the built in solution is usually the more pragmatic starting point, while complex facets, high search load, or the need for horizontal scaling argue for a dedicated search infrastructure early on.

11. FAQ: Full Text Search Standard vs. Proprietary