DEV Community

Cover image for Why "paying invoices" doesn't find "paid" in PostgreSQL full-text search
Daniel Basseda
Daniel Basseda

Posted on AI-assisted

Why "paying invoices" doesn't find "paid" in PostgreSQL full-text search

PostgreSQL full-text search is a solid first retrieval layer for a RAG system. It runs inside the database you already have, needs no API calls, and handles plurals and verb endings out of the box.

Except when it doesn't. I ran into this while writing tests for a small document Q&A example: a chunk that says "Invoices must be paid within thirty days" is not found by the question "paying invoices". No typo, no bug in the query. Just English.

This post shows the miss, why it happens, why trigram similarity doesn't really fix it, and a small fix that does.

Reproduce it

A minimal table, with the tsvector as a generated column so PostgreSQL keeps it in sync with the text:

CREATE TABLE chunks (
    id         serial PRIMARY KEY,
    chunk_text text NOT NULL,
    tsv        tsvector GENERATED ALWAYS AS (to_tsvector('english', chunk_text)) STORED
);

INSERT INTO chunks (chunk_text) VALUES
  ('Invoices must be paid within thirty days.'),
  ('Returns require the original receipt.'),
  ('Send the signed contract to the finance team.');
Enter fullscreen mode Exit fullscreen mode

Now ask the obvious question:

SELECT id, chunk_text
FROM chunks
WHERE tsv @@ plainto_tsquery('english', 'paying invoices');
Enter fullscreen mode Exit fullscreen mode
 id | chunk_text
----+------------
(0 rows)
Enter fullscreen mode Exit fullscreen mode

Zero rows. Search for invoices alone and chunk 1 comes back, so the index is fine. The problem is the verb.

Why it happens

Two things combine here.

1. The stemmer works on spelling, not on grammar. The english configuration uses a Snowball stemmer: rules that strip suffixes. It has no dictionary of irregular verbs. Compare what the stored text and the query turn into:

SELECT tsv FROM chunks WHERE id = 1;
-- 'day':7 'invoic':1 'must':2 'paid':4 'thirti':6 'within':5

SELECT plainto_tsquery('english', 'paying invoices');
-- 'pay' & 'invoic'
Enter fullscreen mode Exit fullscreen mode

ts_debug shows it word by word:

SELECT token, lexemes FROM ts_debug('english', 'pay pays paying paid');
Enter fullscreen mode Exit fullscreen mode
 token  | lexemes
--------+---------
 pay    | {pay}
 pays   | {pay}
 paying | {pay}
 paid   | {paid}
Enter fullscreen mode Exit fullscreen mode

(Whitespace rows removed.) pays and paying lose their suffix and become pay. paid has no suffix to strip, so it stays paid. The same happens with buy/bought and send/sent, which the stemmer keeps as different lexemes.

2. plainto_tsquery joins terms with AND. The query is 'pay' & 'invoic'. The chunk has invoic but not pay, so one missing term rejects the whole chunk. The longer the question, the more chances one word has to miss.

Do trigrams save you?

A common next step is adding pg_trgm and keeping chunks whose similarity() with the question passes a threshold. It looks like it works on the short example:

CREATE EXTENSION IF NOT EXISTS pg_trgm;

SELECT id, round(similarity(chunk_text, 'paying invoices')::numeric, 3) AS sim
FROM chunks ORDER BY id;
Enter fullscreen mode Exit fullscreen mode
 id |  sim
----+-------
  1 | 0.244
  2 | 0.000
  3 | 0.000
Enter fullscreen mode Exit fullscreen mode

But look at where that 0.244 comes from. The two verbs share few trigrams: similarity('paying', 'paid') is 0.200. Most of the score comes from invoices, the word that already matched.

And real chunks are longer. I put the same sentence inside a 798-character chunk of billing text, and the similarity dropped to 0.101. Trigram similarity compares sets of three-letter pieces, so every extra word in the chunk dilutes the score. A cutoff of 0.15, for example, would drop the right chunk.

Trigrams are good at typos and partial words. They don't know that paid is the past tense of pay.

A small fix: expand the forms, OR them together

The query you actually want is "invoices AND (pay OR paid)". PostgreSQL can express that directly:

SELECT to_tsquery('english', 'invoices & (paying | paid)');
-- 'invoic' & ( 'pay' | 'paid' )
Enter fullscreen mode Exit fullscreen mode

That finds chunk 1. To build it from user input, don't concatenate the question into to_tsquery syntax. Run each word through plainto_tsquery as a bound parameter and combine the results with the tsquery operators || (OR) and && (AND):

import re

# Only the forms your documents actually use. Keep it small and reviewed.
IRREGULAR = {
    "pay": ["paid"], "paying": ["paid"], "pays": ["paid"], "paid": ["pay"],
    "send": ["sent"], "sending": ["sent"], "sent": ["send"],
    "buy": ["bought"], "buying": ["bought"], "bought": ["buy"],
}

STOPWORDS = {"how", "do", "i", "the", "a", "an", "to", "of", "for", "my", "when", "is", "are"}


def query_words(question):
    words = re.findall(r"[a-z0-9]+", question.lower())
    return [w for w in words if w not in STOPWORDS]


def build_tsquery(words):
    """AND across words, OR across the forms of each word."""
    groups, params = [], []
    for w in words:
        forms = [w] + IRREGULAR.get(w, [])
        groups.append("(" + " || ".join(["plainto_tsquery('english', %s)"] * len(forms)) + ")")
        params.extend(forms)
    return " && ".join(groups), params


def search(conn, question, limit=3):
    words = query_words(question)
    if not words:
        return []
    tsquery_sql, params = build_tsquery(words)
    sql = (
        "SELECT id, chunk_text, ts_rank(tsv, q) AS rank "
        f"FROM chunks, (SELECT {tsquery_sql} AS q) AS query "
        "WHERE tsv @@ q ORDER BY rank DESC LIMIT %s"
    )
    return conn.execute(sql, params + [limit]).fetchall()
Enter fullscreen mode Exit fullscreen mode

For "paying invoices" this generates ( 'pay' | 'paid' ) & 'invoic'. The only thing interpolated into the SQL string is a fixed template; every word from the user is a parameter.

Tested with psycopg 3 against PostgreSQL 16.15, with one more chunk added ("Customers can pay by card or bank transfer."):

Question Plain plainto_tsquery With expansion
paying invoices no match chunk 1
How were customers paid? no match chunk 4
sent contract no match chunk 3
When are invoices paid? chunk 1 chunk 1

A question that tries SQL injection (paying invoices'); DROP TABLE chunks; --) just becomes extra AND terms, returns nothing, and the table is still there.

The trade-off: the map is manual. Don't import a full list of English irregular verbs. Check which forms appear in your documents and in the questions people really ask, and add those. Each OR group also widens the match a little, so keep an eye on precision.

When this isn't enough

The expansion map fixes a known, small set of words. It won't help when people ask with words the document never uses at all, like "settle a bill" for a chunk about paying invoices. That is a meaning problem, not a spelling problem, and it's where vector search with embeddings earns its cost. In practice I'd keep full-text search for exact terms, codes and names, and combine it with vector search rather than replace it.

The practical lessons:

  • Check what your text actually becomes with to_tsvector and ts_debug. Don't assume the stemmer knows English.
  • Remember that plainto_tsquery uses AND. One unmatched word drops the chunk.
  • Don't treat trigram similarity as a grammar fix, and test any threshold on chunks of your real size.
  • Build a small test set of real questions with the chunk each should find. That's how you notice misses like this one before your users do.

All examples were tested on PostgreSQL 16.15 with the english configuration and pg_trgm, using psycopg 3.


I'm Daniel Basseda. I work in database administration and technical support. This post comes from my book RAG on Postgres: Building a Document Q&A System That Survives Real-World PDFs, which builds a small document Q&A system on PostgreSQL: OCR for scanned PDFs, pgvector, full-text and trigram search, hybrid retrieval and cited answers. There's also an e-book + code bundle with the ready-to-run project and its tests.

Top comments (0)