Search with Elasticsearch or OpenSearch

NoSQL and choosing a database Mid-level Python 3.13+ PostgreSQL 17+ Reviewed By Best Latest Apps

Predict the output

What does PostgreSQL's full-text match return?

SQL · PostgreSQL 17
SELECT to_tsvector('english', 'Running fast APIs')
       @@ to_tsquery('english', 'run & api');

Options
Hint 1 of 1

What does stemming do to 'running'?

Choose an answer, then open Show answer below to check it.

Show answer

Answer: A t

Correct. Stemming reduces running to run and APIs to api, so both terms match.

The english configuration lowercases and stems words, so running and APIs become run and api in both the document and the query. & requires both terms, and both are there.

Why not the others?
  • B f. The english configuration stems the words.
  • C An error: & is not valid. & means AND in tsquery.
  • D NULL. Both sides are non-null.
Result on PostgreSQL 17 (psql)
?column?
t

(1 row)

30-second answer

LIKE '%term%' can't use a normal index, doesn't rank results, and knows nothing about language. A search engine builds an inverted index (term → documents), analyses text (tokenising, lowercasing, stemming), ranks by relevance (BM25), and supports facets, typo tolerance and autocomplete. It's a secondary index, not the source of truth: feed it from the database through events or change data capture, and expect a short delay.

Practise saying it · 30 s. No recording, just a timer

Why a search engine?

Say your answer out loud, then check which of these points you covered:

  • Inverted index
  • Text analysis
  • Relevance ranking
  • Secondary, fed by events
  • Postgres FTS may suffice

Deep dive

PostgreSQL full-text search (tsvector, GIN) is enough for many apps; pg_trgm speeds LIKE and similarity.

Reindex into a new index and swap an alias for mapping changes.

Deep pagination is expensive; use search_after.

Keep the database as the source of truth and be able to rebuild the index.

Common mistakes

  • Writing to the database and the search index in two independent steps with no retry.
  • Using the search engine as the primary store.
  • Changing a field mapping in place.

Senior take & follow-ups

Inverted index, analysis, ranking, and syncing from the source of truth.

Follow-up questions

Think of your answer first, then open the hint.

  1. Is PostgreSQL full-text search enough?

    Show hint

    Often, for moderate data and simple ranking.

  2. How do you keep the index in sync?

    Show hint

    Outbox or CDC events into an indexer.

Related questions

Suggest a fix if something here is wrong or out of date.