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

**Description:**

## The problem

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

Some examples:
- #1426 *"searching for 'foo' doesn't return 'foo.png'"*
- #3024 searching for a URL returns nothing.
- #10559 queries containing stop words return nothing.
- #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](https://www.postgresql.org/docs/current/functions-matching.html) - 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](https://www.postgresql.org/docs/current/pgtrgm.html) 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](https://github.com/Oele/synapse/pull/1) . 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:
```yaml
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.

Start by tracing Synapse's PostgreSQL message search and the event_search table, then compare it with the existing SQLite value column. Review how search configuration and database schema updates are handled before evaluating the proposed word, substring, and both modes. Done should include an agreed configuration, PostgreSQL storage and indexing approach, and a documented backfill decision.

Written by the indexing model from the issue text.