How to Rank Search Results in PostgreSQL: Simple Guide

In PostgreSQL, you can rank search results using full-text search functions like ts_rank or ts_rank_cd which assign a relevance score to each result. Use these functions in the ORDER BY clause to sort results by their rank, showing the most relevant matches first.

Syntax

To rank search results, use the ts_rank or ts_rank_cd functions with a tsvector column and a tsquery. The syntax is:

• ts_rank(vector, query [, weights]): Returns a rank based on the frequency and position of query terms.
• ts_rank_cd(vector, query [, weights]): Similar to ts_rank but uses cover density ranking for better relevance.

You then order your SELECT query by the rank to get results sorted by relevance.

sql

SELECT *, ts_rank(to_tsvector('english', column_name), to_tsquery('english', 'search_term')) AS rank
FROM table_name
WHERE to_tsvector('english', column_name) @@ to_tsquery('english', 'search_term')
ORDER BY rank DESC;

Example

This example shows how to rank rows in a documents table by relevance to the search term 'database'. It uses ts_rank to score matches and orders results by that score.

sql

CREATE TABLE documents (id SERIAL PRIMARY KEY, content TEXT);

INSERT INTO documents (content) VALUES
('PostgreSQL is a powerful database system'),
('Learn how to rank search results in PostgreSQL'),
('This text does not mention the keyword'),
('Database ranking helps find relevant results');

SELECT id, content,
  ts_rank(to_tsvector('english', content), to_tsquery('english', 'database')) AS rank
FROM documents
WHERE to_tsvector('english', content) @@ to_tsquery('english', 'database')
ORDER BY rank DESC;

Output

 id |                      content                       |    rank    
----+--------------------------------------------------+------------
  4 | Database ranking helps find relevant results     | 0.41666667 
  1 | PostgreSQL is a powerful database system          | 0.27083334 
(2 rows)

Common Pitfalls

Common mistakes when ranking search results include:

• Not using to_tsvector and to_tsquery consistently with the same configuration (like 'english').
• Forgetting to filter results with @@ operator before ranking, which can cause irrelevant rows to appear.
• Using ts_rank without understanding that it returns a float score, so ordering by it descending is necessary.
• Not indexing the tsvector column, which slows down searches.

Example of a wrong query and the corrected version:

sql

/* Wrong: No filtering, ranks all rows including irrelevant */
SELECT *, ts_rank(to_tsvector('english', content), to_tsquery('english', 'database')) AS rank
FROM documents
ORDER BY rank DESC;

/* Right: Filter with @@ operator before ranking */
SELECT *, ts_rank(to_tsvector('english', content), to_tsquery('english', 'database')) AS rank
FROM documents
WHERE to_tsvector('english', content) @@ to_tsquery('english', 'database')
ORDER BY rank DESC;

Quick Reference

Function	Description	Usage
ts_rank	Ranks search results by frequency and position of terms	ts_rank(vector, query [, weights])
ts_rank_cd	Ranks using cover density for better relevance	ts_rank_cd(vector, query [, weights])
to_tsvector	Converts text to searchable document vector	to_tsvector('config', text)
to_tsquery	Creates a search query from text	to_tsquery('config', 'query')
@@	Matches tsvector against tsquery	vector @@ query

Key Takeaways

Use ts_rank or ts_rank_cd with to_tsvector and to_tsquery to rank search results in PostgreSQL.

Always filter results with @@ operator before ranking to avoid irrelevant rows.

Order your query by the rank in descending order to show the most relevant results first.

Use consistent text search configuration (like 'english') for both vector and query.

Create indexes on tsvector columns to speed up full-text search queries.