How to Implement PostgreSQL Full-Text Search with Spring Boot

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