How to Implement PostgreSQL Full-Text Search with Spring Boot

  • 4.3/5
  • 895
  • Aug 25, 2024

In this article, we will explore how to implement full-text search with PostgreSQL in a Spring Boot application.

1) Set up PostgreSQL

Here's an example PostgreSQL query to create a PostgreSQL database named "postgres-full-text-search-db", create a table named "articles", and insert 10 rows of sample data into the "articles" table.

Now, let's say we want to perform a full-text search on the data in the "title" and "content" columns. We can add a new column to the table to store the preprocessed search document (i.e., the list of lexemes):

The above query is also helpful when we have an existing table filled with data and want to enable full-text search on that data for selected columns. Now, even if we add or update data in the table, the data will be automatically processed and added to the ts column for use in full-text search.

Now, if we query the table, we should see processed data in the ts column, as shown below:

We can then create a GIN index on the "ts" column:

Now we can query this like:

This SQL query performs a full-text search on the articles table and ranks the results based on their relevance to the search query.

"Ts_rank(ts, Phraseto_tsquery('english', 'Shawshank Redemption')) AS rank" calculates the relevance rank of each article based on the search query. Ts_rank returns a numeric rank representing how well the article matches the search query. The Phraseto_tsquery function creates a query that matches the exact phrase "Shawshank Redemption" in the specified language ('english').

"ts @@ phraseto_tsquery('english', 'Shawshank Redemption')" filters the rows to include only those where the ts column matches the search query. The @@ operator is used for full-text search matching.

"rank DESC" orders the results by the relevance rank in descending order, so the most relevant articles appear first.

"LIMIT 10" Limits the number of rows returned to 10.

"OFFSET 0" Skips the first 0 rows, effectively starting at the beginning of the result set.

2) Create a Spring Boot Project

Creating a Spring Boot project using Spring Initializr is straightforward. Go to Spring Initializr. Click on "Add Dependencies" and select the dependencies we need.

If everything went well, our final pom.xml should look something like this:

2.1) Create Entity

For full-text search, we'll use the "tsvector" column to store precomputed search vectors. In our entity class, we can mark fields that should be used for full-text search. For example:

2.2) Create Repository

To perform full-text search queries in PostgreSQL from Spring Boot, we can use the @Query annotation in our repository interface. Here’s an example:

2.3) Create Service

Let's add a service layer that converts the list of object arrays returned by the repository method into a list of Article objects.

2.4) Create Controller

Use the service in our controller to handle HTTP requests:

2.5) Configure PostgreSQL

To configure PostgreSQL we need to set up the appropriate properties in our application.yml file:

2.6) Run & Test

If you are using an IDE like IntelliJ IDEA or Eclipse, you can run your Spring Boot application directly. Locate the main application class (the one with @SpringBootApplication annotation). Right-click on the class and select "Run" or "Debug".

To test the API we created for full-text search, we can use various methods, including cURL, Postman, and unit tests. For now, let's test it in the browser:

Source Code: GitHub

Index
How to Implement PostgreSQL Full-Text Search with Spring Boot

15 min

Spring's transaction management with the @Transactional annotation

9 min

Spring Boot Rest APIs with PostgreSQL (Spring Boot + Rest APIs)

15 min

Caching in Spring Boot (@Cacheable, @CacheEvict & @CachePut)

21 min

Declarative REST Client in Spring Boot (Spring 6 HTTP Interface)

13 min

Profiling a Spring Boot application with Pyroscope

7 min

Service discovery in Spring Boot (Spring Cloud + Netflix Eureka)

9 min

Dockerize Spring Boot app and Push image to DockerHub (Spring Boot + DockerHub)

4 min

Creating a Jenkins Pipeline for Spring Boot application

2 min

Circuit Breaker Pattern in Microservices (Spring BOOT + Resilience4j)

4 min

Edge Server Pattern in Microservices (Spring Cloud Gateway)

7 min

Monitoring Microservices (Spring Boot + Micrometer + Prometheus + Grafana)

7 min

Spring Cloud config server setup with Git

8 min

Distributed Tracing in Microservices (Spring Cloud Sleuth + Zipkin)

9 min

Circuit Breaker Pattern with Resilience4J in a Spring Boot Application

24 min

Deploying Spring Boot microservices on Kubernetes Cluster

12 min

Reactive programming in Java with Project Reactor

50 min

Spring Reactive with PostgreSQL (Spring Boot WebFlux + PostgreSQL)

13 min

Spring Reactive, Thymeleaf Hello World (Spring Webflux + Thymeleaf + JS/CSS)

9 min

Problem JSON (application/problem+json) in Spring WebFlux

15 min

Spring Boot Login/Logout (Spring Security + MySql + Thymeleaf)

21 min

Securing Server-to-Server Communication with "Spring Boot" & "OAuth 2"

18 min

Sending Emails in Spring Boot via SMTP

7 min

How to create a basic Spring 6 project using Maven

5 min

Spring Boot, Thymeleaf Hello World (Spring Boot + Thymeleaf + JS/CSS)

9 min