inbuxa/inbuxa-server

Fork 0

PostgreSQL search: find words inside URLs and file names in body text #52

Merged

jcoffey-dev merged 1 commits from fix/pg-url-body-tokens into main

2026-09-25 04:42:06 +00:00

jcoffey-dev commented

2026-09-25 04:33:43 +00:00

Owner

Problem

In the rehearsal, body searches on PostgreSQL missed 8 messages across a handful of searches that RocksDB found.

After #37, address fields are split into words the way the built-in index splits them. Language text (subject, body, attachments) still goes straight to PostgreSQL's parser, which keeps a URL, host, path or file name as tokens of its own:

https://x.example/shipping-support/ becomes a url, a host and a url_path.
invoice-2024.pdf becomes a file.
login/?password= stays together too.

So TEXT/BODY "shipping" missed messages where the word appears only inside a link. The built-in index (RocksDB, SQLite, FoundationDB) splits those tokens into words and finds them.

Change

Changes are in crates/store/src/backend/postgres/search.rs, following #37's keyword_terms().

Indexing: url_terms(). Language text is indexed as before, then followed by the word parts of each token that contains a URL separator (/ . @ : ? = & # _ % + ~ \), split with SpaceTokenizer:

Stemming is unchanged. The parts go through the same text search configuration as the rest of the text, so they are stemmed like the words around them. The original tokens stay in the index, so a search written as a URL still matches the URL.
Most text doesn't change. Plain words, words that only carry punctuation (end., (see), and hyphenated words (the parser already splits those) add nothing. Text without links is indexed exactly as before.
Each part is added once per document, so a newsletter's many tracking links don't repeat utm and campaign again and again.

Searching: query_url_terms(). A query word written as a URL, host, file or hyphenated word also matches as its word parts, ORed with the query as written. So shipping-support or invoice-2024.pdf match the new parts, and documents indexed before this change still match as they did.

Index size. Measured with pg_column_size(to_tsvector('english', …)) on sample mail:

Sample	Before	After
plain letter, no links	450 B	450 B
order notice, 3 links	546 B	716 B (+31%)
newsletter, 25 tracking links	5586 B	6430 B (+15%)

The GIN index grows less than the vectors: most parts (https, www, com, utm) are lexemes it already holds.

MySQL is unchanged. InnoDB's parser already splits on this punctuation, and #37 handled its stopwords.

Needs a reindex

Existing messages keep their old vectors, so until they are reindexed, words inside their links still aren't found. Run the reindexAccounts task (or the per-account reindex) after deploying. New and reindexed messages match at once. Searches for a URL written out in full keep matching old messages throughout.

Tests

store::search_tests gains test_url_word_search. It indexes five bodies and runs 19 body searches, with the same expected ids on every backend:

Words found only in a link: in a URL path, a query string, a host or a file name (shipping, support, password, login, jane, invoice, pdf, 2024, example, mail)
Tokens as written: the full URL, shipping-support, invoice-2024.pdf, mail.example.com
Plain words: parcel, records, thanks
Non-matches: billing, example.net

Backend	Result
RocksDB	pass
SQLite	pass
MySQL	pass
PostgreSQL	pass
PostgreSQL on `main`	fails at the first search: `shipping` finds `[3]`, not `[0, 3]`

After the new test, the PostgreSQL suite stops at the account sort assertion (query.rs:689). It fails the same way on main, with the same arrays; this PR doesn't touch it.

QUICK_TEST=1 nstest3.sh <worktree> <Store> --features postgres,mysql store::search_tests

## Problem In the rehearsal, body searches on PostgreSQL missed 8 messages across a handful of searches that RocksDB found. After #37, address fields are split into words the way the built-in index splits them. Language text (subject, body, attachments) still goes straight to PostgreSQL's parser, which keeps a URL, host, path or file name as tokens of its own: - `https://x.example/shipping-support/` becomes a url, a host and a url_path. - `invoice-2024.pdf` becomes a file. - `login/?password=` stays together too. So `TEXT`/`BODY "shipping"` missed messages where the word appears only inside a link. The built-in index (RocksDB, SQLite, FoundationDB) splits those tokens into words and finds them. ## Change Changes are in `crates/store/src/backend/postgres/search.rs`, following #37's `keyword_terms()`. **Indexing: `url_terms()`.** Language text is indexed as before, then followed by the word parts of each token that contains a URL separator (`/ . @ : ? = & # _ % + ~ \`), split with `SpaceTokenizer`: - **Stemming is unchanged.** The parts go through the same text search configuration as the rest of the text, so they are stemmed like the words around them. The original tokens stay in the index, so a search written as a URL still matches the URL. - **Most text doesn't change.** Plain words, words that only carry punctuation (`end.`, `(see`), and hyphenated words (the parser already splits those) add nothing. Text without links is indexed exactly as before. - **Each part is added once per document**, so a newsletter's many tracking links don't repeat `utm` and `campaign` again and again. **Searching: `query_url_terms()`.** A query word written as a URL, host, file or hyphenated word also matches as its word parts, ORed with the query as written. So `shipping-support` or `invoice-2024.pdf` match the new parts, and documents indexed before this change still match as they did. **Index size.** Measured with `pg_column_size(to_tsvector('english', …))` on sample mail: | Sample | Before | After | |---|---|---| | plain letter, no links | 450 B | 450 B | | order notice, 3 links | 546 B | 716 B (+31%) | | newsletter, 25 tracking links | 5586 B | 6430 B (+15%) | The GIN index grows less than the vectors: most parts (`https`, `www`, `com`, `utm`) are lexemes it already holds. MySQL is unchanged. InnoDB's parser already splits on this punctuation, and #37 handled its stopwords. ## Needs a reindex Existing messages keep their old vectors, so until they are reindexed, words inside their links still aren't found. Run the `reindexAccounts` task (or the per-account reindex) after deploying. New and reindexed messages match at once. Searches for a URL written out in full keep matching old messages throughout. ## Tests `store::search_tests` gains `test_url_word_search`. It indexes five bodies and runs 19 body searches, with the same expected ids on every backend: - **Words found only in a link:** in a URL path, a query string, a host or a file name (`shipping`, `support`, `password`, `login`, `jane`, `invoice`, `pdf`, `2024`, `example`, `mail`) - **Tokens as written:** the full URL, `shipping-support`, `invoice-2024.pdf`, `mail.example.com` - **Plain words:** `parcel`, `records`, `thanks` - **Non-matches:** `billing`, `example.net` | Backend | Result | |---|---| | RocksDB | pass | | SQLite | pass | | MySQL | pass | | PostgreSQL | pass | | PostgreSQL on `main` | fails at the first search: `shipping` finds `[3]`, not `[0, 3]` | After the new test, the PostgreSQL suite stops at the account sort assertion (`query.rs:689`). It fails the same way on `main`, with the same arrays; this PR doesn't touch it. ``` QUICK_TEST=1 nstest3.sh <worktree> <Store> --features postgres,mysql store::search_tests ```

jcoffey-dev added 1 commit 2026-09-25 04:38:33 +00:00

PostgreSQL search: find words inside URLs and file names in body text

ci / fork-checks (pull_request) Successful in 48s

Details

ci / build (pull_request) Successful in 3m26s

Details

5927dda7e2

After #37, address fields on PostgreSQL are split into words as the
built-in index splits them, but language text (subject, body,
attachments) still goes straight to PostgreSQL's parser, which keeps a
URL, host, path or file name as tokens of its own:
"https://x.example/shipping-support/" becomes a url, a host and a
url_path, "invoice-2024.pdf" a file. So TEXT/BODY "shipping" missed
messages where the word appears only inside a link, while RocksDB and
the other built-in backends found them: 8 messages across a handful
of searches in the rehearsal.

On insert, language text is now indexed as it was, followed by the
word parts of each token that holds a URL separator (/ . @ : ? = & # _
% + ~ \), split with SpaceTokenizer as keyword_terms() splits addresses.
The parts go through the same text search configuration as the rest of
the text, so they are stemmed like the words around them. Plain words,
words that only carry punctuation ("end.", "(see") and hyphenated words
(the parser already splits those) add nothing, so text without links
is indexed exactly as before. Each part is added once per document.
On sample mail, the text vector of a short order notice with three
links grows from 546 to 716 bytes, a newsletter with 25 tracking links
from 5586 to 6430, and a plain letter not at all.

On search, a query word written as a URL, host, file or hyphenated word
also matches as its word parts, ORed with the query as written, so
"shipping-support" or "invoice-2024.pdf" match the new parts and
documents indexed before this change still match as they did.

Existing messages keep their old vectors until they are reindexed (the
reindexAccounts task); new and reindexed messages match at once.

store::search_tests gains test_url_word_search: five bodies, 19 body
searches for words found only in a URL path, query string, host or
file name, the tokens as written, plain words and non-matches, with the
same expected ids on every backend. It passes on RocksDB, SQLite,
MySQL and PostgreSQL; on main PostgreSQL fails at the first ("shipping"
finds [3], not [0, 3]). On PostgreSQL the suite then stops at the
account sort assertion (query.rs:689) exactly as it does on main.

jcoffey-dev force-pushed fix/pg-url-body-tokens from 54eaf5d3d6 to 5927dda7e2

2026-09-25 04:38:33 +00:00

Compare

jcoffey-dev merged commit c521f060ba into main

2026-09-25 04:42:06 +00:00

jcoffey-dev deleted branch fix/pg-url-body-tokens

2026-09-25 04:42:06 +00:00

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: inbuxa/inbuxa-server#52