inbuxa/inbuxa-server

Fork 0

Search: find addresses by local part, domain or name on PostgreSQL and MySQL #37

Merged

jcoffey-dev merged 1 commits from fix/pg-address-search into main

2026-09-24 19:11:32 +00:00

jcoffey-dev commented

2026-09-24 18:25:42 +00:00

Owner

Problem

In the 3-node PostgreSQL + NATS + Garage rehearsal, IMAP SEARCH FROM "noreply" matched 0–2 messages on PostgreSQL against 23 of 930 on RocksDB.

The message indexer (crates/email/src/message/index/search.rs, the From/To/Cc/Bcc arms) hands each display name and each address to the search store as keyword text (Language::None). The built-in index (RocksDB, SQLite, FoundationDB) splits keyword text with SpaceTokenizer into lowercase runs of alphanumerics, on insert and on search, and requires every query word. So [email protected] is found by the full address, noreply, amazon.com, amazon or com, and by any display-name word. The SQL backends differed:

PostgreSQL (crates/store/src/backend/postgres/search.rs): to_tsvector('simple', '[email protected]') is a single email token, so plainto_tsquery('simple', 'noreply') and 'amazon.com' never match; only the exact address did. Host names, URLs and file paths behave the same, and To/Cc/Bcc, contact and calendar keyword fields and trace keywords all go through the same path.
MySQL (crates/store/src/backend/mysql/search.rs): InnoDB's parser already splits on @ and ., but it never indexes its stopwords (com, de, www, the, ...) or words under innodb_ft_min_token_size (3). The query required every word (+noreply +amazon +com), and a required word that isn't indexed matches nothing. Checked on MySQL 8.0: +amazon +com, +jo and +the +invoice all return no rows. That broke amazon.com, [email protected], two-letter names, and body searches containing a stopword.

Change

PostgreSQL. Keyword text is split exactly as the built-in index splits it (keyword_terms(), SpaceTokenizer joined by spaces) before to_tsvector on insert and before plainto_tsquery/phraseto_tsquery on search. It stays on the simple configuration and the existing GIN indexes. Sort columns (From, To, ...) keep the raw text. Only Language::None text changes; language text is untouched.

Why this option:

Expanded token lists would be the same thing done in the indexer for every backend. The built-in index already tokenizes this way, so doing it in the PostgreSQL backend keeps the other backends' input unchanged.
A prefix/ILIKE strategy can't use the GIN index and is substring, not word, matching.
The simple configuration is already what these columns use. The problem is the parser's email/host/url token types, which no configuration change splits.

MySQL. Query words InnoDB doesn't index (default stopword list, or under 3 characters) are no longer required in MATCH ... AGAINST. In keyword fields they are checked with a word-boundary REGEXP ((^|[^[:alnum:]])word([^[:alnum:]]|$), case-insensitive under the table's _ci collation) on the rows the indexed words select. In language text (subject, body, attachment) they are dropped when other words remain, and checked by REGEXP only when nothing else is left.

Result: +noreply +amazon plus REGEXP com for [email protected], and +invoice for the invoice.

The list and minimum are InnoDB's defaults. A server with a larger innodb_ft_min_token_size would still miss words between the two sizes.

Needs a reindex

PostgreSQL: existing rows hold the old single-token vectors, so address searches only find old messages after a reindex. Run the reindexAccounts task (or the per-account reindex) after deploying. New and re-indexed messages are right at once.

MySQL: needs nothing; only the query changed.

TEXT and stemming ("shipping" 611 vs 435)

This PR doesn't change stemming. SEARCH TEXT ORs From/To/Cc/Bcc into the query as keyword text, so part of that gap is this address bug. Re-measure after the reindex.

What remains is how each backend stems, and neither behavior is clearly wrong:

PostgreSQL searches language text with the detected language's configuration, falling back to english and simple. A message indexed under a language PostgreSQL has no configuration for (or under simple) only matches the exact word.
The built-in index also adds a stemmed-prefix term.

Making them identical would mean reimplementing the built-in stemmer's terms in SQL, which isn't cheap. Left as is.

Tests

store::search_tests gains test_address_search:

Five messages with names and addresses in From/To/Cc/Bcc.
28 searches by full address, local part, domain, a single domain label, display name, a hyphenated local part, and three that must not match.
A TEXT-style OR across the four fields.

The expected ids are the built-in index's results, and every backend must return them.

Store	Result
RocksDb	pass
Sqlite	pass
MySql	pass (whole `search_tests`)
PostgreSql	address tests pass

On main, the new test fails on PostgreSQL (From "noreply": [], expected [0, 2]) and on MySQL (From "[email protected]": [], expected [0]).

On PostgreSQL, search_tests then stops in test_sort at the descending(From) case. That fails identically on main here. The expectation under is_postgres() depends on the database collation (this container is en_US.utf8), so it's unrelated to this change.

Run as QUICK_TEST=1 STORE=<store> cargo test -p tests --features postgres,mysql store::search_tests, under unshare -rn.

## Problem In the 3-node PostgreSQL + NATS + Garage rehearsal, IMAP `SEARCH FROM "noreply"` matched 0–2 messages on PostgreSQL against 23 of 930 on RocksDB. The message indexer (`crates/email/src/message/index/search.rs`, the From/To/Cc/Bcc arms) hands each display name and each address to the search store as keyword text (`Language::None`). The built-in index (RocksDB, SQLite, FoundationDB) splits keyword text with `SpaceTokenizer` into lowercase runs of alphanumerics, on insert and on search, and requires every query word. So `[email protected]` is found by the full address, `noreply`, `amazon.com`, `amazon` or `com`, and by any display-name word. The SQL backends differed: - **PostgreSQL** (`crates/store/src/backend/postgres/search.rs`): `to_tsvector('simple', '[email protected]')` is a single `email` token, so `plainto_tsquery('simple', 'noreply')` and `'amazon.com'` never match; only the exact address did. Host names, URLs and file paths behave the same, and To/Cc/Bcc, contact and calendar keyword fields and trace keywords all go through the same path. - **MySQL** (`crates/store/src/backend/mysql/search.rs`): InnoDB's parser already splits on `@` and `.`, but it never indexes its stopwords (`com`, `de`, `www`, `the`, ...) or words under `innodb_ft_min_token_size` (3). The query required every word (`+noreply +amazon +com`), and a required word that isn't indexed matches nothing. Checked on MySQL 8.0: `+amazon +com`, `+jo` and `+the +invoice` all return no rows. That broke `amazon.com`, `[email protected]`, two-letter names, and body searches containing a stopword. ## Change **PostgreSQL.** Keyword text is split exactly as the built-in index splits it (`keyword_terms()`, `SpaceTokenizer` joined by spaces) before `to_tsvector` on insert and before `plainto_tsquery`/`phraseto_tsquery` on search. It stays on the `simple` configuration and the existing GIN indexes. Sort columns (`From`, `To`, ...) keep the raw text. Only `Language::None` text changes; language text is untouched. Why this option: - *Expanded token lists* would be the same thing done in the indexer for every backend. The built-in index already tokenizes this way, so doing it in the PostgreSQL backend keeps the other backends' input unchanged. - *A prefix/ILIKE strategy* can't use the GIN index and is substring, not word, matching. - The *`simple` configuration* is already what these columns use. The problem is the parser's `email`/`host`/`url` token types, which no configuration change splits. **MySQL.** Query words InnoDB doesn't index (default stopword list, or under 3 characters) are no longer required in `MATCH ... AGAINST`. In keyword fields they are checked with a word-boundary `REGEXP` (`(^|[^[:alnum:]])word([^[:alnum:]]|$)`, case-insensitive under the table's `_ci` collation) on the rows the indexed words select. In language text (subject, body, attachment) they are dropped when other words remain, and checked by `REGEXP` only when nothing else is left. Result: `+noreply +amazon` plus `REGEXP com` for `[email protected]`, and `+invoice` for `the invoice`. The list and minimum are InnoDB's defaults. A server with a larger `innodb_ft_min_token_size` would still miss words between the two sizes. ## Needs a reindex **PostgreSQL:** existing rows hold the old single-token vectors, so address searches only find old messages after a reindex. Run the `reindexAccounts` task (or the per-account reindex) after deploying. New and re-indexed messages are right at once. **MySQL:** needs nothing; only the query changed. ## TEXT and stemming ("shipping" 611 vs 435) This PR doesn't change stemming. `SEARCH TEXT` ORs From/To/Cc/Bcc into the query as keyword text, so part of that gap is this address bug. Re-measure after the reindex. What remains is how each backend stems, and neither behavior is clearly wrong: - **PostgreSQL** searches language text with the detected language's configuration, falling back to `english` and `simple`. A message indexed under a language PostgreSQL has no configuration for (or under `simple`) only matches the exact word. - **The built-in index** also adds a stemmed-prefix term. Making them identical would mean reimplementing the built-in stemmer's terms in SQL, which isn't cheap. Left as is. ## Tests `store::search_tests` gains `test_address_search`: - Five messages with names and addresses in From/To/Cc/Bcc. - 28 searches by full address, local part, domain, a single domain label, display name, a hyphenated local part, and three that must not match. - A TEXT-style OR across the four fields. The expected ids are the built-in index's results, and every backend must return them. | Store | Result | |---|---| | RocksDb | pass | | Sqlite | pass | | MySql | pass (whole `search_tests`) | | PostgreSql | address tests pass | On `main`, the new test fails on PostgreSQL (`From "noreply"`: `[]`, expected `[0, 2]`) and on MySQL (`From "[email protected]"`: `[]`, expected `[0]`). On PostgreSQL, `search_tests` then stops in `test_sort` at the `descending(From)` case. That fails identically on `main` here. The expectation under `is_postgres()` depends on the database collation (this container is `en_US.utf8`), so it's unrelated to this change. Run as `QUICK_TEST=1 STORE=<store> cargo test -p tests --features postgres,mysql store::search_tests`, under `unshare -rn`.

jcoffey-dev added 1 commit 2026-09-24 18:25:42 +00:00

Search: find addresses by local part, domain or name on PostgreSQL and MySQL

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

Details

ci / build (pull_request) Successful in 4m20s

Details

639a415a4f

A 3-node PostgreSQL rehearsal found IMAP SEARCH FROM "noreply" matched
0-2 messages where RocksDB matched 23 of 930. The message indexer hands
each address and display name of From/To/Cc/Bcc to the search store as
keyword text (Language::None). The built-in index splits keyword text
into lowercase runs of alphanumerics, so an address is found by its full
form, its local part, its domain or a display-name word. The SQL
backends didn't:

- PostgreSQL's text parser keeps "[email protected]" as one email
  token (host names and URLs likewise), so neither "noreply" nor
  "amazon.com" ever matched it. Keyword text is now split the same way
  as the built-in index (SpaceTokenizer) before to_tsvector on insert
  and before plainto_tsquery/phraseto_tsquery on search, still under
  the 'simple' configuration, so the GIN index keeps serving the query.
  The sort columns keep the raw text.
- MySQL's FULLTEXT parser already splits on punctuation, but InnoDB
  never indexes its stopwords ("com", "de", "www", ...) or words under
  innodb_ft_min_token_size (3), and a required +word it hasn't indexed
  matches no row. So "amazon.com", "[email protected]" or "jane doe" found
  nothing. Those words are now matched with a word-boundary REGEXP on
  the rows the indexed words select. In language text (bodies,
  subjects) they are dropped when other words remain, and only checked
  when nothing else is left, so "the invoice" no longer finds nothing
  either.

Existing PostgreSQL search indexes hold the old single-token vectors and
need a reindex (the reindexAccounts task) before address searches find
old messages. MySQL needs none: only the query changed.

store::search_tests gains test_address_search: five messages, 28
FROM/TO/CC/BCC searches by full address, local part, domain, domain
labels, display name and hyphenated local part, plus a TEXT-style OR,
with the same expected ids on every backend. It passes on RocksDB,
SQLite, PostgreSQL and MySQL; on main it fails on PostgreSQL (From
"noreply") and MySQL (From "[email protected]").

jcoffey-dev merged commit 5853831bad into main

2026-09-24 19:11:32 +00:00

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: inbuxa/inbuxa-server#37