The watches that matched nothing: Portuguese stemming and the index I built but never hit

Brazil’s federal government publishes what it does in the Diário Oficial da União, the official gazette. Contracts, appointments, grants and sanctions all land there, tens of thousands of acts a month. RegWatch reads it every morning so a law firm doesn’t have to, matches the day’s acts against the terms each client is watching, and mails out a digest.

The previous update ended on a number I left standing: a morning run that ingested 3,338 acts and reported matches=0. No error, no exception, no red line in any log. The ingest worked. The matcher ran. It just found nothing, on a day when the gazette was full of exactly the sort of act my clients had asked about.

The cause was one word in a Postgres search configuration. Fixing it took fifty-four minutes and six commits, and each fix uncovered the next one.

simple means simple

RegWatch stores each act’s text in a tsvector, Postgres’s tokenised full-text column, and matches watch terms against it. The vector was built with config="simple", which I had picked early because it was the option that needed no thought.

The simple configuration lowercases and splits on punctuation. That is all it does. It runs no stemmer, so it has no idea that two forms of a word are the same word:

regwatch=# SELECT to_tsvector('simple','contratos'), to_tsvector('simple','contrato');
  to_tsvector  | to_tsvector
---------------+--------------
 'contratos':1 | 'contrato':1

regwatch=# SELECT to_tsvector('portuguese','contratos'), to_tsvector('portuguese','contrato');
 to_tsvector | to_tsvector
-------------+-------------
 'contrat':1 | 'contrat':1

A client watching contrato was never going to match an act announcing contratos, and Brazilian gazette prose is almost entirely plural. aviso de licitação appears in a heading; the body says licitações. The product was working precisely as built, and finding nothing.

This landed on top of work I had shipped the day before. Watches used to be a flat list of terms that all had to appear in one act. I had just replaced that with term groups, ANDed across groups and ORed within them, working from a three-phase plan I wrote before touching code so each behaviour change would ship on its own tag and stay attributable. The groups change was phase 1. Stemming was phases 2 and 3, and it is the reason phase 1 looked like it had done nothing.

Two deviations, both measured first

The plan’s normalisation table implied a second text column: lowercased, accents preserved, to build the Portuguese vector from. Before writing it I checked whether it was needed:

SELECT to_tsvector('portuguese','AVISO DE LICITAÇÕES')
     = to_tsvector('portuguese','aviso de licitações') AS case_folds;
-- t

to_tsvector case-folds on its own. The column would have been a duplicate of roughly 42 MB of text earning nothing, so the vector is built straight from title and raw_text, and the deviation is written down in the plan next to the query that justifies it.

The second deviation cost more. Match.rank was supposed to be a relevance score, and ranking cannot span a mix of substring and full-text predicates. Rank is now computed from an OR of every term rendered as a full-text query, used only to order a client’s digest, and it carries a comment in the matcher saying so, so that nobody reads it later as a guarantee it does not make.

The normaliser I had to reverse

Switching the vector to config="portuguese" broke the thing that was supposed to make matching reliable.

RegWatch already had a text normaliser. It lowercased, decomposed to NFKD, and dropped every combining character, which is the standard recipe for making licitação and licitacao compare equal. Under simple, stripping accents is exactly right: the tokeniser does no linguistic work, so making both sides accent-free is what lets a client type without a keyboard layout for Portuguese.

Under the Portuguese stemmer it is actively harmful, and the numbers are in the code’s own docstring because I measured them before believing it:

def normalize_pt(s: str) -> str:
    """Lowercase and collapse whitespace, preserving accents.

    Deliberately does NOT strip accents. Measured: with accents removed,
    'licitações' stems to 'licitaco' and 'licitação' to 'licitaca', so the two
    stop matching. Accented, both stem to 'licit'.
    """
    s = unicodedata.normalize("NFC", s.lower())
    return re.sub(r"\s+", " ", s).strip()

The stemmer is trained on real Portuguese. Feed it a word with the accents filed off and it is no longer looking at Portuguese, so it chops at a boundary that means nothing.

That is only half of it. Some of the text arriving from the gazette is NFD-decomposed, with accents as separate combining codepoints, and NFD text stems differently from the NFC text my queries carry. The query side is handled by normalize_pt above. The index side has to happen in Postgres, which meant a small custom function class, because NORMALIZE(x, NFC) takes NFC as a keyword rather than a string literal and a plain Django Func quotes it wrong:

class NormalizeNFC(Func):
    function = "NORMALIZE"
    template = "%(function)s(%(expressions)s, NFC)"
    output_field = TextField()

Two spellings of one word, byte-different and visually identical, silently failing to match text that is plainly on the page. Same class of bug as the plural one, and much harder to have spotted by reading.

The index I built and never hit

Concept terms now went through the stemmer. Entity terms, the ones like a company name where stemming would do damage, were supposed to go through a substring match backed by a pg_trgm GIN index. I added the index and then went to check the query plan, which is the only reason I caught what happened next.

The index was fine. The predicate could not use it.

My plan had specified a trigram-indexed ILIKE, and that would have worked: pg_trgm serves ILIKE from the index directly. But I did not write ILIKE, I wrote Django’s __icontains, and on Postgres that compiles to something else:

UPPER(search_text) LIKE UPPER('%saneamento%')

That UPPER() wrapper is the whole problem. The index holds trigrams of search_text; the predicate asks about trigrams of UPPER(search_text), an expression the planner has to compute for every row before it can compare anything. The term for a predicate the planner can answer from an index is sargable, and wrapping the indexed column in a function is the most common way to lose it. So Postgres did the only thing left and read the whole table.

The fix is one word. __contains compiles to a bare LIKE, and both sides of the comparison were already lowercased by the normaliser, so the case-insensitivity __icontains was buying had been redundant from the start.

Those are real EXPLAIN ANALYZE timings, median of five runs on Postgres 16, but I want to be exact about what they are: a local reproduction on synthetic gazette-shaped text, not a replay of production. RegWatch’s pilot acts were deleted at the end of August, so there is no paired before-and-after against the real corpus left to measure, and I would rather show a reproducible benchmark than a number I cannot rebuild. The script and its CSV are committed next to the chart.

At 100,000 rows the wrapped predicate takes 248 ms against 2.4 ms for the same query the index can serve. The two index-served forms are within noise of each other; the gap that matters is the flat pair against the line that climbs.

The regression test for it is the odd one in the suite, because it cannot assert on results:

def test_entity_substring_lookup_stays_sargable_against_the_trigram_index():
    qs = Act.objects.filter(_term_q("saneamento", KIND_ENTITY))
    sql = str(qs.query)
    assert "UPPER(" not in sql
    assert "LIKE" in sql

Both lookups return identical rows. Only the generated SQL tells them apart, so the SQL is what the test reads. That is the same instinct behind catching an N+1 before it reaches production: assert on how the query was built, because the rows coming back will look right either way.

A rollback that would be worse than the bug

Dropping the old simple vector was the last commit of the afternoon, and it is the one with a trap in it.

The migration reverses structurally. Run it backwards and the column comes back, so Django is satisfied and the schema matches. It comes back empty, and the older matcher resolves every entity term against it. Roll back without repopulating and every entity term matches nothing, for every client, with no error in any log: the exact failure this whole afternoon existed to fix, reintroduced by the safety mechanism meant to undo it. Nothing self-heals either, because the interactive backfill reuses already-ingested acts without rebuilding their vectors.

That one is written into the runbook rather than left in my head, with the repopulating UPDATE sitting directly under the rollback command. A migration that is reversible in the schema and not in the data is not a reversible migration, and the only place that distinction can live is a document the person rolling back is already reading. It is the same instinct that put an audience-scoped token recipe in the runbook when I stood the private service up, so that future-me would not rediscover it at deploy time.

Making it match made it harder to explain

The fix worked. Watches match plurals, accented and unaccented spellings land on the same lexeme, and entity terms hit the index.

Then you go to answer why a particular act is in a digest. The tool I use for that reads back the context around each matched term, and for a stemmed match it has nothing to show: the term as typed does not literally appear anywhere in the act.

It is not wrong. The act matched licitação and contains only licitações, and the two are the same word only inside the stemmer. So the tool now prints “no literal occurrences (matched via stemming)” and I have a feature that is correct and cannot show its work by pointing at a substring.

There is a sharper edge on the same idea. Excludes are evaluated with substring semantics, which is broad enough to suppress any entity term it is paired with. It is not broad enough to suppress a stemmed one: licitação matches licitações through the stemmer, while the exclude licitacao is not a substring of licitacoes. An inclusion can out-reach its own exclusion. That is written down in the matcher as a comment, because I do not have a fix for it that does not involve running excludes through the stemmer too, and I have not yet convinced myself that is what a person means when they type a phrase they want gone.

What I learned

The failure hid because it was a zero rather than an error. A stack trace has a shape you can grep for. A silently empty result set has the same shape as a quiet day, and the gazette does have quiet days, so there was nothing to separate the two until I stopped to ask why every day was quiet. The nastiest bugs I have shipped have all had this property, of being indistinguishable from the system working.

The other one is about defaults. config="simple" was the option I picked because it needed no thought, and it silently defined what the product could find for months. The accent stripper was a correct choice under that default and a bug under the new one. Django’s __icontains is a reasonable-looking call that quietly discarded an index I had built twenty minutes earlier. Each of those behaved exactly as documented, in a context where I had never checked what the documentation said.

What’s next

Stemming fixed recall. It immediately created the opposite problem: one Portuguese word now drives about 12% of a client’s feed, and the obvious way to filter it out would throw away every true positive along with the noise, because the signal sits in an adjacent phrase that no amount of reading the term itself will surface. I got the fix wrong on the first attempt, wrote it down, and the data corrected me a second time in the opposite direction a week later. That is the next update.

The question I still don’t have a good answer to: when a match is correct but cannot be explained by pointing at the text, what do you show the person who asked? “Matched via stemming” is true, and it does not tell them what they wanted to know. If you have built search over a language with real morphology and solved the provenance side of it, I would like to hear how. The series is where I keep publishing the parts I get wrong.