O que devo fazer (e definitivamente não) se você precisar
atualizar um grande número de registros na tabela PostgreSQL "multimilionária" usada ativamente - inicializar o valor do novo campo ou corrigir erros nos registros existentes? E, ao mesmo tempo, economize seu tempo e não perca o dinheiro da empresa devido ao tempo de inatividade.
Prepare os dados do teste:
CREATE TABLE tbl(k text, v integer); INSERT INTO tbl SELECT chr(ascii('a'::text) + (random() * 26)::integer) k , (random() * 100)::integer v FROM generate_series(1, 1000000) i;
Suponha que queremos apenas aumentar o valor de v em 1 para todos os registros com k no intervalo 'q' .. 'z'.
Porém, antes de iniciar os experimentos, salvaremos o conjunto de dados original para obter resultados "limpos" sempre:
CREATE TABLE _tbl AS TABLE tbl;
UPDATE: um por todos e todos por um
A opção mais fácil que vem imediatamente à mente é fazer tudo "em uma única atualização":
UPDATE tbl SET v = v + 1 WHERE k BETWEEN 'q' AND 'z';
[veja em explicar.tensor.ru]Uma operação bastante simples, ao que parece, em linhas completamente “curtas” levou mais de 2,5 segundos. E se a sua expressão é mais complicada, a linha é mais autêntica, há mais registros e até alguns gatilhos intervêm - o tempo pode aumentar, nem mesmo para minutos, mas para horas. Suponha que você esteja pronto para aguardar, e o resto do seu sistema, vinculado a essa base, se ele tiver uma carga OLTP ativa?
O problema é que, assim que UPDATE chega a um registro específico, ele o
bloqueia até o final da execução . Se, simultaneamente com o mesmo registro, ele quiser trabalhar em um UPDATE “local” lançado em paralelo, ele ainda
“aguentará” a espera de um bloco para a solicitação de atualização e permanecerá até o final de seu trabalho.
©
wumo.com/wumoO pior cenário é o de sistemas da Web, onde as conexões com o banco de dados são criadas conforme necessário - afinal, essas conexões "dangling" se acumulam e consomem os recursos do banco de dados e do cliente, se você não criar um mecanismo de defesa separado.
Dividir transações
Em geral, tudo não é muito bom se tudo for feito em uma solicitação. Sim, e mesmo se dividirmos uma grande UPDATE em várias pequenas, mas deixar tudo funcionando
em uma transação - o problema com o bloqueio permanecerá o mesmo, porque os registros mutáveis serão bloqueados até o final de toda a transação.
Portanto, precisamos dividir uma transação grande em várias. Para fazer isso, podemos usar meios externos e escrever algum tipo de script que gere transações separadas, ou usar as oportunidades que o próprio banco de dados pode nos fornecer.
CHAMADA e gerenciamento de transações
A partir do PostgreSQL 11, é
possível gerenciar transações dentro do código processual:
Nos procedimentos chamados pelo comando CALL, bem como em blocos de código anônimos (no comando DO), você pode concluir as transações executando COMMIT e ROLLBACK. Após a transação ser concluída por esses comandos, um novo será iniciado automaticamente.
Mas esta versão está longe de todos, e trabalhar com o CALL tem suas limitações. Portanto, tentaremos resolver nosso problema sem meios externos e para que ele funcione em todas as versões atuais, e mesmo com alterações mínimas no próprio servidor - para que não seja necessário compilar e reiniciar nada.
Pelo mesmo motivo, não consideraremos a opção de organizar
transações autônomas por meio de pg_background .
Gerenciando conexões “dentro” da base
O PostgreSQL historicamente usou métodos diferentes para
emular transações autônomas , gerando conexões adicionais separadas - por meio de linguagens procedurais adicionais ou do
módulo dblink padrão. A vantagem deste último é que, por padrão, ele está incluído na maioria das distribuições e apenas um comando é necessário para ativá-lo no banco de dados:
CREATE EXTENSION dblink;
"... e muitas, muitas crianças nojentas trouxeram"
Mas antes de criar uma ligação dblink, vamos primeiro descobrir como um “desenvolvedor regular” divide um grande conjunto de dados, que ele precisa atualizar, em pequenos.
Ingênuo LIMIT ... OFFSET
A primeira idéia é fazer uma pesquisa de "paginação":
"Vamos selecionar os próximos mil registros de cada vez" , aumentando OFFSET em cada nova solicitação:
UPDATE tbl T SET v = Tv + 1 FROM ( SELECT k , v FROM tbl WHERE k BETWEEN 'q' AND 'z' ORDER BY
Antes de testar o desempenho desta solução, restauraremos o conjunto de dados:
TRUNCATE TABLE tbl; INSERT INTO tbl TABLE _tbl;
Como vimos no plano acima, precisaremos atualizar aproximadamente 384 mil registros. Portanto, vamos ver imediatamente como as atualizações serão executadas mais perto do final -
na região da 300ª iteração de 1000 entradas :
[veja em explicar.tensor.ru]Ah ... atualizar a amostra no final de
todos os registros de 1K nos custará quase tanto tempo quanto a
versão original inteira !
Esta não é a nossa escolha. Ainda pode ser usado de alguma forma, se você receber poucas iterações e pequenos valores de OFFSET. Como o
LIMIT X OFFSET Y do banco de dados é equivalente a "
subtrair / selecionar / formar os primeiros registros X + Y e depois jogar o primeiro Y no lixo ", o que para grandes valores de Y parece trágico.
De fato, esse método
não pode ser aplicado ! Não apenas contamos com valores atualizados para a seleção, também corremos o risco de pular parte dos registros e atualizar a outra parte duas vezes se blocos com as mesmas chaves chegarem à borda da página:
Neste exemplo, atualizamos o registro verde duas vezes e o registro vermelho nunca. Só porque com os mesmos valores das chaves de classificação, a ordem dos próprios registros dentro desse bloco não é fixa.
Triste Ordem por ... Limite
Vamos modificar um pouco a tarefa - adicione um novo campo no qual escreveremos nosso valor v + 1:
ALTER TABLE tbl ADD COLUMN x integer;
Observe que esse design funciona quase instantaneamente, sem reescrever a tabela inteira. Mas se você adicionar um valor PADRÃO, então - apenas
a partir da 11ª versão .
Já ensinado por uma experiência amarga, vamos criar imediatamente um índice no qual apenas as entradas não inicializadas permanecerão:
CREATE INDEX CONCURRENTLY ON tbl(k, v) WHERE x IS NULL;
O índice CONCURRENTLY não bloqueia o trabalho de leitura e gravação com a tabela, enquanto rola lentamente até em um grande conjunto de dados.
Agora, a ideia é
"Vamos selecionar neste índice todas as vezes que apenas os primeiros mil registros " :
UPDATE tbl T SET x = Tv + 1 FROM ( SELECT k, v FROM tbl WHERE k BETWEEN 'q' AND 'z' AND x IS NULL ORDER BY k, v LIMIT 1000
[veja em explicar.tensor.ru]Já muito melhor - a duração de cada transação individual agora é menor em cerca de 6 vezes.
Mas vamos ver novamente em que o plano para a 200ª iteração se transformará:
Update on tbl t (actual time=530.591..530.591 rows=0 loops=1) Buffers: shared hit=789337 read=1 dirtied=1
O tempo piorou novamente (apenas 25%) e os buffers aumentaram - mas por quê?
O fato é que o
MVCC no PostgreSQL deixa "almas mortas" no índice - versões de registros já atualizados, agora não mais adequadas para o índice. Ou seja, tendo apenas os primeiros 1000 registros na 200ª iteração,
ainda fazemos a
varredura , embora mais tarde descartemos as 199K versões anteriores das tuplas já alteradas.
Se as iterações necessárias não forem necessárias várias centenas, mas várias centenas de milhares, a degradação será mais perceptível a cada execução subsequente da consulta.
ATUALIZAÇÃO por segmento
Na verdade, por que estamos tão apegados a esse valor de "1000 registros"? Afinal, não
temos motivos para escolher exatamente 1000 ou outro número específico. Nós apenas queríamos “cortar” todo o conjunto de dados em alguns
segmentos não necessariamente iguais - então vamos usar nosso índice existente para o objetivo a que se destina.
Um
par indexado (k, v) é excelente para nossa tarefa. Vamos criar uma consulta para que ela possa se basear no último par processado:
WITH kv AS ( SELECT k, v FROM tbl WHERE (k, v) > ($1, $2) AND k BETWEEN 'q' AND 'z' AND x IS NULL ORDER BY k, v LIMIT 1 ) , upd AS ( UPDATE tbl T SET x = Tv + 1 WHERE (Tk, Tv) = (TABLE kv) AND Tx IS NULL RETURNING k, v ) TABLE upd LIMIT 1;
Na primeira iteração, basta definir os parâmetros da consulta com o
valor "zero" ('', 0) e, para cada próxima iteração, obtemos o
resultado da consulta anterior .
[veja em explicar.tensor.ru]O tempo de transação / bloqueio é menor que um milissegundo, não há degradação do número de iterações, não é necessária uma varredura preliminar completa de todos os dados na tabela. Ótimo!
Colocando a versão final com dblink DO $$ DECLARE k text = ''; v integer = 0; BEGIN PERFORM dblink_connect('dbname=' || current_database() || ' port=' || current_setting('port'));
Uma vantagem adicional desse método é a capacidade de interromper a execução desse script a qualquer momento e depois retomar a partir do ponto desejado.
Cálculos complexos em UPDATE
Mencionarei separadamente a situação com o difícil cálculo do valor atribuído - quando você precisar calcular algo das tabelas vinculadas.
O tempo gasto em computação também aumenta a duração da transação. Portanto, a melhor opção seria
levar o processo de cálculo desses valores além de UPDATE.
Por exemplo, queremos preencher nosso novo campo x com o número de registros que têm o mesmo valor (k, v). Vamos criar uma tabela "temporária", cuja geração não impõe bloqueios adicionais:
CREATE TABLE tmp AS SELECT k, v, count(*) x FROM tbl GROUP BY 1, 2; CREATE INDEX ON tmp(k, v);
Agora podemos iterar de acordo com o modelo descrito acima, de acordo com esta tabela, atualizando o destino:
UPDATE tbl T SET x = Sx FROM tmp S WHERE (Tk, Tv) = (Sk, Sv) AND (Sk, Sv) = ($1, $2);
Como você pode ver, nenhum cálculo complicado é necessário.
Lembre-se de excluir a tabela auxiliar posteriormente.