Text search in Postgres, without Elasticsearch or Algolia

Draft

When an app needs search, many teams add a search engine: Elasticsearch or OpenSearch (with Kibana to look at it), Algolia, Typesense, Meilisearch. That is a second datastore. Every write has to be copied to it, by a queue, a sync job or change data capture, and results are stale until the copy lands. It has its own bill, its own backups, its own access rules to keep in step with the database's, and it can't join search results with the rest of your data.

PostgreSQL can often do the job itself. First, every approach in brief, with its pros and cons, and a table to compare them. Then all of them on one table, with the same eight documents as the previous lessons. It repeats a little of each lesson on purpose; follow the links for the details.

The approaches

Keyword search: match the text the user typed. The short examples search a column holding the words dog, Dog, dogs, hotdog, doggo, doing, puppy, canine.

ILIKE

Finds a piece of text anywhere in a column.

dog → dog, Dog (case is ignored; LIKE gives only dog)
%dog% → dog, Dog, dogs, hotdog, doggo
do%g → dog, Dog, doing (% is any text, even 'in')
dog_ → dogs (_ is exactly one character)
%dgo% → nothing (no typos)

You search "index", you get SQL indexes, Query tuning.

Search "indexing": nothing. No document contains that exact text.

Pros

Built in, nothing to set up.
Exact and predictable.
With a trigram index, fast even for '%...%'.

Cons

No ranking.
No typos, no word forms: 'indexing' doesn't find 'Indexes'.
Without an index, it reads every row.

Lesson: Text search basics

Fuzzy search (pg_trgm)

Compares strings by the 3-letter chunks they share.

dogz → dog, Dog, dogs, doggo (a typo still matches)
pupy → puppy
dgo → nothing (too short: too few chunks in common)
puppy → puppy (not dog: no meaning)

You search "nueral netwrks", you get Neural networks, despite two typos.

Search "speed up my database": nothing. No title is spelled like it, and trigrams don't know what it means.

Pros

Tolerates typos.
Gives a score to sort by.
Great for short text: names, titles, autocomplete.

Cons

Knows nothing about words or meaning.
Weak on long text and on very short queries.
Trigram indexes are large.

Lesson: Fuzzy search with pg_trgm

Full-text search

Matches words and their forms: 'indexing' finds 'Indexes'.

dogs → dog, Dog, dogs (same word, any form or case)
dog:* → dog, Dog, dogs, doggo (prefix search)
hot dog → nothing (hotdog is a different word)
doing → nothing (a stop word, ignored like 'the')
canine → canine (no synonyms)

You search "indexing tables", you get SQL indexes: "indexing" and "tables" become the words index and tabl.

Search "postgress" (a typo) or "speed up": nothing, though Query tuning is all about getting faster.

Pros

Built in.
Stemming, stop words, search-box syntax (quotes, OR, -word).
Ranking and highlighting.
A GIN index scales to millions of rows.

Cons

No typos, no synonyms, no meaning.
One language configuration per tsvector.
Basic ranking: no BM25 out of the box.

Lesson: Full-text search

Vector search, in several flavours

Vector search matches meaning. Every flavour needs an embedding model to turn text into a vector, for every document and for every search: an extra dependency, with its own cost and latency. They differ in how the vectors are stored and searched, and in who computes them. With a typical embedding model, on the same words (dog, Dog, dogs, hotdog, doggo, doing, puppy, canine), every flavour matches like this:

puppy → puppy, dog, doggo, canine (all close in meaning)
canine → canine, dog, puppy (no shared letters needed)
dgo → unpredictable (a typo is a different text, so a different vector)

Arrays and SQL functions

Embeddings in a DOUBLE PRECISION[] column, distance written in SQL.

You search "speed up my database", you get SQL indexes, Vector search, JOIN patterns: found by meaning, not by words.

The same search on a million rows computes a million distances, every time.

Pros

No extension: works on any Postgres.
Every step of the math is visible.

Cons

No index: every search computes the distance for every row.
Nothing checks that vectors have the same number of dimensions.

Lesson: Vectors

pgvector, no index

A vector column and distance operators, searched by a full scan.

You search "speed up my database", you get The same three documents, always the exact nearest ones.

Still reads every row: fine for thousands, slow for millions.

Pros

Exact results: always the true nearest rows.
Simple: fine up to tens of thousands of rows.
Type checking and fast distance operators.

Cons

Cost grows with rows × dimensions: every search reads the whole table.

Lesson: pgvector: exact search

pgvector + HNSW index

A graph of neighbours that a search walks towards the question.

You search "speed up my database", you get The same three, in milliseconds even on millions of rows.

On a big table it can now and then miss one of the true nearest rows. Add a selective WHERE (say, one author's documents) and you can get fewer than 3 results.

Pros

Fast, with high recall.
Can be created on an empty table; stays good as rows arrive.
Tunable at query time with hnsw.ef_search.

Cons

Approximate: can miss a true neighbour.
Slow to build, and uses a lot of memory.
A selective WHERE filter can return fewer rows than the LIMIT.

Lesson: pgvector: HNSW

pgvector + IVFFlat index

Groups vectors into clusters and only searches the nearest ones.

You search "speed up my database", you get The same three, if the index was built after the data was loaded.

Built on an empty table, the clusters mean nothing and results get worse. With the default of searching 1 cluster, a neighbour in the next cluster is missed.

Pros

Faster to build and smaller than HNSW.

Cons

Must be created after the data is loaded: the clusters come from it.
Lower recall than HNSW at the same speed.
Needs rebuilding when the data changes a lot.

Lesson: pgvector: IVFFlat

pgvector + smaller vectors (halfvec, binary quantization)

Store each number in 16 bits, or even 1 bit, instead of 32.

You search "speed up my database", you get the same three with halfvec, in half the space.

With binary quantization, this lesson's 3-number vectors all become the bits 111: all 8 documents look identical. Real 1,536-number vectors fare much better, but re-rank the top results with the full vectors.

Pros

Half the storage with halfvec, 1/32 with bits; faster indexes.
halfvec indexes up to 4,000 dimensions; vector stops at 2,000.

Cons

Some accuracy lost.
Binary quantization usually needs a second pass that re-ranks with the full vectors.

Lesson: pgvector: smaller vectors

Embeddings computed in the database (pgai)

The database calls the embedding model itself, for every new or changed row. It's here because it comes up a lot when you search for AI in Postgres, but it is no longer maintained.

You insert "Indexing JSON columns", you get its embedding in documents_embeddings shortly after, with no app code.

If the model API is down or its key expires, the row is saved without an embedding yet, and vector search silently misses it.

Pros

Embeddings stay in sync automatically, like an index.
No app code.

Cons

Network calls to a model from inside the database: slow, failure-prone, and holding a connection while they wait.
Not available on most managed Postgres services, which don't allow the extension.
pgai has not been maintained since February 2026.

Lesson: why it is deprecated

Both at once

Hybrid: full-text + vectors

Runs both searches and merges the two rankings.

You search "find meaning", you get Vector search first: top keyword match, and 5th by meaning. Chatbots second: first by meaning, though it shares no word.

If the embedding model is down, only the keyword half can run: plan for that fallback.

Pros

Exact words and names, and meaning when the words differ.
One SQL query.

Cons

Two indexes and two searches to tune.
Still needs an embedding for every document and every search.

Comparison

Approach	Finds	Typos	Meaning	Ranking	Index	Needs	Lesson
ILIKE	Substrings	No	No	No	Trigram GIN	Built in	Text search basics
pg_trgm	Similar spellings	Yes	No	similarity()	GIN, GiST	pg_trgm (contrib)	Fuzzy search
Full-text	Words and their forms	No	No	ts_rank	GIN	Built in	Full-text search
pgvector	Similar meaning	Mostly	Yes	Distance	HNSW, IVFFlat	pgvector + an embedding model	Vectors, pgvector
Hybrid	Words and meaning	Mostly	Yes	Rank fusion	GIN + HNSW	Both of the above	Below

They are not exclusive: one table can have all of them, as the one below does. Start with the simplest one that answers what your users type, and add the next when search results show you why.

One table, every kind of search

A trigram index for ILIKE and typos, a tsvector column for words, an embedding for meaning. Postgres keeps all three up to date in the same transaction as the row itself.

CREATE EXTENSION pg_trgm;
CREATE EXTENSION vector;

CREATE TABLE documents (
  id SERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  body TEXT NOT NULL,
  -- From an embedding model: [databases, web, AI].
  embedding vector(3) NOT NULL,
  -- Kept up to date by Postgres on every INSERT and UPDATE.
  search tsvector GENERATED ALWAYS AS (
    setweight(to_tsvector('english', title), 'A') ||
    setweight(to_tsvector('english', body), 'B')
  ) STORED
);

-- One index per search style, all in the same table.
CREATE INDEX ON documents USING gin (title gin_trgm_ops);   -- ILIKE, fuzzy
CREATE INDEX ON documents USING gin (search);               -- full-text
CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops); -- semantic

Loaded Database:text-search-in-postgres.database-init

INSERT INTO documents (title, body, embedding)
VALUES
  ('SQL indexes', 'Indexes help PostgreSQL find rows without reading the whole table.', '[0.95, 0.03, 0.02]'),
  ('JOIN patterns', 'Joins combine rows from related tables into one result.', '[0.66, 0.17, 0.17]'),
  ('Query tuning', 'Slow queries get faster with the right index and a better plan.', '[0.62, 0.15, 0.23]'),
  ('React components', 'Components split a user interface into small reusable pieces.', '[0.25, 0.26, 0.49]'),
  ('CSS grid layouts', 'Grid and flexbox position elements on a web page.', '[0.15, 0.82, 0.03]'),
  ('Neural networks', 'Neural networks learn patterns from training data.', '[0.14, 0.08, 0.78]'),
  ('Chatbots', 'Language models answer questions written in natural language.', '[0.32, 0.26, 0.42]'),
  ('Vector search', 'Embeddings let a database find documents with a similar meaning.', '[0.91, 0.04, 0.04]');

1. Pattern matching: ILIKE

Characters, anywhere in the text. Simple and exact: 'index' also matches 'Indexes'. No ranking, no typos, no word forms beyond the substring.

Loaded Database:text-search-in-postgres.database-init

SELECT title
FROM documents
WHERE title ILIKE '%index%' OR body ILIKE '%index%';

title
SQL indexes
Query tuning

Seq Scan on documents  (cost=0.00..17.65 rows=2 width=32)
  Filter: ((title ~~* '%index%'::text) OR (body ~~* '%index%'::text))

Details: Text search basics.

2. Fuzzy matching: pg_trgm

Similar spellings. 'sql indxes' has a typo and still finds the document, with a score to sort by.

Loaded Database:text-search-in-postgres.database-init

SELECT title, round(similarity(title, 'sql indxes')::numeric, 3) AS score
FROM documents
WHERE title % 'sql indxes'
ORDER BY score DESC;

title	score
SQL indexes	0.643

Sort  (cost=16.47..16.48 rows=5 width=64)
  Sort Key: (round((similarity(title, 'sql indxes'::text))::numeric, 3)) DESC
  ->  Seq Scan on documents  (cost=0.00..16.41 rows=5 width=64)
        Filter: (title % 'sql indxes'::text)

Details: Fuzzy search with pg_trgm.

3. Full-text search: tsvector

Words, not characters. 'indexing tables' matches 'Indexes' and 'table' through stemming, and ts_rank orders the results.

Loaded Database:text-search-in-postgres.database-init

SELECT title, round(ts_rank(search, query)::numeric, 3) AS rank
FROM documents, websearch_to_tsquery('english', 'indexing tables') AS query
WHERE search @@ query
ORDER BY rank DESC;

title	rank
SQL indexes	0.406

Sort  (cost=16.39..16.40 rows=1 width=64)
  Sort Key: (round((ts_rank(documents.search, '''index'' & ''tabl'''::tsquery))::numeric, 3)) DESC
  ->  Seq Scan on documents  (cost=0.00..16.38 rows=1 width=64)
        Filter: (search @@ '''index'' & ''tabl'''::tsquery)

Details: Full-text search.

4. Semantic search: pgvector

Meaning, not words. 'speed up my database' shares no word with any document, but its embedding, '[0.94, 0.04, 0.03]', is close to SQL indexes and to Vector search, which the model rates as mostly about databases.

Loaded Database:text-search-in-postgres.database-init

SELECT title, round((embedding <=> '[0.94, 0.04, 0.03]')::numeric, 3) AS distance
FROM documents
ORDER BY embedding <=> '[0.94, 0.04, 0.03]'
LIMIT 3;

title	distance
Vector search	0.000
SQL indexes	0.000
JOIN patterns	0.044

Limit  (cost=8.57..8.88 rows=3 width=72)
  ->  Index Scan using documents_embedding_idx on documents  (cost=8.57..62.02 rows=510 width=72)
        Order By: (embedding <=> '[0.94,0.04,0.03]'::vector)

Details: Vectors for the math, pgvector for the type and index.

5. Hybrid: keywords + meaning

The search 'find meaning', embedded as '[0.41, 0.30, 0.28]'. Rank the rows once by full-text and once by vector distance, then merge the two lists with reciprocal rank fusion: each list adds 1 / (60 + position). Rows near the top of both lists win.

Loaded Database:text-search-in-postgres.database-init

WITH keyword AS (
  SELECT id, row_number() OVER (ORDER BY ts_rank(search, query) DESC) AS position
  FROM documents, websearch_to_tsquery('english', 'find or meaning') AS query
  WHERE search @@ query
),
semantic AS (
  SELECT id, row_number() OVER (ORDER BY embedding <=> '[0.41, 0.30, 0.28]') AS position
  FROM documents
  ORDER BY embedding <=> '[0.41, 0.30, 0.28]'
  LIMIT 5
)
SELECT
  documents.title,
  keyword.position AS keyword_position,
  semantic.position AS semantic_position,
  round(
    coalesce(1.0 / (60 + keyword.position), 0) +
    coalesce(1.0 / (60 + semantic.position), 0),
  4) AS score
FROM documents
LEFT JOIN keyword ON keyword.id = documents.id
LEFT JOIN semantic ON semantic.id = documents.id
WHERE keyword.id IS NOT NULL OR semantic.id IS NOT NULL
ORDER BY score DESC
LIMIT 5;

title	keyword_position	semantic_position	score
Vector search	1	5	0.0318
Chatbots		1	0.0164
SQL indexes	2		0.0161
Query tuning		2	0.0161
JOIN patterns		3	0.0159

Limit  (cost=63.72..63.73 rows=5 width=80)
  ->  Sort  (cost=63.72..65.00 rows=510 width=80)
        Sort Key: (round((COALESCE((1.0 / ((60 + keyword."position"))::numeric), '0'::numeric) + COALESCE((1.0 / ((60 + semantic."position"))::numeric), '0'::numeric)), 4)) DESC
        ->  Hash Left Join  (cost=26.03..55.25 rows=510 width=80)
              Hash Cond: (documents.id = semantic.id)
              Filter: ((keyword.id IS NOT NULL) OR (semantic.id IS NOT NULL))
              ->  Hash Left Join  (cost=16.66..33.72 rows=510 width=48)
                    Hash Cond: (documents.id = keyword.id)
                    ->  Seq Scan on documents  (cost=0.00..15.10 rows=510 width=36)
                    ->  Hash  (cost=16.60..16.60 rows=5 width=12)
                          ->  Subquery Scan on keyword  (cost=16.46..16.60 rows=5 width=12)
                                ->  WindowAgg  (cost=16.46..16.55 rows=5 width=16)
                                      Window: w1 AS (ORDER BY (ts_rank(documents_1.search, '''find'' | ''mean'''::tsquery)) ROWS UNBOUNDED PRECEDING)
                                      ->  Sort  (cost=16.45..16.46 rows=5 width=8)
                                            Sort Key: (ts_rank(documents_1.search, '''find'' | ''mean'''::tsquery)) DESC
                                            ->  Seq Scan on documents documents_1  (cost=0.00..16.39 rows=5 width=8)
                                                  Filter: (search @@ '''find'' | ''mean'''::tsquery)
              ->  Hash  (cost=9.31..9.31 rows=5 width=12)
                    ->  Subquery Scan on semantic  (cost=8.68..9.31 rows=5 width=12)
                          ->  Limit  (cost=8.68..9.26 rows=5 width=20)
                                ->  WindowAgg  (cost=8.68..67.12 rows=510 width=20)
                                      Window: w1 AS (ORDER BY (documents_2.embedding <=> '[0.41,0.3,0.28]'::vector) ROWS UNBOUNDED PRECEDING)
                                      ->  Index Scan using documents_embedding_idx on documents documents_2  (cost=8.57..58.20 rows=510 width=12)
                                            Order By: (embedding <=> '[0.41,0.3,0.28]'::vector)

Full-text search is precise on exact words and names; vector search catches meaning when the words differ. Hybrid search runs both and merges the rankings. Reciprocal rank fusion only uses positions, so the two scores never need to be on the same scale. The 60 is the usual constant: it keeps one list's top result from drowning out the other list.

Where embeddings come from

Every approach except vectors works on the text alone. Vectors need a model to compute the embedding of each document, and of each search. Usually the app does it: call the model, then INSERT the vector, as shown in the pgvector lesson.

Timescale's pgai took the other route, and it's in this lesson because it comes up a lot when you search for AI in Postgres. Its PostgreSQL extension called embedding models straight from SQL, and its vectorizer kept an embeddings table in sync with a source table, like an index.

It is deprecated: the project has not been maintained since February 2026, and its README doesn't give a reason. Its own history points at the problem with the approach. In 2025 the vectorizer moved out of the extension into a separate Python worker, because managed Postgres services (Amazon RDS, Supabase, ...) don't let you install the extension. And calling a model over the network from inside the database is a poor fit anyway: a SQL statement waits on an outside API, holding its connection and any locks; the API's timeouts, rate limits and outages become database errors; and the API keys have to live in the database. Computing embeddings in the app, or in a worker next to it, keeps the database doing what it is good at.

The vectorizer, for reference (not runnable here):

-- pgai: a vectorizer keeps an embeddings table in sync with a source table,
-- calling the embedding model for every new or changed row.
SELECT ai.create_vectorizer(
  'documents'::regclass,
  loading => ai.loading_column(column_name => 'body'),
  destination => ai.destination_table(target_table => 'documents_embeddings'),
  embedding => ai.embedding_openai(model => 'text-embedding-3-small', dimensions => 1536)
);

When Postgres is not enough

A dedicated search engine still earns its place when search is the product: hundreds of millions of documents with heavy query traffic that you want off the main database, faceted navigation with live counts on every filter, per-language analyzers and synonyms tuned by a search team, log analytics and dashboards (what Kibana is really for), or a hosted instant-search UI you don't want to build. Below that, which is most apps, Postgres search is one less system to run, and it is always consistent with your data.

What We Learned

One databasesearch lives next to the data: no sync pipeline, no stale index, the same transactions and permissions.
Pick by what users typeexact fragments: ILIKE. Typos: pg_trgm. Words: full-text. Meaning: pgvector.
Hybrid searchfull-text and vector rankings merged with reciprocal rank fusion, in one query.
Embeddings come from outsidea model computes them; your app (or a tool like pgai) stores them in a vector column.