Criando índices ( CREATE INDEX) e parâmetros de indexação ( UNIQUE, CONCURRENTLY)

A gente já falou várias vezes como os índices deixam as buscas mais rápidas e ajudam o banco a não ter que varrer tudo. Agora chegou a hora de entender como eles são criados, quais são os parâmetros do comando CREATE INDEX e quando vale a pena usar opções tipo UNIQUE ou CONCURRENTLY. Tudo isso é importante se você quer não só usar índices, mas também gerenciar eles de um jeito esperto.

Sintaxe do CREATE INDEX

Você pode criar um índice usando o comando CREATE INDEX. Olha só a sintaxe básica:

CREATE INDEX index_name
ON table_name (column_name);

• index_name — Nome do índice. O ideal é que ele mostre pra que serve o índice, tipo idx_users_email pra um índice na coluna email da tabela users.
• table_name — Nome da tabela onde o índice vai ser criado.
• column_name — Coluna que vai ser indexada.

Bora ver um exemplo simples. Imagina que a gente tem uma tabela users:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(255),
    age INT
);

A gente quer deixar a busca de usuários pelo campo email mais rápida. Então, bora criar um índice:

CREATE INDEX idx_users_email
ON users (email);

Agora, quando você rodar queries tipo:

SELECT * FROM users WHERE email = 'example@example.com';

O PostgreSQL vai usar o índice idx_users_email pra achar rapidinho a linha certa.

Índices únicos (UNIQUE)

Um índice único garante que os valores na coluna (ou colunas) escolhida vão ser únicos. Se você tentar inserir um valor repetido, o PostgreSQL não vai deixar.

Índices únicos são muito usados pra chaves, tipo email, username ou outros identificadores que não podem se repetir.

Sintaxe pra criar índice único

A sintaxe pra criar um índice único é quase igual à de um índice normal, só que com a palavra UNIQUE:

CREATE UNIQUE INDEX index_name
ON table_name (column_name);

Imagina que na nossa tabela users o campo email tem que ser único, pra não ter dois usuários com o mesmo endereço. Olha como faz:

CREATE UNIQUE INDEX idx_users_email_unique
ON users (email);

Agora, se você tentar rodar, por exemplo:

INSERT INTO users (name, email, age) VALUES ('John', 'john@example.com', 30);
INSERT INTO users (name, email, age) VALUES ('Jane', 'john@example.com', 25);

O PostgreSQL vai dar erro, porque email tem que ser único.

Criando índices com o parâmetro CONCURRENTLY

Imagina que você tá mexendo numa tabela gigante em produção, onde sempre tem operação rolando (tipo inserção de dados novos). Criar um índice do jeito padrão (CREATE INDEX) trava a tabela, não deixando outros comandos de inserir, atualizar ou deletar dados. Isso pode ser um desastre pra um sistema que tá rodando. Pra evitar isso, dá pra criar o índice "de boa", usando o parâmetro CONCURRENTLY.

Sintaxe

CREATE INDEX CONCURRENTLY index_name
ON table_name (column_name);

A palavra CONCURRENTLY diz pro PostgreSQL criar o índice em paralelo, sem travar a tabela.

Imagina que a gente tem uma tabela orders com milhões de registros e que tá sempre recebendo pedidos novos:

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    order_number VARCHAR(50) NOT NULL,
    order_date DATE NOT NULL,
    customer_id INT NOT NULL
);

Você quer criar um índice pra acelerar buscas por order_date, mas sem travar a tabela:

CREATE INDEX CONCURRENTLY idx_orders_order_date
ON orders (order_date);

Agora o banco vai criar o índice sem travar a tabela, e a galera nem vai perceber que tá rolando esse processo.

Entre as particularidades do CONCURRENTLY tem o seguinte:

1. O índice é criado mais devagar do que no modo normal, porque o PostgreSQL faz isso em várias etapas.
2. Se rolar erro na criação do índice (tipo por causa de dados duplicados), você vai ter que apagar ele na mão e criar de novo.

Parâmetros extras de indexação

O PostgreSQL deixa você adicionar outros parâmetros quando cria índices. Por exemplo, dá pra indexar várias colunas ao mesmo tempo. Isso é útil quando você faz muitas queries filtrando por mais de um campo.

CREATE INDEX idx_users_name_email
ON users (name, email);

Agora as queries com WHERE name = 'John' AND email = 'john@example.com' vão rodar mais rápido.

Dois índices em uma coluna não é a mesma coisa que um índice em duas colunas! O índice em várias colunas acelera buscas onde o WHERE usa todos esses campos juntos.

Exemplos de erros e como resolver

Na hora de criar índices, você pode topar com alguns erros. Olha os mais comuns:

Erro de inserção de duplicados ao criar índice único. Se já tiver linhas duplicadas na tabela, o PostgreSQL não vai conseguir criar o índice único. Nesse caso, tem que apagar ou corrigir os duplicados primeiro.

DELETE FROM users
WHERE email IN (
    SELECT email
    FROM users
    GROUP BY email
    HAVING COUNT(email) > 1
);

Erro de bloqueio ao criar índices. Se você usar a criação normal de índice num banco em produção, os clientes podem sentir lentidão ou até falhas. Usa o parâmetro CONCURRENTLY pra evitar esse perrengue.

Agora imagina que você trabalha numa empresa e te deram a missão de otimizar um banco de dados com milhões de registros. Você pode usar índices pra achar os gargalos e deixar a experiência do usuário muito mais rápida. Por exemplo, colocando o índice certo, você pode reduzir o tempo de uma query de 10 segundos pra alguns milissegundos. Fala sério, isso é muito massa!