- A partir de um alerta de falta de espaço de armazenamento, foi feita uma limpeza no PostgreSQL e foram recuperados mais de 70 GB de espaço não otimizado ou não utilizado, sem apagar índices nem dados
- No começo, foram revisadas técnicas comuns de recuperação, como índices não usados, bloat de tabelas e índices,
REINDEX CONCURRENTLY, pg_repack e a deduplicação de B-Tree do PostgreSQL 13
- A diferença decisiva foi que, ao contrário do Oracle, o PostgreSQL também indexa valores NULL; ao trocar o índice completo de
cancelled_by_user_id por um partial index, ele caiu de 769 MB para menos de 5 MB
- Foram buscados índices de coluna única com
pg_stats.null_frac alto para selecionar candidatos a partial index com a condição WHERE column IS NOT NULL, e depois do reset dos contadores estatísticos, o uso real foi confirmado com pg_stat_all_indexes
- No Django, um
ForeignKey anulável pode criar um índice B-Tree por padrão, então é preciso declarar db_index=False e definir manualmente um índice parcial com models.Index(..., condition=Q(...__isnull=False))
Limpeza iniciada por um alerta de falta de espaço
- A cada poucos meses, o monitoramento do banco de dados emitia alertas de falta de espaço de armazenamento, e normalmente isso era resolvido adicionando mais storage
- Naquele momento, como o sistema estava com baixa carga por causa de um período de isolamento, era uma boa oportunidade para tentar um trabalho de limpeza mais pesado do que o habitual
- No fim, foi possível recuperar mais de 70 GB de espaço não otimizado ou não utilizado sem apagar índices nem dados
- Só com a reconstrução comum de índices e tabelas já foi possível reduzir bastante espaço, e depois uma descoberta inesperada permitiu eliminar mais cerca de 20 GB de valores indexados sem uso
As primeiras causas suspeitas
-
Índices não usados
- Índices não usados são criados para melhorar o desempenho de consultas, mas podem ocupar espaço e deixar
INSERT e UPDATE mais lentos
- Em
pg_stat_all_indexes, foram procurados índices com idx_scan = 0, idx_tup_read = 0 e idx_tup_fetch = 0 para identificar índices que não foram escaneados nem usados em fetch desde o último reset das estatísticas
- Em alguns casos, um índice pode parecer não usado, mas não deve ser removido
- Quando o otimizador do PostgreSQL usa metadados do índice, e não o índice em si
- Quando ele garante restrições unique ou primary key em tabelas que não recebem atualizações há muito tempo
- É preciso revisar a lista item por item para saber se o índice realmente pode ser apagado, e os contadores estatísticos são gerenciados com resets após cada inspeção
- Neste caso, como esse trabalho já era feito periodicamente, não havia índices não usados para remover
-
Bloat de índices e tabelas
- Quando uma linha é atualizada, o PostgreSQL marca a tuple antiga como morta e grava uma nova em outro espaço, o que pode gerar bloat
- O bloat afeta não só tabelas, mas também índices, fazendo com que usem mais armazenamento do que o tamanho realmente necessário
- Como estimar bloat não é algo simples, foram usadas queries da wiki do PostgreSQL e do
pgsql-bloat-estimation para estimar o bloat de tabelas e índices B-Tree
Como reduzir o bloat de índices
-
Recriação e reconstrução de índices
- Para eliminar o bloat de um índice, é preciso recriá-lo
- Fazer drop e create diretamente permite gerar um novo índice em formato otimizado
- O
REINDEX do PostgreSQL reconstrói o índice existente no mesmo lugar
- Como a recriação normal bloqueia a tabela para impedir alterações, em ambiente de produção é mais adequado usar
REINDEX INDEX CONCURRENTLY index_name
- O
REINDEX CONCURRENTLY cria um novo índice com o sufixo _ccnew, sincroniza as alterações e, ao terminar, troca o índice antigo pelo novo e remove o anterior
- Se a reconstrução for interrompida no meio, o novo índice pode ficar inválido e continuar ocupando espaço; por isso, é preciso localizar e remover índices com
_ccnew e indisvalid falso
-
Deduplicação de B-Tree no PostgreSQL 13
- O PostgreSQL 13 introduziu a B-Tree Deduplication, que armazena valores duplicados de forma mais eficiente em índices B-Tree
- Até o PostgreSQL 12, mesmo com muitos valores repetidos, cada duplicado podia ser gravado repetidamente nas folhas do índice, consumindo bastante espaço
- Com a deduplicação ativada no PostgreSQL 13, valores duplicados são armazenados apenas uma vez, o que pode afetar bastante o tamanho de índices com alta repetição
- No PostgreSQL 13,
deduplicate_items = ON é o padrão; se o banco foi atualizado de versão anterior, é necessário usar REINDEX para obter todo o efeito
- Em um exemplo com 1 milhão de linhas, um índice não único em coluna com muitos duplicados caiu de 21 MB para 6840 kB, enquanto um índice de coluna única permaneceu em 21 MB
- Neste caso, o PostgreSQL 13 ainda era recente e o provedor de nuvem não o suportava, então não foi possível reduzir espaço com deduplicação
Bloat de tabela e pg_repack
-
Limitações para remover bloat de tabela
- Tabelas também podem sofrer bloat e fragmentação por causa de dead tuples
- Diferentemente dos índices, tabelas não são simples de recriar: é preciso criar uma nova tabela, migrar dados, sincronizar alterações e recriar índices, constraints e restrições de referência
VACUUM FULL table_name pode recuperar o espaço ocupado por bloat e dead tuples, mas exige lock na tabela- Tanto a recriação da tabela quanto o
VACUUM FULL podem resultar em trabalho grande de desenvolvimento ou downtime
-
Uso do pg_repack
- O pg_repack é uma extensão do PostgreSQL usada para reorganizar tabelas e índices sem downtime
- Para usar, é preciso criar a extensão com
CREATE EXTENSION pg_repack; e executá-la no console como pg_repack -k --table table_name db_name
- O
pg_repack cria uma nova tabela, carrega os dados originais, sincroniza os dados novos, reconstrói os índices e, no fim, troca as duas tabelas
- Na aplicação em produção, há algumas limitações
- É necessário aproximadamente espaço de armazenamento extra equivalente ao tamanho da tabela e dos índices que serão reorganizados
- Se o repack falhar ou for interrompido manualmente, objetos intermediários podem ficar para trás e exigir limpeza manual
- Quando já há pouco espaço disponível, a necessidade de espaço extra pode torná-lo inadequado, então é preciso monitorar a folga e planejar com antecedência
Descoberta inesperada: o PostgreSQL também indexa NULL
- Mesmo depois de concluir a remoção de índices não usados e a limpeza de bloat de tabelas e índices com técnicas comuns, ainda havia espaço para reduzir
- Uma das maiores tabelas armazenava dados de transações, incluindo casos em que o usuário cancelava após o pagamento e recebia reembolso
- A tabela de transações tinha foreign keys tanto para o usuário comprador quanto para o usuário que cancelou, e havia índices B-Tree definidos em ambos os campos
- O usuário comprador tinha restrição
NOT NULL, então todas as linhas tinham valor
- O usuário que cancelou era anulável, e como cancelamentos eram raros, a maior parte dos valores era NULL
- Esperava-se que o índice do usuário que cancelou fosse muito menor do que o do usuário comprador, mas na prática os tamanhos eram iguais
- No Oracle, aprende-se que NULLs are not indexed, mas no PostgreSQL valores NULL também são indexados
- O índice completo original era o seguinte
CREATE INDEX transaction_cancelled_by_ix
ON transactions(cancelled_by_user_id);
- Para testar a hipótese, ele foi trocado por um partial index que exclui valores NULL
DROP INDEX transaction_cancelled_by_ix;
CREATE INDEX transaction_cancelled_by_part_ix
ON transactions(cancelled_by_user_id)
WHERE cancelled_by_user_id IS NOT NULL;
- Depois da reindexação, o índice completo tinha 769 MB, e mais de 99% dos valores eram NULL
- O partial index sem os NULL ficou com menos de 5 MB, reduzindo mais de 99% do índice
- Após resetar as estatísticas da tabela e observar por algum tempo, foi confirmado que o novo índice era usado da mesma forma que o antigo, removendo mais de 760 MB de tuples indexadas sem uso sem impacto de desempenho
Como encontrar candidatos a partial index
- Depois do bom resultado com um partial index, foram buscados índices parecidos consultando colunas com
pg_stats.null_frac alto
null_frac é a estimativa do PostgreSQL para a proporção de valores NULL em uma coluna- A query de busca de candidatos aplicava as seguintes condições
- Excluir primary keys, já que não podem virar partial index
- Excluir índices que já fossem partial
- Excluir índices compostos
- Incluir apenas índices maiores que 10 MB
- No exemplo de resultado,
tx_cancelled_by_ix tinha 1418 MB e null_frac de 96,15%, aparecendo como candidato com potencial de economia de cerca de 1363 MB
- Excluir todos os NULL do índice nem sempre é vantajoso
- Queries com condição
IS NULL podem se beneficiar de um índice sobre valores NULL
- Além dos valores não nulos, outros valores raramente consultados também podem ser excluídos via partial index
- Os 20 GB do título foram possíveis porque reduzir cerca de 10 GB no banco primário também diminuía uma quantidade parecida de armazenamento na réplica
Como migrar para partial index no Django ORM
-
ForeignKey anulável e índice implícito
- O Django cria implicitamente um índice B-Tree em campos
models.ForeignKey se db_index=False não for especificado
- Se um
ForeignKey anulável que armazena o usuário que cancelou não tiver configuração explícita, o Django criará um índice completo
- Para criar um partial index, é preciso definir
db_index=False no campo FK e adicionar um models.Index condicional em Meta.indexes
class Transaction(models.Model):
cancelled_by_user = models.ForeignKey(
to=User,
null=True,
on_delete=models.CASCADE,
db_index=False,
)
class Meta:
indexes = (
models.Index(
fields=('cancelled_by_user_id', ),
name='%(class_name)s_cancelled_by_part_ix',
condition=Q(cancelled_by_user_id__isnull=False),
),
)
- Um foreign key anulável é um bom candidato a partial index
- Para evitar que funcionalidades implícitas criem índices sem perceber, foi criado um check no Django que obriga sempre declarar
db_index em foreign keys
-
Como trocar um índice completo por um partial index sem downtime
- Para substituir um índice completo por um partial index sem downtime nem perda de desempenho, a mudança precisa ser feita em etapas
- O modelo Django relacionado foi alterado para trocar o índice completo por um partial index, mas a migration gerada pelo Django não foi executada diretamente
- Essa migration poderia desativar a constraint de FK, fazer drop do índice completo existente e criar o novo partial index, causando downtime e perda de desempenho
- O SQL foi gerado com
./manage.py sqlmigrate, depois apenas o CREATE INDEX foi extraído, ajustado para CONCURRENTLY e executado manualmente no banco
- Como o índice completo antigo ainda existia, as queries continuaram usando-o enquanto o partial index era criado
- Para verificar se o novo partial index estava sendo usado, os contadores estatísticos do índice completo foram resetados com
pg_stat_reset_single_table_counters(<full index oid>)
- Depois disso,
idx_scan, idx_tup_read e idx_tup_fetch em pg_stat_all_indexes foram observados para comparar o uso do índice completo com o partial index
- Quando ficou claro que o partial index estava sendo usado, o índice completo foi removido e foi possível confirmar o espaço realmente liberado
- Depois que o estado do banco ficou alinhado ao estado do modelo, a migration foi marcada como executada com
./manage.py migrate --fake
- Em ambientes como dev, QA e staging, onde downtime é menos crítico, a migration do Django foi executada normalmente e o índice completo foi trocado pelo partial index
Resumo final
- Apenas disco, parâmetros de armazenamento e ajustes de configuração têm limite no quanto podem melhorar desempenho e uso de espaço
- A última margem de melhoria estava nos próprios objetos do banco de dados, neste caso, na definição dos índices
- O fluxo aplicado pode ser resumido em três etapas
- Remover índices não usados
- Fazer repack de tabelas e índices e, se possível, ativar B-Tree deduplication
- Usar partial indexes para indexar apenas os valores necessários
- Esse tipo de limpeza pode ser uma alternativa prática para ganhar alguns dias antes de precisar provisionar mais storage
1 comentários
Opiniões no Hacker News
Mesmo com bastante espaço de armazenamento, é bom manter os índices pequenos. Assim, mais dados conseguem caber no conjunto quente
Dito isso, para quem opera bancos de dados na escala de TB, “é só adicionar mais armazenamento” também é sempre uma opção válida. Especialmente fora da nuvem; em hardware próprio, SSDs NVMe enterprise custam cerca de US$ 80/TB, e RAM DDR4 fica em torno de US$ 1,20/GB. Considerando aproximadamente US$ 1000 por 4 horas de engenharia, dá para comprar 800 GB de RAM ou 12 TB de armazenamento
Só backups semanais de um ano já transformam 1 TB em 50 TB, mesmo que seja armazenamento offline. Na nossa empresa também é fácil adicionar mais armazenamento ao DB ativo, e fizemos isso por anos, mas mover backups de múltiplos TB foi ficando cada vez mais trabalhoso, então chegamos a um ponto em que precisamos reduzir os dados de produção mesmo que isso tenha custo de engenharia
Se for algo pontual, a infraestrutura existente talvez absorva custos como espaço livre em rack ou slots PCIe sobrando, mas essa amortização só funciona bem em pequena escala. Além disso, conforme o sistema cresce, o custo de gerenciamento e o aumento de latência inevitavelmente reduzem o desempenho por unidade de equipamento. Se a solução for continuar aumentando o sistema, o sistema como um todo vai ficando cada vez mais lento até poder se tornar inoperável. Em contrapartida, uma solução que reduz os recursos necessários aumenta o desempenho por unidade de recurso e cria, por si só, um ativo de maior qualidade
Uma otimização que economiza 1 TB hoje pode economizar 2 TB daqui a alguns anos, e apenas algumas decisões assim podem gerar uma diferença de uma ordem de grandeza ou mais. Isso é especialmente verdadeiro em escalas maiores e, no fim, é sempre preciso encontrar um equilíbrio
A opção de “jogar hardware em cima” às vezes pode ser sensata, mas, na maioria dos casos, não acho realista transformar hoje um servidor com 128 GB de RAM em um com 1 TB de RAM amanhã por US$ 1K
Só que, por ser um custo mensal em vez de uma despesa única, a conta fica um pouco mais complexa. Claro que, se o banco de dados cresce a uma taxa constante, o provisionamento único on-premises também acaba sendo, na prática, parecido com um custo mensal
O texto menciona a deduplicação de B-tree, que existe no PostgreSQL 13 mas não no 12 usado por eles, e diz que a grande maioria dos valores em alguns índices de chaves estrangeiras é NULL
Fico curioso se a deduplicação de B-tree teria ajudado nesse caso. Pela seção 63.4.2 da documentação do PostgreSQL 13, parece que sim: “B-Tree deduplication is just as effective with ‘duplicates’ that contain a NULL value...” Claro que provavelmente não seria tão eficaz quanto o índice parcial aplicado no texto, mas é interessante
https://www.postgresql.org/docs/13/btree-implementation.html
https://news.ycombinator.com/item?id=25989467
Este artigo foi útil não só pela “descoberta” central dos índices parciais, mas também por reunir, de modo geral, boas técnicas para ter em mente quando houver preocupação com uso ineficiente de espaço no PostgreSQL
Ainda assim, para startups ou empresas em estágio inicial, vale lembrar que, nesses problemas de armazenamento, quase sempre é melhor optar por adicionar mais espaço em disco do que se preocupar com otimização de tamanho. Desenvolvedores são caros, discos são baratos
Quando trabalhei em uma startup que crescia rápido, um dos fundadores insistia em continuar fazendo upgrades em um único servidor; havia redundância e backups, mas a arquitetura era simples, então era fácil administrar, depurar e recuperar. Durante os primeiros 5 anos da empresa, o sistema inteiro rodou em um único servidor enquanto crescia exponencialmente e atendia milhões de usuários no mundo todo. Depois de ver isso, fiquei convencido de que upgrades devem ser feitos apenas quando necessário, da forma mais simples e direta possível
Usar um índice parcial que se encaixa claramente no caso de uso, como quando a maioria dos valores é NULL, está mais para modelagem correta e não deve ser visto como otimização prematura ou desperdício de tempo de desenvolvedor
Em um contexto parecido, estes scripts ajudaram bastante
https://github.com/NikolayS/postgres_dba
Consegui recuperar cerca de 10% do espaço de armazenamento, ou seja, aproximadamente 100 GB, reorganizando a ordem das colunas em tabelas grandes
Na semana passada, só com
reindexepg_repack, recuperei 200 GB em um DB de 600 GB. A pior tabela tinha 17 GB de dados e 142 GB de índices; depois da reindexação, caiu para 21 GBEssa tabela era indexada em excesso, com vários índices sobre diferentes combinações de colunas. Acho que uma das causas dos índices enormes era a distribuição dos dados. Desde 2015 houve inserções, atualizações e exclusões contínuas; quanto mais antigos os dados, maior a probabilidade de terem sido excluídos, então há mais dados de anos recentes, mas ainda assim cerca de 0,1% é de 2015. Acredito que essa distribuição enviesada com cauda longa pode ter dificultado para o vacuum lidar com o inchaço dos índices
Isso também foi discutido na época: An unexpected find that freed 20GB of unused index space in PostgreSQL - https://news.ycombinator.com/item?id=25988871 - fevereiro de 2021, 78 comentários
Recomendo muito o pganalyze.com para encontrar índices não utilizados, oportunidades de otimização e queries com alta latência
Realmente, dizer “economizou 20 GB com um truque estranho” não era exagero. Em ambientes antigos de Oracle / SQL Server, recebemos muitos pedidos de pessoas querendo usar um formato peculiar de índice do PostgreSQL, algo de que quase nunca se ouve falar, mas eu não sabia que valores NULL também eram indexados
No primeiro projeto grande de que participei, conforme o conjunto de dados crescia, a equipe não conseguia entender por que as operações tinham ficado lentas
Em índices, o tempo de inserção por registro é log(n). Se o banco de teste tinha 1.000 registros e passava para algo perto de 65k, o tempo de inserção aumentava 60% (2^10 contra 2^16). O sucesso torna tudo mais lento, e há um limite para upgrades de servidor. Se, por causa de uma funcionalidade vaga que o negócio queria, você adiciona mais alguns índices, agora isso dobra
Também administro muitos bancos com centenas de milhões de registros e mais de 40 índices por tabela/coleção
Uma busca binária dentro de uma página de B-tree leva menos de 100 ciclos. Mesmo uma travessia de B-tree sobre 100 milhões de registros ainda deveria estar na casa dos microssegundos, e a busca binária por cima disso também deveria estar em microssegundos, ainda que não em centenas de nanossegundos
Será que esse princípio também se aplica quando um ou alguns valores são muito comuns? Por exemplo, quando 90% de uma coluna inteira são 0
where val = 0deixa 90% das linhas, pode optar por um scan da tabela em vez do índice