Full-text search with Postgres in Supabase - Mini Project: Build & Apply

Full-text search with Postgres

📖 Scenario: You are building a simple blog platform using Supabase, which uses Postgres as its database. You want to add a feature that allows users to search blog posts by keywords in the title and content.

🎯 Goal: Create a Postgres table for blog posts and implement a full-text search configuration that allows searching posts by keywords in the title and content.

📋 What You'll Learn

Create a table called posts with columns id, title, and content.

Add a column search_vector of type tsvector to store searchable text.

Create a trigger to update search_vector automatically when title or content changes.

Write a query that uses full-text search to find posts matching a search phrase.

💡 Why This Matters

🌍 Real World

Full-text search is used in many applications like blogs, e-commerce, and document management to let users find relevant content quickly.

💼 Career

Knowing how to implement full-text search with Postgres is valuable for backend developers and cloud engineers working with databases and search features.

1

Create the posts table

Create a table called posts with columns id as serial primary key, title as text, and content as text.

Supabase

-- Your code here

Hint

Use SERIAL PRIMARY KEY for id to auto-increment.

2

Add a search_vector column

Alter the posts table to add a column called search_vector of type tsvector.

Supabase

CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  title TEXT,
  content TEXT
);
-- Your code here

Hint

The tsvector type stores searchable text for full-text search.

3

Create a trigger to update search_vector

Create a trigger function called posts_search_vector_update that updates search_vector by combining title and content using to_tsvector('english', ...). Then create a trigger on posts that calls this function before insert or update.

Supabase

CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  title TEXT,
  content TEXT
);

ALTER TABLE posts ADD COLUMN search_vector tsvector;
-- Your code here

Hint

The trigger updates search_vector automatically when a post is added or changed.

4

Write a full-text search query

Write a SELECT query to find all columns from posts where search_vector matches the search phrase 'cloud infrastructure' using to_tsquery('english', 'cloud & infrastructure'). Order results by rank using ts_rank.

Supabase

CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  title TEXT,
  content TEXT
);

ALTER TABLE posts ADD COLUMN search_vector tsvector;

CREATE FUNCTION posts_search_vector_update() RETURNS trigger AS $$
BEGIN
  NEW.search_vector := to_tsvector('english', coalesce(NEW.title, '') || ' ' || coalesce(NEW.content, ''));
  RETURN NEW;
END
$$ LANGUAGE plpgsql;

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

-- Your code here

Hint

Use @@ operator to match search_vector with to_tsquery. Use ts_rank to order results by relevance.

Practice

1. What is the main purpose of using to_tsvector in Postgres full-text search?

easy

A. To convert text into a searchable document format

B. To insert new rows into a table

C. To delete unwanted text from a column

D. To create a backup of the database

Solution

1. Step 1: Understand to_tsvector function

   to_tsvector converts plain text into a special searchable format called a tsvector, which breaks text into words and normalizes them for searching.

2. Step 2: Identify its role in full-text search

   This conversion allows Postgres to quickly find matching words during a search query.

3. Final Answer:

   To convert text into a searchable document format -> Option A

4. Quick Check:

   to_tsvector = searchable format [OK]

Hint: Remember: to_tsvector prepares text for searching [OK]

Common Mistakes:

• Confusing to_tsvector with data insertion
• Thinking it deletes text
• Assuming it backs up data

2. Which of the following is the correct syntax to perform a full-text search using to_tsquery in Postgres?

easy

A. SELECT * FROM articles WHERE to_tsquery('search & term') @@ content;

B. SELECT * FROM articles WHERE to_tsquery(content) @@ 'search & term';

C. SELECT * FROM articles WHERE to_tsquery('search & term') = content;

D. SELECT * FROM articles WHERE content @@ to_tsquery('search & term');

Solution

1. Step 1: Understand to_tsquery usage

   to_tsquery creates a search query from a string like 'search & term'. It should be on the right side of the @@ operator.

2. Step 2: Check correct operator usage

   The @@ operator checks if the tsvector (usually a column) matches the tsquery. So the column (tsvector) is on the left, and to_tsquery is on the right.

3. Final Answer:

   SELECT * FROM articles WHERE content @@ to_tsquery('search & term'); -> Option D

4. Quick Check:

   tsvector @@ tsquery = correct syntax [OK]

Hint: Remember: tsvector column @@ to_tsquery('query') [OK]

Common Mistakes:

• Putting to_tsquery on the left side
• Using = instead of @@ operator
• Passing column name inside to_tsquery

3. Given the table products with a column description, what will this query return?

SELECT id FROM products WHERE to_tsvector(description) @@ to_tsquery('red & apple');

medium

A. All products with descriptions containing both 'red' and 'apple'

B. All products with descriptions containing either 'red' or 'apple'

C. All products with descriptions containing the exact phrase 'red apple'

D. No products because the syntax is incorrect

Solution

1. Step 1: Analyze the query logic

   The query uses to_tsvector(description) to convert descriptions into searchable format, then checks if it matches to_tsquery('red & apple').

2. Step 2: Understand the & operator in tsquery

   The & means AND, so it matches rows containing both 'red' and 'apple' anywhere in the description, not necessarily as a phrase.

3. Final Answer:

   All products with descriptions containing both 'red' and 'apple' -> Option A

4. Quick Check:

   'red & apple' means both words present [OK]

Hint: Use & for AND, | for OR in to_tsquery [OK]

Common Mistakes:

• Thinking & means OR
• Assuming exact phrase match
• Confusing syntax causing no results

4. You wrote this query but it returns no results:

SELECT * FROM feedback WHERE to_tsvector(comment) @@ to_tsquery('happy | sad');

What is the most likely reason?

medium

A. The to_tsquery syntax is invalid

B. The comment column is not indexed with a tsvector column

C. The @@ operator is used incorrectly

D. The query should use LIKE instead of full-text search

Solution

1. Step 1: Check query correctness

   The syntax and operator usage are correct for full-text search with to_tsvector and to_tsquery.

2. Step 2: Identify performance and result issues

   If no results appear, often the comment column is not indexed as a tsvector, so the search is slow or ineffective.

3. Final Answer:

   The comment column is not indexed with a tsvector column -> Option B

4. Quick Check:

   Missing tsvector index causes no results [OK]

Hint: Always create a tsvector index for fast full-text search [OK]

Common Mistakes:

• Assuming syntax error when query is correct
• Using LIKE instead of full-text search
• Misusing @@ operator

5. You want to build a search feature on a blog using Supabase and Postgres full-text search. Which approach best improves search speed and accuracy for large text columns?

hard

A. Store search keywords in a separate table and join on it

B. Use LIKE '%keyword%' queries on the text column without indexes

C. Create a generated tsvector column indexed with GIN and query using @@ operator

D. Use regular expressions to scan text columns at query time

Solution

1. Step 1: Understand full-text search optimization

   Creating a generated column that stores the tsvector form of the text allows fast searching without recalculating on each query.

2. Step 2: Use GIN index for fast lookup

   Adding a GIN index on the tsvector column speeds up searches dramatically, making queries efficient and scalable.

3. Step 3: Query with @@ operator for accuracy

   Using the @@ operator with to_tsquery on the indexed tsvector column ensures accurate full-text matching.

4. Final Answer:

   Create a generated tsvector column indexed with GIN and query using @@ operator -> Option C

5. Quick Check:

   Generated tsvector + GIN index = fast, accurate search [OK]

Hint: Index tsvector columns with GIN for best full-text search [OK]

Common Mistakes:

• Using LIKE instead of full-text search
• Not indexing tsvector columns
• Relying on regex for large text search