Forem: Renan Maringolo

Construindo Busca Completa no PostgresSQL / Building Complete Search in PostgresSQL

Renan Maringolo — Tue, 17 Dec 2024 15:14:45 +0000

PT-BR:

A busca de texto completo (FTS) no PostgreSQL é poderosa, rápida e fácil de configurar, eliminando a necessidade de ferramentas externas como Elasticsearch em muitos cenários. Inclusive, nunca havia feito essa funcionalidade e inicialmente pensei em usar ES e até questionei colegas a respeito. Só que o problema do ES é que pode ser "too much" e caro ao que você precisa!

Passo 1: Criando a tabela e populando com dados

Primeiro, crio uma tabela simples e adiciono dados para realizar nossas buscas: Artigos com colunas id, titulo e descrição!

CREATE TABLE artigos (
  id SERIAL PRIMARY KEY,
  titulo VARCHAR(80) NOT NULL,
  descricao VARCHAR(200) NOT NULL
);

INSERT INTO artigos (titulo, descricao) VALUES
  ('Aprenda PostgreSQL', 'Um guia completo sobre PostgreSQL'),
  ('Introdução ao SQL', 'Entenda os fundamentos de SQL'),
  ('Busca de texto no Postgres', 'Como implementar busca de texto no PostgreSQL'),
  ('Programação em Java', 'Um curso básico de Java'),
  ('Desenvolvimento web com Rails', 'Aprenda a criar aplicações com Rails'),
  ('Curso avançado de SQL', 'Domine SQL com exemplos práticos'),
  ('Aprendizado de Máquina', 'Introdução ao Machine Learning');

Executando a query para verificar:

SELECT * FROM artigos;

Passo 2: Implementando a busca simples com LIKE

Uma busca simples em SQL usa LIKE ou ILIKE (case-insensitive):

SELECT id, titulo, descricao
FROM artigos
WHERE titulo ILIKE '%PostgreSQL%' OR descricao ILIKE '%PostgreSQL%';

Problemas com o LIKE:

É case-sensitive (exceto ILIKE).
Não oferece relevância ou ranqueamento.
Torna-se lento com grandes volumes de dados, pois não há índice.

Passo 3: Configurando a busca de texto completo (FTS)

Como falei no início em relação ao ES, o PostgreSQL possui recursos nativos para busca de texto completo. Aqui estão os dois principais:

to_tsvector: Converte um texto em um vetor de busca (documento pesquisável).
to_tsquery: Converte um texto em uma query para busca.

Exemplo básico:

SELECT * 
FROM artigos
WHERE to_tsvector('portuguese', titulo || ' ' || descricao) 
      @@ to_tsquery('portuguese', 'Rails');

Aqui, buscamos pela palavra Rails nos títulos e descrições.

Passo 4: Adicionando relevância com ts_rank

O ts_rank calcula a relevância da busca, permitindo ranquear os resultados.

SELECT id, titulo, descricao,
       ts_rank(to_tsvector('portuguese', titulo), to_tsquery('portuguese', 'PostgreSQL')) AS rank_titulo,
       ts_rank(to_tsvector('portuguese', descricao), to_tsquery('portuguese', 'PostgreSQL')) AS rank_descricao
FROM artigos
WHERE to_tsvector('portuguese', titulo || ' ' || descricao) 
      @@ to_tsquery('portuguese', 'PostgreSQL')
ORDER BY rank_titulo DESC, rank_descricao DESC;

Passo 5: Usando GIN e sendo feliz com sua performance!

Adicionando um índice GIN acelera a busca significativamente:

CREATE INDEX idx_artigos_fts 
ON artigos USING GIN (to_tsvector('portuguese', titulo || ' ' || descricao));

Agora a busca utiliza o índice GIN para maior performance!

Passo 6: Adicionando busca aproximada com pg_trgm

Para permitir buscas com erros de digitação (reann), usamos a extensão pg_trgm e a função SIMILARITY:

Habilite a extensão:

CREATE EXTENSION IF NOT EXISTS pg_trgm;

Combine busca FTS com busca aproximada:

SELECT id, titulo, descricao,
       ts_rank(to_tsvector('portuguese', titulo), to_tsquery('portuguese', 'PostgreSQL')) AS rank_titulo,
       similarity('PostgreSQL', titulo || ' ' || descricao) AS similaridade
FROM artigos
WHERE to_tsvector('portuguese', titulo || ' ' || descricao) 
      @@ to_tsquery('portuguese', 'PostgreSQL')
   OR similarity('PostgreSQL', titulo || ' ' || descricao) > 0.2
ORDER BY rank_titulo DESC, similaridade DESC;

O resultado final: Busca de texto completo com ranking e similaridade

SELECT id, titulo, descricao,
       ts_rank(to_tsvector('portuguese', titulo), to_tsquery('portuguese', 'SQL')) AS rank_titulo,
       ts_rank(to_tsvector('portuguese', descricao), to_tsquery('portuguese', 'SQL')) AS rank_descricao,
       similarity('SQL', titulo || ' ' || descricao) AS similaridade
FROM artigos
WHERE to_tsvector('portuguese', titulo || ' ' || descricao) 
      @@ to_tsquery('portuguese', 'SQL')
   OR similarity('SQL', titulo || ' ' || descricao) > 0.2
ORDER BY rank_titulo DESC, rank_descricao DESC, similaridade DESC;

Conclusão

Antes de colocar inúmeras ferramentas para resolver problema de Busca Completa, tente utilizar isso na sua aplicação, pois na maioria esmagadora das vezes resolverá os problemas. Com o uso de índices GIN e a extensão pg_trgm, conseguimos um sistema de busca rápido e eficiente. 🚀

fontes:

EN:

Full-text search (FTS) in PostgreSQL is powerful, fast, and easy to configure, eliminating the need for external tools like Elasticsearch in many scenarios. In fact, I had never implemented this functionality before and initially thought about using ES and even asked colleagues about it. But the problem with ES is that it can be "too much" and expensive for what you need!

Step 1: Creating the table and populating with data

First, I create a simple table and add data to perform our searches: Articles with columns id, title, and description!

CREATE TABLE articles (
  id SERIAL PRIMARY KEY,
  title VARCHAR(80) NOT NULL,
  description VARCHAR(200) NOT NULL
);

INSERT INTO articles (title, description) VALUES
  ('Learn PostgreSQL', 'A complete guide on PostgreSQL'),
  ('Introduction to SQL', 'Understand the fundamentals of SQL'),
  ('Text search in Postgres', 'How to implement text search in PostgreSQL'),
  ('Programming in Java', 'A basic Java course'),
  ('Web development with Rails', 'Learn to create applications with Rails'),
  ('Advanced SQL Course', 'Master SQL with practical examples'),
  ('Machine Learning', 'Introduction to Machine Learning');

Running the query to verify:

SELECT * FROM articles;

Step 2: Implementing a simple search with LIKE

A simple search in SQL uses LIKE or ILIKE (case-insensitive):

SELECT id, title, description
FROM articles
WHERE title ILIKE '%PostgreSQL%' OR description ILIKE '%PostgreSQL%';

Problems with LIKE:

It is case-sensitive (except ILIKE).
It does not offer relevance or ranking.
It becomes slow with large volumes of data since there is no index.

Step 3: Configuring full-text search (FTS)

As I mentioned earlier about ES, PostgreSQL has native features for full-text search. Here are the two main ones:

to_tsvector: Converts a text into a search vector (searchable document).
to_tsquery: Converts a text into a search query.

Example:

SELECT * 
FROM articles
WHERE to_tsvector('english', title || ' ' || description) 
      @@ to_tsquery('english', 'Rails');

Here, we search for the word "Rails" in titles and descriptions.

Step 4: Adding relevance with ts_rank

ts_rank calculates the search relevance, allowing us to rank the results.

SELECT id, title, description,
       ts_rank(to_tsvector('english', title), to_tsquery('english', 'PostgreSQL')) AS rank_title,
       ts_rank(to_tsvector('english', description), to_tsquery('english', 'PostgreSQL')) AS rank_description
FROM articles
WHERE to_tsvector('english', title || ' ' || description) 
      @@ to_tsquery('english', 'PostgreSQL')
ORDER BY rank_title DESC, rank_description DESC;

Step 5: Using GIN and being happy with its performance!

Adding a GIN index significantly speeds up the search:

CREATE INDEX idx_articles_fts 
ON articles USING GIN (to_tsvector('english', title || ' ' || description));

Now the search uses the GIN index for better performance!

Step 6: Adding approximate search with pg_trgm

To allow searches with typos (reann), we use the pg_trgm extension and the SIMILARITY function:

Enable the extension:

CREATE EXTENSION IF NOT EXISTS pg_trgm;

Combine FTS with approximate search:

SELECT id, title, description,
       ts_rank(to_tsvector('english', title), to_tsquery('english', 'PostgreSQL')) AS rank_title,
       similarity('PostgreSQL', title || ' ' || description) AS similarity
FROM articles
WHERE to_tsvector('english', title || ' ' || description) 
      @@ to_tsquery('english', 'PostgreSQL')
   OR similarity('PostgreSQL', title || ' ' || description) > 0.2
ORDER BY rank_title DESC, similarity DESC;

Result: Full-text search with ranking and similarity

SELECT id, title, description,
       ts_rank(to_tsvector('english', title), to_tsquery('english', 'SQL')) AS rank_title,
       ts_rank(to_tsvector('english', description), to_tsquery('english', 'SQL')) AS rank_description,
       similarity('SQL', title || ' ' || description) AS similarity
FROM articles
WHERE to_tsvector('english', title || ' ' || description) 
      @@ to_tsquery('english', 'SQL')
   OR similarity('SQL', title || ' ' || description) > 0.2
ORDER BY rank_title DESC, rank_description DESC, similarity DESC;

Conclusion

Before introducing countless tools to solve the full-text search problem, try using this in your application, as in the overwhelming majority of cases, it will solve the issues. With the use of GIN indexes and the pg_trgm extension, we achieve a fast and efficient search system. 🚀

Sources:

Adding Redis easily with Docker

Renan Maringolo — Thu, 15 Jul 2021 19:44:18 +0000

Overview

In this chapter, i'll walk you through the installation, configuration and how to use Redis in a simple way. In this process, i'll use Docker to create a container with the Redis image.

Requirements

Docker

Installation & Configuration

In this first step, you need to install Docker on your machine by following the steps below.

1.Update Software Repositories:

sudo apt-get update

2.Install some prerequisite packages that let apt use packages over HTTPS:

sudo apt install apt-transport-https ca-certificates curl software-properties-common

3.So, add GPG Key to repository Docker on your SO:

curl -fsSL https://download.docker.com/linux/ubuntu/gpg | sudo apt-key add -

4.Add the repository Docker to fonts of the APT:

sudo add-apt-repository "deb [arch=amd64] https://download.docker.com/linux/ubuntu focal stable"

5.Again:

sudo apt update

6.Finally, install Docker:

sudo apt-get install docker-ce docker-ce-cli containerd.io

Check if the Docker was installed correctly:

sudo docker run hello-world

Executing the command Docker without sudo:

sudo usermod -aG docker ${USER}

Working with images from Docker

Docker containers are built with Docker images. By default, it searches from Docker Hub, where the image is registered.

docker run --name redis-tutorial -p 6379:6379 -d redis

Explanation of this command. For the creation of a container, we need an image, if there is no image already created, the run command will search the internet and download it to your host (which is your local machine) and automatically create a container. The --name command allows you to name the container. The -p command maps port 6379 on the container (right side) to port 6379 on the host (left side). The -d command runs the container in the background. The last command is the name of the image it should fetch from the remote repository redis.

Okay, if all went well, you should find your container. To do this is very simple! The docker ps command lists all containers that are active, and you should have found something like this:

CONTAINER ID| IMAGE| COMMAND| CREATED| STATUS| PORTS| NAMES
763fa8c3e7ca| redis| "docker-entrypoint.s…"| 2 hours ago| Up 2 hours| 0.0.0.0:6379->6379/tcp, :::6379->6379/tcp| redis-tutorial

In the first column is the Container ID, in the second column is image (redis, postgres, ruby...), in the third the command it runs in the background, in the fourth and fifth, the date the container was created and respectively the status in which he finds himself. Right at the end, we have the port and the connection protocol that was made and lastly you must remember, it is the name that we defined in the previous command.

Well, now that we have a container running in the background and we know its name, we need to go in, after all, the container is a separate process from the operating system and if we want to run things in there, we need to tell the docker that we want it, right ?
The exec command executes something inside the container and there are several modes and types of commands for this, but for now, let's just go into the container! Run:

docker exec -it redis-tutorial bash

explanation of this command line: exec allows you to execute commands in the container without the need to be inside it. -it is a way to associate your terminal and interact with the container. bash will cause us to enter the container's BASH.

Now that we are inside the container with a Redis image, we can run the Redis CLI:

redis-cli.

Try Redis commands, for example: hmset student:xxx id 1 college nnn address xn. If the return is OK, it means everything is fine and you can now play with Redis :)