liveBook · Manning

Tsvector

As explained in Just Use Postgres!

A tsvector lexeme is a normalized term stored as an element of PostgreSQL's searchable text-vector representation.

A tsvector stores normalized lexemes for PostgreSQL full-text search. Tokenization, stemming, and stop-word removal happen before storage, so searches can reuse the processed representation instead of repeating normalization for every query. section 6.1 section 6.2.1

A tsvector representation records more than the normalized term:

  • Stores lexemes such as 'explor', 'fragil', and 'peac' rather than their original word forms. section 6.2.1
  • Preserves source positions, including positions skipped by removed stop words, for distance-sensitive operators such as <->. section 6.2.1
  • Supports matching with the @@ operator after the application query has been normalized into tsquery. section 6.3
  • Can carry A–D weights so ts_rank can prioritize document regions such as a movie title over its description. section 6.4

Generate and store movie-search lexemes

SELECT * FROM to_tsvector( 'The explorers must save the fragile peace between Earth and the aliens.' ); -- 'alien':12 'earth':9 'explor':2 'fragil':6 'must':3 'peac':7 'save':4 ALTER TABLE omdb.movies ADD COLUMN lexemes tsvector GENERATED ALWAYS AS ( to_tsvector( 'english', coalesce(name, '') || ' ' || coalesce(description, '') ) ) STORED; The generated column keeps normalized lexemes alongside each movie and can later be indexed for faster searches.

Search and rank stored vectors

SELECT id, name, vote_average, ts_rank(lexemes, to_tsquery('ghosts')) AS search_rank FROM omdb.movies WHERE lexemes @@ to_tsquery('ghosts') ORDER BY search_rank DESC, vote_average DESC NULLS LAST LIMIT 10; The query matches the normalized ghost lexeme, calculates a rank for each matching vector, and orders the results by relevance before using vote_average as a tiebreaker. section 6.3

How Just Use Postgres! covers it

How deep it goes
Explains normalization, positions, storage, weights, matching, ranking, and indexing, but does not detail internal index algorithms.
Examples
Demonstrates movie descriptions, generated columns, positional output, weighted title fields, ghost searches, and execution plans.
What it assumes
Assumes basic PostgreSQL SQL and text columns while teaching tokenization, normalization, lexeme storage, and full-text matching.
What you can do after
Build it from scratch by generating stored vectors, querying them with tsquery, ranking matches, and preparing them for indexing.
How it connects
Connects tsvector with lexemes, tokenization, stemming, stop words, tsquery, full-text search, ts_rank, GIN, and GiST indexes.

Also explained in

Compare how 2 books explain tsvector →

Related concepts

This explainer was generated by Manning's knowledge graph from the text of Just Use Postgres!. The section links point to the parts of the book it draws on.