Full-text search in Postgres with tsvector

When I started building search for NotasMD, my first instinct was to budget time for integrating Elasticsearch or Algolia. But with under a million rows, Postgres already ships everything needed: tsvector, tsquery, and GIN indexes.

The problem with LIKE '%query%'

A LIKE with wildcards on both sides can't use a regular B-tree index, so Postgres ends up scanning the whole table. It works fine with 100 rows, but degrades quickly.

-- Scans the entire table, no index used
SELECT * FROM notes WHERE content LIKE '%markdown%';

Generated column + GIN index

The fix is to add a tsvector column generated automatically from the content, then index it with GIN:

ALTER TABLE notes
  ADD COLUMN search_vector tsvector
  GENERATED ALWAYS AS (to_tsvector('english', title || ' ' || content)) STORED;
 
CREATE INDEX notes_search_idx ON notes USING GIN (search_vector);

With that in place, a search goes from scanning rows to resolving against an index:

SELECT id, title, ts_rank(search_vector, query) AS rank
FROM notes, to_tsquery('english', 'markdown & editor') query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 20;

ts_rank for relevance

ts_rank weighs matches by frequency and position, so results where the term appears in the title rank above ones that only mention it once in the body. It's not as sophisticated as BM25, but for search inside personal notes it's more than enough.

When something external is worth it

If you need faceting, aggressive typo tolerance, or search across tens of millions of documents with guaranteed low latency, Elasticsearch or Meilisearch are still the better choice. But for most internal products or MVPs, tsvector avoids one more piece of infrastructure to maintain.