1---
2title: Use tsvector for Full-Text Search
3impact: MEDIUM
4impactDescription: 100x faster than LIKE, with ranking support
5tags: full-text-search, tsvector, gin, search
6---
7 
8## Use tsvector for Full-Text Search
9 
10LIKE with wildcards can't use indexes. Full-text search with tsvector is orders of magnitude faster.
11 
12**Incorrect (LIKE pattern matching):**
13 
14```sql
15-- Cannot use index, scans all rows
16select * from articles where content like '%postgresql%';
17 
18-- Case-insensitive makes it worse
19select * from articles where lower(content) like '%postgresql%';
20```
21 
22**Correct (full-text search with tsvector):**
23 
24```sql
25-- Add tsvector column and index
26alter table articles add column search_vector tsvector
27  generated always as (to_tsvector('english', coalesce(title,'') || ' ' || coalesce(content,''))) stored;
28 
29create index articles_search_idx on articles using gin (search_vector);
30 
31-- Fast full-text search
32select * from articles
33where search_vector @@ to_tsquery('english', 'postgresql & performance');
34 
35-- With ranking
36select *, ts_rank(search_vector, query) as rank
37from articles, to_tsquery('english', 'postgresql') query
38where search_vector @@ query
39order by rank desc;
40```
41 
42Search multiple terms:
43 
44```sql
45-- AND: both terms required
46to_tsquery('postgresql & performance')
47 
48-- OR: either term
49to_tsquery('postgresql | mysql')
50 
51-- Prefix matching
52to_tsquery('post:*')
53```
54 
55Reference: [Full Text Search](https://supabase.com/docs/guides/database/full-text-search)
56