Full-Text Search in PostgreSQL — Creating the Right Index

Search and rank documents well

Search and organize Within a lot of Text !

Let’s look at full-text search indexing in PostgreSQL. Text search is a common task for a lot of application developers and startups in the initial stages. But one may not have the infra/capacity to deal with this. This is when PostgreSQL as a database can handle a lot of functionality to get started.

Let’s take an example and learn how PostgreSQL handles full-text search and indexing.

It’s a simple schema. We’re creating an application where users can save notes. And the goal is to provide users with a search capability on these notes. Since it’s not a primary functionality of the application, and the usage is expected to be low in the start, we can choose to have postgresql handle our text indexing and search for a while.

create table notes (
    id serial primary key ,
    title text ,
    content text
)

Let’s insert some data into this table.

insert into notes (title, content) VALUES ('I love Running',
                        ' I wrote a post about running. ' ||
                        'And it was my first run on the streets. '); 

insert into notes (title, content) VALUES (
                       'Indexing in Postgresql. fulltext searching', 
                     'Ill write an article on full txt indexing in ' 
                      || 'postgres and avoid setting up a new || 
                    infrastructure') ;

So, every time a user hits a search query, our database takes the responsibility to provide search results based on the title and content columns. First let’s look at some components which handle full text search in Postgres, and then build upon this to implement a proper indexing to search quickly.

Search Without an Index

To search a text column, we need to use two special functions within PostgreSQL.

• to_tsvector
• to_tsquery

to_tsvector

This method takes in a text field as an input and generates terms that can be used for searching. Execute the query below to see the output of how our note title’s will be parsed.