The PostgreSQL tsvector Pattern for Blazing-Fast Skill Searches

If you're indexing thousands of records and reaching for queries, you're leaving performance on the table. Here's how to wire up PostgreSQL's full-text search with GIN indexes to cut query times from seconds to single-digit milliseconds.

This pattern powers the Mr. Technology skills scanner — 71k+ skills indexed and searchable in real-time.

The Problem with LIKE

and do sequential scans. On 70k rows, that's a full table scan every query. Fine for a prototype. Catastrophic at scale.

Enter tsvector

PostgreSQL has built-in full-text search. It works in two steps:

1. Add a generated column

lets you rank matches — name matches rank higher than tag matches. tokenizes and stems the text.

2. Index it with GIN

avoids locking the table during index build. On 71k rows this takes a few seconds. On 7M rows, still under a minute.

3. Query with tsquery

is the match operator. orders results by relevance.

Autocomplete (Prefix Matching)

Standard doesn't do prefix matching out of the box. For autocomplete, use with trailing wildcards, or a separate trigram index:

Combine both for full-text + autocomplete.

The Production Checklist

• **Weights matter** — tune values based on what actually converts in your UI
• **Update triggers** — if or changes, recomputes automatically (it's STORED)
• **Partial indexes** — if 80% of queries target active skills, on the index cuts it down significantly
• **EXPLAIN ANALYZE** — always verify the index is being used, not a seq scan

The Result

That's not a micro-optimization. That's the difference between a search box that feels responsive and one that feels broken.

If you're running any kind of catalog, directory, or skills database and you're not using this pattern — you should be.