Hot Updates: um olhar para superar ineficiências em cenários com muitos updates no PostgreSQL

Multi Versionamento — MVCC

O multi versionamento do PostgreSQL (Multiversion Concurrency Control – MVCC) é uma ótima estratégia para lidar de forma eficiente com transações em ambientes com ampla concorrência. Por fornecer uma visão dos dados naquele exato instante e ignorar as atualizações em trânsito, ele minimiza contenções de bloqueio de metodologias tradicionais de bancos de dados. Isso permite um melhor desempenho em ambientes concorrentes de multi usuários.

A vantagem do modelo MVCC para o controle de concorrência, ao invés do bloqueio dos registros, é que os bloqueios de dados adquiridos para leitura de tais não entram em conflito com os bloqueios adquiridos para gravação. Em outras palavras, a leitura nunca bloqueia a gravação e esta, por sua vez, nunca bloqueia a leitura.

Apesar de lidar bem com a questão da concorrência, ele paga o seu preço quando precisamos lidar com atualizações massivas de registros em tabelas.

O porquê disso

O PostgreSQL, mantém a versão antiga da linha da tabela na tabela a cada update. Ele adiciona uma nova versão da linha (“tupla”) e marca a versão antiga como inválida. Em outras palavras, um update no PostgreSQL não é muito diferente de um delete seguido por um insert.

Implicações

• Tuplas antigas (“mortas”) devem ser removidas da tabela eventualmente através de rotina conhecida como vacuum.
• Cada atualização de registro requer a adição de novas entradas nos índices da tabela, e modificar um índice é muito mais caro do que modificar a tabela em si.

Mas o que ocorre quando um registro é atualizado?

Os registros são armazenados em blocos. Quando um bloco está preenchido, novos registros serão armazenados no bloco seguinte e assim sucessivamente. Porém, quando atualizamos um registro, na verdade, estamos inserindo um novo registro e marcando o atual como “inválido” para uma limpeza futura. Consequentemente, esse novo registro poderá estar sendo armazenado em um novo bloco, caso não existir espaço livre no bloco original do registro.

Quando o novo registro é armazenado em um novo bloco, faz-se necessária a atualização de seus índices, pois os mesmos não estarão mais apontando para o bloco original. Como já mencionamos anteriormente, trata-se de uma operação custosa. Além disso, ainda será necessário pagar o preço em algum momento futuro de um vacuum para liberar o espaço ocupado pela versão “inválida do registro em seu bloco original.

Se o nosso objetivo é evitar toda e qualquer retenção desnecessária para agilizar updates e novos inserts, devemos privilegiar HOT UPDATES.

HOT UPDATES

Uma “Heap Only Tuple” é uma tupla que não é referenciada de fora do bloco da tabela. Isto é, caso tenha ocorrido algum update, o novo registro terá sido inserido no mesmo bloco de origem. Seu número de ponteiro de linha é armazenado na versão de linha antiga.

Neste caso, o PostgreSQL não precisará modificar os índices num processo de atualização, pois o endereço externo do registro permaneceu o mesmo.

Também reduzirá a necessidade de operações onerosas de vacuum, pois o qualquer operação que utilize o bloco — um select, por exemplo — reconhecerá a existência de registros “inválidos” através de hot links e, automaticamente, eliminará os mesmos.

O que é necessário para que ocorra um HOT UPDATE?

• Não pode haver definição de índice para alguma coluna que está sendo atualizada.
• Espaço no bloco para acomodar o registro atualizado.

Nem sempre podemos abdicar de criar índices para colunas que sofrem atualizações. Mas se for possível, estaremos viabilizando atualizações mais rápidas.

Para viabilizar a questão do espaço no bloco, podemos lançar mão do uso da configuração de storage de tabela chamado fillfactor.

FILLFACTOR

Ele determina o percentual de espaço que o banco de dados utilizará durante o processo de insert. Por default, 100% do espaço é utilizado, mas podemos alterar essa configuração para reservar algum espaço e garantir outro no bloco para as atualizações que saberemos que irão ocorrer.

alter table mytable set (fillfactor = 90);

Seu valor pode ser definido entre 10 e 100. Contudo, o mesmo irá acarretar em mais espaço utilizado em disco, quando abaixo de 100.

Conclusões

Recomenda-se viabilizar HOT UPDATES sempre que for possível. É um recurso bastante interessante para diminuir o custo de atualizações sobre dados de tabelas, sempre que for possível. Mas é preciso ter em mente estas duas premissas:

• espaço em blocos.
• restrições com relação à liberdade para a criação deliberada de índices.

Obviamente, é preciso levar em conta qual o propósito desejado. Se objetivo é privilegiar consultas rápidas, você não poderá abrir mão facilmente de todo e qualquer índice que melhore qualquer plano de execução ainda que pagando um preço maior em seus inserts e updates. Se a necessidade é garantir velocidade e o mínimo de desperdício de processamento em manutenções de limpeza, atualização de índices e estatísticas, considere observar quais tipos de updates ocorrem em seu banco de dados.

De fato, não existe uma fórmula mágica. Tudo é uma troca e questão de priorização.

Referências:

https://www.postgresql.org/docs/13/mvcc-intro.html

https://www.cybertec-postgresql.com/en/hot-updates-in-postgresql-for-better-performance/