Advanced Full-Text Search in PostgreSQL: Beyond the Basics

Advanced Full-Text Search in PostgreSQL: Beyond the Basics PostgreSQL has powerful built-in support for full-text search. While many developers know how to use basic to_tsvector and to_tsquery functions, there’s a lot more depth to uncover. In this guide, we’ll explore more advanced features like ranking, prefix matching, weighting, and combining full-text with structured filters. 1. Basic Setup and Indexing Start by making sure your text content is properly indexed. Here's how to create a GIN index for full-text search: CREATE TABLE articles ( id SERIAL PRIMARY KEY, title TEXT, body TEXT, tsv TSVECTOR ); UPDATE articles SET tsv = to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, '')); CREATE INDEX tsv_idx ON articles USING GIN(tsv); Use a trigger to keep the tsvector updated automatically: CREATE FUNCTION articles_tsv_trigger() RETURNS trigger AS $$ BEGIN NEW.tsv := to_tsvector('english', coalesce(NEW.title, '') || ' ' || coalesce(NEW.body, '')); RETURN NEW; END $$ LANGUAGE plpgsql; CREATE TRIGGER tsv_update BEFORE INSERT OR UPDATE ON articles FOR EACH ROW EXECUTE FUNCTION articles_tsv_trigger(); 2. Basic Full-Text Query SELECT * FROM articles WHERE tsv @@ to_tsquery('english', 'postgres & search'); This matches rows containing both "postgres" and "search". 3. Phrase and Prefix Searching To search for phrases or prefixes, use: SELECT * FROM articles WHERE tsv @@ phraseto_tsquery('english', 'full text search'); For prefix searching: SELECT * FROM articles WHERE tsv @@ to_tsquery('english', 'search:'); 4. Ranking Results Use ts_rank or ts_rank_cd to order by relevance: SELECT , ts_rank(tsv, to_tsquery('english', 'postgres')) AS rank FROM articles WHERE tsv @@ to_tsquery('english', 'postgres') ORDER BY rank DESC; 5. Weighting Different Fields You can assign different weights to fields to prioritize title over body, for example: UPDATE articles SET tsv = setweight(to_tsvector('english', coalesce(title, '')), 'A') || setweight(to_tsvector('english', coalesce(body, '')), 'B'); 6. Combining With Structured Filters Full-text search can be combined with other SQL conditions: SELECT * FROM articles WHERE tsv @@ to_tsquery('english', 'index') AND published = true AND category = 'PostgreSQL'; 7. Highlighting Matches Use ts_headline to highlight matched terms: SELECT title, ts_headline('english', body, to_tsquery('english', 'search')) FROM articles WHERE tsv @@ to_tsquery('english', 'search'); 8. JSON and Full-Text You can even index and search JSON fields: CREATE INDEX idx_json_search ON documents USING GIN (to_tsvector('english', data::text)); Conclusion PostgreSQL's full-text search is an underutilized powerhouse. When used correctly, it allows you to build scalable, sophisticated search features directly inside your database—no third-party engine needed. Whether you're powering a blog or a large-scale document system, mastering these techniques will take your search functionality to the next level. If this post helped you, consider supporting me: buymeacoffee.com/hexshift

Apr 15, 2025 - 23:54

Advanced Full-Text Search in PostgreSQL: Beyond the Basics

PostgreSQL has powerful built-in support for full-text search. While many developers know how to use basic to_tsvector and to_tsquery functions, there’s a lot more depth to uncover. In this guide, we’ll explore more advanced features like ranking, prefix matching, weighting, and combining full-text with structured filters.

1. Basic Setup and Indexing

Start by making sure your text content is properly indexed. Here's how to create a GIN index for full-text search:

CREATE TABLE articles (
  id SERIAL PRIMARY KEY,
  title TEXT,
  body TEXT,
  tsv TSVECTOR
);

UPDATE articles SET tsv = to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, ''));

CREATE INDEX tsv_idx ON articles USING GIN(tsv);

Use a trigger to keep the tsvector updated automatically:

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

CREATE TRIGGER tsv_update BEFORE INSERT OR UPDATE ON articles
FOR EACH ROW EXECUTE FUNCTION articles_tsv_trigger();

2. Basic Full-Text Query

SELECT * FROM articles
WHERE tsv @@ to_tsquery('english', 'postgres & search');

This matches rows containing both "postgres" and "search".

3. Phrase and Prefix Searching

To search for phrases or prefixes, use:

SELECT * FROM articles
WHERE tsv @@ phraseto_tsquery('english', 'full text search');

For prefix searching:

SELECT * FROM articles
WHERE tsv @@ to_tsquery('english', 'search:*');

4. Ranking Results

Use ts_rank or ts_rank_cd to order by relevance:

SELECT *, ts_rank(tsv, to_tsquery('english', 'postgres')) AS rank
FROM articles
WHERE tsv @@ to_tsquery('english', 'postgres')
ORDER BY rank DESC;

5. Weighting Different Fields

You can assign different weights to fields to prioritize title over body, for example:

UPDATE articles
SET tsv = setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
           setweight(to_tsvector('english', coalesce(body, '')), 'B');

6. Combining With Structured Filters

Full-text search can be combined with other SQL conditions:

SELECT * FROM articles
WHERE tsv @@ to_tsquery('english', 'index')
AND published = true
AND category = 'PostgreSQL';

7. Highlighting Matches

Use ts_headline to highlight matched terms:

SELECT title, ts_headline('english', body, to_tsquery('english', 'search'))
FROM articles
WHERE tsv @@ to_tsquery('english', 'search');

8. JSON and Full-Text

You can even index and search JSON fields:

CREATE INDEX idx_json_search ON documents
USING GIN (to_tsvector('english', data::text));

Conclusion

PostgreSQL's full-text search is an underutilized powerhouse. When used correctly, it allows you to build scalable, sophisticated search features directly inside your database—no third-party engine needed. Whether you're powering a blog or a large-scale document system, mastering these techniques will take your search functionality to the next level.

If this post helped you, consider supporting me: buymeacoffee.com/hexshift