SQLite VACUUM e ANALYZE: estatísticas e espaço em disco

Dois trabalhos de manutenção diferentes

ANALYZE e VACUUM aparecem juntos com frequência, mas resolvem problemas distintos.

• O ANALYZE coleta estatísticas sobre seus dados para que o planejador de consultas tome decisões mais inteligentes. Ele grava numa tabela chamada sqlite_stat1 e não mexe nas suas linhas.
• O VACUUM reconstrói o arquivo do banco para recuperar páginas não usadas e desfragmentar o armazenamento. Ele não altera planos de consulta diretamente.

Se as consultas estão escolhendo o índice errado, você quer ANALYZE. Se o arquivo ficou muito maior do que deveria depois de muitos DELETE, você quer VACUUM. Misturar os dois é receita pra desperdiçar tempo de manutenção.

O que o ANALYZE faz na prática

O planejador de consultas precisa adivinhar. Quando ele vê algo como WHERE status = 'active', precisa estimar quantas linhas batem com a condição — uma? um milhão? — pra decidir se usa um índice ou faz uma varredura completa na tabela. Sem estatísticas, ele recorre a heurísticas bem grosseiras.

O ANALYZE percorre cada índice e registra informações resumidas sobre como os valores estão distribuídos:

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  status TEXT,
  customer_id INTEGER
);

CREATE INDEX idx_orders_status ON orders(status);

INSERT INTO orders(status, customer_id) VALUES
  ('shipped', 1), ('shipped', 2), ('shipped', 3),
  ('shipped', 4), ('pending', 5);

ANALYZE;

SELECT * FROM sqlite_stat1;

A linha do sqlite_stat1 informa ao planejador quantas linhas o índice tem (mais ou menos) e quantos duplicados existem por chave típica. Na próxima vez que você rodar WHERE status = 'pending', ele já sabe que pending é raro e vai usar o índice; já em WHERE status = 'shipped', pode preferir um scan, que sai mais barato.

Dá pra analisar uma tabela ou índice específico em vez do banco inteiro:

ANALYZE orders;
ANALYZE idx_orders_status;

Rode ANALYZE depois de cargas em massa, após grandes mudanças de schema, ou quando perceber que o planner está escolhendo planos ruins em tabelas cuja distribuição mudou.

PRAGMA optimize: o padrão moderno

Disparar ANALYZE cegamente sempre que uma conexão fecha é desperdício — na maioria das vezes, nada mudou o suficiente para justificar. O SQLite já oferece um wrapper mais inteligente:

PRAGMA optimize;

PRAGMA optimize verifica o que mudou no banco desde a última análise e roda o ANALYZE apenas nas tabelas que precisam. A recomendação oficial é chamá-lo em cada conexão de vida longa logo antes de fechá-la, e periodicamente em conexões que ficam abertas por horas.

É barato quando nada mudou e eficaz quando algo mudou. Comece pelo optimize; só recorra ao ANALYZE puro quando precisar forçar uma atualização.

O que o VACUUM realmente faz no SQLite

Quando você apaga linhas ou dropa uma tabela, o SQLite marca aquelas páginas como livres, mas não encolhe o arquivo. Essas páginas livres são reaproveitadas em inserts futuros, então na maior parte do tempo isso não é problema. Só que duas coisas vão se acumulando ao longo de uma vida inteira de mexidas no banco:

1. Espaço livre que o sistema operacional não enxerga. Seu arquivo .db continua com 2 GB mesmo que só 800 MB sejam dados de verdade.
2. Fragmentação. As linhas de uma mesma tabela acabam espalhadas por páginas não adjacentes, prejudicando o desempenho de varredura.

O VACUUM resolve os dois problemas copiando o banco inteiro para um arquivo novo, bem compactado, e substituindo o original:

CREATE TABLE logs (id INTEGER PRIMARY KEY, body TEXT);
WITH RECURSIVE n(value) AS (
    SELECT 1 UNION ALL SELECT value + 1 FROM n WHERE value < 1000
)
INSERT INTO logs(body) SELECT 'entrada ' || value FROM n;
DELETE FROM logs WHERE id < 900;

VACUUM;

SELECT count(*) FROM logs;

Depois do VACUUM, o arquivo fica do tamanho que ele teria se você tivesse inserido apenas as 100 linhas restantes do zero. Como efeito colateral, todos os rowids permanecem iguais e o layout em disco volta a ser contíguo.

Algumas coisas que você precisa saber antes de rodar:

• Ele precisa de um lock exclusivo no banco durante toda a operação. Nenhuma outra conexão consegue escrever.
• Ele exige mais ou menos o dobro do tamanho do banco em espaço livre em disco — o novo arquivo é construído ao lado do antigo.
• Não dá pra rodar dentro de uma transação, e ele dá erro se houver alguma transação ativa.
• Em bancos de vários GB, pode demorar bastante. Se planeje.

Quando rodar VACUUM no SQLite de fato

Para a maioria das aplicações: não rode, a menos que algo específico tenha mudado.

Boas razões para rodar VACUUM:

• Você acabou de dropar uma tabela grande ou apagou um lote enorme de linhas e quer reduzir o tamanho do banco SQLite.
• O banco vem sofrendo muita rotatividade há anos e as queries que fazem scan de tabelas estão mais lentas do que costumavam ser.
• Você vai distribuir o arquivo do banco junto com um release e quer deixá-lo o menor possível.

Razões ruins:

• "Só por garantia." Ele reescreve o arquivo inteiro todas as vezes. Não tem nada de seguro em fazer isso num sistema em produção.
• Depois de cada lote de deletes. As páginas liberadas seriam reaproveitadas de qualquer jeito.

auto_vacuum e VACUUM incremental

Se você quer que o SQLite gerencie as páginas livres automaticamente, você define o auto_vacuum na criação do banco — não dá pra mudar depois sem rodar um vacuum completo:

PRAGMA auto_vacuum = INCREMENTAL;

Três modos:

• NONE (padrão): páginas livres continuam no arquivo e são reaproveitadas em inserts futuros.
• FULL: todo commit que libera páginas trunca o arquivo na sequência. É prático, só que cada transação paga esse custo.
• INCREMENTAL: o SQLite mantém o controle das páginas livres, mas só as devolve ao sistema quando você pede:

PRAGMA auto_vacuum = INCREMENTAL;
CREATE TABLE t(x INTEGER);
WITH RECURSIVE n(value) AS (
    SELECT 1 UNION ALL SELECT value + 1 FROM n WHERE value < 1000
)
INSERT INTO t SELECT value FROM n;
DELETE FROM t;

PRAGMA incremental_vacuum(100);
PRAGMA freelist_count;

PRAGMA incremental_vacuum(N) devolve até N páginas livres para o sistema operacional — é rápido, não segura um lock exclusivo por muito tempo e dá pra agendar a execução sem dor de cabeça. É o meio-termo ideal para bancos com muita escrita que precisam se manter enxutos sem pagar o preço de um VACUUM completo.

VACUUM INTO: gerando uma cópia compacta

O VACUUM INTO grava uma cópia nova e compactada num arquivo separado, sem mexer no original:

VACUUM INTO 'backup.db';

Isso é genuinamente útil:

• Backups. O resultado é um snapshot consistente e totalmente compactado — sem páginas pela metade e sem .wal para se preocupar. Bem melhor do que copiar o arquivo com cp.
• Reduzir o tamanho do banco SQLite sem travar quem escreve por muito tempo. Você roda o vacuum num arquivo paralelo e depois faz a troca atômica. Os writers não ficam bloqueados durante todo o processo.
• Distribuição. Dá pra entregar uma cópia enxuta e desfragmentada do banco de desenvolvimento.

O arquivo de destino não pode existir. Se já existir, você toma um erro.

Receita prática de manutenção

Para um banco de aplicação típico:

-- Em cada conexão de longa duração, antes de fechar:
PRAGMA optimize;

-- Após uma carga em massa grande ou alteração de esquema:
ANALYZE;

-- Após excluir muitos dados e querer recuperar espaço em disco:
VACUUM;

-- Para backups:
VACUUM INTO '/backups/app-2026-04-23.db';

Se o banco vive recebendo writes e deletes e precisa ficar no ar 24/7, defina auto_vacuum = INCREMENTAL no momento da criação e dispare PRAGMA incremental_vacuum(N) de tempos em tempos — uma vez por dia, em horário de pouco movimento, costuma resolver.

Por que meu arquivo SQLite está tão grande?

Dois pragmas te mostram exatamente o que está acontecendo:

CREATE TABLE t(x INTEGER);
WITH RECURSIVE n(value) AS (
    SELECT 1 UNION ALL SELECT value + 1 FROM n WHERE value < 10000
)
INSERT INTO t SELECT value FROM n;
DELETE FROM t WHERE x > 100;

PRAGMA page_count;
PRAGMA freelist_count;
PRAGMA page_size;

• page_count × page_size = tamanho atual do arquivo.
• freelist_count × page_size = bytes desperdiçados em páginas não utilizadas.

Se freelist_count representa uma fração grande de page_count, rodar um VACUUM (ou incremental_vacuum) vai encolher o arquivo de forma perceptível. Se for pequena, o banco já está compactado de forma eficiente e o VACUUM não vai ajudar.

Armadilhas comuns

• Rodar VACUUM dentro de uma transação. Não dá. Faça commit antes.
• Esquecer que o VACUUM precisa de espaço livre em disco. Um banco de 10 GB precisa de mais uns ~10 GB livres para conseguir vacuumizar.
• Configurar auto_vacuum depois que já existem dados. Não tem efeito nenhum até o próximo VACUUM completo. Defina isso na criação do banco, se for o seu caso.
• Rodar ANALYZE esperando arquivos menores. Isso é trabalho do VACUUM.
• Rodar VACUUM esperando planos de consulta melhores. Isso é trabalho do ANALYZE.

Os dois comandos se complementam — nenhum substitui o outro.

A seguir: transações

Comandos de manutenção como o VACUUM deixam clara uma coisa que vínhamos tomando como certa: o modelo transacional do SQLite e o que ele bloqueia, e quando. O próximo capítulo começa por aí — como as transações funcionam, o que BEGIN / COMMIT / ROLLBACK realmente garantem e como usá-las para manter operações com múltiplos comandos atômicas.