Full-text search with Postgres in Supabase - Step-by-Step Execution

Process Flow - Full-text search with Postgres

User inputs search query

↓

Convert query to tsquery format

↓

Postgres searches tsvector column

↓

Match rows returned

↓

Results sent back to user

The user enters a search phrase, which is converted into a format Postgres understands. Postgres then searches the indexed text data and returns matching rows.

Execution Sample

Supabase

SELECT * FROM articles
WHERE to_tsvector('english', content) @@ to_tsquery('english', 'cloud & infrastructure');

This query searches the 'content' column of 'articles' for rows matching the words 'cloud' AND 'infrastructure'.

Process Table

Step	Action	Input/Condition	Result
1	Receive search query	'cloud & infrastructure'	Query string ready
2	Convert to tsquery	to_tsquery('english', 'cloud & infrastructure')	tsquery: 'cloud' & 'infrastructure'
3	Convert content to tsvector	to_tsvector('english', content)	tsvector of content created
4	Match tsquery against tsvector	tsvector @@ tsquery	True if content matches both words
5	Return matching rows	Rows where match is True	Rows with both words in content
6	End	No more rows	Query complete

💡 All rows checked; only those matching both 'cloud' and 'infrastructure' returned

Status Tracker

Variable	Start	After Step 2	After Step 3	After Step 4	Final
search_query	null	'cloud & infrastructure'	'cloud & infrastructure'	'cloud & infrastructure'	'cloud & infrastructure'
tsquery	null	'cloud' & 'infrastructure'	'cloud' & 'infrastructure'	'cloud' & 'infrastructure'	'cloud' & 'infrastructure'
tsvector	null	null	tsvector of content	tsvector of content	tsvector of content
match_result	null	null	null	True or False per row	Only True rows returned

Key Moments - 3 Insights

Why do we convert the search query to tsquery format?

Because Postgres uses tsquery to understand the search terms logically (AND, OR). This is shown in step 2 of the execution_table.

What does the @@ operator do in the query?

It checks if the tsvector (document text) matches the tsquery (search terms). This matching step is step 4 in the execution_table.

Why do we convert content to tsvector before searching?

Because tsvector stores text in a searchable format optimized for full-text search, as shown in step 3.

Visual Quiz - 3 Questions

Look at the execution_table, what is the tsquery value after step 2?

A 'cloud infrastructure'

B 'cloud' | 'infrastructure'

C 'cloud' & 'infrastructure'

D 'cloud' && 'infrastructure'

💡 Hint

Check the 'Result' column in row with Step 2 in execution_table

At which step does Postgres check if the content matches the search terms?

A Step 4

B Step 3

C Step 2

D Step 5

💡 Hint

Look for the step where '@@' operator is applied in execution_table

If the search query was changed to 'cloud | infrastructure', how would tsquery change after step 2?

A 'cloud' & 'infrastructure'

B 'cloud' | 'infrastructure'

C 'cloud infrastructure'

D 'cloud' && 'infrastructure'

💡 Hint

The operator in tsquery reflects AND (&) or OR (|) logic, see step 2 in execution_table

Concept Snapshot

Full-text search in Postgres uses tsvector to store searchable text and tsquery to represent search terms.
Use @@ operator to match tsquery against tsvector.
Convert both content and query to these formats for efficient search.
Supports logical operators like & (AND) and | (OR).
Example: SELECT * FROM table WHERE to_tsvector(col) @@ to_tsquery('term1 & term2');

Full Transcript

Full-text search with Postgres works by converting the user's search phrase into a tsquery format that Postgres understands. The text content in the database is converted into a tsvector format, which is optimized for searching. Postgres then uses the @@ operator to check if the tsvector matches the tsquery. Rows that match are returned as search results. This process involves converting both the query and the content into special formats and then performing a logical match. The key steps are receiving the query, converting it, converting the content, matching, and returning results.