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…