Postgres: inchaço, pg_repack e restrições diferidas

O efeito do inchaço nas tabelas e índices é amplamente conhecido e está presente não apenas no Postgres. Existem maneiras de lidar com isso imediatamente, como VACUUM FULL ou CLUSTER, mas elas bloqueiam tabelas durante a operação e, portanto, nem sempre podem ser usadas.

O artigo conterá um pouco de teoria sobre como ocorre o inchaço, como você pode combatê-lo, sobre restrições diferidas e os problemas que elas trazem ao uso da extensão pg_repack.

Este artigo foi escrito com base em meu discurso na PgConf.Rússia 2020.

Por que ocorre o inchaço?

Postgres é baseado em um modelo multiversão (MVCC). Sua essência é que cada linha da tabela pode ter várias versões, enquanto as transações não veem mais do que uma dessas versões, mas não necessariamente a mesma. Isto permite que várias transações funcionem simultaneamente e praticamente não tenham impacto umas nas outras.

Obviamente, todas essas versões precisam ser armazenadas. Postgres trabalha com memória página por página e uma página é a quantidade mínima de dados que pode ser lida do disco ou gravada. Vejamos um pequeno exemplo para entender como isso acontece.

Digamos que temos uma tabela à qual adicionamos vários registros. Novos dados apareceram na primeira página do arquivo onde a tabela está armazenada. Estas são versões ativas de linhas que estão disponíveis para outras transações após um commit (para simplificar, assumiremos que o nível de isolamento é Read Committed).

Em seguida, atualizamos uma das entradas, marcando assim a versão antiga como não mais relevante.

Passo a passo, atualizando e excluindo versões de linhas, acabamos com uma página em que aproximadamente metade dos dados são “lixo”. Esses dados não são visíveis para nenhuma transação.

Postgres tem um mecanismo VÁCUO, que limpa versões obsoletas e abre espaço para novos dados. Mas se ele não estiver configurado de forma agressiva o suficiente ou estiver ocupado trabalhando em outras tabelas, então “dados inúteis” permanecerão e teremos que usar páginas adicionais para novos dados.

Portanto, em nosso exemplo, em algum momento a tabela consistirá em quatro páginas, mas apenas metade dela conterá dados ativos. Com isso, ao acessar a tabela, leremos muito mais dados do que o necessário.

Mesmo que VACUUM agora exclua todas as versões de linhas irrelevantes, a situação não melhorará drasticamente. Teremos espaço livre em páginas ou mesmo páginas inteiras para novas linhas, mas ainda estaremos lendo mais dados do que o necessário.
A propósito, se uma página completamente em branco (a segunda em nosso exemplo) estivesse no final do arquivo, o VACUUM seria capaz de cortá-la. Mas agora ela está no meio, então nada pode ser feito com ela.

Quando o número dessas páginas vazias ou muito esparsas aumenta, o que é chamado de inchaço, isso começa a afetar o desempenho.

Tudo o que foi descrito acima é a mecânica da ocorrência de inchaço nas tabelas. Nos índices isso acontece da mesma maneira.

Estou com inchaço?

Existem várias maneiras de determinar se você tem inchaço. A ideia do primeiro é usar estatísticas internas do Postgres, que contêm informações aproximadas sobre o número de linhas nas tabelas, o número de linhas “ativas”, etc. Tomamos como base escrita do PostgreSQL Experts, que pode avaliar tabelas inchadas junto com índices brinde e inchados btree. Em nossa experiência, seu erro é de 10 a 20%.

Outra maneira é usar a extensão pgstattuple, que permite que você olhe dentro das páginas e obtenha um valor de inchaço estimado e exato. Mas no segundo caso, você terá que escanear a tabela inteira.

Consideramos aceitável um pequeno valor de inchaço, de até 20%. Pode ser considerado um análogo do fillfactor para mesas и índices. Em 50% e acima, podem começar problemas de desempenho.

Maneiras de combater o inchaço

O Postgres tem várias maneiras de lidar com o inchaço imediatamente, mas nem sempre são adequadas para todos.

Configure AUTOVACUUM para que não ocorra inchaço. Ou, mais precisamente, para mantê-lo em um nível aceitável para você. Isto parece um conselho do “capitão”, mas na realidade nem sempre é fácil de conseguir. Por exemplo, você tem um desenvolvimento ativo com alterações regulares no esquema de dados ou algum tipo de migração de dados está ocorrendo. Como resultado, o seu perfil de carga pode mudar frequentemente e normalmente variar de tabela para tabela. Isso significa que você precisa trabalhar constantemente um pouco à frente e ajustar o AUTOVACUUM às mudanças de perfil de cada mesa. Mas obviamente isso não é fácil de fazer.

Outro motivo comum pelo qual o AUTOVACUUM não consegue acompanhar as tabelas é porque há transações de longa duração que o impedem de limpar os dados disponíveis para essas transações. A recomendação aqui também é óbvia - livre-se de transações “pendentes” e minimize o tempo de transações ativas. Mas se a carga do seu aplicativo for um híbrido de OLAP e OLTP, você poderá ter simultaneamente muitas atualizações frequentes e consultas curtas, bem como operações de longo prazo - por exemplo, criar um relatório. Nessa situação, vale pensar em distribuir a carga pelas diferentes bases, o que permitirá um maior ajuste de cada uma delas.

Outro exemplo - mesmo que o perfil seja homogêneo, mas o banco de dados esteja sob uma carga muito alta, mesmo o AUTOVACUUM mais agressivo pode não aguentar e ocorrerá inchaço. A escala (vertical ou horizontal) é a única solução.

O que fazer em uma situação em que você configurou o AUTOVACUUM, mas o inchaço continua a crescer.

Equipe VÁCUO CHEIO reconstrói o conteúdo de tabelas e índices e deixa apenas dados relevantes neles. Para eliminar o inchaço, funciona perfeitamente, mas durante sua execução é capturado um bloqueio exclusivo na tabela (AccessExclusiveLock), o que não permitirá executar consultas nesta tabela, até mesmo selects. Se você puder interromper seu serviço ou parte dele por algum tempo (de dezenas de minutos a várias horas, dependendo do tamanho do banco de dados e do hardware), essa opção é a melhor. Infelizmente, não temos tempo para executar o VACUUM FULL durante a manutenção programada, portanto este método não é adequado para nós.

Equipe CLUSTER Reconstrói o conteúdo das tabelas da mesma forma que VACUUM FULL, mas permite especificar um índice segundo o qual os dados serão ordenados fisicamente no disco (mas no futuro a ordem não será garantida para novas linhas). Em certas situações, esta é uma boa otimização para uma série de consultas - com leitura de vários registros por índice. A desvantagem do comando é a mesma do VACUUM FULL - ele bloqueia a tabela durante a operação.

Equipe REINDEXAR semelhante aos dois anteriores, mas reconstrói um índice específico ou todos os índices da tabela. Os bloqueios são um pouco mais fracos: ShareLock na tabela (evita modificações, mas permite seleção) e AccessExclusiveLock no índice que está sendo reconstruído (bloqueia consultas usando este índice). Porém, na 12ª versão do Postgres apareceu um parâmetro CONCORRENTEMENTE, que permite reconstruir o índice sem bloquear a adição, modificação ou exclusão simultânea de registros.

Nas versões anteriores do Postgres, você pode obter um resultado semelhante a REINDEX CONCURRENTLY usando CRIAR ÍNDICE SIMULTANEAMENTE. Ele permite criar um índice sem bloqueio estrito (ShareUpdateExclusiveLock, que não interfere nas consultas paralelas), substituir o índice antigo por um novo e excluir o índice antigo. Isso permite eliminar o inchaço do índice sem interferir no seu aplicativo. É importante considerar que ao reconstruir índices haverá uma carga adicional no subsistema de disco.

Assim, se para os índices existem maneiras de eliminar o inchaço “on the fly”, então não há nenhuma para as tabelas. É aqui que várias extensões externas entram em ação: pg_repack (anteriormente pg_reorg), pgcompact, pgcompacttable e outros. Neste artigo não vou compará-los e falarei apenas sobre o pg_repack, que, após algumas modificações, nós mesmos usamos.

Como funciona o pg_repack

Digamos que temos uma tabela completamente comum - com índices, restrições e, infelizmente, com inchaço. A primeira etapa do pg_repack é criar uma tabela de log para armazenar dados sobre todas as alterações durante a execução. O gatilho replicará essas alterações para cada inserção, atualização e exclusão. Em seguida, é criada uma tabela semelhante à original em estrutura, mas sem índices e restrições, para não retardar o processo de inserção de dados.

Em seguida, o pg_repack transfere os dados da tabela antiga para a nova tabela, filtrando automaticamente todas as linhas irrelevantes e, em seguida, cria índices para a nova tabela. Durante a execução de todas essas operações, as alterações se acumulam na tabela de logs.

A próxima etapa é transferir as alterações para a nova tabela. A migração é realizada em várias iterações e, quando restam menos de 20 entradas na tabela de log, o pg_repack adquire um bloqueio forte, migra os dados mais recentes e substitui a tabela antiga pela nova nas tabelas do sistema Postgres. Este é o único e muito curto período em que você não poderá trabalhar com a mesa. Depois disso, a tabela antiga e a tabela com logs são excluídas e o espaço é liberado no sistema de arquivos. O processo está completo.

Tudo parece ótimo na teoria, mas o que acontece na prática? Testamos o pg_repack sem carga e sob carga, e verificamos seu funcionamento em caso de parada prematura (ou seja, usando Ctrl+C). Todos os testes foram positivos.

Fomos ao armazém - e então nem tudo saiu como esperávamos.

Primeira panqueca à venda

No primeiro cluster recebemos um erro sobre uma violação de uma restrição exclusiva:

$ ./pg_repack -t tablename -o id
INFO: repacking table "tablename"
ERROR: query failed: 
    ERROR: duplicate key value violates unique constraint "index_16508"
DETAIL:  Key (id, index)=(100500, 42) already exists.

Esta limitação tinha um nome gerado automaticamente index_16508 - foi criada por pg_repack. Com base nos atributos incluídos na sua composição, determinamos a “nossa” restrição que lhe corresponde. O problema acabou sendo que esta não é uma limitação completamente comum, mas adiada (restrição diferida), ou seja sua verificação é realizada posteriormente ao comando sql, o que leva a consequências inesperadas.

Restrições diferidas: por que são necessárias e como funcionam

Uma pequena teoria sobre restrições diferidas.
Vejamos um exemplo simples: temos um livro de referência de carros com dois atributos - o nome e a ordem do carro no diretório.

create table cars
(
  name text constraint pk_cars primary key,
  ord integer not null constraint uk_cars unique
);

Digamos que precisávamos trocar o primeiro e o segundo carro. A solução simples é atualizar o primeiro valor para o segundo e o segundo para o primeiro:

begin;
  update cars set ord = 2 where name = 'audi';
  update cars set ord = 1 where name = 'bmw';
commit;

Mas quando executamos este código, esperamos uma violação de restrição porque a ordem dos valores na tabela é única:

[23305] ERROR: duplicate key value violates unique constraint “uk_cars”
Detail: Key (ord)=(2) already exists.

Como posso fazer isso de forma diferente? Opção um: adicionar uma substituição de valor adicional a um pedido que com certeza não existe na tabela, por exemplo “-1”. Em programação, isso é chamado de “troca de valores de duas variáveis ​​por uma terceira”. A única desvantagem deste método é a atualização adicional.

Opção dois: redesenhar a tabela para usar um tipo de dados de ponto flutuante para o valor do pedido em vez de números inteiros. Então, ao atualizar o valor de 1, por exemplo, para 2.5, a primeira entrada automaticamente “ficará” entre a segunda e a terceira. Esta solução funciona, mas existem duas limitações. Primeiro, não funcionará para você se o valor for usado em algum lugar da interface. Segundo, dependendo da precisão do tipo de dados, você terá um número limitado de inserções possíveis antes de recalcular os valores de todos os registros.

Opção três: adiar a restrição para que ela seja verificada apenas no momento do commit:

create table cars
(
  name text constraint pk_cars primary key,
  ord integer not null constraint uk_cars unique deferrable initially deferred
);

Como a lógica da nossa solicitação inicial garante que todos os valores sejam únicos no momento do commit, ela será bem-sucedida.

O exemplo discutido acima é, obviamente, muito sintético, mas revela a ideia. Em nossa aplicação, usamos restrições diferidas para implementar a lógica responsável por resolver conflitos quando os usuários trabalham simultaneamente com objetos widget compartilhados no quadro. O uso de tais restrições nos permite tornar o código do aplicativo um pouco mais simples.

Em geral, dependendo do tipo de restrição, o Postgres possui três níveis de granularidade para verificá-las: níveis de linha, transação e expressão.

Fonte: begriffs

CHECK e NOT NULL são sempre verificados no nível da linha; para outras restrições, como pode ser visto na tabela, existem opções diferentes. Você pode ler mais aqui.

Para resumir brevemente, as restrições adiadas em diversas situações fornecem código mais legível e menos comandos. Porém, você tem que pagar por isso complicando o processo de depuração, pois o momento em que ocorre o erro e o momento em que você fica sabendo dele são separados no tempo. Outro possível problema é que o escalonador nem sempre será capaz de construir um plano ideal se a solicitação envolver uma restrição diferida.

Melhoria do pg_repack

Abordamos o que são restrições diferidas, mas como elas se relacionam com o nosso problema? Vamos lembrar o erro que recebemos anteriormente:

$ ./pg_repack -t tablename -o id
INFO: repacking table "tablename"
ERROR: query failed: 
    ERROR: duplicate key value violates unique constraint "index_16508"
DETAIL:  Key (id, index)=(100500, 42) already exists.

Ocorre quando os dados são copiados de uma tabela de log para uma nova tabela. Isso parece estranho porque... os dados na tabela de log são confirmados junto com os dados na tabela de origem. Se satisfizerem as restrições da tabela original, como poderão violar as mesmas restrições na nova?

Acontece que a raiz do problema está na etapa anterior do pg_repack, que cria apenas índices, mas não restrições: a tabela antiga tinha uma restrição exclusiva e a nova criou um índice exclusivo.

É importante notar aqui que se a restrição for normal e não adiada, então o índice exclusivo criado será equivalente a esta restrição, porque Restrições exclusivas no Postgres são implementadas através da criação de um índice exclusivo. Mas no caso de uma restrição diferida, o comportamento não é o mesmo, pois o índice não pode ser diferido e é sempre verificado no momento em que o comando sql é executado.

Assim, a essência do problema está no “atraso” da verificação: na tabela original ela ocorre no momento do commit, e na nova tabela no momento da execução do comando sql. Isso significa que precisamos ter certeza de que as verificações são realizadas da mesma forma em ambos os casos: sempre com atraso ou sempre imediatamente.

Então, que ideias tivemos?

Crie um índice semelhante ao adiado

A primeira ideia é realizar ambas as verificações em modo imediato. Isso pode gerar diversas restrições de falsos positivos, mas se forem poucas, isso não deve afetar o trabalho dos usuários, já que tais conflitos são uma situação normal para eles. Eles ocorrem, por exemplo, quando dois usuários começam a editar o mesmo widget ao mesmo tempo, e o cliente do segundo usuário não tem tempo de receber a informação de que o widget já está bloqueado para edição pelo primeiro usuário. Em tal situação, o servidor recusa o segundo usuário e seu cliente reverte as alterações e bloqueia o widget. Um pouco mais tarde, quando o primeiro usuário concluir a edição, o segundo receberá a informação de que o widget não está mais bloqueado e poderá repetir a ação.

Para garantir que as verificações estejam sempre no modo não diferido, criamos um novo índice semelhante à restrição diferida original:

CREATE UNIQUE INDEX CONCURRENTLY uk_tablename__immediate ON tablename (id, index);
-- run pg_repack
DROP INDEX CONCURRENTLY uk_tablename__immediate;

No ambiente de teste, recebemos apenas alguns erros esperados. Sucesso! Executamos o pg_repack novamente em produção e obtivemos 5 erros no primeiro cluster em uma hora de trabalho. Este é um resultado aceitável. Porém, já no segundo cluster o número de erros aumentou significativamente e tivemos que parar o pg_repack.

Por que isso aconteceu? A probabilidade de ocorrência de um erro depende de quantos usuários estão trabalhando com os mesmos widgets ao mesmo tempo. Aparentemente, naquele momento houve muito menos mudanças competitivas com os dados armazenados no primeiro cluster do que nos demais, ou seja, tivemos apenas “sorte”.

A ideia não funcionou. Nesse ponto, vimos duas outras soluções: reescrever o código do nosso aplicativo para dispensar restrições adiadas ou “ensinar” o pg_repack a trabalhar com elas. Escolhemos o segundo.

Substitua os índices da nova tabela por restrições adiadas da tabela original

O objetivo da revisão era óbvio - se a tabela original tiver uma restrição diferida, então para a nova você precisará criar essa restrição, e não um índice.

Para testar nossas mudanças, escrevemos um teste simples:

• tabela com restrição diferida e um registro;
• inserir dados em um loop que entre em conflito com um registro existente;
• faça uma atualização – os dados não entram mais em conflito;
• confirmar as alterações.

create table test_table
(
  id serial,
  val int,
  constraint uk_test_table__val unique (val) deferrable initially deferred 
);

INSERT INTO test_table (val) VALUES (0);
FOR i IN 1..10000 LOOP
  BEGIN
    INSERT INTO test_table VALUES (0) RETURNING id INTO v_id;
    UPDATE test_table set val = i where id = v_id;
    COMMIT;
  END;
END LOOP;

A versão original do pg_repack sempre travava na primeira inserção, a versão modificada funcionava sem erros. Ótimo.

Vamos para a produção e novamente obtemos um erro na mesma fase de cópia dos dados da tabela de log para uma nova:

$ ./pg_repack -t tablename -o id
INFO: repacking table "tablename"
ERROR: query failed: 
    ERROR: duplicate key value violates unique constraint "index_16508"
DETAIL:  Key (id, index)=(100500, 42) already exists.

Situação clássica: tudo funciona em ambientes de teste, mas não em produção?!

APPLY_COUNT e a junção de dois lotes

Começamos a analisar o código literalmente linha por linha e descobrimos um ponto importante: os dados são transferidos da tabela de log para uma nova em lotes, a constante APPLY_COUNT indicava o tamanho do lote:

for (;;)
{
num = apply_log(connection, table, APPLY_COUNT);

if (num > MIN_TUPLES_BEFORE_SWITCH)
     continue;  /* there might be still some tuples, repeat. */
...
}

O problema é que os dados da transação original, em que diversas operações poderiam potencialmente violar a restrição, quando transferidos, podem acabar na junção de dois lotes - metade dos comandos serão confirmados no primeiro lote, e a outra metade no segundo. E aqui, dependendo da sua sorte: se as equipes não violarem nada no primeiro lote, então está tudo bem, mas se violarem, ocorre um erro.

APPLY_COUNT é igual a 1000 registros, o que explica porque nossos testes foram bem-sucedidos - eles não cobriram o caso de “junção de lote”. Usamos dois comandos - inserir e atualizar, então exatamente 500 transações de dois comandos sempre foram colocadas em lote e não tivemos problemas. Depois de adicionar a segunda atualização, nossa edição parou de funcionar:

FOR i IN 1..10000 LOOP
  BEGIN
    INSERT INTO test_table VALUES (1) RETURNING id INTO v_id;
    UPDATE test_table set val = i where id = v_id;
    UPDATE test_table set val = i where id = v_id; -- one more update
    COMMIT;
  END;
END LOOP;

Portanto, a próxima tarefa é garantir que os dados da tabela original, que foram alterados em uma transação, acabem na nova tabela também dentro de uma transação.

Recusa de lote

E novamente tivemos duas soluções. Primeiro: vamos abandonar completamente o particionamento em lotes e transferir dados em uma transação. A vantagem desta solução era a sua simplicidade - as alterações de código necessárias eram mínimas (aliás, nas versões mais antigas o pg_reorg funcionava exatamente assim). Mas há um problema: estamos criando uma transação de longa duração, e isso, como dito anteriormente, é uma ameaça ao surgimento de um novo inchaço.

A segunda solução é mais complexa, mas provavelmente mais correta: criar uma coluna na tabela de log com o identificador da transação que adicionou dados à tabela. Então, quando copiarmos os dados, podemos agrupá-los por este atributo e garantir que as alterações relacionadas sejam transferidas juntas. O lote será formado a partir de diversas transações (ou uma grande) e seu tamanho irá variar dependendo de quantos dados foram alterados nessas transações. É importante ressaltar que como os dados de diferentes transações entram na tabela de log de forma aleatória, não será mais possível lê-los sequencialmente, como acontecia antes. seqscan para cada solicitação com filtragem por tx_id é muito caro, é necessário um índice, mas também tornará o método mais lento devido à sobrecarga de atualização. Em geral, como sempre, você precisa sacrificar alguma coisa.

Então decidimos começar pela primeira opção, por ser mais simples. Primeiro, era necessário entender se uma transação longa seria um problema real. Como a transferência principal de dados da tabela antiga para a nova também ocorre em uma transação longa, a questão se transformou em “quanto vamos aumentar esta transação?” A duração da primeira transação depende principalmente do tamanho da mesa. A duração de uma nova depende de quantas alterações se acumulam na tabela durante a transferência de dados, ou seja, dependendo da intensidade da carga. A execução do pg_repack ocorreu durante um período de carga mínima de serviço e o volume de alterações foi desproporcionalmente pequeno em comparação com o tamanho original da tabela. Decidimos que poderíamos negligenciar o tempo de uma nova transação (para comparação, em média é de 1 hora e 2-3 minutos).

Os experimentos foram positivos. Lançamento em produção também. Para maior clareza, aqui está uma imagem com o tamanho de um dos bancos de dados após a execução:

Como estávamos totalmente satisfeitos com esta solução, não tentamos implementar a segunda, mas estamos considerando a possibilidade de discuti-la com os desenvolvedores da extensão. Nossa revisão atual, infelizmente, ainda não está pronta para publicação, pois resolvemos o problema apenas com restrições diferidas únicas, e para um patch completo é necessário fornecer suporte para outros tipos. Esperamos poder fazer isso no futuro.

Talvez você tenha uma dúvida: por que nos envolvemos nessa história com a modificação do pg_repack e não usamos, por exemplo, seus análogos? Em algum momento também pensamos nisso, mas a experiência positiva de utilizá-lo anteriormente, em tabelas sem restrições diferidas, nos motivou a tentar entender a essência do problema e resolvê-lo. Além disso, o uso de outras soluções também requer tempo para realizar testes, então decidimos que primeiro tentaríamos resolver o problema e, se percebêssemos que não poderíamos fazer isso em um tempo razoável, começaríamos a procurar análogos .

Descobertas

O que podemos recomendar com base em nossa própria experiência:

1. Monitore seu inchaço. Com base nos dados de monitoramento, você pode entender o quão bem o autovacuum está configurado.
2. Ajuste o AUTOVACUUM para manter o inchaço em um nível aceitável.
3. Se o inchaço ainda estiver crescendo e você não conseguir superá-lo usando ferramentas prontas para usar, não tenha medo de usar extensões externas. O principal é testar tudo bem.
4. Não tenha medo de modificar soluções externas para atender às suas necessidades - às vezes isso pode ser mais eficaz e ainda mais fácil do que alterar seu próprio código.

Fonte: habr.com