DEV Community

Atlas Whoff
Atlas Whoff

Posted on

PostgreSQL Full-Text Search: Fast, Relevant Search Without Elasticsearch

#javascript #typescript #webdev #postgres

PostgreSQL Full-Text Search: Fast, Relevant Search Without Elasticsearch

Elasticsearch is powerful but heavy. For most apps, PostgreSQL's built-in full-text search
handles millions of documents with sub-100ms queries.

Setup 

-- Add a tsvector column for efficient search
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') ||
  setweight(to_tsvector('english', coalesce(tags, '')), 'C');

-- Create GIN index for fast search
CREATE INDEX posts_search_idx ON posts USING gin(search_vector);

-- Auto-update on insert/update
CREATE OR REPLACE FUNCTION update_search_vector()
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_update
  BEFORE INSERT OR UPDATE ON posts
  FOR EACH ROW EXECUTE FUNCTION update_search_vector();
Enter fullscreen mode Exit fullscreen mode

Basic Search Query 

-- Search for posts matching 'react hooks'
SELECT id, title, ts_rank(search_vector, query) AS rank
FROM posts,
  plainto_tsquery('english', 'react hooks') query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 20;
Enter fullscreen mode Exit fullscreen mode

In TypeScript with Prisma 

async function searchPosts(query: string, limit = 20) {
  const results = await prisma.$queryRaw<SearchResult[]>`
    SELECT
      id,
      title,
      excerpt,
      ts_rank(search_vector, plainto_tsquery('english', ${query})) AS rank,
      ts_headline(
        'english',
        content,
        plainto_tsquery('english', ${query}),
        'MaxWords=50, MinWords=20, StartSel=<mark>, StopSel=</mark>'
      ) AS highlight
    FROM posts
    WHERE search_vector @@ plainto_tsquery('english', ${query})
      AND published = true
    ORDER BY rank DESC
    LIMIT ${limit}
  `

  return results
}
Enter fullscreen mode Exit fullscreen mode

ts_headline extracts and highlights the matching snippet — crucial for search UX.

Phrase Search and Prefix Matching 

-- Exact phrase
WHERE search_vector @@ phraseto_tsquery('english', 'react hooks')

-- Prefix matching (for autocomplete)
WHERE search_vector @@ to_tsquery('english', 'reac:*')

-- Boolean operators
WHERE search_vector @@ to_tsquery('english', 'react & hooks & !class')
-- posts with 'react' AND 'hooks' but NOT 'class'
Enter fullscreen mode Exit fullscreen mode

Search API Route 

// app/api/search/route.ts
export async function GET(request: Request) {
  const { searchParams } = new URL(request.url)
  const q = searchParams.get('q')?.trim()

  if (!q || q.length < 2) {
    return Response.json({ results: [] })
  }

  const results = await searchPosts(q)

  return Response.json({ results, query: q })
}
Enter fullscreen mode Exit fullscreen mode

Search UI Component 

function SearchBox() {
  const [query, setQuery] = useState('')
  const [results, setResults] = useState([])

  const debouncedSearch = useMemo(
    () => debounce(async (q: string) => {
      if (q.length < 2) return setResults([])
      const res = await fetch(`/api/search?q=${encodeURIComponent(q)}`)
      const data = await res.json()
      setResults(data.results)
    }, 200),
    []
  )

  return (
    <div className="relative">
      <input
        value={query}
        onChange={(e) => {
          setQuery(e.target.value)
          debouncedSearch(e.target.value)
        }}
        placeholder="Search..."
        className="w-full border rounded px-4 py-2"
      />
      {results.length > 0 && (
        <div className="absolute top-full left-0 right-0 bg-white border rounded-b shadow-lg">
          {results.map(r => (
            <a key={r.id} href={`/posts/${r.id}`} className="block p-3 hover:bg-gray-50">
              <p className="font-medium">{r.title}</p>
              <p
                className="text-sm text-gray-500"
                dangerouslySetInnerHTML={{ __html: r.highlight }}
              />
            </a>
          ))}
        </div>
      )}
    </div>
  )
}
Enter fullscreen mode Exit fullscreen mode

Performance Benchmarks

With a GIN index on tsvector:

  • 1M rows: ~5-20ms
  • 10M rows: ~20-80ms
  • 100M rows: consider Elasticsearch or Typesense

The AI SaaS Starter Kit ships with Prisma + PostgreSQL pre-configured, ready to add full-text search. $99 one-time.

Top comments (0)