PostgreSQL Full-Text Search Done Right

I spent two weeks setting up Elasticsearch for a project that had maybe 50,000 rows. Two weeks. Index mappings, JVM tuning, cluster config, and it still crashed on startup half the time.

Then I deleted the whole thing and used PostgreSQL built-in full-text search instead. Took an afternoon. Same results, no extra service to babysit.

For most projects, PostgreSQL full-text search is enough. Here is how I set it up.

The Basics

PostgreSQL has two functions for full-text search: to_tsvector and to_tsquery. The first turns text into a searchable vector, the second turns a user query into something that can match against it.

Here is the simplest version:

SELECT title, ts_rank_cd(tsv, query) AS rank
FROM articles, to_tsquery('english', 'postgres & search') AS query
WHERE tsv @@ query
ORDER BY rank DESC;

That assumes you have a tsvector column called tsv. Which you should. Computing tsvector on the fly for every query is slow and dumb.

Creating the Index

Here is how I create a generated tsvector column and a GIN index on it:

ALTER TABLE articles ADD COLUMN tsv tsvector
  GENERATED ALWAYS AS (
    to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, ''))
  ) STORED;

CREATE INDEX idx_articles_tsv ON articles USING GIN(tsv);

The GENERATED ALWAYS AS part means PostgreSQL maintains the column automatically. No triggers, no cron jobs, no sync issues. You insert or update a row, the tsvector updates.

GIN indexes are the right choice for tsvector. They handle the inverted-index structure efficiently. GiST works too but is slower for searching.

Querying With User Input

Here is the part that took me the longest to figure out. Users type weird things. Plain to_tsquery chokes on colons, ampersands, and unbalanced quotes. So you need to sanitize input.

Here is a Python function I use to convert user input into a safe tsquery:

import re

def build_tsquery(user_input):
    # Split on whitespace, strip special chars
    terms = re.findall(r'\w+', user_input.lower())
    if not terms:
        return ''
    # Join with ampersand for AND search
    return ' & '.join(terms)

# Usage
query_str = build_tsquery('postgresql full text search')
# Returns: 'postgresql & full & text & search'

Then pass that to plainto_tsquery or build a raw query string. I prefer plainto_tsquery for simple cases because it handles the parsing for you:

SELECT id, title, ts_rank_cd(tsv, query) AS rank
FROM articles, plainto_tsquery('english', ) AS query
WHERE tsv @@ query
ORDER BY rank DESC
LIMIT 20;

is the user input. plainto_tsquery handles the sanitization. Use it unless you need boolean operators.

Ranking and Relevance

There are two ranking functions: ts_rank and ts_rank_cd. The second one ( cover density ) ranks higher when matching terms are closer together. I prefer ts_rank_cd for most use cases.

You can also weight columns. Title matches should rank higher than body matches. Here is how:

ALTER TABLE articles ADD COLUMN tsv tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(body, '')), 'B')
  ) STORED;

Weight A is highest, D is lowest. Title gets A, body gets B. Now title matches naturally float to the top.

When NOT to Use This

PostgreSQL full-text search is not a replacement for Elasticsearch in every case. Here is where it falls short:

- You need fuzzy search with typo tolerance. PostgreSQL has trigram similarity ( pg_trgm ) but it is not as good as Elasticsearch Levenshtein-based matching.

- You have millions of documents and need distributed search across nodes. PostgreSQL runs on one machine ( unless you shard manually ).

- You need real-time analytics on search results, faceted navigation, or autocomplete at scale. Elasticsearch handles these better.

For a blog, a documentation site, an internal tool, or anything under a few hundred thousand rows, PostgreSQL is more than enough. Stop adding services you do not need.

:)