Full-text search with Postgres in Supabase - Deep Dive

Overview - Full-text search with Postgres

What is it?

Full-text search in Postgres is a way to quickly find words or phrases inside large amounts of text stored in a database. It breaks down text into searchable parts and matches queries against them. This helps users find relevant information fast without scanning every word manually.

Why it matters

Without full-text search, searching text in databases would be slow and inefficient, especially with large data. It would be like looking for a needle in a haystack by checking every straw. Full-text search makes searching fast and accurate, improving user experience and saving computing resources.

Where it fits

Before learning full-text search, you should understand basic SQL queries and how databases store data. After mastering it, you can explore advanced search features like ranking results, phrase search, and integrating search with web applications using Supabase.

Mental Model

Core Idea

Full-text search breaks text into meaningful parts and matches queries against these parts to find relevant results quickly.

Think of it like...

Imagine a library where every book has an index listing important words and their page numbers. Instead of reading every page, you look up the word in the index to find where it appears. Full-text search is like that index for your database text.

┌─────────────────────────────┐
│       User Query Input       │
└─────────────┬───────────────┘
              │
              ▼
┌─────────────────────────────┐
│   Text is broken into tokens │
│   (words, stems, lexemes)    │
└─────────────┬───────────────┘
              │
              ▼
┌─────────────────────────────┐
│   Search Index (tsvector)    │
│   stores tokens with weights │
└─────────────┬───────────────┘
              │
              ▼
┌─────────────────────────────┐
│   Query matches tokens in    │
│   index to find relevant rows│
└─────────────────────────────┘

Build-Up - 7 Steps

1

Foundation

Understanding Text Storage in Postgres

Concept: Learn how Postgres stores text data and why normal text search is slow.

Postgres stores text in columns like VARCHAR or TEXT. When you search with simple SQL LIKE queries, it scans every row and checks if the text matches. This is slow for large tables because it looks at every character.

Result

Searching large text columns with LIKE is slow and inefficient.

Understanding that normal text search scans all data explains why a faster method like full-text search is needed.

2

Foundation

Basics of Full-text Search Components

Concept: Introduce the key Postgres types: tsvector and tsquery.

Postgres uses tsvector to store processed text as tokens (words, stems). tsquery represents the search query in a similar tokenized form. Matching tsquery against tsvector finds relevant rows quickly.

Result

You know the two main data types that power full-text search.

Knowing these types helps you understand how Postgres prepares and searches text efficiently.

3

Intermediate

Creating a Full-text Search Index

🤔Before reading on: do you think a full-text search index is created automatically or must be defined explicitly? Commit to your answer.

Concept: Learn how to create an index that speeds up full-text search queries.

You create a GIN index on a tsvector column that stores processed text. For example: ALTER TABLE articles ADD COLUMN document_with_weights tsvector; UPDATE articles SET document_with_weights = to_tsvector('english', title || ' ' || body); CREATE INDEX idx_fts ON articles USING GIN(document_with_weights); This index lets Postgres find matches fast.

Result

Queries using the tsvector column run much faster due to the index.

Understanding that the index must be created and maintained explains why full-text search is fast and how to optimize it.

4

Intermediate

Writing Full-text Search Queries

🤔Before reading on: do you think full-text search queries use plain text or special syntax? Commit to your answer.

Concept: Learn how to write queries using to_tsquery or plainto_tsquery functions.

You write queries like: SELECT * FROM articles WHERE document_with_weights @@ to_tsquery('english', 'cloud & infrastructure'); The @@ operator checks if the tsvector matches the tsquery. to_tsquery allows logical operators like & (AND), | (OR). plainto_tsquery treats input as plain words combined with AND.

Result

You can find rows matching complex search conditions efficiently.

Knowing the query syntax and operators lets you build powerful search filters.

5

Intermediate

Ranking Search Results by Relevance

🤔Before reading on: do you think full-text search returns results in any order or ranked by relevance? Commit to your answer.

Concept: Learn how to rank results using ts_rank or ts_rank_cd functions.

You can order results by relevance score: SELECT *, ts_rank(document_with_weights, to_tsquery('english', 'cloud & infrastructure')) AS rank FROM articles WHERE document_with_weights @@ to_tsquery('english', 'cloud & infrastructure') ORDER BY rank DESC; This shows the most relevant matches first.

Result

Search results are sorted by how well they match the query.

Ranking results improves user experience by showing the best matches first.

6

Advanced

Handling Language and Stemming Variations

🤔Before reading on: do you think full-text search treats different word forms as the same or different? Commit to your answer.

Concept: Learn how Postgres uses dictionaries and stemming to match word variations.

Postgres supports multiple languages with dictionaries that reduce words to their root form (stemming). For example, 'running' and 'runs' become 'run'. You specify language in to_tsvector and to_tsquery, e.g., to_tsvector('english', text). This helps match different forms of a word.

Result

Search matches are more flexible and accurate across word forms.

Understanding stemming and language support helps build better search experiences for diverse content.

7

Expert

Optimizing and Maintaining Full-text Search in Production

🤔Before reading on: do you think full-text search indexes update automatically with data changes or require manual steps? Commit to your answer.

Concept: Learn about index maintenance, triggers, and performance tuning in real systems.

Full-text search indexes do not update automatically when text changes. You must update the tsvector column manually or use triggers to keep it in sync. For example, create a trigger that updates document_with_weights on INSERT or UPDATE. Also, monitor index size and vacuuming to keep performance high. Use EXPLAIN to check query plans.

Result

Your full-text search stays fast and accurate as data changes.

Knowing how to maintain and tune full-text search prevents common production issues like stale indexes or slow queries.

Under the Hood

Postgres converts text into a tsvector, which is a sorted list of lexemes (normalized words) with optional weights. It builds a GIN index on this vector for fast lookup. When a query arrives, it is parsed into a tsquery, also a set of lexemes with logical operators. The system matches tsquery against tsvector using the index, quickly finding rows containing the requested words or phrases.

Why designed this way?

This design balances speed and flexibility. Using lexemes and stemming reduces data size and matches word variations. GIN indexes allow fast searches on large text without scanning all rows. Alternatives like scanning raw text or using slower indexes were too inefficient for large datasets.

┌───────────────┐        ┌───────────────┐
│   Raw Text    │        │   User Query  │
└──────┬────────┘        └──────┬────────┘
       │                        │
       ▼                        ▼
┌───────────────┐        ┌───────────────┐
│  to_tsvector  │        │  to_tsquery   │
│ (tokenizes & │        │ (tokenizes &  │
│  normalizes)  │        │  parses query)│
└──────┬────────┘        └──────┬────────┘
       │                        │
       ▼                        ▼
┌──────────────────────────────┐
│          GIN Index            │
│  (on tsvector column)         │
└─────────────┬────────────────┘
              │
              ▼
┌──────────────────────────────┐
│   Match tsquery against       │
│   tsvector using index        │
└─────────────┬────────────────┘
              │
              ▼
┌──────────────────────────────┐
│   Return matching rows fast   │
└──────────────────────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does full-text search match exact word forms only or also variations? Commit to your answer.

Common Belief: Full-text search only matches exact words typed in the query.

Reality: Full-text search uses stemming and dictionaries to match word variations like plurals and tenses.

Why it matters: Believing this limits search effectiveness and causes missed results for users who use different word forms.

Quick: Does the full-text search index update automatically when data changes? Commit to your answer.

Common Belief: The full-text search index updates automatically whenever the text data changes.

Reality: You must manually update the tsvector column or use triggers; the index does not update by itself.

Why it matters: Ignoring this causes stale search results and confusion when new data is not found.

Quick: Is full-text search always faster than LIKE queries? Commit to your answer.

Common Belief: Full-text search is always faster than LIKE queries for any text search.

Reality: For very small datasets or simple exact matches, LIKE can be faster; full-text search shines with large text and complex queries.

Why it matters: Misusing full-text search can add unnecessary complexity and overhead in simple cases.

Quick: Can full-text search handle phrase searches by default? Commit to your answer.

Common Belief: Full-text search can natively search for exact phrases easily.

Reality: Postgres full-text search does not support exact phrase search by default; it matches words but not their order or adjacency.

Why it matters: Expecting phrase search leads to wrong assumptions about search results and requires additional techniques.

Expert Zone

1

Weights assigned to lexemes in tsvector allow fine-tuning relevance, but many users ignore them, missing better ranking.

2

GIN indexes speed up search but can grow large; using GIST indexes trades speed for smaller size and different performance.

3

Combining full-text search with trigram indexes can improve phrase and fuzzy matching beyond default capabilities.

When NOT to use

Avoid full-text search for very small datasets or when exact substring matching is needed; use simple LIKE or regex queries instead. For complex phrase or fuzzy search, consider external search engines like Elasticsearch or specialized extensions.

Production Patterns

In production, maintain tsvector columns with triggers or generated columns, use GIN indexes for fast search, combine ranking functions for relevance, and monitor index bloat. Integrate with Supabase APIs to provide search in web apps with real-time updates.

Connections

Inverted Index

Full-text search uses an inverted index structure similar to search engines.

Understanding inverted indexes from information retrieval helps grasp how Postgres finds words quickly in large text.

Natural Language Processing (NLP)

Full-text search applies NLP techniques like stemming and stop-word removal.

Knowing NLP basics explains why search matches word roots and ignores common words, improving relevance.

Library Catalog Indexing

Like a library index pointing to book pages, full-text search indexes point to database rows containing words.

This connection shows how indexing transforms slow manual search into fast lookup.

Common Pitfalls

#1 Not updating the tsvector column after inserting or updating text data.

Wrong approach:

INSERT INTO articles (title, body) VALUES ('Cloud Basics', 'Learn cloud infrastructure'); -- No tsvector update

Correct approach:

INSERT INTO articles (title, body, document_with_weights) VALUES ('Cloud Basics', 'Learn cloud infrastructure', to_tsvector('english', 'Cloud Basics' || ' ' || 'Learn cloud infrastructure'));

Root cause:

Misunderstanding that tsvector columns do not auto-update and must be refreshed manually or via triggers.

#2 Using LIKE queries for large text search instead of full-text search.

Wrong approach:

SELECT * FROM articles WHERE body LIKE '%cloud%';

Correct approach:

SELECT * FROM articles WHERE document_with_weights @@ plainto_tsquery('english', 'cloud');

Root cause:

Not knowing full-text search exists or how it improves performance for text search.

#3 Expecting phrase search to work with full-text search by default.

Wrong approach:

SELECT * FROM articles WHERE document_with_weights @@ to_tsquery('english', 'cloud infrastructure'); -- expects phrase match

Correct approach:

Use additional techniques like phrase operators or combine with trigram indexes for phrase search.

Root cause:

Assuming full-text search matches word order and adjacency without extra configuration.

Key Takeaways

Full-text search in Postgres transforms text into tokens and uses special indexes to find matches quickly.

It supports language-aware features like stemming to match word variations, improving search relevance.

You must create and maintain tsvector columns and indexes to keep search fast and accurate.

Ranking functions help order results by relevance, enhancing user experience.

Full-text search is powerful but has limits; understanding its design helps avoid common mistakes and optimize usage.

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