Spring Boot & Postgres - Text Search

Jul 1, 2024 · 3 min read · jdbc text-search liquibase ·

Share on:

Overview

Spring boot application with postgres text search implementation

Github: https://github.com/gitorko/project103

Text Search

Using like keyword for search is not efficient for text search. There is no ranking and no indexes can be used.

1select * from customer where description like '%play%';

Elastic search can also be used to search text for large scale. For simpler small scale text search you can use postgres and leverage existing database.

to_tsvector - Will remove stop words, find lexical words, adds positions
to_tsquery - Will search the tsvector

gin - generalized inverted index will be created to search.

SQL queries

 1select description::tsvector
 2from customer;
 3
 4select to_tsvector(description)
 5from customer;
 6
 7select to_tsquery('Loves')
 8from customer;
 9
10select websearch_to_tsquery('Loves and Skating')
11from customer;
12
13select to_tsvector(description) @@ websearch_to_tsquery('Loves and Skating')
14from customer;
15
16select *
17from customer
18where to_tsvector(name || ' ' || coalesce(description, '')) @@ websearch_to_tsquery('Loves and Skating');
19
20select *, ts_rank(to_tsvector(name || ' ' || coalesce(description, '')), websearch_to_tsquery('Loves and Skating')) as rank
21from customer
22where to_tsvector(name || ' ' || coalesce(description, '')) @@ websearch_to_tsquery('Loves and Skating')
23order by rank desc;

Code

1CREATE TABLE customer (
2      id bigserial PRIMARY KEY,
3      name varchar(255) UNIQUE NOT NULL,
4      description text,
5      tsv tsvector generated always as ( to_tsvector('english'::regconfig, name || ' ' || coalesce(description, '')) ) stored
6);

 1package com.demo.project103.repository;
 2
 3import java.util.List;
 4
 5import com.demo.project103.domain.Customer;
 6import org.springframework.data.jpa.repository.JpaRepository;
 7import org.springframework.data.jpa.repository.Query;
 8import org.springframework.data.repository.query.Param;
 9import org.springframework.stereotype.Repository;
10
11@Repository
12public interface CustomerRepository extends JpaRepository<Customer, Long> {
13
14    @Query(value = """
15            select * from customer where
16            tsv @@ websearch_to_tsquery(:search);
17            """, nativeQuery = true)
18    List<Customer> searchByText(@Param("search") String search);
19
20    @Query(value = """
21            select *, ts_rank(tsv, websearch_to_tsquery(:search)) as rank
22            from customer
23            where tsv @@ websearch_to_tsquery(:search)
24            order by rank desc;
25            """, nativeQuery = true)
26    List<Customer> rankSearchByText(@Param("search") String search);
27}

Postman

Import the postman collection to postman

Postman Collection

Setup

 1# Project103
 2
 3Spring Boot & Postgres - Text Search
 4
 5### Version
 6
 7Check version
 8
 9```bash
10$java --version
11openjdk version "21.0.3" 2024-04-16 LTS
12```
13
14### Postgres DB
15
16```
17docker run -p 5432:5432 --name pg-container -e POSTGRES_PASSWORD=password -d postgres:14
18docker ps
19docker exec -it pg-container psql -U postgres -W postgres
20CREATE USER test WITH PASSWORD 'test@123';
21CREATE DATABASE "test-db" WITH OWNER "test" ENCODING UTF8 TEMPLATE template0;
22grant all PRIVILEGES ON DATABASE "test-db" to test;
23```
24
25Schema
26
27```bash
28
29CREATE SCHEMA america;
30CREATE SCHEMA asia;
31
32select nspname as "Schema"
33from pg_catalog.pg_namespace
34where nspname not like 'pg_%';
35```
36
37```bash
38docker stop pg-container
39docker start pg-container
40```
41
42### Dev
43
44To run the backend in dev mode.
45
46```bash
47./gradlew clean build
48./gradlew bootRun
49```

References

https://www.postgresql.org/docs/current/textsearch.html