Ranking Full-Text Search Results in PostgreSQL Using ts_rank and ts_rank_cd With Hibernate 6 and posjsonhelper
Learn how to rank PostgreSQL full-text search results by relevance using ts_rank, ts_rank_cd, and Hibernate’s Criteria API for better search UX.
CORE
·
Join the DZone community and get the full member experience.
Join For FreeIn a previous article, we explored how to implement full-text search in PostgreSQL using Hibernate 6 and the posjsonhelper library. We built queries with to_tsvector, to_tsquery, and their simpler wrappers for the plainto_tsquery, phraseto_tsquery, and websearch_to_tsquery functions.
This time, we’ll extend that foundation and explore how to rank search results based on their relevance using PostgreSQL’s built-in ranking functions like ts_rank and ts_rank_cd.
We’ll also demonstrate how to use them programmatically in Hibernate through the posjsonhelper library.
Why Ranking Matters
A typical full-text search returns all matching records, but not necessarily in a meaningful order.
For example, imagine searching “Postgres ranking” in a database of articles. Some records might mention the term once, while others include it repeatedly or in their titles — yet both would appear equally if we rely only on the @@ operator.
That’s where ranking functions come in.
ts_rank— computes a relevance score based on term frequency and inverse document frequency (TF/IDF).ts_rank_cd— a variant using cover density ranking, favoring documents where search terms appear close together.
(For more information about built-in ranking methods, check Postgres documentation.)
Ranking lets your application:
- Prioritize results by relevance.
- Improve search UX by showing the best matches first.
- Keep using PostgreSQL’s native features — no need for external search engines.
When Full-Text Search Alone Is Enough
While modern projects often explore vector similarity search (e.g., using pgvector with embeddings), not every system needs that level of complexity.
Full-text search — especially when enhanced with ranking — is usually sufficient when:
- You want exact or linguistic matches, not semantic ones.
- Your dataset is moderate in size (hundreds of thousands, not billions of rows).
- You want explainable ranking logic (based on term frequency and proximity).
- You need to stay entirely within PostgreSQL — no additional infrastructure.
If your users expect “semantic” search, then vector embeddings are worth considering. But for structured text like product descriptions, articles, or messages, Postgres full-text search with ranking is often all you need.
PostgreSQL Ranking Functions Overview
Both ts_rank and ts_rank_cd take a tsvector (your indexed document) and a tsquery (your search query):
SELECT ts_rank(to_tsvector('english', content), to_tsquery('english', 'postgres & ranking'));
They return a numeric score representing how relevant the document is to the search query.
You can then order results using this score:
ORDER BY ts_rank(to_tsvector('english', content), to_tsquery('english', 'postgres & ranking')) DESC;
Implementing Ranking in Hibernate Using posjsonhelper
The posjsonhelper library adds type-safe, Criteria-API-compatible support for PostgreSQL functions like to_tsvector, to_tsquery, and text operators.
Although it doesn’t include wrappers at this moment for ranking functions, you can easily invoke them through Hibernate’s CriteriaBuilder#function method.
Let’s see how this works in practice.
Example 1: Ranking With ts_rank
public List<Item> findItemsByWebSearchToTSQuerySortedByTsRank(String phrase, boolean ascSort) {
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Item> cq = cb.createQuery(Item.class);
Root<Item> root = cq.from(Item.class);
// Build weighted tsvector using posjsonhelper functions
Expression<String> shortNameVec = cb.function("setweight", String.class,
new TSVectorFunction(root.get("shortName"), new RegconfigTypeCastOperatorFunction((NodeBuilder) cb, ENGLISH_CONFIGURATION, hibernateContext), (NodeBuilder) cb),
cb.literal("A")
);
Expression<String> fullNameVec = cb.function("setweight", String.class,
new TSVectorFunction(root.get("fullName"), new RegconfigTypeCastOperatorFunction((NodeBuilder) cb, ENGLISH_CONFIGURATION, hibernateContext), (NodeBuilder) cb),
cb.literal("B")
);
Expression<String> shortDescriptionVec = cb.function("setweight", String.class,
new TSVectorFunction(root.get("shortDescription"), new RegconfigTypeCastOperatorFunction((NodeBuilder) cb, ENGLISH_CONFIGURATION, hibernateContext), (NodeBuilder) cb),
cb.literal("C")
);
Expression<String> fullDescriptionVec = cb.function("setweight", String.class,
new TSVectorFunction(root.get("fullDescription"), new RegconfigTypeCastOperatorFunction((NodeBuilder) cb, ENGLISH_CONFIGURATION, hibernateContext), (NodeBuilder) cb),
cb.literal("D")
);
// Concatenate tsvectors (|| operator)
SqmExpression<String> fullVector = (SqmExpression<String>) cb.concat(cb.concat(shortNameVec, fullNameVec), cb.concat(shortDescriptionVec, fullDescriptionVec));
// Build tsquery
Expression<String> queryExpr = new WebsearchToTSQueryFunction((NodeBuilder) cb, ENGLISH_CONFIGURATION, phrase);
// WHERE clause using @@ operator
TextOperatorFunction matches = new TextOperatorFunction((NodeBuilder) cb, fullVector, new WebsearchToTSQueryFunction((NodeBuilder) cb, new RegconfigTypeCastOperatorFunction((NodeBuilder) cb, ENGLISH_CONFIGURATION, hibernateContext), phrase), hibernateContext);
cq.where(matches);
// Ranking
Expression<Double> rankExpr = cb.function(
"ts_rank", Double.class,
fullVector,
queryExpr
);
cq.orderBy(ascSort ? cb.asc(rankExpr) : cb.desc(rankExpr));
return entityManager.createQuery(cq).getResultList();
}The full code example can be found here.
This query produces SQL similar to:
select
i1_0.id,
i1_0.full_description,
i1_0.full_name,
i1_0.short_description,
i1_0.short_name
from
item i1_0
where
(
(
setweight(to_tsvector(?::regconfig, i1_0.short_name), 'A')||setweight(
to_tsvector(?::regconfig, i1_0.full_name), 'B'
)
)||(
setweight(to_tsvector(?::regconfig, i1_0.short_description), 'C')||setweight(
to_tsvector(?::regconfig, i1_0.full_description), 'D'
)
)
) @@ websearch_to_tsquery(?::regconfig, ?)
order by
ts_rank(((setweight(to_tsvector(?::regconfig, i1_0.short_name), 'A')||setweight(to_tsvector(?::regconfig, i1_0.full_name), 'B'))||(setweight(to_tsvector(?::regconfig, i1_0.short_description), 'C')||setweight(to_tsvector(?::regconfig, i1_0.full_description), 'D'))), websearch_to_tsquery('english', ?))
The same thing can be implemented with HQL language, like below:
public List<Item> findItemsByWebSearchToTSQuerySortedByTsRankInHQL(String phrase, boolean ascSort) {
String statement = "from Item as item where " +
"text_operator_function(" + // text_operator_function - start
"concat(" + // main concat - start
"concat(" + // first concat - start
"function('setweight', to_tsvector('%1$s', item.shortName), 'A')" +
"," +
"function('setweight', to_tsvector('%1$s', item.fullName), 'B')" +
")" + // first concat - end
"," + // main concat - separator
"concat(" + // second concat - start
"function('setweight', to_tsvector('%1$s', item.shortDescription), 'C')" +
"," +
"function('setweight', to_tsvector('%1$s', item.fullDescription), 'D')" +
")" + // first second - end
")" + // main concat - end
"," + // text_operator_function - separator
"websearch_to_tsquery(cast_operator_function('%1$s','regconfig'), :phrase)" + // websearch_to_tsquery operator
")" + // text_operator_function - end
" order by " + // order - start
"function('ts_rank', " + // ts_rank function - start
"concat(" + // main concat - start
"concat(" + // first concat - start
"function('setweight', to_tsvector('%1$s', item.shortName), 'A')" +
"," +
"function('setweight', to_tsvector('%1$s', item.fullName), 'B')" +
")" + // first concat - end
"," + // main concat - separator
"concat(" + // second concat - start
"function('setweight', to_tsvector('%1$s', item.shortDescription), 'C')" +
"," +
"function('setweight', to_tsvector('%1$s', item.fullDescription), 'D')" +
")" + // first second - end
")" + // main concat - end
"," + // ts_rank function - separator
"websearch_to_tsquery(cast_operator_function('%1$s','regconfig'), :phrase)" + // websearch_to_tsquery operator
")" + // ts_rank function - end
(ascSort ? " asc" : "desc");
TypedQuery<Item> query = entityManager.createQuery(statement.formatted(ENGLISH_CONFIGURATION), Item.class);
query.setParameter("phrase", phrase);
return query.getResultList();
}The full code example can be found here.
Example 2: Ranking With ts_rankd_cd
public List<Item> findItemsByWebSearchToTSQuerySortedByTsRankCd(String phrase, boolean ascSort) {
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Item> cq = cb.createQuery(Item.class);
Root<Item> root = cq.from(Item.class);
// Build weighted tsvector using posjsonhelper functions
Expression<String> shortNameVec = cb.function("setweight", String.class,
new TSVectorFunction(root.get("shortName"), new RegconfigTypeCastOperatorFunction((NodeBuilder) cb, ENGLISH_CONFIGURATION, hibernateContext), (NodeBuilder) cb),
cb.literal("A")
);
Expression<String> fullNameVec = cb.function("setweight", String.class,
new TSVectorFunction(root.get("fullName"), new RegconfigTypeCastOperatorFunction((NodeBuilder) cb, ENGLISH_CONFIGURATION, hibernateContext), (NodeBuilder) cb),
cb.literal("B")
);
Expression<String> shortDescriptionVec = cb.function("setweight", String.class,
new TSVectorFunction(root.get("shortDescription"), new RegconfigTypeCastOperatorFunction((NodeBuilder) cb, ENGLISH_CONFIGURATION, hibernateContext), (NodeBuilder) cb),
cb.literal("C")
);
Expression<String> fullDescriptionVec = cb.function("setweight", String.class,
new TSVectorFunction(root.get("fullDescription"), new RegconfigTypeCastOperatorFunction((NodeBuilder) cb, ENGLISH_CONFIGURATION, hibernateContext), (NodeBuilder) cb),
cb.literal("D")
);
// Concatenate tsvectors (|| operator)
SqmExpression<String> fullVector = (SqmExpression<String>) cb.concat(cb.concat(shortNameVec, fullNameVec), cb.concat(shortDescriptionVec, fullDescriptionVec));
// Build tsquery
Expression<String> queryExpr = new WebsearchToTSQueryFunction((NodeBuilder) cb, ENGLISH_CONFIGURATION, phrase);
// WHERE clause using @@ operator
TextOperatorFunction matches = new TextOperatorFunction((NodeBuilder) cb, fullVector, new WebsearchToTSQueryFunction((NodeBuilder) cb, new RegconfigTypeCastOperatorFunction((NodeBuilder) cb, ENGLISH_CONFIGURATION, hibernateContext), phrase), hibernateContext);
cq.where(matches);
// Ranking
Expression<Double> rankExpr = cb.function(
"ts_rank_cd", Double.class,
fullVector,
queryExpr
);
cq.orderBy(ascSort ? cb.asc(rankExpr) : cb.desc(rankExpr));
return entityManager.createQuery(cq).getResultList();
}Code looks almost identical; the only difference is the use of the ts_rank_cd function.
The full code example can be found here.
Example 3: Custom Weights and Normalization
ts_rank and ts_rank_cd support additional arguments — for example, passing a custom weight array to control how much each part of your text contributes to the overall rank, just like in the example below:
public List<Item> findItemsByWebSearchToTSQuerySortedByTsRankWithCustomWeight(String phrase, boolean ascSort, double[] weights) {
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Item> cq = cb.createQuery(Item.class);
Root<Item> root = cq.from(Item.class);
// Build weighted tsvector using posjsonhelper functions
Expression<String> shortNameVec = cb.function("setweight", String.class,
new TSVectorFunction(root.get("shortName"), new RegconfigTypeCastOperatorFunction((NodeBuilder) cb, ENGLISH_CONFIGURATION, hibernateContext), (NodeBuilder) cb),
cb.literal("A")
);
Expression<String> fullNameVec = cb.function("setweight", String.class,
new TSVectorFunction(root.get("fullName"), new RegconfigTypeCastOperatorFunction((NodeBuilder) cb, ENGLISH_CONFIGURATION, hibernateContext), (NodeBuilder) cb),
cb.literal("B")
);
Expression<String> shortDescriptionVec = cb.function("setweight", String.class,
new TSVectorFunction(root.get("shortDescription"), new RegconfigTypeCastOperatorFunction((NodeBuilder) cb, ENGLISH_CONFIGURATION, hibernateContext), (NodeBuilder) cb),
cb.literal("C")
);
Expression<String> fullDescriptionVec = cb.function("setweight", String.class,
new TSVectorFunction(root.get("fullDescription"), new RegconfigTypeCastOperatorFunction((NodeBuilder) cb, ENGLISH_CONFIGURATION, hibernateContext), (NodeBuilder) cb),
cb.literal("D")
);
// Concatenate tsvectors (|| operator)
SqmExpression<String> fullVector = (SqmExpression<String>) cb.concat(cb.concat(shortNameVec, fullNameVec), cb.concat(shortDescriptionVec, fullDescriptionVec));
// Build tsquery
Expression<String> queryExpr = new WebsearchToTSQueryFunction((NodeBuilder) cb, ENGLISH_CONFIGURATION, phrase);
// WHERE clause using @@ operator
TextOperatorFunction matches = new TextOperatorFunction((NodeBuilder) cb, fullVector, new WebsearchToTSQueryFunction((NodeBuilder) cb, new RegconfigTypeCastOperatorFunction((NodeBuilder) cb, ENGLISH_CONFIGURATION, hibernateContext), phrase), hibernateContext);
cq.where(matches);
// Ranking
Expression<Double> rankExpr = cb.function(
"ts_rank", Double.class,
new ArrayFunction<>((NodeBuilder) cb, Arrays.stream(weights).mapToObj(w -> (SqmExpression<Double>) cb.literal(w)).toList(), hibernateContext)
, fullVector
, queryExpr
);
cq.orderBy(ascSort ? cb.asc(rankExpr) : cb.desc(rankExpr));
return entityManager.createQuery(cq).getResultList();
}Full code example can be found here; the same example, but implemented with HQL, can be found here.
Performance Considerations
While ranking functions such as ts_rank and ts_rank_cd significantly improve the quality of search results, they also introduce a computational and I/O overhead that’s important to understand, especially when operating on large datasets.
When PostgreSQL executes a full-text search with ranking, it typically performs two main operations:
- Index-based filtering — The
@@operator uses a GIN or GiST index to quickly locate the documents that match the query terms.
This part is very efficient and happens almost entirely in memory. - Ranking and sorting — Once matching rows are found, PostgreSQL must read the corresponding tsvector values from disk (or cache) and compute the relevance score for each row. This step involves I/O operations and CPU processing, since PostgreSQL needs to access the document lexemes and calculate how well they match the query. If the matching dataset is large, this can become a noticeable bottleneck — even when proper indexes are in place.
In other words, the index helps PostgreSQL find relevant rows quickly, but ranking requires touching each matched row’s data, which may trigger additional reads from storage. The cost grows roughly with the number of results that pass the @@ filter.
For example:
- Ranking 100 results is typically negligible (a few milliseconds).
- Ranking 10,000 or more results may involve enough I/O to impact response times, especially on spinning disks or when data doesn’t fit in shared buffers.
- Sorting ranked results (e.g., ORDER BY ts_rank(...) DESC) adds additional work, since PostgreSQL must maintain an in-memory or temporary sort buffer.
To mitigate these effects:
- Always filter first, and rank only the subset of matched rows.
- Consider using precomputed tsvector columns and GIN indexes to minimize recomputation.
- For very high-volume search workloads, caching top results or using a dedicated search engine (like Elasticsearch) might be beneficial.
Ranking is a powerful feature, but it’s not free. When applied to a large dataset, even with correct indexing, the I/O required to fetch and score every matched row can impact overall query performance. Designing queries and indexes with this in mind will ensure that your search remains both relevant and responsive.
Conclusion
By extending our earlier full-text search implementation with ts_rank and ts_rank_cd, we can now sort results by relevance, not just existence.
This approach combines the power of PostgreSQL’s ranking functions with the flexibility of Hibernate’s Criteria API, keeping your queries both expressive and type-safe.
While more advanced vector search solutions exist, for most business applications, PostgreSQL’s full-text search with ranking remains a simple, explainable, and highly effective solution.
Opinions expressed by DZone contributors are their own.
Comments