Aula 13: Índices no PostgreSQL — B-tree, GiST, GIN, BRIN e Hash

Nesta aula, exploraremos detalhadamente os índices no PostgreSQL, uma funcionalidade vital para otimizar consultas e garantir o desempenho eficiente dos bancos de dados. Você aprenderá sobre os diversos tipos de índices disponíveis como B-tree, GiST, GIN, BRIN e Hash, entendendo suas diferenças e aplicações práticas. Ao final desta aula, você estará apto a criar índices eficazmente, interpretar suas estruturas e resolver problemas comuns relacionados. Esta habilidade é essencial para administradores de banco de dados, desenvolvedores e profissionais de TI que buscam maximizar a performance de suas aplicações. Nos projetos da JRT Technology Solutions, nossos especialistas utilizam índices diariamente para otimizar sistemas críticos implementados para nossos clientes.

O que você vai aprender nesta aula

Conceitos fundamentais de índices no PostgreSQL
Criação e gerenciamento de índices B-tree, GiST, GIN, BRIN e Hash
Questões de desempenho associadas a diferentes tipos de índices
Testagem e verificação de criação e eficácia de índices
Resolução de problemas comuns relacionados a índices

Pré-requisitos e Ambiente

Antes de iniciar, você deve ter o PostgreSQL instalado e funcionando em sua máquina. É preferível que você já tenha conhecimentos básicos sobre comandos SQL e tenha completado as aulas anteriores deste curso. Vamos cobrir procedimentos em sistemas baseados em Ubuntu/Debian e CentOS/RHEL/Rocky Linux.

Conceitos Fundamentais sobre Índices no PostgreSQL

No PostgreSQL, os índices são usados para aumentar a velocidade das operações de recuperação de dados. Um índice é uma estrutura separada que armazena um subconjunto dos dados de uma tabela, permitindo que a recuperação ocorra mais rapidamente em comparação com uma varredura sequencial através da tabela.

Os tipos de índices no PostgreSQL incluem:

B-tree: O tipo de índice padrão, ideal para consultas de correspondência exata e intervalos.
GiST: Suporta operadores complexos e é útil para dados geoespaciais e de pesquisa em extensão.
GIN: Útil para pesquisa de valores que contêm conjuntos de itens, como arrays ou textos de palavras.
BRIN: Frugal em espaço, ideal para grandes tabelas e permite operações de varredura rápidas dentro de intervalos.
Hash: Para buscas de igualdade, mas menos útil para operações de intervalo e não suportando replicação de alguma forma até versões recentes.

Passo a Passo — Criação de Índices no PostgreSQL

A seguir, vamos criar e gerenciar diferentes tipos de índices em suas tabelas no PostgreSQL. Este guia fornecerá passos para cada tipo de índice mencionado acima.

Conecte-se ao seu banco de dados PostgreSQL com o psql:
```
psql -U seu_usuario -d seu_banco_de_dados
```

Crie uma tabela de exemplo para ensaiar a criação de índices:

CREATE TABLE alunos (
    id SERIAL PRIMARY KEY,
    nome VARCHAR(100),
    idade INT,
    turma VARCHAR(10)
);

CREATE TABLE

Adicione alguns dados de exemplo:

INSERT INTO alunos (nome, idade, turma) VALUES
('Ana', 21, 'A1'),
('Bruno', 22, 'A2'),
('Carlos', 23, 'A3');

INSERT 0 3

Crie um índice B-tree com base no nome dos alunos:
```
CREATE INDEX idx_nome ON alunos(nome);
```
```
CREATE INDEX
```

Crie um índice GiST em um campo geométrico hipotético:


CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE INDEX idx_gist_nome ON alunos USING gist(id);

CREATE INDEX

Criar um índice GIN em um campo array:

ALTER TABLE alunos ADD COLUMN disciplinas TEXT[];
CREATE INDEX idx_gin_disciplinas ON alunos USING gin (disciplinas);

CREATE INDEX

Crie um índice BRIN na idade:

CREATE INDEX idx_brin_idade ON alunos USING brin(idade);

CREATE INDEX

Crie um índice Hash para pesquisa direta na turma:

CREATE INDEX idx_hash_turma ON alunos USING hash(turma);

CREATE INDEX

Verificando a Instalação / Testando a Configuração

Após criar os índices, é crucial verificar se eles estão funcionando conforme o esperado. Você pode listar todos os índices de uma tabela com o seguinte comando:

SELECT indexname FROM pg_indexes WHERE tablename = 'alunos';


 indexname
-----------
 idx_nome
 idx_gist_nome
 idx_gin_disciplinas
 idx_brin_idade
 idx_hash_turma
(5 rows)

Erros Comuns e Como Resolver

Erro 1: “No such file or directory”
Causa: Tentativa de criar um índice sem a extensão necessária instalada.

Solução: Use CREATE EXTENSION IF NOT EXISTS antes de criar índices especiais.
Erro 2: “Index type hash does not exist”
Causa: O uso de índices Hash em versões mais antigas do PostgreSQL não suportava criação direta em tabelas replicadas.

Solução: Atualize para uma versão do PostgreSQL que suporte totalmente índices Hash.
Erro 3: “Access exclusive lock”
Causa: Tentar criar um índice quando a tabela está em uso intensa.

Solução: Tente criar o índice durante um período de baixa atividade ou considere o uso de CREATE INDEX CONCURRENTLY.
Erro 4: “Column does not exist”
Causa: Referência a uma coluna que não foi criada ou foi mal especificada.

Solução: Verifique a sintaxe e assegure que a coluna exista na tabela alvo.

Boas Práticas e Dicas Avançadas

Utilizar índices de forma eficaz pode resultar em grandes melhorias no desempenho. Aqui estão algumas dicas de boas práticas:

Analise a frequência das consultas para decidir quais índices criar.
Evite criar muitos índices em uma tabela, pois isso pode diminuir a performance de operações de gravação.
Use o comando EXPLAIN para entender como um índice está sendo utilizado em consultas.
Mantenha os índices atualizados, especialmente após grandes cargas de dados.

Resumo da Aula 13

Hoje cobrimos a importância e a implementação de diferentes tipos de índices no PostgreSQL, incluindo B-tree, GiST, GIN, BRIN e Hash. Aprendemos sobre as melhores ocasiões para usar cada tipo de índice e como sua aplicação pode resultar em melhorias significativas de desempenho em consultas. A JRT Technology Solutions continua comprometida em oferecer suporte e serviços de treinamento para otimizar suas soluções em PostgreSQL. Na próxima aula, vamos explorar as técnicas avançadas de otimização de consultas em PostgreSQL, uma habilidade crítica para qualquer DBA ou desenvolvedor que lide com grandes volumes de dados.

Quer aprender na prática com especialistas?

A JRT Technology Solutions oferece treinamentos e implementação de PostgreSQL para equipes corporativas.

Falar no WhatsApp