Warming up the neural circuits...
By the end of this chapter you will:
A search bar that returns exactly what users want — not just rows that happen to match — is the difference between a toy app and a professional product.
Imagine walking into a library with two million books. No catalog. No Dewey Decimal system. You need a book about "building APIs with Node.js." You walk shelf by shelf, reading every title. You'd give up after ten minutes.
That's what happens when you use WHERE title ILIKE '%search term%' on a table with millions of rows. Your database walks every row, one at a time — a full table scan. Now imagine the librarian installed a card catalog: you flip to "Node.js" and instantly find shelf L-42. That card catalog is what a proper search system does for your database.
But here's the nuance: not every library needs a computerized catalog system. A small collection (a few thousand books) works fine with a simple alphabetical shelf arrangement. Similarly, not every app needs Elasticsearch. PostgreSQL's built-in full-text search can handle workloads you might be tempted to throw a dedicated search engine at.
This chapter teaches you the spectrum — from PostgreSQL tsvector to Elasticsearch clusters — so you pick the right tool at the right time, and know exactly when to upgrade.
PostgreSQL ships with a surprisingly capable full-text search engine. It's not Elasticsearch, but for many applications — internal tools, admin panels, content sites with under a million documents — it's more than enough.
The core data type is tsvector, which stores pre-processed, tokenized document text. The matching type is tsquery, which represents a search query. A GIN (Generalized Inverted Index) index makes lookups fast.
-- Create a table with a tsvector column
CREATE TABLE articles (
id SERIAL PRIMARY KEY,
title TEXT NOT NULL,
body TEXT NOT NULL,
search_vector tsvector
GENERATED ALWAYS AS (
setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
setweight(to_tsvector(
The setweight function assigns importance levels — 'A' (highest) for title matches, 'B' for body matches. ts_rank then factors these weights into the relevance score. A match in the title ranks higher than the same match in the body.
websearch_to_tsquery vs plainto_tsquery vs to_tsquery: Use websearch_to_tsquery when your users type Google-style queries ("exact phrase" -exclude +require). Use plainto_tsquery when you want all words treated as AND terms. Use to_tsquery when you need full Boolean operators (&, |, !).
A GIN index on a tsvector column is an inverted index. For every unique lexeme (word stem) in your documents, the index stores a list of document IDs where that lexeme appears. When you search for "postgresql", Postgres looks up the lexeme postgresql in the index, gets the document list, and returns matching rows — no sequential scan needed.
-- See what the index actually stores
SELECT * FROM ts_debug('english', 'The quick brown foxes jumped over the lazy dogs');The output shows how PostgreSQL normalizes: foxes → fox, jumped → jump, dogs → dog. This stemming is language-specific — 'english' strips English stop words and applies English stemming rules.
GIN indexes are fast for reads but expensive for writes. Each INSERT/UPDATE must tokenize the text and update the posting lists for every lexeme. For write-heavy tables, consider using gin_pending_list_limit or batching updates during low-traffic windows.
PostgreSQL's ts_rank is a good starting point but not configurable in the way Elasticsearch's BM25 is. Here's how to squeeze more out of it:
-- Normalize document length (shorter docs get a boost)
SELECT title,
ts_rank(search_vector, query, 1) / (1 + ln(length(body))) AS normalized_rank
FROM articles,
websearch_to_tsquery('english', 'postgresql') query
WHERE search_vector @@ query
ORDER BY normalized_rank DESC;
-- Boost recent articles
SELECT
The ts_rank third argument (1 in the first query) controls behavior — bitmask 1 divides by the sum of matched lexeme weights, bitmask 2 divides by the number of unique words in the document.
PostgreSQL FTS has real limitations:
pg_trgm extension for trigram-based fuzzy search, but it's a different mechanism with different tradeoffs.Elasticsearch is built on Apache Lucene. It's not a database — it's a distributed search and analytics engine. Key concepts:
Indices and documents: An index is like a database, documents are like rows. But unlike a database, you define a mapping (schema) that tells Elasticsearch how to analyze each field.
// PUT /articles
{
"mappings": {
"properties": {
"title": {
"type": "text",
"analyzer": "english",
"fields": {
"keyword
The text type with an analyzer means Elasticsearch will tokenize, lowercase, stem, and remove stop words at index time. The .keyword sub-field stores the raw, unanalyzed string for exact matches and sorting.
The Query DSL: Elasticsearch's query language is JSON-based and incredibly expressive:
// POST /articles/_search
{
"query": {
"bool": {
"must": [
{ "multi_match": { "query": "postgresql performance", "fields": ["title^3", "
The multi_match with title^3 boosts title matches 3× over body matches. The filter context is cached automatically and doesn't affect scoring. The aggs (aggregations) return facet counts — something PostgreSQL FTS cannot do in a single query.
You never search your primary database directly with Elasticsearch. Instead, you maintain a search index that mirrors your data.
The naive approach — indexing on every write — couples your primary database to Elasticsearch. If Elasticsearch is down, your writes fail. Better: Change Data Capture (CDC).
The Outbox Pattern for search indexing:
// Outbox table + worker pattern
async function createArticle(article: Article) {
const client = await db.connect();
try {
await client.query('BEGIN');
const row = await
The outbox pattern ensures your primary write succeeds even if Elasticsearch is temporarily unavailable. Downside: eventual consistency — there's a lag between writing to PostgreSQL and the document appearing in search results.
When you change your Elasticsearch mapping, you need to reindex. Doing this without taking search offline:
// Zero-downtime reindex using aliases
async function reindexWithAlias() {
const aliasName = 'articles';
const oldIndex = `${aliasName}_v1`;
const newIndex = `${aliasName}_v2`;
// 1. Create the new index with updated mappings
Your application always reads/writes to the articles alias. During the swap, writes pause for milliseconds — acceptable for most applications.
If you're the only developer, your dataset is under 500K records, and your search needs are basic keyword matching — stick with PostgreSQL FTS. Elasticsearch adds operational complexity (cluster management, heap tuning, snapshot backups) that isn't worth it at small scale. You can always migrate later using the CDC patterns shown above.
In 2023, GitHub launched their new code search engine, rewriting it from the ground up. The old system used Elasticsearch. The new one uses a custom Rust-based search engine called Blackbird, built on a custom inverted index. Why the investment?
GitHub hosts over 200 million repositories. Searching code is fundamentally different from searching documents — you need to match exact strings, respect programming language syntax, and handle special characters. A search for fn merge( should match function definitions across millions of Rust files.
Blackbird compiles search queries into an execution plan, splits the work across shards, and uses a trigram-based index for substring matching. They serve over 5,000 search queries per second with p99 latency under 2 seconds.
The lesson: GitHub started with Elasticsearch (good enough for 10+ years), then built a custom solution only when the scale and specificity of their problem demanded it. Don't build custom search engines. Use PostgreSQL FTS → Elasticsearch → then, only if you're GitHub-scale, consider custom.
| Mistake | Why it's wrong | What to do instead |
|---|---|---|
Using ILIKE '%term%' for search | Full table scan on every query; unusable beyond 10K rows | Use PostgreSQL FTS with GIN index or Elasticsearch |
| Searching your primary DB directly from the frontend | No , no query sanitization, exposes schema | Put an layer between your frontend and search engine |
| Indexing everything with default settings | Misses language-specific stemming, stop words, synonym handling | Define analyzers per field; test with actual user queries |
| Not monitoring index size and query latency | GIN indexes bloat over time; Elasticsearch heap pressure grows silently | Set up pgstattuple monitoring for GIN; Elasticsearch _cat/indices alerts |
Using ts_rank without normalization | Long documents naturally score higher because they contain more term occurrences | Normalize by document length or use ts_rank_cd (cover density ranking) |
| Reindexing by dropping and recreating the index | Search is unavailable during the reindex window (minutes to hours) | Use the alias-swap technique described above |
| Ignoring search analytics | You can't improve what you don't measure — zero-result queries, click-through rates | Log every search query with its result count; build a dashboard |
@elastic/elasticsearch client defaults to a ConnectionPool. Override maxRetries: 3 and requestTimeout: 30000 for production.logs-2024-01, logs-2024-02). Templates prevent mapping explosions — where dynamic field mapping creates thousands of field types.gin_fuzzy_search_limit for trigram indexes: When using pg_trgm for fuzzy matching, set gin_fuzzy_search_limit to cap the number of rows scanned. Default is 0 (unlimited), which can wreck performance.filter context vs must context: filter clauses don't score documents and are cached. Put term/range conditions in filter, full-text match conditions in must.scroll for deep : The scroll API is deprecated for user-facing search. Use search_after with a sort tiebreaker (e.g., _id) for stable, performant deep pagination.track_total_hits: Setting track_total_hits: false (Elasticsearch 7+) skips the expensive exact count computation, reducing query time by 30-50% when you don't need exact total hit counts.websearch_to_tsquery handles some sanitization (removes operators), but to_tsquery and plainto_tsquery can throw errors on malformed input. Always wrap in a try-catch or pre-sanitize.{ "match": { "title": userInput } } not { "query_string": { "query": userInput } }.PostgreSQL FTS setup: Create a products table with name, description, and a search_vector tsvector column. Insert 20 products. Write a query that searches for products using websearch_to_tsquery. Add a GIN index and compare query plans with EXPLAIN ANALYZE before and after.
Ranking comparison: Using the same products table, write three queries: one using ts_rank, one using ts_rank normalized by document length, and one using ts_rank_cd. Compare the result ordering. Which ranking function produces the most intuitive results for your product data?
Build a search API endpoint: Create an Express endpoint GET /api/search?q=... that accepts a query string, searches a PostgreSQL table with tsvector, and returns JSON results with highlighted snippets. Use ts_headline to generate snippets showing where the match occurred within the document body.
Elasticsearch reindex script: Write a Node.js script that creates a new index with an updated mapping (add a popularity integer field), copies all documents from the old index, and atomically swaps the alias. The script should handle the case where the reindex fails midway and retry safely.
Hybrid search with PostgreSQL + pgvector: Extend the search system to support semantic search alongside keyword search. Add a pgvector column with OpenAI embeddings, then implement a hybrid query that combines ts_rank scores with cosine similarity scores using Reciprocal Rank Fusion (RRF). What's the optimal weight between keyword and semantic relevance for your dataset?
CDC pipeline: Implement a complete Change Data Capture pipeline using PostgreSQL LISTEN/NOTIFY. When a row in your primary table changes, the pipeline updates the corresponding Elasticsearch document within 2 seconds. Write integration tests that verify eventually consistent behavior — including the failure-and-recovery scenario when Elasticsearch is temporarily down.
Q: What's the difference between ILIKE '%term%' and PostgreSQL full-text search?
A: ILIKE performs a sequential scan checking every row character-by-character. It can't use standard B-tree indexes for substring patterns. PostgreSQL FTS uses tsvector (pre-tokenized text) and GIN indexes (inverted indexes) to look up matching documents directly, without scanning every row. At 100K rows, ILIKE might take 500ms; FTS with a GIN index takes 5ms.
Q: What does a GIN index store? A: A GIN (Generalized Inverted Index) stores a mapping from each unique key (lexeme in FTS, trigram in trigram search, array in array columns) to the list of row IDs that contain that key. When you search for "postgresql", the index directly returns all rows containing that lexeme without scanning the table.
Q: Why not put Elasticsearch directly in front of your primary database? A: Because Elasticsearch is eventually consistent with your primary data store. If a write succeeds in PostgreSQL but fails in Elasticsearch (network blip, ES OOM), your search results are stale. The correct pattern is CDC — write to PostgreSQL first, asynchronously index to Elasticsearch.
Q: You have 50 million documents in Elasticsearch. A reindex takes 4 hours. How do you deploy a mapping change with zero downtime and zero data loss?
A: Use the alias-swap technique: (1) Create a new index with the updated mapping. (2) Initiate a _reindex from the old index to the new — this runs in the background on Elasticsearch. (3) While reindex is running, use dual-write: all new writes go to both the old and new index. (4) When reindex completes + dual-writes are up to date, atomically swap the alias from old to new using _aliases API. (5) Monitor for 24 hours, then delete the old index. The dual-write step ensures no writes are lost during the reindex window.
Q: Explain BM25 vs tf-idf. Which one does PostgreSQL ts_rank implement and which does Elasticsearch use by default?
A: Both are relevance scoring algorithms. TF-IDF (Term Frequency × Inverse Document Frequency) scores a document by how often the term appears in it (TF) weighted by how rare the term is across all documents (IDF). BM25 improves on TF-IDF by capping term frequency (a term appearing 100 times isn't 10× more relevant than 10 times) and normalizing for document length. PostgreSQL ts_rank is closer to tf-idf (no saturation, basic normalization). Elasticsearch uses BM25 by default since version 5.0. BM25 generally produces better relevance rankings for natural language search.
Q: You're designing search for a multi-tenant SaaS. Each tenant has 100K-500K documents. 10,000 tenants. How do you partition Elasticsearch?
A: One shared index with a tenant_id field and filtered aliases per tenant. Every query includes { "term": { "tenant_id": "123" } } in the filter context (cached, doesn't affect scoring). This scales to millions of tenants. One index per tenant hits Elasticsearch's index limit (~1000 indices per node before performance degrades). For very large tenants (>1M docs), route them to a dedicated index using an application-level routing layer.
Search is a spectrum. At one end, PostgreSQL FTS gives you 80% of what most apps need with zero operational overhead — tsvector columns, GIN indexes, and ts_rank handle basic keyword search, ranking, and even weighted field searches. At the other end, Elasticsearch gives you faceted search, fuzzy matching, aggregations, and horizontal scaling across billions of documents. The art is knowing when to move along the spectrum. The signal: when your PostgreSQL queries start taking >200ms with proper GIN indexes, or when you need features PostgreSQL FTS fundamentally cannot provide (facets, "did you mean," distributed scaling). When you do adopt Elasticsearch, use CDC patterns to keep it eventually consistent with your primary database, and always have a reindex strategy that doesn't take search offline.
tsvector (tokenized documents), tsquery (search terms), and GIN indexes (inverted index for fast lookup).websearch_to_tsquery handles Google-style syntax; plainto_tsquery for AND queries; to_tsquery for Boolean operators.setweight('A'/'B'/'C'/'D') assigns importance levels to fields in a tsvector.ts_rank by document length to prevent long documents from dominating results.Which PostgreSQL data type stores pre-processed, tokenized document text for full-text search?
A: tsvector
What index type is required for performant PostgreSQL full-text search? A: GIN (Generalized Inverted Index)
True or false: websearch_to_tsquery('english', 'hello world') treats "hello" and "world" as OR terms by default.
A: False — it treats them as AND terms (both must match)
Which Elasticsearch context (must vs filter) is cached automatically and doesn't affect scoring?
A: filter context
What pattern ensures Elasticsearch stays eventually consistent with PostgreSQL without coupling writes? A: Change Data Capture (CDC) via the outbox pattern
You have 10M articles and need to change the Elasticsearch mapping. Which technique avoids search downtime during the migration? A: Alias swap — create new index, reindex, swap alias atomically
What's the maximum recommended JVM heap size for Elasticsearch nodes? A: 32 GB (compressed OOPs threshold), and never more than 50% of total server RAM