Full-text search

Find rows that contain words, rank the best matches first, and mark the found words in bold.

• Category: Indexes & search
• Keywords: full-text, fulltext, text search, search engine, find words, FULLTEXT, BM25, relevance, ranking, best matches, phrase, prefix, wildcard, exclude word, stemming, stem, stop words, language, case folding, Porter, Nepali search, Devanagari, bold, search result page, news search, forum search, how the index is used, count from the index, text match is checked on them, word positions
• See also: Indexes, Text functions, Regular expressions, WHERE, EXPLAIN

Full-text search finds rows that contain some words, like a search box on a website. LIKE '%bank%' must read every row and check every letter; it also finds bank inside embankment. A FULLTEXT index keeps a list of words, and for each word the rows that have it. So a search in 200,000 news articles takes a few milliseconds.

Three functions work together:

• match says if a row has the words (use it in WHERE).
• search_rank says how well a row matches (use it in ORDER BY).
• highlight returns the text with the found words inside <b> and </b>.

The examples use a news table: id, symbol, published_at, title, body, with articles in English and Nepali.

Example: A news search page: the 3 best articles for 'bank' (sample data: content-news)

Setup:

CREATE FULLTEXT INDEX news_text ON news (title, body);

SELECT id,
       highlight(title, 'bank') AS title,
       round(search_rank(title, body, 'bank'), 3) AS rank
FROM news
WHERE MATCH(title, body, 'bank')
ORDER BY search_rank(title, body, 'bank') DESC
LIMIT 3;

Result:

(3 rows)

CREATE FULLTEXT INDEX

Syntax:

CREATE FULLTEXT INDEX [IF NOT EXISTS] name ON table (column [, column ...]) [WITH (language = 'english')]

Makes a full-text index on one or more text columns. With several columns, they are searched together as one text.

• Returns: a message: index 'name' created on table 'table' (N rows).
• NULL: a NULL column counts as empty text.
• Notes:
  • A query uses the index when match(...) names the same columns as the index (the order of the columns does not matter). match(title, 'bank') does not use an index on (title, body).
  • match, search_rank and highlight also work without an index. Then KDB reads every row. That is fine for small tables.
  • Like every index it lives in memory. kdb serve builds it on a background thread right after the database opens, after the other indexes (see built at open); a query that needs it sooner builds it itself. It takes a few seconds for 200,000 articles. INSERT, UPDATE and DELETE keep it up to date.
  • Since 2026-10-02 the words of each partition are also saved on disk, so a restart does not split every row into words again (see segments on disk).
  • The index keeps where each word is in the text, so a phrase ("net profit") is also found from the index. With them, the index took a little less memory than the text itself (302 MB for 378 MB of news, measured before 2026-10-02).
  • Since 2026-10-02 the index is smaller and its searches skip more. Where a row is: two numbers (partition, row), about 40 bytes a row instead of about 200. The rows of a word are packed in blocks of 128; each block notes its last row, so a search for a rare word with a common one jumps over whole blocks of the common word. Each block also notes the highest count of the word in it, for faster top-N ranking later.
  • GET /api/stats shows its memory under memory: fulltext_index_bytes (part of index_bytes), with fulltext_indexes, fulltext_docs and fulltext_terms (distinct words). JSON (GIN) indexes are counted there too.
  • DROP INDEX, SHOW INDEXES and the other rules of indexes are the same. A FULLTEXT index cannot be UNIQUE.
  • A column of another type (LONG, TIMESTAMP, ...) gives an error.

Example: Search news in the title and the body (sample data: content-news)

CREATE FULLTEXT INDEX news_text ON news (title, body);
SELECT id, title FROM news WHERE MATCH(title, body, 'city bank') ORDER BY id;

Result:

CREATE FULLTEXT INDEX news_text ON news (title, body)

index 'news_text' created on table 'news' (5 rows)

SELECT id, title FROM news WHERE match(title, body, 'city bank') ORDER BY id

(2 rows)

Example: EXPLAIN shows the index (sample data: content-news)

Setup:

CREATE FULLTEXT INDEX news_text ON news (title, body);

EXPLAIN SELECT id FROM news WHERE MATCH(body, title, 'bank');
EXPLAIN SELECT id FROM news WHERE MATCH(title, 'bank');

Result:

EXPLAIN SELECT id FROM news WHERE match(body, title, 'bank')

(4 rows)

EXPLAIN SELECT id FROM news WHERE match(title, 'bank')

(4 rows)

The first query uses news_text (same columns, other order). The second names only title, so it reads the table.

Example: Forum: search the posts (sample data: content-forum)

Setup:

CREATE FULLTEXT INDEX posts_body ON posts (body);

SELECT id, thread_id, author, body FROM posts WHERE MATCH(body, 'bonus') ORDER BY id;

Result:

(2 rows)

Example: Changes are in the index at once (sample data: content-news)

Setup:

CREATE FULLTEXT INDEX news_text ON news (title, body);

UPDATE news SET body = 'Coastal Bank Bank and Prabhu Bank signed the merger.' WHERE id = 5;
SELECT id FROM news WHERE MATCH(title, body, 'prabhu');
DELETE FROM news WHERE id = 4;
SELECT id FROM news WHERE MATCH(title, body, 'gainers');

Result:

UPDATE news SET body = 'Coastal Bank Bank and Prabhu Bank signed the merger.' WHERE id = 5

1 row(s) updated

SELECT id FROM news WHERE match(title, body, 'prabhu')

(1 row)

DELETE FROM news WHERE id = 4

1 row(s) deleted

SELECT id FROM news WHERE match(title, body, 'gainers')

(0 rows)

Example: Only text columns (sample data: content-news; shows an error)

CREATE FULLTEXT INDEX news_time ON news (published_at);

Result:

Error: index 'news_time' on table 'news' is FULLTEXT but column 'published_at' is TIMESTAMP; expected STRING (text) columns

Language: stems and stop words

Syntax:

CREATE FULLTEXT INDEX name ON table (columns) WITH (language = 'english')

By default a search finds whole words only: planning does not find plans. An index made WITH (language = 'english') cuts each word to its stem first (the Porter2 English stemmer), so plans, planned and planning are all plan.

What 'english' changes:

• Notes:
  • The search text is cut with the same rules as the rows. match, search_rank and highlight on the columns of such an index use them too, also when they read the rows instead of the index.
  • Words with digits, Nepali and other scripts are not stemmed.
  • An index without the option (or language = 'simple') works exactly as before.
  • To change the language of an index, DROP INDEX it and create it again. A table has one FULLTEXT index for the same columns.
  • Other languages give an error. Data with such an index needs KDB 2026-10-02 or newer (see versions).

Example: 'planning' finds 'plans' and 'planned' only with the English analyzer (sample data: content-news)

Setup:

CREATE FULLTEXT INDEX news_en ON news (title, body) WITH (language = 'english');

SELECT id, title FROM news WHERE MATCH(title, body, 'planning') ORDER BY id;
SELECT id, highlight(body, 'shares') AS body FROM news WHERE MATCH(title, body, 'share') ORDER BY id;
SELECT match('Coastal Bank plans', 'planning') AS without_index;

Result:

SELECT id, title FROM news WHERE match(title, body, 'planning') ORDER BY id

(2 rows)

SELECT id, highlight(body, 'shares') AS body FROM news WHERE match(title, body, 'share') ORDER BY id

(2 rows)

SELECT match('Coastal Bank plans', 'planning') AS without_index

(1 row)

Example: Stop words do not count in a phrase (sample data: content-news)

Setup:

CREATE FULLTEXT INDEX news_en ON news (title, body) WITH (language = 'english');

SELECT id FROM news WHERE MATCH(title, body, '"talks about the merger"');
SELECT count(*) AS found FROM news WHERE MATCH(title, body, 'the');

Result:

SELECT id FROM news WHERE match(title, body, '"talks about the merger"')

(1 row)

SELECT count(*) AS found FROM news WHERE match(title, body, 'the')

(1 row)

Example: Only english and simple (sample data: content-news; shows an error)

CREATE FULLTEXT INDEX news_fr ON news (title, body) WITH (language = 'french');

Result:

Error: index 'news_fr' on table 'news' has language 'french'; expected 'english' or 'simple'

match

Syntax:

match(column [, column ...], 'words')
MATCH(column [, column ...], 'words')

true when the text of the columns has the words of the search text. The search text can have phrases, prefixes and words that must not be there (see Search text).

• Returns: BOOL.
• NULL: NULL search text gives NULL (the row is not found). A NULL column counts as empty text.
• Notes:
  • English letters A-Z: case does not matter. Other letters must be the same.
  • Whole words only, no stemming: bank does not find banks. Use a prefix (bank*), or an index made WITH (language = 'english').
  • Every word must be there. There is no OR inside the search text. For "one word or the other" write two calls: MATCH(body, 'bonus') OR MATCH(body, 'dividend') (this reads every row).
  • A search with no word to find (empty, only -word, only *) finds nothing.
  • At least 2 arguments (1 column and the words), at most 64.

Example: Every word must be there, in any order and any case (sample data: content-news)

SELECT id, title FROM news WHERE MATCH(title, body, 'BANK city') ORDER BY id;

Result:

(2 rows)

Example: Nepali words, and the same number in Devanagari digits (sample data: content-news)

SELECT id, title FROM news WHERE MATCH(title, body, 'नाफा') ORDER BY id;
SELECT id FROM news WHERE MATCH(title, body, '१२.५') ORDER BY id;

Result:

SELECT id, title FROM news WHERE match(title, body, 'नाफा') ORDER BY id

(1 row)

SELECT id FROM news WHERE match(title, body, '१२.५') ORDER BY id

(2 rows)

Example: Search together with other conditions (sample data: content-news)

SELECT id, symbol, title
FROM news
WHERE MATCH(title, body, 'bank') AND published_at >= '2026-09-01'
ORDER BY id;

Result:

(2 rows)

Example: match on plain text values (sample data: content-news)

SELECT match('City Bank profit rises', 'BANK') AS a,
       match('City Bank profit rises', 'banks') AS b,
       match('City Bank profit rises', 'bank*') AS c,
       match('City Bank', NULL) AS d;

Result:

(1 row)

Example: The search text is missing (sample data: content-news; shows an error)

SELECT id FROM news WHERE MATCH(title);

Result:

Error: line 1, column 27: function 'match' takes 2 to 64 argument(s), got 1

How the index is used

Syntax:

use index name (full-text search)
use index other (...) (r row(s), the text match is checked on them)

With a FULLTEXT index on the same columns as match, KDB always uses it. It never goes back to reading the text of every row, even for a word that is in most rows.

• Notes:
  • The ORDER BY ... LIMIT shortcut is for relational tables only, when ORDER BY is the first key columns, ascending, and those columns are numbers or times that cannot be NULL. On a time-series table the matching rows are sorted after they are found.
  • EXPLAIN shows the index it picks, but not the count or the LIMIT shortcut: those are decided while the query runs.

Example: A symbol finds fewer rows than the word: its index goes first (sample data: content-news)

Setup:

CREATE FULLTEXT INDEX news_text ON news (title, body);
CREATE INDEX news_symbol ON news (symbol);

EXPLAIN SELECT id FROM news WHERE MATCH(title, body, 'bank');
EXPLAIN SELECT id FROM news WHERE MATCH(title, body, 'bank') AND symbol = 'COAST';
SELECT id, title FROM news WHERE MATCH(title, body, 'bank') AND symbol = 'COAST';

Result:

EXPLAIN SELECT id FROM news WHERE match(title, body, 'bank')

(4 rows)

EXPLAIN SELECT id FROM news WHERE match(title, body, 'bank') AND symbol = 'COAST'

(5 rows)

SELECT id, title FROM news WHERE match(title, body, 'bank') AND symbol = 'COAST'

(1 row)

Example: Count the articles with a phrase: from the index (sample data: content-news)

Setup:

CREATE FULLTEXT INDEX news_text ON news (title, body);

SELECT count(*) AS articles FROM news WHERE MATCH(title, body, '"city bank"');

Result:

(1 row)

Segments on disk

Splitting text into words is the slow part of building a full-text index. Since 2026-10-02 KDB saves the words of each partition in a file, a segment, so the next open builds the index from those files and reads no row.

• Notes:
  • The segments are in the folder fts/ of the database. They are a copy of what the rows already say: deleting the folder only makes the next build slower. Backups do not copy them.
  • JSON (USING GIN) indexes get segments too.
  • An encrypted database encrypts its segments like every other file.
  • kdb check reads every segment and prints a WARNING for a damaged one. It does not count as damage: KDB never uses a damaged segment.
  • GET /api/stats counts them under search: segments_written, segment_extensions, segments_merged, segments_loaded, segment_rows_loaded, segments_damaged, segments_stale, and rows_indexed (rows a build still split into words). After a clean restart rows_indexed stays at 0.
  • An older KDB ignores the folder and opens the database as before. kdb downgrade to a KDB from before 2026-10-02 moves the segments to _trash.

Growing partitions. A table that gets new rows all day (a news feed, a log) would write the segment of today's partition again at every quiet moment, for a few new rows. Instead, KDB writes only the new rows in an extra segment (fts/<name>.1.kfs, .2.kfs, ...) that names the segment before it. Small extra segments are merged into one as they pile up, and into a new whole segment when they reach half of it, so a partition never has more than 5 files. Old ones go to _trash.

/api/stats search also shows segment_extensions (extra segments written) and segments_merged (older segments a merge replaced). The result is faster restarts: after a clean shutdown no row is read to build a full-text index, even on a table that grows all day.

search_rank

Syntax:

search_rank(column [, column ...], 'words')

A number that says how well a row matches: higher is better, 0 when the row does not match. It uses the BM25 formula (the formula of most search engines):

• a word that is rare in the table counts more than a common word,
• a word that is there many times counts more,
• a long text counts a little less.

• Returns: DOUBLE, 0 or more.
• NULL: NULL search text gives NULL.
• Notes:
  • The number depends on all rows of the table (how rare each word is). Compare ranks only inside one query. When rows change, the rank of the same row can change.
  • Words with - do not add to the rank.
  • The fastest form: WHERE MATCH(cols, 'words') ORDER BY search_rank(cols, 'words') DESC LIMIT n, with the same columns and the same words, and a FULLTEXT index on those columns. KDB takes the n best rows straight from the index. EXPLAIN then says best n row(s) by search_rank, top-k by rank (WAND).
  • Only the best rows are ranked: in that form KDB keeps the n best rows found so far. A row, or a whole block of 128 rows of a word, that cannot rank above the n-th best even with the most mentions of each word is skipped without working out its rank. The answer is the same as ranking every match. Conditions on other columns (AND category = 3) are fine: when they turn rows down, KDB looks for more. search in GET /api/stats counts the ranks worked out (bm25_evaluations), the top-n searches (top_k_searches) and what they skipped (skipped_blocks, skipped_docs).
  • Without a FULLTEXT index on the same columns, search_rank reads the whole table once per query.
  • On values that are not columns of a table (search_rank('bank bank', 'bank')), the value is ranked alone, as if it were the only row.

Example: The best matches first (sample data: content-news)

Setup:

CREATE FULLTEXT INDEX news_text ON news (title, body);

SELECT id, title, round(search_rank(title, body, 'bank'), 3) AS rank
FROM news
WHERE MATCH(title, body, 'bank')
ORDER BY rank DESC;

Result:

(3 rows)

Example: Rows that do not match get 0 (sample data: content-news)

SELECT id, round(search_rank(title, body, 'city bank'), 3) AS rank FROM news ORDER BY id;

Result:

(5 rows)

Example: The fast top-20 form: EXPLAIN says 'best 20 row(s) by search_rank' (sample data: content-news)

Setup:

CREATE FULLTEXT INDEX news_text ON news (title, body);

EXPLAIN SELECT id, title FROM news
WHERE MATCH(title, body, 'bank')
ORDER BY search_rank(title, body, 'bank') DESC
LIMIT 20;

Result:

(7 rows)

Example: Forum: the best posts for 'bonus' (sample data: content-forum)

Setup:

CREATE FULLTEXT INDEX posts_body ON posts (body);

SELECT id, author, body, round(search_rank(body, 'bonus'), 3) AS rank
FROM posts
WHERE MATCH(body, 'bonus')
ORDER BY search_rank(body, 'bonus') DESC
LIMIT 5;

Result:

(2 rows)

highlight

Syntax:

highlight(column [, column ...], 'words')

Returns the text with every found word inside <b> and </b>, for a search result page.

• Returns: STRING.
• NULL: NULL search text gives NULL. A NULL column is left out; when every column is NULL the result is NULL.
• Notes:
  • highlight does not check that the row matches. A row may match because of its body, while you highlight only its title (the title then has no <b>).
  • Each word of a phrase is marked alone, wherever it is. A prefix (bank*) marks the whole word (<b>Banks</b>). Words with - are not marked.
  • Web pages: highlight changes no other character. When users can type < or & (in a forum they can), escape the text first. See the last example.

Example: Mark the search word in the titles (sample data: content-news)

SELECT id, highlight(title, 'bank') AS title
FROM news
WHERE MATCH(title, body, 'bank')
ORDER BY id;

Result:

(3 rows)

Example: Nepali text, a phrase and a prefix (sample data: content-news)

SELECT id, highlight(body, 'नाफा १२.५') AS body FROM news WHERE id = 2;
SELECT id, highlight(title, body, '"net profit" bank*') AS text FROM news WHERE id = 1;

Result:

SELECT id, highlight(body, 'नाफा १२.५') AS body FROM news WHERE id = 2

(1 row)

SELECT id, highlight(title, body, '"net profit" bank*') AS text FROM news WHERE id = 1

(1 row)

Example: Forum: escape what users typed, then highlight (safe for a web page) (sample data: content-forum)

Setup:

INSERT INTO posts VALUES (9, 2, 7, 'ram', 'Is <b>ACME</b> a hydro IPO & a good buy?', '2026-09-02T09:30:00+05:45');

SELECT id,
       highlight(replace(replace(replace(body, '&', '&amp;'), '<', '&lt;'), '>', '&gt;'), 'hydro ipo') AS safe_html
FROM posts
WHERE MATCH(body, 'hydro ipo')
ORDER BY id;

Result:

(1 row)

In the last example the <b> that the user typed became <b>, so only the search words are bold. (A search for the words amp, lt or gt would then also mark letters inside &, < and >.)

Search text

Syntax:

word word       every word must be there, anywhere, in any order
"word word"     these words next to each other, in this order
word*           a word that starts with these letters
-word           this word must not be there
-"word word"    this phrase must not be there

How KDB cuts text (and the search text) into words:

• Notes:
  • There is no OR, no AND word, no brackets, no fuzzy search, no synonyms, and no stop words unless the index is English. bank OR merger looks for three words, and one of them is or.
  • A phrase does not cross from one column to the next: the end of title and the start of body are not "next to each other".
  • Words joined by punctuation in the search text work like a phrase: city-bank is the same as "city bank".
  • A prefix works on one word only: net-prof* and "net prof*" do not find net profit.
  • At least one word must be without -. -merger alone finds nothing.

Example: A phrase, a prefix, and a word that must not be there (sample data: content-news)

SELECT id FROM news WHERE MATCH(title, body, '"net profit"') ORDER BY id;
SELECT id FROM news WHERE MATCH(title, body, 'hydro*') ORDER BY id;
SELECT id FROM news WHERE MATCH(title, body, 'bank -merger') ORDER BY id;

Result:

SELECT id FROM news WHERE match(title, body, '"net profit"') ORDER BY id

(1 row)

SELECT id FROM news WHERE match(title, body, 'hydro*') ORDER BY id

(2 rows)

SELECT id FROM news WHERE match(title, body, 'bank -merger') ORDER BY id

(2 rows)

Example: Nepali: a word ending needs a prefix search (sample data: content-news)

SELECT id FROM news WHERE MATCH(title, body, 'बैंक') ORDER BY id;
SELECT id FROM news WHERE MATCH(title, body, 'बैंक*') ORDER BY id;

Result:

SELECT id FROM news WHERE match(title, body, 'बैंक') ORDER BY id

(0 rows)

SELECT id FROM news WHERE match(title, body, 'बैंक*') ORDER BY id

(1 row)

Example: Numbers: 2,840 and 2840 are the same word (sample data: content-news)

SELECT id, title FROM news WHERE MATCH(title, body, '2840');
SELECT id, title FROM news WHERE MATCH(title, body, '2,840');

Result:

SELECT id, title FROM news WHERE match(title, body, '2840')

(1 row)

SELECT id, title FROM news WHERE match(title, body, '2,840')

(1 row)

Example: OR is not an operator, and a search needs a word to find (sample data: content-news)

SELECT id FROM news WHERE MATCH(title, body, 'bank OR merger') ORDER BY id;
SELECT id FROM news WHERE MATCH(title, body, '-merger') ORDER BY id;
SELECT id FROM news WHERE MATCH(title, body, 'bank') OR MATCH(title, body, 'hydropower') ORDER BY id;

Result:

SELECT id FROM news WHERE match(title, body, 'bank OR merger') ORDER BY id

(0 rows)

SELECT id FROM news WHERE match(title, body, '-merger') ORDER BY id

(0 rows)

SELECT id FROM news WHERE match(title, body, 'bank') OR match(title, body, 'hydropower') ORDER BY id

(4 rows)