Message search only matches whole words — proposal for opt-in substring search

[Oele]

Description:

The problem

The current message search functionality is not behaving the way many users would expect.

Some examples:

• Postgres full text search fails to index terms including full stops (SYN-528) #1426 "searching for 'foo' doesn't return 'foo.png'"
• Search for URLs in messages doesn't return any results #3024 searching for a URL returns nothing.
• Message search result not matching PostgreSQL English stop words #10559 queries containing stop words return nothing.
• Searching channels for literal strings like "C++20" produces non-useful results #12609 C++20 gives unhelpful results.

I've reproduced each of these on current develop.

A recent real world example: I was unable to find a recent discussion about the Qwen large language model, by searching for qwen - turned out that message I was trying to find contained not qwen but qwen3.6.

Why is this happening

The current message search functionality is based on PostgreSQL's tsvector type, which stores a collection of lexemes , which are essentially normalized forms of words. This normalization is language specific, and it's currently hardcoded to English.

An example:

The cats are running quickly

Will be stored in these lexemes:

cat
run
quick

Note that very common words like 'the' and 'are' are completely dropped, and other words are 'normalized' to their singular form or stem.

This normalization enables some level of 'fuzzy search' - the example message above will be returned when regardless of whether a user is searching for 'quick', 'quicker', 'quickest', 'quickly', etc.

This solution won't work very well though in case of:

• other languages than English;
• things that aren't really words (for example, urls, or strings like 'qwen3.6') - they are normalized/lexicalized in unpredictable ways
• substrings - the tsvector search only allows to search for whole words - (we might be able to get it to match on the beginning of a lexeme, but not random flexible substrings)
• a quoted piece of a sentence that doesn't start or end exactly on a word boundary - for example "re runn".

In other words; it is very good at doing a fuzzy search for a collection of standardized English words in a message, but it's not providing the type of raw text search comparable to what a grep on a text file, or a ctrl+f in a browser or other desktop app, or a search function in a commercial chat application would provide.

Proposed change

Let's add a real, raw, language agnostic substring search functionality that will allow users to freely search messages by text.

Because some users may like the current functionality, for example because they like the 'fuzzy search' element of it, and because providing real substring search implies an increase in storage, I think the new functionality should be opt-in, and the server admin should be free to choose between 3 options:

• change nothing - keep the word/lexeme based search
• fully switch to substring search;
• get both word and substring based search results.

Implementation

We can use PostgreSQL's ILIKE operator - it's a vendor specific case insensitive variant of the standard SQL LIKE operator, which allows searching for simple patterns - ideal for substring searches.

With the help of a GIN trgm index, it can be very fast.

To be able to create such an index and do efficient queries, we would need to have the message plain text in a separate column of the event_search table - something we already have in the value column in SQLite, but not yet in PostgreSQL.

There is a working implementation of this proposal in this PR . Linked for reference rather than review - happy to reshape it to whatever we agree here.

SQLite

Doing something similar in SQLite is hard - for one, we would have to switch to FTS5 to get similar indexing capabilities - without it, every search would result in a full table scan. Also, SQLite only supports one 'tokenizer' per table - which means we would have to choose between either the 'stemming' one which does the English language 'lexeme' type thing currently in use, or the 'trigram' one which can provide an efficient substring index.

Config

Add a new config section, defaulting to the current 'word' functionality:

search:
  mode: both        # "word" (default), "substring", or "both"
  substring:
    min_length: 3

Mode	Behaviour
word	Unchanged current behaviour, and still the default
substring	Terms matched literally, anywhere in a message, including inside words
both	Union of the two

A three-way enum rather than two booleans, so "neither enabled" is unrepresentable.

Terms shorter than min_length are dropped - this is mainly there because the trigram index used only works on substrings of 3 characters or longer - so setting this to anything below 3 can result in full table scans, with the associated performance hit.

Database schema change

Add a value column to event_search - matching what already exists in SQLite, and add a pg_trgm GIN index on it.

Decisions I'd like input on

Backfill

The new value column will only be filled for new messages when the search mode is set to substring or both .

When switching from word to substring or both mode, the column will need to be backfilled for old messages.

This can either be done with a script that has to be run once by the admin manually, or by a background update.

Since this is an operation that can result in a lot of extra storage usage, I think this should be a conscious action by the admin, so a manual script is probably the best option here - but I'm curious to hear opinions on this.

The additional storage in practice

On my homeserver with 1.4 million indexed events:

description	size
value column	275 MB
pg_trgm GIN index	276 MB
total added	551 MB

event_search was about 1492 MB before adding the column, so it grew by about 37%.

The backfill itself took about ten minutes.

Alternatives considered

Prefix lexeme search

This could fix some, but not all of the issues:

  to_tsvector('english','talking about qwen3.6 today') @@ to_tsquery('english','qwen:*')  ->  t

Searching for qwen:* instead of qwen will result in a match on a message with qwen3.6. This only works in case a lexeme starts with the word we're searching for, so it only solves a subset of the cases we've seen:

Case	Prefix search (qwen:*)	Substring search
#1426 - foo finds foo.png	✓	✓
#3024 - URLs	mostly	✓
#10559 - queries containing stop words	✗	✓
#12609 - C++	✗	✓
Infix - day inside holiday.png	✗	✓
Partial words - "re runn"	✗	✓
Cost	free - uses the existing index	+551 MB on a 1.4M-event homeserver, plus a one-off backfill

Directly index the content body in event_json instead of adding a value column

Would save us from adding an extra column, but this type of 'functional index' evaluates its expression on every INSERT, and can result in errors like unsupported Unicode escape sequence in case a body contains weird values like U+0000. This will cause INSERTs to fail, breaking message ingestion.