Postgres full-text search on French text: the stop-word trap

A few months ago I inherited a small Postgres 14 instance on a French VPS (OVH, 4 vCPU, 8 GB RAM) that powers an internal search over about 600k French documents — product notes, support tickets, a few thousand PDFs converted to plain text. The search was to_tsvector('french', body) @@ plainto_tsquery('french', $1) with a GIN index. It worked, until the support team started complaining that queries like “problème de paiement” returned almost nothing, while “souci de paiement” returned plenty.

I reproduced it in a scratch database. The first thing I did was look at what the parser actually produced:

SELECT to_tsvector('french', 'problème de paiement');
-- 'paiement':3 'problem':1
SELECT to_tsvector('french', 'problème');
-- 'problem':1

So the stemmer is fine — problème maps to problem. The problem was elsewhere: queries where the user typed only stop words or near-stop-words. de is a French stop word, and in a phrase query it just disappears. That part is expected. What surprised me was how many documents contained only a short accented term in a title field and nothing else, so the GIN index entry was tiny and the ranking fell apart. I ended up building a small expression index and a separate unaccented tsvector column, populated by a trigger:

CREATE EXTENSION IF NOT EXISTS unaccent;
ALTER TABLE docs ADD COLUMN search_fr tsvector;
UPDATE docs SET search_fr =
  to_tsvector('french', unaccent(coalesce(title,'') || ' ' || coalesce(body,'')));
CREATE INDEX docs_search_fr_idx ON docs USING gin (search_fr);

The trigger is boring but I’ll paste it because the ordering matters — unaccent before to_tsvector, not after:

CREATE OR REPLACE FUNCTION docs_search_fr_update() RETURNS trigger AS $$
BEGIN
  NEW.search_fr := to_tsvector('french',
    unaccent(coalesce(NEW.title,'') || ' ' || coalesce(NEW.body,'')));
  RETURN NEW;
END $$ LANGUAGE plpgsql;

CREATE TRIGGER docs_search_fr_trg BEFORE INSERT OR UPDATE ON docs
FOR EACH ROW EXECUTE FUNCTION docs_search_fr_update();

Why this shape: unaccent is not immutable, so you cannot put it directly in an expression index without lying to the planner. Doing it in a trigger keeps the index honest and lets me backfill with a plain UPDATE. The cost is write amplification — every document update recomputes the whole tsvector — and roughly 40% more disk for the index on this dataset. For 600k rows that’s fine; for a table taking thousands of writes a second I would not do this.

Where it breaks: the French stemmer still collapses things you might not want (paiement, payer, payé all become pai), so exact-match searches on legal terms get noisy. unaccent also doesn’t handle ligatures like œ the way you’d hope on older Postgres builds, and I’ve had cœur and coeur land in different lexemes. I did not solve that; I just documented it and told support to try both spellings.

The open question for me: is anyone running a French search setup where they kept the accents instead of stripping them, and if so, how did you handle users typing without accents on a French keyboard layout? I’d rather learn from a working setup than keep guessing.