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 dados 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 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, este novo registro poderá estar sendo armazenado em um novo bloco caso não existir espaço livre no bloco original do registro.

Ilustração representando atualizações com storage em diferentes blocos

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.

Ilustração representando um hot update — atualização com storage no mesmo bloco

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 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 de 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 para garantir espaço 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 e;
• 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. Mas 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/