Ainda não usa o PDB Monitor?


Opa, pessoall, tudo certo?

Hoje, irei falar um pouco sobre tabelas versionadas. Quantas vezes você já não precisou saber os status de uma coluna, qual era o valor anterior, quando o valor alterou, quais status a coluna de uma tupla (brincadeira vamos falar linha mesmo) já esteve. Normalmente para obter isso precisamos de uma outra tabela, uma trigger pra atualizar, mas a partir do MariaDB 10.3.4 não temos mais essa necessidade. Ok, e quais outros “problemas” posso resolver com a implementação do versionamento? Podemos utilizá-lo para análise de dados, ver em quanto tempo o usuário leva pra passar de um ponto a outro, podemos fazer uma restauração pontual, afinal, quem nunca fez um update no id errado né (eu sei que você já fez isso, sim), e para forense e questões legais, talvez você precise guardar o dado por no mínimo um ano. Certo sei para que posso usar, mas como implemento, vamos lá:

Criando uma tabela versionada, para criar precisamos apenas adicionar ao final “WITH SYSTEM VERSIONING”:

CREATE TABLE teste1( id int(11) auto_increment primary key, value varchar(40) ) WITH SYSTEM VERSIONING;

Ou realizarmos o alter table:

ALTER TABLE teste2 ADD SYSTEM VERSIONING;

Para dropar, é só trocar o ADD por DROP, quando adicionamos o versionamento, o MariaDB implicitamente adiciona duas colunas escondidas, o ROW_START e o ROW_END, podemos deixar essas colunas explicitas da seguinte forma:

ALTER TABLE teste1 ADD COLUMN ts TIMESTAMP(6) GENERATED ALWAYS AS ROW START, ADD COLUMN te TIMESTAMP(6) GENERATED ALWAYS AS ROW END, ADD PERIOD FOR SYSTEM_TIME(ts, te), ADD SYSTEM VERSIONING;

Certo, agora, que que já sabemos, como faço para pesquisar esses históricos das tabelas? Fácil, você vai apenas adicionar “FOR SYSTEM_TIME” no seu select, exemplo:

SELECT * FROM teste1 FOR SYSTEM_TIME ALL;

Caso os valores ROW_START e ROW_END estejam implícitos, você pode obrigá-los a ficarem a mostra adicionando as colunas no select da seguinte forma:

SELECT *, ROW_START, ROW_END FROM teste1 FOR SYSTEM_TIME ALL;

Você pode também fazer selects com base na data da modificação, utilizando o “AS OF TIMESTAMP”, “BETWEEN x AND y” e “FROM x TO y”:

Como fiz a exclusão de alguns valores para teste é possível ver que, atualmente, a tabela está com apenas 3 linhas, mas no dia 17/02/2021 as 23:17:55 eu ainda não havia excluído os ID’s 4,5 e 6.

Certo, mas qual a diferença entre BETWEEN e FROM?

A diferença é que utilizando o FROM você o valor “final” não será incluído, e utilizando o BETWEEN irá incluir o valor “final”, lembrando que isso vai trazer como estava a tabela e histórico entre as datas passadas ou na dada especificada com o AS OF.

Caso você esteja se perguntando: “Mas, dessa forma, meu tablespace irá ficar gigante”, calma, calma, pequeno gafanhoto, você pode separar em partições, tendo, dessa forma, uma partição com os dados atuais e outra ou outras com os valores de histórico, porém para isso temos que ter alguns cuidados:

  • Tabelas particionadas não podem ter foreing key.
  • A partir, do MariaDB 10.5 as tabelas versionadas já são particionada entre no mínimo partição atual e histórico, ao adicionar o sistema de versionamento o MariaDB irá adicionar as partições da seguinte maneira:
CREATE TABLE t (x INT) WITH SYSTEM VERSIONING PARTITION BY SYSTEM_TIME ( PARTITION p0 HISTORY, PARTITION pn CURRENT );

Caso você queira separar por um período pode fazer da seguinte forma:

Certo, agora que sabemos como salvar, como buscar, como separar, e se precisarmos excluir os históricos, como fazemos? Podemos fazer isso de algumas maneiras:

Podemos dropar a partição:

ALTER TABLE teste3 DROP PARTITION p0;

Podemos deletar o histórico adicionando a palavra HISTORY no comando DELETE:

DELETE HISTORY FROM teste3:

Podemos fazer o delete escolhendo uma data:

DELETE HISTORY FROM teste2 BEFORE SYSTEM_TIME ‘2021-02-18 00:00:00:’

Podemos dropar todo sistema de versionamento e adicionar novamente:

ALTER TABLE teste1 DROP SYSTEM VERSIONING; ALTER TABLE teste1 ADD SYSTEM VERSIONING;

Voltando a criação, “mas poxa Roberto vou versionar toda minha tabela só queria versionar a coluna de status, ou não preciso versionar algumas colunas”, certo aqui está sua solução você pode escolher o que versionar ou o que não versionar nas colunas:

CREATE TABLE t ( x INT WITH SYSTEM VERSIONING, y INT ); CREATE TABLE t2 ( x INT, y INT WITHOUT SYSTEM VERSIONING ) WITH SYSTEM VERSIONING;

Finalizado, todo aprendizado de como utilizar vamos aos pontos negativos:

  • Implicitamente o MariaDB adiciona o ROW_END como primary key na sua tabela, então, você vai ter uma chave composta como primary key, como a segunda chave é uma data, podem ocorrem alguns erros durante a replicação, fiz alguns testes não consegui simular.
  • Você não poderá versionar uma coluna virtual
  • Mysqldump e qualquer backup lógico, não irá salvar os valores de histórico, apenas backups físicos.

Variáveis linkadas a função de versionamento:

system_versioning_alter_history: variável dinâmica, aceita os valores ERROR e KEEP, é responsável por autorizar ou não a alteração na tabela “ALTER TABLE”, pois feito sem cuidado pode implicar em errors ou warnings.

system_versioning_alter_history: variável dinâmica do tipo varchar, utilizada para realizar um FOR SYSTEM_TIME AS OF implícito, você inserindo a data, ela vai realizar o select baseado na data para todas as tabelas, importante: Não tem efeito em DML ou seja, INSERT SELECT or REPLACE SELECT.

Conclusão

A conclusão tirada das tabelas versionadas é que, sim, elas são super eficientes, e resolvem grande parte dos problemas com versionamento de dados nos bancos de dados, lados positivos, você pode guardar dados antigos ou versões antigas pelo tempo que quiser, esse problema não necessita mais de ter uma tabela de histórico para cada tabela que precisamos versionar, com triggers para cada uma, e sendo bem feito não teremos problemas de performance para enfrentar. Lados negativos, necessitamos tomar muito cuidado com o uso visto que, um ALTER TABLE pode ocasionar em problemas futuros, não tive problema, mas podemos ter problemas com as replicações por conta da primary key composta, temos que cuidar com os backups, pois caso forem lógicos teremos que fazer um script para obter estes dados, acredito que para muitos casos podemos utilizar com tranquilidade essa forma de versionamento, desde que sempre bem cuidado.

Espero ter ajudado ou agregado algum conteúdo a sua carreira, ajudado a solucionar um problema de forma simples, ou entender o porquê daquela replicação talvez ter parado.

Obrigado!!