A full-text index is a database index that maps individual words to the rows and positions where they occur, so that keyword searches over large text columns run in milliseconds instead of scanning every row. In indexing, it is the specialized structure that makes natural-language search possible inside a database, with features such as relevance ranking, stemming, phrase search and boolean operators.
An ordinary B-tree index cannot help with a query such as WHERE body LIKE '%invoice%', because the search term may appear anywhere in the value. A full-text index solves this by building an inverted index, the same core structure used by dedicated search engines, directly inside the database system.
How a full-text index is built
Creating a full-text index runs every document through a text analysis pipeline. The text is split into tokens, normalized and reduced to index terms, and each term is recorded in a posting list of the documents, and often the positions, where it appears. At query time, the search terms pass through the same pipeline, so a search for “Running” can match a stored “runs”.
| Stage | What happens | Example |
|---|---|---|
| Tokenization | Text is split into words | “Fast-moving trains” becomes fast, moving, trains |
| Normalization | Case and accents are unified | “Café” becomes cafe |
| Stop-word removal | Very common words are dropped | the, and, of are skipped |
| Stemming or lemmatization | Words are reduced to a root form | connected and connection become connect |
| Posting list update | Term is linked to row and position | train appears in rows 7 and 91 |
The choice of language configuration matters, because stemming rules and stop-word lists differ between languages. Indexing German text with an English configuration produces poor matches, and languages written without spaces, such as Chinese or Japanese, require dedicated tokenizers such as n-gram parsers.
Implementations in major database systems
All mainstream relational databases ship some form of full-text index, but syntax, ranking and update behavior vary considerably. The table below summarizes the most common implementations.
| Database | Index type | Query syntax | Notable trait |
|---|---|---|---|
| PostgreSQL | GIN or GiST on tsvector | @@ to_tsquery(...) | Configurable dictionaries, ts_rank |
| MySQL InnoDB | FULLTEXT index | MATCH ... AGAINST | Natural-language and boolean modes |
| SQL Server | Full-text catalog and index | CONTAINS, FREETEXT | Populated asynchronously |
| Oracle Database | Oracle Text CONTEXT index | CONTAINS | Index sync on commit or schedule |
| SQLite | FTS5 virtual table | MATCH | Built-in BM25 ranking |
Update behavior is a key difference. PostgreSQL updates a GIN index within the writing transaction, using a pending list to speed up inserts, while SQL Server and Oracle may populate the index in the background, so newly inserted rows can be briefly invisible to searches. Applications that require immediate searchability must account for this delay.
Relevance ranking and query features
Beyond finding matches, a full-text index supports ranking results by relevance. Most implementations use variants of TF-IDF or BM25, which score a document higher when the search term appears often in it but rarely across the whole collection. Some systems also reward term proximity, so documents where the search words appear close together rank higher.
- Boolean operators: required, excluded and optional terms, such as
+invoice -draft. - Phrase search: exact word sequences, which requires positional information in the index.
- Prefix search: matches such as
optim*for optimize, optimizer and optimization. - Weighting: titles can count more than body text, for example with weights A to D in PostgreSQL.
Typo tolerance is generally not part of a standard full-text index. Fuzzy matching requires additional techniques such as trigram indexes, while synonyms need a thesaurus or synonym dictionary configured explicitly.
Full-text index compared with other approaches
Teams often weigh a database full-text index against simpler pattern matching or a dedicated search engine. Each option fits a different scale and feature set.
| Approach | Strengths | Weaknesses | Good fit |
|---|---|---|---|
| `LIKE` with B-tree index | Simple, exact | Only prefix searches use the index | Codes and name prefixes |
| Trigram index | Substring and fuzzy matching | No linguistic ranking | Typo-tolerant name lookup |
| Database full-text index | Ranked keyword search, transactional | Limited analysis features | Search inside an application database |
| Dedicated search engine | Rich analysis, facets, scaling | Separate system to sync and run | Large catalogs and log search |
For many applications, a database full-text index is good enough and far simpler, because it needs no additional infrastructure and stays consistent with the data. A separate engine such as Elasticsearch becomes worthwhile when search requires faceting, advanced relevance tuning or very large volumes.
Costs and practical pitfalls
Full-text indexes are large and expensive to maintain. The index can approach or exceed the size of the indexed text, and every insert or update must tokenize the new content and modify many posting lists. On write-heavy tables this overhead is significant and should be measured before production use.
| Symptom | Likely cause | Remedy |
|---|---|---|
| Common words find nothing | Stop-word list removes them | Adjust or disable the stop-word list |
| Short terms are ignored | Minimum token length | Lower the length setting and rebuild |
| New rows not found | Asynchronous population | Wait, or configure synchronous updates |
| Query does not use the index | Expression differs from index definition | Match configuration and expression exactly |
| Poor results in another language | Wrong language configuration | Store and index the language per row |
In MySQL InnoDB, for example, the default minimum token length is three characters, so searches for two-letter terms return no rows until the setting is changed and the index is rebuilt. Defaults like this explain many reports that full-text search is broken when it is simply configured differently than expected.
In plain words
A full-text index works like the index at the back of a thick book. Instead of reading every page to find where a word appears, the reader looks up the word and gets a list of page numbers.
A database does the same with large amounts of text, such as product descriptions or support tickets. It keeps a list of every meaningful word and where it occurs, so a search finds the right records almost instantly and can even put the most relevant ones first.
A real-world example
A help desk application stores about three million support tickets in PostgreSQL. Agents search for phrases such as “refund declined”, and the original query used ILIKE '%refund%', which scanned the whole table and took around eight seconds on busy days.
The developers add a generated tsvector column that combines the ticket subject with weight A and the body with weight B, using the English configuration, and create a GIN index on it. Searches now use websearch_to_tsquery and order results with ts_rank. Typical queries return in under 50 milliseconds, tickets whose subject contains the terms rank first, and a search for “refunds” also finds tickets mentioning “refunded” because of stemming.