← Back to the section

When you build search for a site, you want a "Молоко" product with a match right in its name to rank above a product where the word "молоко" appears somewhere near the end of the description. That's exactly what weights are for in PostgreSQL.

Matching and ranking — two different jobs

Full-text search in PostgreSQL works in two steps.

Matching is the answer to the question "is there a match at all?". The @@ operator simply returns true or false. It doesn't care where exactly the word occurred — in the title or in a footnote.

Ranking is sorting the results by match quality. This is where weights come in: they tell the ranking function that a match in name is worth more than a match in description.

The key point: weights do not affect matching. They affect only the order in which the results appear.

The four weight levels

PostgreSQL gives exactly four levels — A, B, C, D. It's not a number but a letter label, stored right inside the tsvector next to each word. By default the ts_rank function converts them into numbers like this:

LetterMultiplier
A1.0
B0.4
C0.2
D0.1

If you don't set weights manually, every word gets weight D — the baseline level of "ordinary text."

How to set weights — the setweight function

The setweight() function takes a tsvector and a letter, and returns the same tsvector but with all words labeled with that letter. Important: a single call can label only one tsvector, that is — one field.

To mark up several fields with different weights, you build a separate tsvector for each and then concatenate them with the || operator:

SELECT
    setweight(to_tsvector('russian', coalesce(name,        '')), 'A') ||
    setweight(to_tsvector('russian', coalesce(summary,     '')), 'B') ||
    setweight(to_tsvector('russian', coalesce(description, '')), 'D')
FROM product
WHERE id = 1;

coalesce is mandatory here: if a field is NULL, then to_tsvector returns NULL, and the whole result of the || operator also becomes NULL.

In the output, each lexeme will be labeled with a position and a letter:

'молок':1A 'натурал':2B 'деревн':3D ...

This is the physical content of the tsvector — positions and weight labels.

Store the tsvector in the table, don't compute it on the fly

Computing the tsvector right in the WHERE clause is a full table scan with a computation on every row. With thousands of rows it works, with millions — it doesn't.

The right approach: create a generated column of type STORED. PostgreSQL will recompute it automatically on every INSERT or UPDATE and store the result on disk. You build a GIN index on this column, and the search then goes through the index.

ALTER TABLE product
    ADD COLUMN tsv tsvector
    GENERATED ALWAYS AS (
        setweight(to_tsvector('russian', coalesce(name,        '')), 'A') ||
        setweight(to_tsvector('russian', coalesce(summary,     '')), 'B') ||
        setweight(to_tsvector('russian', coalesce(description, '')), 'D')
    ) STORED;

CREATE INDEX product_tsv_idx ON product USING GIN (tsv);

After this, any search will use the index, and there's no need to update tsv manually.

Searching and ranking the results

Matching — with the @@ operator:

SELECT id, name
FROM product
WHERE tsv @@ websearch_to_tsquery('russian', 'молоко');

This returns all rows where "молоко" appears in any field. The order is still arbitrary.

To sort by match quality — add ts_rank and ORDER BY:

SELECT
    id,
    name,
    ts_rank(tsv, websearch_to_tsquery('russian', 'молоко')) AS rank
FROM product
WHERE tsv @@ websearch_to_tsquery('russian', 'молоко')
ORDER BY rank DESC
LIMIT 20;

A "Молоко" product with a match in name (weight A) gets a rank of about 0.6. A "Кефир" product, where the word "молоко" is mentioned in the description (weight D), gets about 0.06. As a result, the right product ends up higher.

ts_rank and ts_rank_cd — what's the difference

ts_rank accounts for the weights of the lexemes and how often they occur in the document.

ts_rank_cd (cover density) additionally accounts for the distance between the matched words. If you search for "свежее молоко" and they stand next to each other in the text — the rank is higher than when there's a paragraph of text between them.

For short fields (name, summary) the difference is negligible. For long descriptions, ts_rank_cd gives a more natural order.

How to widen the gap between weights

The default multipliers (A=1.0, D=0.1) sometimes don't separate good and bad matches enough. In that case you override the values with an array in ts_rank:

ts_rank(
    ARRAY[0.05, 0.1, 0.4, 1.0],   -- {D, C, B, A} — in exactly this order
    tsv,
    websearch_to_tsquery('russian', 'молоко')
)

This is useful when "random" text in description starts to compete with exact matches in name.

Pitfalls

The language must match everywhere. If you indexed with to_tsvector('russian', ...) but search with websearch_to_tsquery('english', ...) — the dictionaries reduce words to different stems. "Молоко" in the Russian dictionary becomes молок, in the simple one it stays молоко. Matching won't work, and weights have nothing to do with it. Fix the language in one place: as a parameter in the config or in the function.

setweight overwrites the previous weight. setweight(setweight(tsv, 'A'), 'B') leaves only B. Weights don't add up — each call overwrites everything.

Stop-words don't make it into the tsvector at all. Words like "и", "не", "для" are filtered out by the dictionary during parsing, and it's impossible to label them with a weight.

A GIN index doesn't support INCLUDE. This means an index-only scan over GIN is unavailable — the query will still go to the table for additional fields. More on covering indexes — in the Covering Index article.

On a write-heavy table, keep an eye on GIN. Every update to a field included in tsv updates the GIN index. By default fastupdate = on — this speeds up inserts by accumulating changes in a pending list. Under heavy load, watch that the pending list doesn't grow uncontrollably.

How to choose weights for a real project

A standard scheme for a product catalog:

FieldWeightWhy
nameAAn exact match is the strongest signal
brand, tagsBImportant, but not as valuable as the name
summaryCA contextual mention
descriptionDUseful for coverage, but can be incidental

Changing weights is a product decision, not a technical one. Before changing them, it's worth collecting typical search queries and comparing the results before and after. Otherwise ranking will change from release to release for no visible reason.

In short

  • PostgreSQL gives four weight levels: A / B / C / D (A is the highest, D is the baseline).
  • Weights do not affect matching (@@) — only ranking (ts_rank, ts_rank_cd).
  • Weights are set via setweight(to_tsvector(...), 'A') separately for each field, then concatenated with ||.
  • A ready tsvector is best stored in a generated column (STORED) with a GIN index — it isn't recomputed on every query.
  • The dictionary language at indexing time and at search time must match, otherwise matching breaks.
  • ts_rank_cd additionally accounts for the distance between words — useful for long texts.
  • Choosing weights is a product decision that's worth validating against real queries.