Building Full-Text Search Without Elasticsearch

Elasticsearch is powerful but operationally heavy. For most applications — including NodWatch's internal search over cached TMDB data — PostgreSQL's full-text search is fast enough and already there.

tsvector and tsquery

PostgreSQL stores pre-processed lexemes in a tsvector column. A query becomes a tsquery. The @@ operator matches them.

sql

-- Ad-hoc search (no index)
SELECT title FROM movies
WHERE to_tsvector('english', title || ' ' || overview) @@ plainto_tsquery('english', 'space odyssey');

to_tsvector tokenizes text, removes stop words, and stems words (running → run). plainto_tsquery turns a plain string into a query without requiring users to know tsquery syntax.

Stored tsvector with GIN Index

For production, store the tsvector and index it:

sql

ALTER TABLE movies ADD COLUMN search_vector tsvector;
 
UPDATE movies
SET search_vector = to_tsvector(
  'english',
  coalesce(title, '') || ' ' ||
  coalesce(original_title, '') || ' ' ||
  coalesce(overview, '')
);
 
CREATE INDEX idx_movies_search ON movies USING GIN(search_vector);

Keep it updated with a trigger:

sql

CREATE FUNCTION movies_search_vector_trigger() RETURNS trigger AS $$
BEGIN
  new.search_vector :=
    to_tsvector('english', coalesce(new.title, '') || ' ' || coalesce(new.overview, ''));
  RETURN new;
END
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER movies_search_update
BEFORE INSERT OR UPDATE ON movies
FOR EACH ROW EXECUTE FUNCTION movies_search_vector_trigger();

Ranked Results

ts_rank scores matches by frequency and position. Higher weight for title matches than overview:

sql

SELECT
  id,
  title,
  ts_rank(
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(overview, '')), 'C'),
    plainto_tsquery('english', $1)
  ) AS rank
FROM movies
WHERE search_vector @@ plainto_tsquery('english', $1)
ORDER BY rank DESC
LIMIT 20;

Weights: A (1.0) > B (0.4) > C (0.2) > D (0.1).

Fuzzy Matching with pg_trgm

Full-text search doesn't handle typos. pg_trgm does:

sql

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_movies_title_trgm ON movies USING GIN(title gin_trgm_ops);
 
-- Find titles similar to "Godfater" (typo)
SELECT title, similarity(title, 'Godfater') AS sim
FROM movies
WHERE title % 'Godfater'
ORDER BY sim DESC
LIMIT 10;

% is the similarity threshold operator. Default is 0.3 — adjust with SET pg_trgm.similarity_threshold = 0.2.

Combined Approach: FTS + Trigram

Use FTS for speed, trigram as a fallback:

sql

SELECT id, title, poster_path FROM movies
WHERE search_vector @@ plainto_tsquery('english', $1)
   OR title % $1
ORDER BY
  CASE WHEN search_vector @@ plainto_tsquery('english', $1) THEN 0 ELSE 1 END,
  ts_rank(search_vector, plainto_tsquery('english', $1)) DESC
LIMIT 20;

Drizzle Integration

// lib/search.ts
import { sql } from "drizzle-orm";
import { db } from "@/lib/db";
 
export async function searchMovies(query: string, limit = 20) {
  return db.execute(sql`
    SELECT id, title, poster_path, vote_average, release_date,
      ts_rank(search_vector, plainto_tsquery('english', ${query})) AS rank
    FROM movies
    WHERE search_vector @@ plainto_tsquery('english', ${query})
       OR title % ${query}
    ORDER BY rank DESC
    LIMIT ${limit}
  `);
}

Autocomplete with Prefix Matching

For prefix search (as you type), use to_tsquery with the :* prefix operator:

sql

SELECT title FROM movies
WHERE search_vector @@ to_tsquery('english', 'spac:*')
ORDER BY vote_count DESC
LIMIT 10;

This matches space, spaceship, spacecraft — useful for instant search.

Performance Numbers

On a table of 500,000 movies with a GIN index:

Full-text search query: ~5ms
Trigram similarity: ~20ms
Combined query: ~25ms

For most apps, this is fast enough without Elasticsearch. When you need distributed search, highlighting in returned snippets, or advanced relevance tuning across hundreds of millions of documents, that's when Elasticsearch earns its operational overhead.