Postgresql Full Text Search with Spring Boot - Part 1

Full Text search

As the data in the database table starts to grow into millions of rows LIKE query doesn’t perform well. As it can take more than few seconds to minutes in some cases ( depending on the volumn of data in the table) for a single query. To solve this issue it’s best to index data in the column using full text search. Full text search enables faster retreival for search keyword. Most of the databases (eg. MySQL, Postgresql, sql server) come loaded with full text search feature. In this post we will be focusing on Postgresql full text search.

Prerequisite

• Postgresql
• Spring Boot

Full Text search setup

1. Create a table PRODUCT with columns product_id, name and description.

    create table product
    (
        product_id      uuid      default uuid_generate_v4() not null
            primary key,
        name            varchar(255)                         not null,
        description     varchar(1000)                        not null
    )
   

   Run the above query, this will create a table with name of PRODUCT.

   NOTE - if uuid gives error you might have to install the following extension

        CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
   

2. Add data to column

    insert into product(description, name)
    VALUEs ('description 1','Butter Chicken');
       
    insert into product(description, name)
    VALUEs ('description 2','Samosa');
       
    insert into product(description, name)
    VALUEs ('description 3', 'Mango Lassi');
       
    insert into product(description, name)
    VALUEs ('description 4','Mutton Curry');
       
    insert into product(description, name)
    VALUEs ('description 5', 'Tandoori murg');
   

3. add full text index to name and description columns

        CREATE INDEX idx_products_name ON product USING GIN (to_tsvector('english', name));
        CREATE INDEX idx_products_description ON product USING GIN (to_tsvector('english', description));
   

   This will create full text search indexes on the column name and description. English in to_tsvector instructs that the values in the particular column are in English language. So the values in the columns will be tokenized based on the english tokenizer.

4. Query on full text index column

   NOTE: queries are performed on the above 5 row data in step 2

   • The following query will return the result based on the search input

      select * from product where to_tsvector('english', name) @@ to_tsquery('english', 'mutton');
     

   • Typing substring of the query won’t yield any result. e.g. The following query will return 0 rows

      select * from product where to_tsvector('english', name) @@ to_tsquery('english', 'mu');
     

   • To make this scenario work append :* at end of the query. The following query should return 2 rows.

      select * from product where to_tsvector('english', name) @@ to_tsquery('english', 'mu:*');
     

   • To search within multiple columns, use the following query. This should return 5 rows

      select * from product where to_tsvector('english', name) @@ to_tsquery('english', 'de:*')
                or to_tsvector('english', description) @@ to_tsquery('english', 'de:*');
     

Conclusion

In this we created product table and added full text search to name and description columns and also fetched data using the full text search syntax. In the next article we will see how to integrate this into spring boot using spring jpa. POSTGRESQL Full Text Search with Spring Boot - Part 2