Introdução
A otimização do desempenho do PostgreSQL é essencial para aplicações à escala. Consultas lentas, índices em falta e uma configuração inadequada podem comprometer seriamente o desempenho da aplicação. Engenheiros seniores devem compreender o planeamento de consultas, estratégias de indexação e afinação da configuração para manter sistemas responsivos.
Analisar o Desempenho das Consultas
EXPLAIN ANALYZE
EXPLAIN ANALYZE SELECT * FROM orders
WHERE user_id = 123
AND created_at > '2024-01-01'
ORDER BY created_at DESC
LIMIT 10;
Interpretação do output:
- Seq Scan: Varredura completa da tabela (frequentemente mau para tabelas grandes)
- Index Scan: Utilização de índice (bom)
- Index Only Scan: Melhor — dados apenas a partir do índice
- Bitmap Heap Scan: Combina múltiplas condições de índice
- actual time: Tempo real de execução
- rows: Linhas efetivamente processadas
Identificar Consultas Lentas
-- Ativar o registo de consultas lentas em postgresql.conf
-- log_min_duration_statement = 1000 -- Registar consultas > 1 segundo
-- Encontrar consultas atualmente em execução
SELECT pid, now() - query_start AS duration, state, query
FROM pg_stat_activity
WHERE state <> 'idle'
AND query NOT LIKE '% FROM pg_stat_activity %'
ORDER BY duration DESC;
-- Encontrar consultas de longa duração
SELECT datname, pid, state, query, age(clock_timestamp(), query_start) AS age
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY age DESC;
Terminar Consultas de Longa Duração
-- Cancelar consulta (de forma graciosa)
SELECT pg_cancel_backend(pid);
-- Terminar ligação (forçado)
SELECT pg_terminate_backend(pid);
Estratégias de Indexação
Índices Básicos
-- Índice de coluna única
CREATE INDEX idx_orders_user_id ON orders(user_id);
-- Índice composto (a ordem das colunas importa!)
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);
-- Índice único
CREATE UNIQUE INDEX idx_users_email ON users(email);
Índices Parciais
Indexar apenas as linhas que correspondem a uma condição:
-- Indexar apenas utilizadores ativos
CREATE INDEX idx_active_users ON users(email)
WHERE status = 'active';
-- Indexar apenas encomendas recentes
CREATE INDEX idx_recent_orders ON orders(user_id, created_at)
WHERE created_at > '2024-01-01';
Índices de Expressão
-- Índice em email em minúsculas
CREATE INDEX idx_users_email_lower ON users(LOWER(email));
-- Índice no ano extraído
CREATE INDEX idx_orders_year ON orders(EXTRACT(YEAR FROM created_at));
Índices de Cobertura (INCLUDE)
Incluir colunas não indexadas para permitir index-only scans:
CREATE INDEX idx_orders_user ON orders(user_id)
INCLUDE (total, status, created_at);
-- A consulta pode agora ser satisfeita inteiramente a partir do índice
SELECT total, status, created_at FROM orders WHERE user_id = 123;
Quando Usar Diferentes Tipos de Índice
| Tipo de Índice | Caso de Uso |
|---|
| B-tree | Predefinição, consultas de igualdade e de intervalo |
| Hash | Apenas igualdade (raramente melhor do que B-tree) |
| GIN | Pesquisa full-text, JSONB, arrays |
| GiST | Dados geométricos, pesquisa full-text |
| BRIN | Tabelas grandes com ordenação natural |
Indexação de JSONB
-- Índice GIN para consultas de contenção JSONB
CREATE INDEX idx_products_metadata ON products USING gin(metadata);
-- Consulta: WHERE metadata @> '{"category": "electronics"}'
-- Indexar um caminho JSONB específico
CREATE INDEX idx_products_category ON products((metadata->>'category'));
Otimização de Consultas
Evitar SELECT *
-- Mau
SELECT * FROM orders WHERE user_id = 123;
-- Bom
SELECT id, total, status, created_at FROM orders WHERE user_id = 123;
Usar LIMIT com ORDER BY
-- Mau: Ordena todas as linhas
SELECT * FROM orders ORDER BY created_at DESC;
-- Bom: Para cedo com índice
SELECT * FROM orders ORDER BY created_at DESC LIMIT 10;
Paginação Eficiente
-- Mau: OFFSET varre e descarta linhas
SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 10000;
-- Bom: Paginação por keyset
SELECT * FROM orders
WHERE id < 12345 -- Último ID da página anterior
ORDER BY id DESC
LIMIT 20;
Operações em Lote
-- Mau: Muitas transações pequenas
INSERT INTO logs (message) VALUES ('log 1');
INSERT INTO logs (message) VALUES ('log 2');
-- ... mais 1000
-- Bom: Uma única transação com múltiplas linhas
INSERT INTO logs (message) VALUES
('log 1'), ('log 2'), ('log 3'), ...;
-- Ou usar COPY para carregamentos em lote
COPY logs(message) FROM '/path/to/data.csv' CSV;
Evitar N+1 no Código da Aplicação
-- Em vez de consultar utilizadores e depois encomendas separadamente,
-- usar JOIN ou LATERAL JOIN
SELECT u.*, o.*
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.id IN (1, 2, 3);
Otimização de PostGIS
Consultas de Geometria
-- Armazenar ponto
INSERT INTO locations (name, coordinates)
VALUES ('Office', ST_SetSRID(ST_MakePoint(-73.935242, 40.730610), 4326));
-- Consultar como GeoJSON
SELECT ST_AsGeoJSON(ST_Transform(coordinates, 3857))
FROM locations;
-- Encontrar pontos dentro do raio
SELECT * FROM locations
WHERE ST_DWithin(
coordinates::geography,
ST_MakePoint(-73.935, 40.730)::geography,
1000 -- metros
);
-- Índice espacial (essencial!)
CREATE INDEX idx_locations_geo ON locations USING gist(coordinates);
Afinação de Configuração
Definições de Memória
# postgresql.conf
# Memória partilhada para caching (25% da RAM)
shared_buffers = 4GB
# Memória por operação (sort, hash)
work_mem = 256MB
# Memória para operações de manutenção
maintenance_work_mem = 1GB
# Estimativa do query planner da cache
effective_cache_size = 12GB
Definições de Ligações
# Máximo de ligações
max_connections = 200
# Pooling de ligações (usar PgBouncer para melhor escalabilidade)
Desempenho de Escrita
# Definições de WAL para cargas de trabalho com muitas escritas
wal_buffers = 64MB
checkpoint_completion_target = 0.9
max_wal_size = 4GB
Manutenção de Tabelas
VACUUM e ANALYZE
-- Atualizar estatísticas (ajuda o query planner)
ANALYZE orders;
-- Recuperar espaço de linhas eliminadas
VACUUM orders;
-- Limpeza completa (bloqueia a tabela)
VACUUM FULL orders;
-- Definições de autovacuum
-- autovacuum = on (predefinição)
REINDEX
-- Reconstruir índice inchado
REINDEX INDEX idx_orders_user_id;
-- Reconstruir todos os índices na tabela
REINDEX TABLE orders;
-- Concurrently (sem lock, PostgreSQL 12+)
REINDEX INDEX CONCURRENTLY idx_orders_user_id;
Estatísticas da Tabela
-- Verificar o tamanho da tabela
SELECT pg_size_pretty(pg_total_relation_size('orders'));
-- Verificar o tamanho do índice
SELECT pg_size_pretty(pg_relation_size('idx_orders_user_id'));
-- Estimativa de bloat da tabela
SELECT schemaname, tablename,
pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) as size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC;
Pooling de Ligações
Configuração do PgBouncer
# pgbouncer.ini
[databases]
mydb = host=localhost port=5432 dbname=mydb
[pgbouncer]
listen_port = 6432
listen_addr = *
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20
Monitorização
Consultas Essenciais
-- Estatísticas de utilização de índices
SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;
-- Índices não utilizados (candidatos a remoção)
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND schemaname = 'public';
-- Padrões de acesso à tabela
SELECT schemaname, relname, seq_scan, idx_scan,
n_tup_ins, n_tup_upd, n_tup_del
FROM pg_stat_user_tables
ORDER BY seq_scan DESC;
-- Taxa de acertos na cache (deve ser > 99%)
SELECT
sum(heap_blks_read) as heap_read,
sum(heap_blks_hit) as heap_hit,
sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as ratio
FROM pg_statio_user_tables;
Manipulação de JSONB
Renomear Propriedades de Campos JSON
-- Exemplo: Renomear «wrongname» para «name» em JSONB aninhado
UPDATE products AS p
SET metadata = (
jsonb_set(
p.metadata::jsonb #- '{objectfield,wrongname}',
'{objectfield,name}',
p.metadata::jsonb #> '{objectfield,wrongname}'
)
)::json
WHERE p.metadata::jsonb ? 'objectfield'
AND p.metadata::jsonb #> '{objectfield}' ? 'wrongname';
Consultar Arrays JSONB
-- Encontrar itens em que o array contém um valor específico
SELECT * FROM products
WHERE metadata->'tags' ? 'featured';
-- Consultar elementos de array aninhados
SELECT * FROM products
WHERE metadata @> '{"categories": [{"name": "electronics"}]}';
-- Agregar elementos de array JSONB
SELECT id, jsonb_array_elements(metadata->'variants') as variant
FROM products;
Anonimização de Dados
Para ambientes de desenvolvimento/staging, anonimizar dados de produção:
-- Anonimizar dados de utilizadores para uso não produtivo
UPDATE users SET
first_name = SUBSTR(MD5(RANDOM()::text), 1, 2 + FLOOR(3*RANDOM())::int),
last_name = SUBSTR(MD5(RANDOM()::text), 1, 4 + FLOOR(4*RANDOM())::int),
address = CONCAT(
SUBSTR(MD5(RANDOM()::text), 1, 5 + FLOOR(5*RANDOM())::int), ' ',
SUBSTR(MD5(RANDOM()::text), 1, 2 + FLOOR(2*RANDOM())::int), ', ',
FLOOR(100*RANDOM())::int, ', ',
SUBSTR(MD5(RANDOM()::text), 1, 2 + FLOOR(3*RANDOM())::int)
),
email = CONCAT(
SUBSTR(MD5(RANDOM()::text), 1, 5 + FLOOR(5*RANDOM())::int), '@',
SUBSTR(MD5(RANDOM()::text), 1, 2 + FLOOR(3*RANDOM())::int), '.test'
),
phone = CONCAT('+1', FLOOR(1000000000 + 8999999999*RANDOM())::bigint);
Encontrar Tabelas Grandes
Identificar as tabelas que consomem mais armazenamento:
SELECT
schemaname AS schema,
tablename AS table,
pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total_size,
pg_size_pretty(pg_relation_size(schemaname || '.' || tablename)) AS table_size,
pg_size_pretty(pg_indexes_size(schemaname || '.' || tablename)) AS indexes_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC
LIMIT 20;
Gestão de Chaves Estrangeiras
Desativar Temporariamente para Operações em Lote
-- Desativar verificações de chaves estrangeiras (usar com cautela!)
SET session_replication_role = 'replica';
-- Executar operações em lote
TRUNCATE TABLE orders CASCADE;
-- Ou migrações de dados complexas...
-- Reativar verificações de chaves estrangeiras
SET session_replication_role = 'origin';
Checklist de Boas Práticas
- [ ] Indexar colunas usadas em WHERE, JOIN, ORDER BY
- [ ] Usar EXPLAIN ANALYZE para consultas lentas
- [ ] Implementar pooling de ligações
- [ ] Configurar shared_buffers de forma adequada
- [ ] Executar ANALYZE após alterações de dados em lote
- [ ] Monitorizar e remover índices não utilizados
- [ ] Usar paginação por keyset para grandes conjuntos de dados
- [ ] Manter as estatísticas atualizadas
- [ ] Usar índices GIN para consultas JSONB
- [ ] Anonimizar dados em ambientes não produtivos
Conclusão
A otimização do desempenho do PostgreSQL requer compreender a execução de consultas, indexação adequada e afinação da configuração. Comece com EXPLAIN ANALYZE para identificar estrangulamentos, adicione os índices apropriados e afine a configuração com base na carga de trabalho. A monitorização regular garante que o desempenho não se degrada ao longo do tempo.
