Full-text search with Postgres in Supabase - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ service_behavior

intermediate

How does Postgres rank full-text search results?

Postgres uses a ranking function to order full-text search results. Which of the following best describes how the ts_rank function ranks documents?

A It ranks documents based on the number of times the search term appears, giving higher weight to terms appearing earlier in the document.

B It ranks documents randomly to distribute load evenly across results.

C It ranks documents by the length of the document, preferring shorter documents regardless of term frequency.

D It ranks documents alphabetically by the first word in the document.

💡 Hint

Think about how relevance is usually measured in search engines.

The ts_rank function ranks documents by how often and where the search terms appear, giving higher scores to documents where terms appear more frequently and earlier.

❓ Configuration

intermediate

Which SQL snippet creates a full-text search index correctly?

You want to create a full-text search index on the content column of a documents table. Which SQL command correctly creates a GIN index for this purpose?

A CREATE INDEX idx_content_fts ON documents USING HASH (content);

B CREATE INDEX idx_content_fts ON documents USING BTREE (to_tsvector('english', content));

C CREATE INDEX idx_content_fts ON documents USING GIN (to_tsvector('english', content));

D CREATE INDEX idx_content_fts ON documents (content);

💡 Hint

Consider which index type supports fast full-text search queries.

GIN indexes are optimized for full-text search with to_tsvector. BTREE and HASH do not support this efficiently.

❓ Architecture

advanced

Best architecture for real-time full-text search updates?

You have a Postgres database with a large articles table. You want to keep the full-text search index updated in real-time as new articles are inserted or updated. Which architecture is best?

A Use triggers on the <code>articles</code> table to update a materialized view with the search vector, and refresh the view periodically.

B Use triggers to update a tsvector column in the same table on insert/update, and index that column with a GIN index.

C Manually rebuild the full-text search index once a day during low traffic hours.

D Use an external search engine like Elasticsearch and sync data once a week.

💡 Hint

Think about minimizing delay and keeping search fast.

Updating a tsvector column via triggers keeps the search vector current and indexed for fast queries. Materialized views and batch rebuilds cause delays.

❓ security

advanced

Preventing SQL injection in full-text search queries

You build a search feature using Postgres full-text search. Users enter search terms that are included in SQL queries. Which practice best prevents SQL injection attacks?

A Use parameterized queries or prepared statements to pass user input safely to the <code>plainto_tsquery</code> function.

B Escape single quotes in user input manually before inserting into the query string.

C Allow only alphanumeric characters in user input by filtering out all others before query.

D Use string concatenation to build the query but limit input length to 100 characters.

💡 Hint

Think about how databases safely handle user input.

Parameterized queries ensure user input is treated as data, not code, preventing injection. Manual escaping or filtering is error-prone.

✅ Best Practice

expert

Optimizing full-text search performance on large datasets

You have a Postgres table with millions of rows and a full-text search index on a tsvector column. Queries are slow during peak hours. Which approach best improves performance without sacrificing result accuracy?

A Remove the <code>tsvector</code> column and compute it at query time to save storage space.

B Switch the index type to GiST for faster writes, even if reads become slower.

C Disable the full-text search index and perform sequential scans with <code>to_tsvector</code> on the fly.

D Use a GIN index with the <code>fastupdate</code> option enabled and vacuum analyze regularly to keep statistics updated.

💡 Hint

Consider index maintenance and query speed trade-offs.

GIN indexes with fastupdate enabled speed up writes and keep reads fast. Regular vacuuming keeps statistics accurate for query planning.

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