Leveraging PostgreSQL Full Text Search for Enhanced Content Discovery in Content Management Systems

CMS (information Management Systems) have become essential for properly managing and delivering digital information. However, as the volume of content expands, it might be difficult to identify useful information. PostgreSQL's Full Text Search (FTS) features shine here. In this post, we'll look at how PostgreSQL FTS may be used to provide robust search capabilities within CMS platforms.

Example Scenario: Building a Blog Post Search

Consider a CMS that hosts a large number of blog articles. Traditional searches may focus on exact keyword matches, ignoring content that use similar phrases or synonyms. Let's have a look at how PostgreSQL FTS can enhance the search experience:

Step 1: Creating a Full Text Search Index

To get started, create a full-text search index on the relevant columns of your database table. In this case, we'll index the title and content columns of the blog_posts table:

CREATE INDEX blog_posts_fts_idx
ON blog_posts
USING gin(to_tsvector('english', title || ' ' || content));

Step 2: Implementing FTS Queries

Now, let's explore various FTS queries to enhance content discovery:

1. Basic Text Search:

Retrieve all blog posts containing the word "technology":

SELECT id, title
FROM blog_posts
WHERE to_tsvector('english', title || ' ' || content) @@ to_tsquery('english', 'technology');

2. Ranking Results:

Rank the results by relevance using the ts_rank function:

SELECT id, title, ts_rank(to_tsvector('english', title || ' ' || content), to_tsquery('english', 'technology')) AS rank
FROM blog_posts
WHERE to_tsvector('english', title || ' ' || content) @@ to_tsquery('english', 'technology')
ORDER BY rank DESC;

3. Phrase Search:

Search for blog posts containing the phrase "machine learning":

SELECT id, title
FROM blog_posts
WHERE to_tsvector('english', title || ' ' || content) @@ phraseto_tsquery('english', 'machine learning');

4. Synonyms and Stemming:

Consider synonyms and stemming for a broader search:

SELECT id, title
FROM blog_posts
WHERE to_tsvector('english', title || ' ' || content) @@ to_tsquery('english', 'analyzing | analysing');

5. Highlighting Matched Words:

Highlight matched words using the ts_headline function:

SELECT id, title, ts_headline('english', title || ' ' || content, to_tsquery('english', 'database')) AS highlighted_text
FROM blog_posts
WHERE to_tsvector('english', title || ' ' || content) @@ to_tsquery('english', 'database');