Web DevelopmentTutorials

Implementing Full-Text Search Without Elasticsearch

You don't always need a dedicated search engine. Implement performant full-text search with PostgreSQL's built-in capabilities.

Mbeah Essilfie

April 26, 2026 at 06:04 AM

9 min read 453 views

Implementing Full-Text Search Without Elasticsearch

When PostgreSQL Search Is Enough

Elasticsearch is powerful but adds operational complexity. For most applications under 10M documents, PostgreSQL's built-in full-text search is more than sufficient.

Setting Up tsvector

-- Add a search vector column
ALTER TABLE posts ADD COLUMN search_vector tsvector;

-- Populate it with weighted content
UPDATE posts SET search_vector =
  setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
  setweight(to_tsvector('english', coalesce(excerpt, '')), 'B') ||
  setweight(to_tsvector('english', coalesce(content, '')), 'C');

-- Create a GIN index for fast lookups
CREATE INDEX idx_posts_search ON posts USING GIN (search_vector);

Weights (A > B > C > D) control relevance ranking — title matches score higher than body matches.

Auto-Update with Triggers

CREATE OR REPLACE FUNCTION update_post_search_vector()
RETURNS trigger AS $$
BEGIN
  NEW.search_vector :=
    setweight(to_tsvector('english', coalesce(NEW.title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(NEW.excerpt, '')), 'B') ||
    setweight(to_tsvector('english', coalesce(NEW.content, '')), 'C');
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER posts_search_vector_update
  BEFORE INSERT OR UPDATE ON posts
  FOR EACH ROW
  EXECUTE FUNCTION update_post_search_vector();

Querying

-- Basic search
SELECT title, ts_rank(search_vector, query) AS rank
FROM posts, to_tsquery('english', 'typescript & vue') AS query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 20;

-- With prefix matching (for autocomplete)
SELECT title
FROM posts
WHERE search_vector @@ to_tsquery('english', 'type:*')
LIMIT 5;

Integration with Prisma

// server/api/search.get.ts
export default defineEventHandler(async (event) => {
  const { q, page = 1, limit = 20 } = getQuery(event)
  if (!q || typeof q !== 'string') return { data: [] }

  // Convert user query to tsquery format
  const searchTerms = q.trim().split(/\s+/).join(' & ')

  const posts = await prisma.$queryRaw`
    SELECT id, title, slug, excerpt,
           ts_rank(search_vector, to_tsquery('english', ${searchTerms})) as rank,
           ts_headline('english', content, to_tsquery('english', ${searchTerms}),
             'StartSel=<mark>, StopSel=</mark>, MaxWords=50') as highlight
    FROM posts
    WHERE search_vector @@ to_tsquery('english', ${searchTerms})
      AND status = 'published'
    ORDER BY rank DESC
    LIMIT ${limit}
    OFFSET ${(page - 1) * limit}
  `

  return { data: posts }
})

ts_headline generates highlighted snippets showing where the match occurred — perfect for search result previews.

Fuzzy Matching with pg_trgm

For typo tolerance, add trigram similarity:

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_posts_title_trgm ON posts USING GIN (title gin_trgm_ops);

-- Find posts with similar titles (typo-tolerant)
SELECT title, similarity(title, 'javscript tutoral') AS sim
FROM posts
WHERE similarity(title, 'javscript tutoral') > 0.3
ORDER BY sim DESC;