You Don’t Always Need Elasticsearch: How PostgreSQL Full-Text Search Works Under the Hood

Why Your First Search Doesn’t Scale

When you build search into an application, the first instinct is usually simple:

1
SELECT *
2
FROM posts
3
WHERE content ILIKE '%postgres%';

It works.

Then your application grows.

Users start searching for running when the document says run, for database indexing when the document contains both words, for "postgres database" as a phrase, for PostgreSQL with different capitalization, and for results ranked by relevance.

Suddenly, ILIKE '%query%' starts looking less like search and more like repeatedly asking the database to read everything.

Here’s the conceptual problem. Even though you might throw five million rows at it, ILIKE never gets smarter. The database has no structure saying:

database → rows 4, 18, 73, 91, 1042, ...

Instead, it asks each row one at a time:

Row 1    → does body contain "database"?
Row 2    → does body contain "database"?
Row 3    → does body contain "database"?
Row 4    → does body contain "database"?
...

An ordinary B-tree index doesn’t rescue you either. A B-tree is great for WHERE id = 123 or WHERE email = 'foo@example.com', but a substring search with a leading wildcard doesn’t map onto it.

Part 1: Turn Documents Into Searchable Structures

Instead of storing only:

"The quick brown fox jumps over the lazy dog."

we preprocess it into something closer to:

quick
brown
fox
jump
lazi
dog

Do this for every document and you can construct an inverted index — the direction is reversed:

document → words              becomes              word → documents

                          quick     →  1, 7, 42
                          brown     →  1, 19, 42
                          fox       →  1, 5, 42
                          jump      →  1, 8, 42
                          database  →  3, 9, 17, 100
                          postgres  →  4, 10, 27

That is the fundamental idea behind essentially every modern lexical search system. Elasticsearch does the same high-level thing: analyze text, build an inverted index mapping terms to documents, and use that structure to execute searches and rank results.

PostgreSQL can do this too. The central data type is tsvector.

Part 2: What tsvector Actually Is

PostgreSQL defines tsvector as a representation of a document optimized for text search — a sorted list of distinct, normalized lexemes with optional positional and weight information.

Don’t read that as “a special column containing words.” Take a concrete example:

1
SELECT to_tsvector(
2
    'english',
3
    'The quick brown fox jumps and jumps again'
4
);

Conceptually you get:

'again':8
'brown':3
'fox':4
'jump':5,7
'quick':2

The original prose has become a lexeme → positions map. 'jump':5,7 means the word occurred twice, at positions 5 and 7 — stored once, not duplicated. That’s the mental model to keep for the whole article:

Document
   ↓
Normalized searchable representation

{
    again → [8]
    brown → [3]
    fox   → [4]
    jump  → [5,7]
    quick → [2]
}

How Those Lexemes Are Made

to_tsvector() doesn’t just split(" "). Its pipeline has several distinct layers:

                 TEXT
                  │
                  ▼
             ┌─────────┐
             │ Parser  │    → what kind of thing is this?
             └────┬────┘
                  │  token + type
                  ▼
          ┌─────────────────┐
          │  Configuration  │   → token type → dictionary mapping
          └────────┬────────┘
                   ▼
             Dictionaries    → what should it be indexed as?
                   │
                   ▼
              Lexeme(s)
                   │
                   ▼
              tsvector

This separation is the key insight:

PARSER        "What is this piece of text?"
   ↓
DICTIONARY    "What should I index it as?"

The parser breaks input into tokens and classifies them. PostgreSQL’s default parser recognizes 23 token types — asciiword, word, email, url, int, float, version, tag, and more. So something like foo@bar.com costs $19.99 gets classified as email, asciiword, and float. You can inspect this with ts_debug().

The configuration decides, per token type, which dictionaries run. You don’t want to process postgres, someone@example.com, and 192.168.1.1 all the same way. A dictionary then does one of several things: it recognizes a token and returns lexemes, treats it as a stopword (returns nothing), or passes it on to the next dictionary in a chain.

This is why the pipeline isn’t just tokenization. The full transformation is:

Raw text
   ↓
Tokenization + classification
   ↓
Dictionary chain:
   ├── stopword?   → discard
   ├── synonym?    → replace
   ├── stemmable?  → normalize
   └── unknown?    → next dictionary
   ↓
Lexeme + position + optional weight
   ↓
Sorted + deduplicated
   ↓
tsvector

Stopwords and Stemming

Some words — the, a, an, is, of, in — provide almost no search discrimination, so they’re dropped. But an important subtlety: PostgreSQL doesn’t pretend stopwords never existed. Their positions still leave gaps that can influence proximity-based ranking. For “in the list of stop words” you get:

'list':3  'stop':5  'word':6

Positions 1, 2, and 4 are missing — they were stopwords. The words disappear from the lexeme set, but their positional footprint doesn’t necessarily.

Stemming is where running can match run. The built-in English stemmer is a Snowball dictionary:

1
SELECT ts_lexize('english_stem', 'stars');  -- {star}

So both sides of a search can be normalized into the same lexeme, and the comparison happens at the lexeme level, not the string level:

running → run
run     → run

That’s one of the fundamental differences between full-text search and substring search.

Part 3: Positions and Weights Matter More Than They Look

Why store fox → 4 instead of just fox? Because position tells the engine where the term occurred — and that enables phrase matching and smarter ranking.

Compare “The fox jumps quickly” with “The fox is somewhere across the field and eventually jumps.” Both contain fox and jump, but in the first they’re adjacent. That’s the difference between:

'fox' & 'jump'        both match any two documents
'fox' <-> 'jump'      lexemes that appear successively

tsquery_phrase() gives you the same phrase capability with explicit distances. Positions aren’t metadata — they’re part of what makes PostgreSQL more powerful than ILIKE '%fox%'.

PostgreSQL also lets you attach weights — A, B, C, D — to positions so a match in a title (weight A) can outrank one buried in the body (weight D):

title:  postgres → [1:A]
body:   postgres → [14:D, 38:D, 92:D]

Internally a tsvector is a set of lexeme → positions entries, sorted and deduplicated. Positions are capped at 16383, and duplicate positions are discarded. It is not a database equivalent of ["fox", "jump", "jump"]; it’s a specialized search data type.

Part 4: GIN Turns the Representation Upside Down

Here’s a critical distinction: a tsvector alone doesn’t make search fast. You could search with:

1
SELECT *
2
FROM documents
3
WHERE to_tsvector('english', body) @@ to_tsquery('english', 'database');

But on a large table, regenerating tsvector values while scanning every row isn’t the architecture you want. That’s where GIN — the Generalized Inverted Index — comes in.

Suppose your table looks like:

Row 1 → {postgres, database, index}
Row 2 → {database, sql}
Row 3 → {postgres, search}
Row 4 → {database, index}

The row-oriented representation is row → terms. GIN builds the inverted one — term → rows:

postgres  → [Row 1, Row 3]
database  → [Row 1, Row 2, Row 4]
index     → [Row 1, Row 4]
sql       → [Row 2]
search    → [Row 3]

This is why it’s so much faster. With 10 million documents and only 20,000 containing postgres:

Sequential scan:           Inverted index:
10,000,000 rows            postgres
   ↓                            ↓
Does this contain postgres?    GIN
   ↓                            ↓
(read EVERYTHING)        20,000 candidate rows

The database has eliminated the other millions of rows before touching them. At a high level, GIN keeps (key, posting list) pairs — a lexeme pointing at the rows that contain it.

A typical production setup stores the vector so it isn’t recomputed on every query:

1
ALTER TABLE documents
2
ADD COLUMN search_vector tsvector
3
GENERATED ALWAYS AS (
4
    to_tsvector(
5
        'english',
6
        coalesce(title, '') || ' ' ||
7
        coalesce(body, '')
8
    )
9
) STORED;
10

11
CREATE INDEX documents_search_idx
12
ON documents
13
USING GIN (search_vector);

Now your inserts and updates automatically maintain the search representation and the index over it.

Part 5: The Other Half — tsquery and @@

We have the document representation (tsvector). Now we need the search representation: tsquery.

Document → tsvector
Query    → tsquery

A document like “The quick brown fox jumps” becomes 'quick':2 'brown':3 'fox':4 'jump':5. A query like "fox jump" becomes a structured expression over lexemes:

postgres & database    →      AND
                             /    \
                       postgres  database

postgres | mysql       →      OR
                             /   \
                       postgres mysql

!postgres              →     NOT
                              │
                          postgres

'fox' <-> 'jump'       →     fox immediately followed by jump

The query language supports &, |, !, and <-> for boolean and phrase matching. So the engine isn’t doing string contains string — it’s evaluating a structured query against normalized lexemes.

The crucial requirement: the query must go through the same pipeline as the document. If the document’s running is normalized to run, then a user’s running must also normalize to run, so the comparison becomes run == run rather than running == run.

During @@, PostgreSQL intersects posting lists:

              fox & jump
                /     \
               /       \
     fox postings   jump postings
      {1,5,8,12,20}  {1,3,8,15}
               \       /
                INTERSECT
              {1, 8}

Only rows 1 and 8 contain both. Phrase queries (fox <-> jump) then use positions to distinguish “rows with both words” from “rows where the words are actually adjacent” — which is precisely why positions exist.

Part 6: Ranking — Matching and Relevance Are Different Jobs

GIN answers “which rows contain these lexemes?” It does not answer “which matches are most relevant?” That’s a separate layer.

PostgreSQL provides ts_rank() and ts_rank_cd() to score candidate rows after matching:

GIN               → "Which documents match?"
   ↓
ts_rank / cd      → "Which matching documents are more relevant?"

Conceptually, ranking considers term frequency (a term appearing several times scores higher), position weights (A–D), and — for ts_rank_cd — cover density, which rewards matches that appear close together.

Part 7: PostgreSQL vs Elasticsearch

At this point PostgreSQL starts looking surprisingly similar to Elasticsearch — and that’s because, at the fundamental information-retrieval level, they’re solving the same problem through the same shape.

Elasticsearch:                    PostgreSQL:

text analysis                     parser
   ↓                                ↓
tokens                            dictionaries
   ↓                                ↓
inverted index                    tsvector
   ↓                                ↓
query                             GIN
   ↓                                ↓
relevance scoring                 tsquery
                                      ↓
                                  ts_rank

Both do full-text search. The real question isn’t “which understands search” — both do. It’s:

How much search infrastructure does your application actually need?

For a small-to-medium application, your data (users, posts, products, documents, messages, comments, knowledge base) probably already lives in PostgreSQL. Adding Elasticsearch means introducing an entire second system — and with it a whole new set of problems:

                Application
                    │
             ┌──────┴──────┐
             ▼             ▼
        PostgreSQL     Elasticsearch
             │             │
          primary        search
           data          index

How does data get from PostgreSQL → Elasticsearch?
What if indexing fails?   How do we retry?
How do we handle stale documents?
How do we synchronize deletes?
How do we monitor, deploy, back up, and scale a cluster?

With PostgreSQL full-text search, data and search index live inside the same database system:

                Application
                     │
                     ▼
                PostgreSQL
                /        \
             data       GIN
                        index

That can be dramatically simpler operationally — and you still get stemming, stopwords, phrase queries, boolean queries, positional matching, weighted fields, relevance ranking, and indexed full-text search without a search cluster.

But PostgreSQL Isn’t “Elasticsearch Inside”

This distinction matters. Elasticsearch is a dedicated search engine built around Lucene, with fuzzy matching, prefix queries, autocomplete, multi-field search, intervals, and its default BM25 relevance similarity (which factors in term frequency, document frequency, and document length).

PostgreSQL’s ranking functions are different. ts_rank ≠ BM25. They’re both relevance approaches, but they aren’t numerically equivalent — and that difference starts to matter when relevance is a core product feature.

The honest comparison:

Part 8: The Whole System in One Picture

Let’s trace one query end to end. The document: “PostgreSQL makes database searching fast. PostgreSQL provides powerful indexing.”

1. Raw text        → PostgreSQL makes database searching fast. ...
2. Parser          → each word classified as asciiword
3. Dictionaries    → postgresql, make, database, search, fast,
                     postgresql, provid, power, index
4. tsvector        → 'database':3  'fast':5  'index':9  'make':2
                     'postgresql':1,6  'power':8  'provid':7  'search':4
5. GIN             → postgresql → [Row 17, 42, 91, ...]
                     database   → [Row 17, 25, ...]
6. User searches   → "postgresql database"  ⇒  postgresql & database
7. GIN lookup      → intersect posting lists ⇒ candidate rows
8. Ranking         → ts_rank / ts_rank_cd ⇒ ordered results

The cleanest mental model separates three distinct jobs:

┌──────────────────────────────┐
│ DOCUMENT UNDERSTANDING       │
│   TEXT → Parser + Dicts      │
│          → tsvector          │
└──────────────┬───────────────┘
               │
               ▼
┌──────────────────────────────┐
│ SEARCH ACCELERATION          │
│          GIN                 │
│          → candidate rows    │
└──────────────┬───────────────┘
               │
               ▼
┌──────────────────────────────┐
│ RELEVANCE                    │
│   ts_rank / ts_rank_cd       │
│          → ranked results    │
└──────────────────────────────┘

And that’s the whole point. tsvector is not the index. It’s the normalized document representation. GIN is the inverted index built over that representation. tsquery is the structured form of the user’s search. @@ does the matching, and ts_rank / ts_rank_cd score the survivors.

DOCUMENT SIDE                      QUERY SIDE
Raw text             │             User query
   ↓                 │                ↓
Parser               │            tsquery
   ↓                 │                ↓
Dictionaries         │            GIN lookup
   ↓                 │                ↓
tsvector             │         ───────────────
   ↓                 │        Matching rows
GIN                  │                ↓
                     │           ts_rank / cd
                     │                ↓
                     │        Ranked results

Because PostgreSQL already contains the fundamental building blocks of a lexical search engine — text analysis, normalized lexemes, positional information, an inverted index, query expressions, and relevance scoring — it can deliver surprisingly capable search without a separate cluster.

For a small-to-medium application whose primary data already lives in PostgreSQL, that’s an extremely pragmatic Elasticsearch alternative. When search becomes a specialized subsystem demanding fuzzy matching, autocomplete, advanced relevance tuning, or large distributed workloads, a dedicated engine becomes the more compelling choice.

Further Reading

• PostgreSQL Full Text Search documentation
• Elasticsearch Full-Text Search
• PostgreSQL GIN index README