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.