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.