DBA: limpando registros de clones de uma tabela sem PK

Há situações em que, em uma tabela sem uma chave primária ou algum outro índice exclusivo, clones completos de registros já existentes caem inadvertidamente.



Por exemplo, os valores da métrica cronológica são gravados no fluxo COPY do PostgreSQL e, em seguida, uma falha repentina, e parte dos dados completamente idênticos volta novamente.

Como livrar o banco de dados de clones desnecessários?

Quando PK não é um assistente

A maneira mais fácil é impedir que isso aconteça. Por exemplo, role a mesma tecla PRIMARY. Mas isso nem sempre é possível sem aumentar a quantidade de dados armazenados.

Por exemplo, se a precisão do sistema original for maior que a precisão do campo no banco de dados:

metric | ts | data -------------------------------------------------- cpu.busy | 2019-12-20 00:00:00 | {"value" : 12.34} cpu.busy | 2019-12-20 00:00:01 | {"value" : 10} cpu.busy | 2019-12-20 00:00:01 | {"value" : 11.2} cpu.busy | 2019-12-20 00:00:03 | {"value" : 15.7} 

Você já reparou? A contagem, em vez de 00:00:02, foi gravada no banco de dados com ts um segundo antes, mas permaneceu bastante válida do ponto de vista do aplicativo (afinal, os valores dos dados são diferentes!).

Obviamente, você pode criar PK (métrica, ts) - mas, em seguida, obteremos conflitos de inserção para dados válidos.

Você pode criar PK (métrica, ts, dados) - mas isso aumentará muito seu volume, que não usaremos.

Portanto, a opção mais correta é criar um índice regular não exclusivo (métrica, ts) e lidar com problemas após o fato, se eles surgirem.

"A guerra clônica começou"

Ocorreu algum tipo de acidente, e agora temos que destruir os registros de clone da tabela.



Vamos simular os dados de origem:

 CREATE TABLE tbl(k text, v integer); INSERT INTO tbl VALUES ('a', 1) , ('a', 3) , ('b', 2) , ('b', 2) -- oops! , ('c', 3) , ('c', 3) -- oops!! , ('c', 3) -- oops!! , ('d', 4) , ('e', 5) ; 

Então nossa mão tremia três vezes, Ctrl + V preso, e agora ...

Primeiro, vamos entender que nossa tabela pode ser muito grande; portanto, depois de encontrarmos todos os clones, é aconselhável literalmente "cutucar um dedo" para excluir registros específicos sem precisar procurá-los novamente .

E existe uma maneira - trata- se do ctid , o identificador físico de um registro específico.

Ou seja, primeiro, precisamos coletar registros ctid no contexto do conteúdo completo da linha da tabela. A opção mais fácil é converter a linha inteira em texto:

 SELECT T::text , array_agg(ctid) ctids FROM tbl T GROUP BY 1; 

 t | ctids --------------------------------- (e,5) | {"(0,9)"} (d,4) | {"(0,8)"} (c,3) | {"(0,5)","(0,6)","(0,7)"} (b,2) | {"(0,3)","(0,4)"} (a,3) | {"(0,2)"} (a,1) | {"(0,1)"} 

Sim, vemos imediatamente que, se houver mais de um registro na matriz, é tudo o que há clones. Vamos deixar apenas eles:

 SELECT unnest(ctids[2:]) FROM ( SELECT array_agg(ctid) ctids FROM tbl T GROUP BY T::text ) T; 

 unnest ------ (0,6) (0,7) (0,4) 

Como o valor da string serializada em si não é interessante para nós, nós simplesmente o jogamos fora das colunas retornadas da subconsulta.

Tudo o que resta é fazer com que DELETE use o conjunto que recebemos:

 DELETE FROM tbl WHERE ctid = ANY(ARRAY( SELECT unnest(ctids[2:]) FROM ( SELECT array_agg(ctid) ctids FROM tbl T GROUP BY T::text ) T )::tid[]); 

Verifique-se:


[veja em explicar.tensor.ru]

Sim, está tudo correto: nossos três registros foram selecionados para a única verificação Seq de toda a tabela e o nó Excluir usou uma única passagem para procurar dados usando o Tid Scan :

 -> Tid Scan on tbl (actual time=0.050..0.051 rows=3 loops=1) TID Cond: (ctid = ANY ($0)) 

Se você limpou muitos registros, não se esqueça de dirigir o VACUUM ANALYZE .

Vamos verificar uma tabela maior e com muitas tomadas:

 TRUNCATE TABLE tbl; INSERT INTO tbl SELECT chr(ascii('a'::text) + (random() * 26)::integer) k -- a..z , (random() * 100)::integer v -- 0..99 FROM generate_series(1, 10000) i; 

[veja em explicar.tensor.ru]

Portanto, o método funciona com êxito, mas deve ser aplicado com alguma cautela. Porque para cada registro excluído, há uma leitura da página de dados no Tid Scan e uma em Excluir.