Module 7: Full-Text Search

PostgreSQL has powerful built-in full-text search. No need for Elasticsearch in most cases.

Basic Full-Text Search

-- Simple search
SELECT * FROM posts
WHERE to_tsvector('english', title || ' ' || content)
      @@ plainto_tsquery('english', 'database performance');

Key Concepts

Term	Description
tsvector	Searchable text format (normalized, stemmed)
tsquery	Search query format
@@	Match operator
ts_rank	Relevance scoring

Setting Up FTS

Add Search Column

-- Add tsvector column
ALTER TABLE posts ADD COLUMN search_vector tsvector;

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

-- Create GIN index (fast lookups)
CREATE INDEX posts_search_idx ON posts USING GIN(search_vector);

Keep It Updated (Trigger)

-- Auto-update on insert/update
CREATE OR REPLACE FUNCTION posts_search_update() RETURNS trigger AS $$
BEGIN
  NEW.search_vector :=
    setweight(to_tsvector('english', coalesce(NEW.title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(NEW.content, '')), 'B');
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

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

Search Weights

Weights prioritize where matches occur:

-- A = highest, D = lowest
setweight(to_tsvector('english', title), 'A')      -- Title matches rank highest
setweight(to_tsvector('english', content), 'B')    -- Content matches rank lower
setweight(to_tsvector('english', tags), 'C')       -- Tags even lower
setweight(to_tsvector('english', comments), 'D')   -- Comments lowest

Search Queries

Basic Search

// lib/search.ts
export async function searchPosts(query: string) {
  return db.$queryRaw`
    SELECT
      id, title, content,
      ts_rank(search_vector, plainto_tsquery('english', ${query})) as rank
    FROM posts
    WHERE search_vector @@ plainto_tsquery('english', ${query})
    ORDER BY rank DESC
    LIMIT 20
  `
}

Phrase Search

-- Exact phrase
SELECT * FROM posts
WHERE search_vector @@ phraseto_tsquery('english', 'full text search');

Boolean Search

-- AND: both terms required
SELECT * FROM posts
WHERE search_vector @@ to_tsquery('english', 'postgres & performance');

-- OR: either term
SELECT * FROM posts
WHERE search_vector @@ to_tsquery('english', 'postgres | mysql');

-- NOT: exclude term
SELECT * FROM posts
WHERE search_vector @@ to_tsquery('english', 'postgres & !mysql');

-- Prefix: starts with
SELECT * FROM posts
WHERE search_vector @@ to_tsquery('english', 'data:*');

Highlighting Results

SELECT
  id,
  ts_headline('english', content, plainto_tsquery('english', 'database'),
    'StartSel=<mark>, StopSel=</mark>, MaxWords=50, MinWords=25'
  ) as highlighted_content
FROM posts
WHERE search_vector @@ plainto_tsquery('english', 'database');

Output:

...optimizing your <mark>database</mark> performance is critical for...

Complete Search Implementation

// lib/search.ts
interface SearchResult {
  id: string
  title: string
  content: string
  rank: number
  headline: string
}

export async function searchPosts(
  query: string,
  page = 1,
  limit = 10
): Promise<{ results: SearchResult[]; total: number }> {
  const offset = (page - 1) * limit

  const results = await db.$queryRaw<SearchResult[]>`
    SELECT
      id,
      title,
      content,
      ts_rank(search_vector, websearch_to_tsquery('english', ${query})) as rank,
      ts_headline('english', content,
        websearch_to_tsquery('english', ${query}),
        'StartSel=<mark>, StopSel=</mark>, MaxWords=50'
      ) as headline
    FROM posts
    WHERE search_vector @@ websearch_to_tsquery('english', ${query})
    ORDER BY rank DESC
    LIMIT ${limit} OFFSET ${offset}
  `

  const countResult = await db.$queryRaw<[{ count: bigint }]>`
    SELECT COUNT(*) as count
    FROM posts
    WHERE search_vector @@ websearch_to_tsquery('english', ${query})
  `

  return {
    results,
    total: Number(countResult[0].count),
  }
}

API Route

// app/api/search/route.ts
import { searchPosts } from '@/lib/search'

export async function GET(request: Request) {
  const { searchParams } = new URL(request.url)
  const query = searchParams.get('q')
  const page = parseInt(searchParams.get('page') || '1')

  if (!query) {
    return Response.json({ error: 'Query required' }, { status: 400 })
  }

  const results = await searchPosts(query, page)
  return Response.json(results)
}

Search UI

// components/search.tsx
'use client'

import { useState, useEffect } from 'react'
import { useDebounce } from '@/hooks/use-debounce'

export function Search() {
  const [query, setQuery] = useState('')
  const [results, setResults] = useState([])
  const [loading, setLoading] = useState(false)

  const debouncedQuery = useDebounce(query, 300)

  useEffect(() => {
    if (!debouncedQuery) {
      setResults([])
      return
    }

    setLoading(true)
    fetch(`/api/search?q=${encodeURIComponent(debouncedQuery)}`)
      .then(res => res.json())
      .then(data => setResults(data.results))
      .finally(() => setLoading(false))
  }, [debouncedQuery])

  return (
    <div>
      <input
        type="search"
        value={query}
        onChange={e => setQuery(e.target.value)}
        placeholder="Search..."
        className="w-full px-4 py-2 border rounded-lg"
      />

      {loading && <p>Searching...</p>}

      <ul className="mt-4 space-y-4">
        {results.map((result: any) => (
          <li key={result.id} className="p-4 border rounded">
            <h3 className="font-bold">{result.title}</h3>
            <p
              className="text-sm text-gray-600"
              dangerouslySetInnerHTML={{ __html: result.headline }}
            />
          </li>
        ))}
      </ul>
    </div>
  )
}

Supabase Full-Text Search

// Using Supabase client
const { data, error } = await supabase
  .from('posts')
  .select('id, title, content')
  .textSearch('search_vector', query, {
    type: 'websearch',
    config: 'english',
  })
  .limit(10)

Performance Tips

1. Use GIN indexes - Much faster than sequential scans
2. Stored tsvector column - Don't compute on every query
3. Use triggers - Keep search column auto-updated
4. Limit results - Always paginate
5. Consider materialized views - For complex multi-table searches

When to Use External Search

PostgreSQL FTS is great for most cases. Consider Elasticsearch/Algolia when:

• Billions of documents - Postgres struggles at extreme scale
• Complex faceted search - Multiple filter dimensions
• Real-time indexing - Sub-second updates critical
• Typo tolerance - Built-in fuzzy matching needed
• Analytics - Search query analytics important

Summary

1. tsvector/tsquery - PostgreSQL's search primitives
2. Weights - Prioritize title matches over content
3. GIN index - Essential for performance
4. Triggers - Auto-update search column
5. ts_headline - Highlight matching text
6. websearch_to_tsquery - User-friendly query parsing

Next: Module 8: Authentication →