PostgreSQL Full-Text Search for Monoliths Before Elasticsearch

How does PostgreSQL full-text search work?

PostgreSQL full-text search is the database's built-in way to match natural-language queries against documents. It ships with the PostgreSQL server your monolith already runs, so there is no extra service to deploy or keep in sync.

It works by normalizing both the document and the query into the same comparable form. to_tsvector parses a document into tokens and runs them through a text search configuration such as english or simple. The configuration decides which dictionaries apply. With english, a stemmer reduces "running" and "runs" to the lexeme run, and stop words like "the" and "and" are dropped because they appear almost everywhere and do not help separate one document from another. The result is a tsvector, a sorted list of lexemes and their positions. The positions are kept so that phrase matching and ranking can use them later.

Queries become a tsquery. The @@ operator returns true when a tsvector satisfies a tsquery. Both sides should use the same configuration. If they don't, the query may stem "running" differently from the document and miss rows it should match.

Copy-- Document side: stemming and stop-word removal under the 'english' configuration.
SELECT to_tsvector('english', 'a fat cat sat on a mat - it ate a fat rats');
-- 'ate':9 'cat':3 'fat':2,11 'mat':7 'rat':12 'sat':4
-- Stop words ('a', 'on', 'it') are dropped and 'rats' is stemmed to 'rat'.
-- Positions are kept for phrase matching and ranking.

-- Query side: four ways to build a tsquery from input.
SELECT to_tsquery('english', 'fat & rat');            -- 'fat' & 'rat'  (explicit operators)
SELECT plainto_tsquery('english', 'The Fat Rats');    -- 'fat' & 'rat'  (plain text, ANDed)
SELECT phraseto_tsquery('english', 'The Fat Rats');   -- 'fat' <-> 'rat' (adjacent, in order)
SELECT websearch_to_tsquery('english', '"fat rat" or cat -dog');  -- search-box syntax

-- to_tsquery rejects malformed input. websearch_to_tsquery does not.
-- SELECT to_tsquery('english', 'fat &');            -- ERROR: syntax error in tsquery
SELECT websearch_to_tsquery('english', 'fat &');      -- 'fat'

-- Matching rows in an existing table with @@.
-- Use the same configuration on both sides so the two stems agree.
CREATE TABLE articles (
  id    bigserial PRIMARY KEY,
  title text NOT NULL,
  body  text NOT NULL
);

INSERT INTO articles (title, body) VALUES
  ('Training log', 'She was running every morning before work.'),
  ('Gardening',    'The tomatoes need more sun this year.');

SELECT id, title
FROM articles
WHERE to_tsvector('english', title || ' ' || body)
      @@ websearch_to_tsquery('english', 'runs');
-- Returns 'Training log': 'running' and 'runs' both stem to 'run'.

Shows how to_tsvector turns a document into stemmed lexemes with positions, how the four tsquery builders interpret input, and how @@ matches rows in an ordinary table when both sides use the same 'english' configuration.

Why should a monolith try PostgreSQL search before adding Elasticsearch?

When search lives in the same database as the data, the search index is updated in the same transaction as the row. A tsvector column, whether generated or maintained by a trigger, changes when the row changes, and the GIN index entry commits or rolls back with it. A user who saves a document and immediately searches for it will find it. You don't need eventual-consistency caveats in the UI or "try again in a few seconds" support tickets.

CopyPostgreSQL only                    PostgreSQL + Elasticsearch

 App ──write/search──► Postgres     App ──write──► Postgres
                       (rows +       │                 │ CDC / outbox / queue
                        GIN index)   │                 ▼
                                     │            sync worker ──► Elasticsearch
                                     └────────search──────────────┘

The right-hand side adds several pieces you have to own:

• A sync path. You need change capture, an outbox, or dual writes. Each one can fail partway and leave the two stores out of step.
• Reindex jobs. Mapping changes and backfills need their own tooling and runbooks.
• A second cluster. It needs its own capacity planning, upgrades, security, backups and on-call alerts.

For a team already running PostgreSQL, the first option reuses things you already have. Migrations, backups, monitoring, connection pooling and access control all cover search without extra work. Search can be tested with ordinary integration tests against one database. A feature can ship as a migration plus a query instead of a new service.

This approach suits monoliths with moderate corpus sizes and relevance needs that are "good enough". It also suits teams that cannot staff a search cluster. Taking on Elasticsearch's cost is easier to justify once you have measured PostgreSQL falling short.

Copy A) PostgreSQL only: one store, one transaction

 +-------+   write + search    +--------------------------------+
 |  App  | ------------------> | PostgreSQL                     |
 +-------+                     |  BEGIN                         |
                               |  row -> tsvector -> GIN entry  |
                               |  COMMIT: row + index together  |
                               +--------------------------------+

 B) PostgreSQL + Elasticsearch: two stores, async sync

 +-------+      write          +------------+
 |  App  | ------------------> | PostgreSQL |
 +-------+                     +------------+
     |                               |  CDC / outbox / dual write
     |                               v
     |                         +-------------+     reindex jobs,
     |                         | sync worker |     backfills
     |                         +-------------+
     |                               |  async: lag window
     |                               v
     |        search           +---------------+
     +-----------------------> | Elasticsearch |  2nd cluster:
                               +---------------+  upgrades, backups,
                                                  capacity, on-call

With PostgreSQL alone, the row and its GIN index entry commit in one transaction. Adding Elasticsearch requires a sync path, reindex tooling and a second cluster, and search results can lag behind committed writes.

How do you implement tsvector columns and GIN indexes?

You have three ways to get a tsvector onto disk or into a query:

Default to a generated column. It cannot drift from its source columns. Use a trigger only when the vector needs data from other tables.

An expression index on to_tsvector('english', body) is only used when the query contains that exact expression. The planner matches expressions structurally, not by meaning. Call the one-argument to_tsvector(body) and the index is ignored. The one-argument form also depends on default_text_search_config, so it is not immutable and cannot be indexed at all. Always pass the configuration explicitly.

Create the index with CREATE INDEX ... USING gin (search_vector). Then run EXPLAIN ANALYZE on a realistic @@ query. Look for a Bitmap Index Scan on that index rather than a Seq Scan.

GIN is fast to read but expensive to write. One row update touches an index entry for every distinct lexeme in the document. The fastupdate option, on by default, appends changes to a pending list and merges them in batches. The trade-off is cheaper writes in exchange for slower searches, which must scan the pending list, and occasional latency spikes when it flushes. Vacuum regularly and watch for index bloat.

GiST is a reasonable alternative on write-heavy tables where update cost or index size matters more than read speed. Its results are lossy, so PostgreSQL rechecks the matching rows against the table.

Copy-- Stored generated column: recomputed on every write, cannot drift from its sources.
-- The two-argument to_tsvector is immutable, so it is allowed here.
ALTER TABLE articles
  ADD COLUMN search_vector tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(body,  '')), 'B')
  ) STORED;

-- GIN index on the column. fastupdate is on by default; it is set here to make it visible.
CREATE INDEX articles_search_gin ON articles
  USING gin (search_vector) WITH (fastupdate = on);

-- Confirm the planner uses it. Look for a Bitmap Index Scan on articles_search_gin.
EXPLAIN ANALYZE
SELECT id, title
FROM articles
WHERE search_vector @@ websearch_to_tsquery('english', 'connection pooling');

-- Expression-index alternative: no extra column, but queries must repeat this exact expression.
CREATE INDEX articles_body_expr_gin ON articles
  USING gin (to_tsvector('english', body));
-- Matches the index:      WHERE to_tsvector('english', body) @@ to_tsquery('english', 'vacuum')
-- Does not use the index: WHERE to_tsvector(body) @@ to_tsquery('vacuum')

-- Cap the pending list size for this index (value in kB) to bound search cost and flush spikes.
ALTER INDEX articles_search_gin SET (gin_pending_list_limit = 1024);

-- Merge the pending list into the main index now, e.g. after a bulk load.
SELECT gin_clean_pending_list('articles_search_gin'::regclass);

-- Write-heavy table where update cost or index size matters more than read speed:
-- GiST is smaller and cheaper to update, but lossy, so matches are rechecked against the heap.
-- CREATE INDEX articles_search_gist ON articles USING gist (search_vector);

Adds a stored generated tsvector column, indexes it with GIN, checks index use with EXPLAIN ANALYZE, shows the exact-match rule for expression indexes, tunes the GIN pending list, and notes the GiST alternative for write-heavy tables.

How do you rank results across title, body and tags?

Ranking starts at index-build time. Wrap each source field in setweight before concatenating: title as A, tags as B, body as C, and leave D for low-value text such as footers or metadata. The weight is stored on every lexeme position in the tsvector, so a match in the title stays distinguishable from the same word in the body.

At query time, ts_rank scores rows by how often query terms appear. ts_rank_cd (cover density) also rewards terms that appear close together, which usually suits multi-word queries better. Both functions accept an optional weight array, ordered {D, C, B, A}, whose default is {0.1, 0.2, 0.4, 1.0}. You can tune these weights per query without rebuilding anything. ts_rank_cd needs position data, so a tsvector passed through strip scores zero.

The normalization argument is a bitmask you combine with |:

• 1 divides by 1 + the log of document length, and 2 divides by document length. These stop long bodies from winning on raw term count.
• 4 divides by the mean harmonic distance between extents. It applies only to ts_rank_cd.
• 8 and 16 divide by the number of unique words, or by 1 + its log.
• 32 maps the score into the 0–1 range with rank / (rank + 1).

There are two limits. First, the GIN index only finds matching rows. The rank is computed separately for each match by reading its tsvector. A broad query that matches 200,000 rows has to score all 200,000 before LIMIT 20 can apply, so it can be slow even with a GIN index. Second, there is no built-in BM25-style scoring. PostgreSQL does not weight rare terms above common ones using corpus-wide statistics. Each row is scored using only its own contents.

Copy-- Weights are baked into the tsvector when the row is written.
-- tags is plain text here: array_to_string() is not IMMUTABLE,
-- so it cannot be used in a generated column.
CREATE TABLE articles (
  id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title  text NOT NULL,
  tags   text,
  body   text,
  search tsvector GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(tags,  '')), 'B') ||
    setweight(to_tsvector('english', coalesce(body,  '')), 'C')
  ) STORED
);

-- The GIN index only finds matching rows. It does not store ranks.
CREATE INDEX articles_search_idx ON articles USING gin (search);

-- Weight array order is {D, C, B, A}. This query boosts tags
-- above the default 0.4 without rebuilding the table.
-- Normalization 1|4: divide by 1 + log(doc length), and by the
-- mean harmonic distance between extents (ts_rank_cd only).
SELECT id,
       title,
       ts_rank_cd('{0.1, 0.2, 0.6, 1.0}', search, q, 1 | 4) AS rank
FROM articles,
     websearch_to_tsquery('english', 'connection pooling') AS q
WHERE search @@ q        -- GIN index narrows to matching rows
ORDER BY rank DESC       -- rank is computed for every match...
LIMIT 20;                -- ...before LIMIT can discard any

-- No IDF/BM25: a rare term and a common term with the same
-- frequency in a row contribute the same score.

A generated tsvector column weights title A, tags B and body C, and the query ranks GIN-filtered matches with ts_rank_cd using a custom {D, C, B, A} weight array and the 1|4 normalization bitmask.

How do you handle user queries, autocomplete, typos and multiple languages?

Free-form input should go through websearch_to_tsquery. It accepts quoted phrases, or, and a leading - for negation. It does not raise syntax errors on stray punctuation the way to_tsquery does, so you can pass raw user input without writing an escaping layer. For search-as-you-type, websearch_to_tsquery has no prefix syntax. Build the query yourself instead: normalize the final token and append :* in a to_tsquery expression. Highlighted snippets come from ts_headline. It re-parses the original text, not the stored tsvector, so its cost grows with document size. Call it only on the page of rows left after LIMIT, never across the full match set.

Typos need pg_trgm. A trigram GIN index (gin_trgm_ops) supports the % similarity operator. Run fuzzy matching against a small vocabulary table of known terms rather than against full bodies. When the full-text query returns nothing or very little, find the closest vocabulary terms and offer them as "did you mean" suggestions.

The text search configuration controls stemming, so choose one per language. You can store a regconfig column per row and pass it to to_tsvector. To make accents irrelevant, copy a configuration and map its word tokens through unaccent before the stemmer. Content that mixes languages inside one document will be stemmed incorrectly in at least one of them. For that content, simple is often the safer fallback.

Changing a configuration means recomputing every row's tsvector and rebuilding its indexes. On a large table, do this as a planned migration:

• Add a new column.
• Backfill it in batches.
• Build the new index with CREATE INDEX CONCURRENTLY.
• Switch queries over to the new column.

Each extension and dictionary also adds ongoing work. It must exist in every environment, survive upgrades, and stay in sync across replicas.

When should you avoid PostgreSQL full-text search and move to Elasticsearch?

Move when one of these signals shows up and stays:

• Faceted counts over large result sets. PostgreSQL counts facets by aggregating every matching row. Elasticsearch computes aggregations inside the index.
• Relevance tuning beyond ts_rank weights. This includes per-field boosting, decay functions and rescoring.
• Synonyms at scale, or learning-to-rank. Thesaurus dictionaries live on each server and need a reindex when they change.
• Search competing with OLTP. Ranking and aggregation use CPU, memory and I/O that checkout or write paths need.

Illustrative estimate (assumed inputs, not measured): assume 20 million documents, 30 faceted searches per second at peak, and 100,000 matches per broad query. That is about 3 million rows aggregated per second, before ranking. Compare this with the per-core throughput you observe in EXPLAIN ANALYZE on your own data.

A read replica isolates that load, but it has costs. Replication lag means a user may not find a document they just saved. The application also needs routing logic to decide which queries can tolerate stale reads.

What design keeps a later Elasticsearch migration cheap?

The cost of moving to Elasticsearch is driven less by Elasticsearch itself than by how many places in your code know that search is PostgreSQL. If controllers, background jobs and admin screens each build their own to_tsquery calls, ts_rank expressions and ILIKE fallbacks, a migration means finding and rewriting every one of them. Each has slightly different filtering, ranking and pagination behavior that someone will need to reproduce and test.

Put search behind one module that the rest of the monolith calls. A minimal interface needs only a few operations:

• search(query, filters, page) returns ranked document IDs, highlights and a total count.
• suggest(prefix) returns autocomplete candidates.
• index(document) and remove(id) are called by the application when records change.

Callers pass domain concepts such as "status is published" or "tag is billing". They never pass SQL fragments, and the module never leaks tsvector or GIN details. Return IDs and hydrate full records from PostgreSQL afterward. That keeps the primary database as the source of truth whichever engine does the matching.

This boundary pays off before any migration. You get one place to tune ranking, one place to add query logging and one test suite that captures expected relevance. When PostgreSQL stops being enough, you write a second implementation of the same interface and run both side by side to compare results. Then you switch traffic. That work is scoped to a single module and can be estimated up front. Untangling search logic spread across a codebase that has grown for years is far harder to plan.

How OneEnv can help

Disclosure: OneEnv is the product behind this blog.

OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.

Applied to PostgreSQL full-text search, this is where it helps:

Saves time

• Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
• One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
• Changing a shared variable is one edit and one review instead of editing every service's .env by hand.

Saves cost

• Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
• Less duplicated configuration to maintain: one shared definition inherited by many services.
• A wrong or stale value is caught at review time by the services it affects, not by a failed deploy.
• OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.

These are qualitative benefits, not measured results — how much you save depends on how many services share the value.

Want to try it? Start at oneenv.io.