SQL VACUUM e ANALYZE

A Ana guardou um histórico de 20 mil visitas ao site da loja dentro do loja.db, e o arquivo passou de 45 KB para mais de 1 MB. Depois ela apagou a tabela inteira... e o arquivo continuou com 1 MB. Onde foi parar o espaço? Nesta aula você vai ver dois comandos de manutenção do banco: o VACUUM ("aspirador de pó"), que arruma o arquivo por dentro e devolve o espaço vazio, e o ANALYZE ("analise"), que conta como os dados estão distribuídos para o SQLite escolher o caminho mais rápido em cada consulta.

O tamanho de um arquivo só existe no disco, então as medidas desta aula saíram do terminal da Ana, com o ls -l antes e depois de cada passo. Mas os dois comandos rodam também no editor do guia: lá dá para ver as páginas livres e as estatísticas do banco em memória, nos quadros "Resultado".

A ideia: o fichário com fichas em branco

Imagine o fichário de clientes de uma loja, com folhas numeradas. Quando um cliente sai, você apaga a ficha dele, mas a folha continua no fichário, em branco, esperando a ficha de um cliente novo. O fichário não fica mais fino. Para ele ficar fino de novo, alguém precisa tirar todas as fichas, jogar fora as folhas em branco e montar o fichário de novo, com as fichas bem juntinhas.

Por que o banco não joga fora as páginas vazias na hora? Porque é mais rápido reaproveitá-las: o próximo INSERT usa uma página da lista em vez de aumentar o arquivo. Na maioria dos bancos, esse é o comportamento padrão.

1. O espaço que não volta

Antes de começar, a Ana mede o arquivo e confere quantas páginas ele tem, e se a opção auto_vacuum (que você vai ver na seção 4) está ligada:

$ ls -l loja.db
-rw-r--r-- 1 ana ana 45056 out 18 11:14 loja.db
$ sqlite3 loja.db "PRAGMA auto_vacuum; PRAGMA page_count;"
0
11

São 45056 bytes: 11 páginas de 4096. O auto_vacuum está em 0, desligado, que é o padrão do SQLite para bancos novos e o que você vai encontrar na maioria dos bancos.

Agora o histórico de visitas: uma tabela nova com 20 mil linhas, criadas de uma vez com um WITH RECURSIVE que conta de 1 a 20000 (veja CTE Recursiva):

$ sqlite3 loja.db
SQLite version 3.50.4 2025-07-30 19:33:53
Enter ".help" for usage hints.
sqlite> CREATE TABLE historico (id INTEGER PRIMARY KEY, evento TEXT);
sqlite> WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 20000) INSERT INTO historico SELECT i, 'visita ao produto ' || (i % 45 + 1) || ' registrada pelo site da loja' FROM n;
sqlite> .quit
$ ls -l loja.db
-rw-r--r-- 1 ana ana 1200128 out 18 11:21 loja.db

De 45 KB para 1,2 MB. A Ana desiste do histórico e apaga a tabela inteira:

$ sqlite3 loja.db "DROP TABLE historico;"
$ ls -l loja.db
-rw-r--r-- 1 ana ana 1200128 out 18 11:21 loja.db
$ sqlite3 loja.db "PRAGMA page_count; PRAGMA freelist_count;"
293
282

1200128

O arquivo continua exatamente do mesmo tamanho, mesmo sem a tabela.

page_count = 293

O arquivo tem 293 páginas (293 × 4096 = 1200128).

freelist_count = 282

Dessas, 282 estão livres: eram do histórico e agora estão em branco. Só 11 páginas têm dados de verdade, as mesmas 11 do começo.

Mais de 96% do arquivo está vazio. Os dados da loja estão intactos; só o espaço não voltou para o disco.

2. VACUUM: remontar o arquivo

É aqui que entra o aspirador:

$ sqlite3 loja.db
SQLite version 3.50.4 2025-07-30 19:33:53
Enter ".help" for usage hints.
sqlite> .timer on
sqlite> VACUUM;
Run Time: real 0.013 user 0.001175 sys 0.000000
sqlite> .quit
$ ls -l loja.db
-rw-r--r-- 1 ana ana 45056 out 18 11:21 loja.db
$ sqlite3 loja.db "PRAGMA page_count; PRAGMA freelist_count;"
11
0
$ sqlite3 loja.db "SELECT count(*) FROM itens_pedido;"
220

.timer on

Faz o sqlite3 mostrar quanto tempo cada comando levou (aqui, 13 milésimos de segundo, num banco pequeno).

VACUUM;

Copia todo o conteúdo do banco para um arquivo temporário, bem arrumado, sem páginas vazias, e depois põe essa cópia no lugar do original.

45056

O arquivo voltou ao tamanho do começo: de 1,2 MB para 44 KB.

11 e 0

Onze páginas, nenhuma livre.

220

Os dados continuam todos lá. O VACUUM só arruma; não apaga nada.

Além de devolver o espaço, o VACUUM põe as páginas de cada tabela em sequência no arquivo. Num banco que passou anos recebendo e apagando dados, as páginas de uma tabela ficam espalhadas, como um fichário bagunçado, e ler fica um pouco mais lento. Depois do VACUUM, tudo fica em ordem.

O mesmo acontece no editor do guia, com o banco em memória. Lá o histórico ocupa menos páginas, porque o banco do editor usa páginas de 8192 bytes:

CREATE TABLE historico (id INTEGER PRIMARY KEY, evento TEXT);
WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 20000)
INSERT INTO historico
SELECT i, 'visita ao produto ' || (i % 45 + 1) || ' registrada pelo site da loja' FROM n;

SELECT page_count, freelist_count FROM pragma_page_count, pragma_freelist_count;

DROP TABLE historico;
SELECT page_count, freelist_count FROM pragma_page_count, pragma_freelist_count;

VACUUM;
SELECT page_count, freelist_count FROM pragma_page_count, pragma_freelist_count;

Os três SELECT contam a história inteira: 150 páginas com o histórico; 150 páginas, 141 delas livres, depois do DROP; e 9 páginas, nenhuma livre, depois do VACUUM. O FROM pragma_page_count, pragma_freelist_count junta dois PRAGMAs numa linha só, com a forma de função que você viu em PRAGMAs Úteis.

CREATE TABLE historico (id INTEGER PRIMARY KEY, evento TEXT);
WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 20000)
INSERT INTO historico
SELECT i, 'visita ao produto ' || (i % 45 + 1) || ' registrada pelo site da loja' FROM n;

DELETE FROM historico;

SELECT page_count, freelist_count FROM pragma_page_count, pragma_freelist_count;

CREATE TABLE historico (id INTEGER PRIMARY KEY, evento TEXT);
WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 20000)
INSERT INTO historico
SELECT i, 'visita ao produto ' || (i % 45 + 1) || ' registrada pelo site da loja' FROM n;

DELETE FROM historico;

VACUUM;
SELECT page_count, freelist_count FROM pragma_page_count, pragma_freelist_count;

3. Quando usar o VACUUM (e quando não)

O VACUUM é seguro, mas não é de graça. Para remontar o arquivo, ele:

• lê e reescreve o banco inteiro: num banco de 13 milésimos de segundo, nada; num banco de vários gigabytes, pode levar minutos;
• precisa de espaço livre no disco: a documentação do SQLite avisa que ele pode usar até o dobro do tamanho do banco, entre a cópia temporária e o diário;
• trava o banco enquanto trabalha: nenhum outro programa consegue escrever, e ele não roda dentro de uma transação (veja Quando dá errado).

4. auto_vacuum: o espaço volta sozinho

Existe um jeito de o espaço voltar sozinho, sem VACUUM: a opção auto_vacuum. Num banco que já tem tabelas, mudar essa opção só vale depois de um VACUUM, que remonta o arquivo no modo novo. A Ana liga a opção (FULL = "completo") e repete o teste do histórico:

$ sqlite3 loja.db
SQLite version 3.50.4 2025-07-30 19:33:53
Enter ".help" for usage hints.
sqlite> PRAGMA auto_vacuum = FULL;
sqlite> VACUUM;
sqlite> PRAGMA auto_vacuum;
1
sqlite> CREATE TABLE historico (id INTEGER PRIMARY KEY, evento TEXT);
sqlite> WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 20000) INSERT INTO historico SELECT i, 'visita ao produto ' || (i % 45 + 1) || ' registrada pelo site da loja' FROM n;
sqlite> .quit
$ ls -l loja.db
-rw-r--r-- 1 ana ana 1204224 out 18 11:21 loja.db
$ sqlite3 loja.db "DROP TABLE historico;"
$ ls -l loja.db
-rw-r--r-- 1 ana ana 49152 out 18 11:21 loja.db

Desta vez o arquivo diminuiu na hora, sem VACUUM: no fim de cada transação, o SQLite move as páginas livres para o fim do arquivo e corta o arquivo ali. Repare que, com a opção ligada, o banco ficou com 49152 bytes, uma página a mais que os 45056 do começo: é o "mapa" que o SQLite mantém para saber o que pode mover.

Atenção: o auto_vacuum devolve o espaço, mas não arruma a ordem das páginas, e com o tempo pode até deixar o arquivo mais bagunçado. O VACUUM continua sendo o único jeito de remontar o arquivo por completo.

5. ANALYZE: as estatísticas

Agora o outro comando da aula, que não mexe no tamanho do arquivo. Para cada consulta, o SQLite escolhe um caminho: ler a tabela inteira, ou usar um índice (aula EXPLAIN QUERY PLAN). Para escolher bem, ele precisa saber como os dados estão distribuídos: quantas linhas a tabela tem, quantas linhas cada valor de um índice costuma ter. O ANALYZE conta isso e guarda o resultado numa tabela especial, a sqlite_stat1:

$ sqlite3 loja.db
SQLite version 3.50.4 2025-07-30 19:33:53
Enter ".help" for usage hints.
sqlite> SELECT * FROM sqlite_stat1;
Parse error: no such table: sqlite_stat1
sqlite> ANALYZE;
sqlite> SELECT * FROM sqlite_stat1;
itens_pedido|sqlite_autoindex_itens_pedido_1|220 3 1
pedidos||90
funcionarios||8
clientes||30
categorias||8
fornecedores||6
produtos||45

no such table: sqlite_stat1

Antes do primeiro ANALYZE, a tabela de estatísticas nem existe.

ANALYZE;

Lê as tabelas e os índices do banco e cria a sqlite_stat1. Num banco grande, pode levar um tempo; dá para analisar só uma tabela: ANALYZE produtos;.

tbl | idx | stat

As três colunas da sqlite_stat1: a tabela, o índice (vazio quando a linha fala da tabela) e os números.

pedidos||90

A tabela pedidos tem 90 linhas.

220 3 1

Sobre o índice da chave (pedido_id, produto_id): a tabela tem 220 linhas; cada valor de pedido_id aparece, em média, em umas 3 linhas (são 2 ou 3 itens por pedido); e cada par (pedido_id, produto_id), em 1 linha só.

Com um índice novo, a conta aparece para ele também. A Ana cria um índice de produtos por categoria e analisa de novo, agora no editor:

CREATE INDEX idx_produtos_categoria ON produtos (categoria_id);
ANALYZE;

SELECT tbl, idx, stat FROM sqlite_stat1 ORDER BY tbl;

A linha nova, produtos | idx_produtos_categoria | 45 6, diz: 45 produtos, e cada categoria tem, em média, uns 6 (45 divididos por 8 categorias dá 5,6, arredondado para cima). Com esse número, o SQLite sabe que procurar "os produtos da categoria 2" pelo índice vai achar umas 6 linhas, bem menos que ler as 45. Numa tabela de milhões de linhas, uma estimativa boa como essa é o que separa uma consulta de um segundo de uma de um minuto.

6. PRAGMA optimize: estatísticas em dia, sem esforço

Quando rodar o ANALYZE? Depois de criar um índice, ou quando os dados mudarem muito. Para não ter de pensar nisso, existe o PRAGMA optimize: ele confere o que mudou e só roda o ANALYZE nas tabelas em que vale a pena. A Ana testa com um índice recém-criado:

sqlite> DROP INDEX idx_produtos_categoria;
sqlite> CREATE INDEX idx_produtos_categoria ON produtos (categoria_id);
sqlite> SELECT * FROM sqlite_stat1 WHERE tbl = 'produtos';
sqlite> PRAGMA optimize;
sqlite> SELECT * FROM sqlite_stat1 WHERE tbl = 'produtos';
produtos|idx_produtos_categoria|45 6
sqlite> EXPLAIN QUERY PLAN SELECT nome FROM produtos WHERE categoria_id = 2;
QUERY PLAN
`--SEARCH produtos USING INDEX idx_produtos_categoria (categoria_id=?)

Logo depois do CREATE INDEX, o índice ainda não tinha estatística (o primeiro SELECT não mostrou nada). O PRAGMA optimize percebeu o índice novo e analisou a tabela: a linha 45 6 apareceu. E o EXPLAIN QUERY PLAN mostra o SQLite usando o índice para achar os produtos da categoria 2 (veja Quando o Índice Ajuda).

A documentação do SQLite recomenda rodar o PRAGMA optimize antes de fechar cada conexão (e, em programas que ficam muito tempo abertos, de tempos em tempos). Na maioria das vezes ele não faz nada e termina num instante; quando há o que fazer, faz só o necessário.

7. Formas diferentes de fazer

Quando dá errado

BEGIN;
DELETE FROM itens_pedido WHERE pedido_id = 90;
VACUUM;

SELECT * FROM sqlite_stat1;

PRAGMA auto_vacuum = FULL;
PRAGMA auto_vacuum;

VACUUM;
PRAGMA auto_vacuum;

Nos outros bancos

-- PostgreSQL: VACUUM existe, com outro sentido; ANALYZE também
VACUUM produtos;          -- marca o espaço para reaproveitar
VACUUM FULL produtos;     -- reescreve a tabela e devolve o espaço (como o nosso VACUUM)
ANALYZE produtos;

-- MySQL
OPTIMIZE TABLE produtos;  -- reorganiza e devolve o espaço
ANALYZE TABLE produtos;   -- estatísticas

-- SQL Server
UPDATE STATISTICS produtos;

Referência rápida

Exercícios

Missão final: a estatística do índice novo

CREATE INDEX idx_clientes_cidade ON clientes (cidade);

SELECT idx, stat FROM sqlite_stat1 WHERE tbl = 'clientes';

CREATE INDEX idx_clientes_cidade ON clientes (cidade);
ANALYZE;

SELECT idx, stat FROM sqlite_stat1 WHERE tbl = 'clientes';

São 8 cidades diferentes para 30 clientes: 30 ÷ 8 = 3,75, arredondado para cima, 4. O ANALYZE clientes;, só da tabela, também resolve. Teresina, com 10 clientes, tem bem mais que a média; a sqlite_stat1 guarda só a média, e para a maioria das consultas isso basta.

Resumo

• Apagar dados não diminui o arquivo: as páginas ficam livres para reaproveitar. PRAGMA freelist_count mostra quantas.
• VACUUM remonta o arquivo, devolve o espaço e põe as páginas em ordem, sem apagar nenhum dado. No teste da Ana: de 1200128 para 45056 bytes.
• O VACUUM reescreve o banco inteiro, pode precisar do dobro do espaço e não roda dentro de transação. Use depois de apagar muitos dados, não "por garantia".
• auto_vacuum = FULL devolve o espaço sozinho a cada transação; o padrão é NONE. Mudar o modo exige um VACUUM.
• ANALYZE calcula as estatísticas (linhas por tabela, linhas por valor de índice) e as guarda em sqlite_stat1; o SQLite as usa para escolher o caminho das consultas.
• PRAGMA optimize, antes de fechar a conexão, mantém as estatísticas em dia fazendo só o necessário.