Início / dba / Perguntas / 247478
Accepted
Chessbrain
Asked: 2019-09-11 15:41:16 +0800 CST

Maneira mais eficiente de inserir dados na tabela de arquivamento sem influenciar a produção?

  • 772

Por exemplo, digamos que temos a tabela A:

create table if not exists T(
column1 int,
column2 varchar,
column3 date
);

e tabela de arquivamento TArchive:

create table if not exists TArchive(
column1 int,
column2 varchar,
column3 date
);

Qual seria a melhor abordagem para inserir dados anteriores à xdata no TArchive sem bloquear a tabela T na produção? Sob a suposição de que a tabela T tem uma grande quantidade de linhas.

Estou pesquisando isso há horas. No SQL Server você tem diferentes abordagens como: https://www.brentozar.com/archive/2018/04/how-to-delete-just-some-rows-from-a-really-big-table/ Mas no PostgreSQL Eu mal consigo encontrar nada.

Você deve apenas pegar os dados diretamente da tabela T e importá-los para o TArchive?

Você deve importar seus dados para uma tabela temporária primeiro e depois importá-los para a tabela de arquivamento? E se sim, por que essa abordagem seria melhor quando você está fazendo 2x as inserções para os mesmos dados.

Quantas funções você deve fazer? Uma função para governá-los todos? Ou uma função para arquivamento e outra para deletar os dados antigos?

Existem outras abordagens?

postgresql performance

3 respostas

  1. Best Answer

    Você provavelmente não pode encontrar nenhum artigo sobre o assunto porque é muito simples:

    WITH old_rows AS (
   DELETE FROM t
   WHERE ...
   RETURNING *
)
INSERT INTO tarchive
SELECT * FROM old_rows;

    Isso não bloqueará tmuito a tabela — se simultâneas SELECTe modificações de dados não serão afetadas (a menos que estejam tentando modificar uma das linhas excluídas).

    Seus principais problemas são:

    • Essa instrução pode levar muito tempo, mantendo bloqueios e bloqueando o autovacuum enquanto é executado.

    • Depois, a mesa não será menor, mas mais vazia. Isso prejudica as verificações sequenciais e desperdiça espaço em cache.

    Particularmente, o segundo problema dói, e todos os remédios exigem que a mesa fique indisponível por um tempo:

    • VACUUM (FULL)irá reorganizar a tabela, mas bloqueará qualquer acesso à tabela enquanto ela é executada.

    • Substituir a tabela por uma diferente bloqueará as modificações de dados por um tempo:

      BEGIN;
LOCK TABLE bloated IN SHARE ROW EXCLUSIVE MODE;
CREATE TABLE bloated_copy (LIKE bloated INCLUDING ALL);
INSERT INTO bloated_copy SELECT * FROM bloated;
DROP TABLE bloated;
ALTER TABLE bloated_copy RENAME TO bloated;
COMMIT;

    Por tudo isso, é uma boa ideia planejar como você deseja se livrar dos dados antigos logo ao projetar seu sistema, mas, na minha experiência, isso quase sempre é esquecido. A maneira menos dolorosa de se livrar de dados antigos é particionar as tabelas por tempo.

    • 2

  2. A abordagem que funcionou para mim foi fazer o script de arquivamento funcionar em lotes com intervalos entre eles. Então ficaria assim:

    • encontre as primeiras 5.000 linhas que precisam de arquivamento
    • copiá-los para o arquivo
    • excluí-los da tabela de origem
    • dormir (30s)
    • continue repetindo até que não seja necessário arquivar mais linhas

    Dessa forma, seu servidor não ficará sob uma carga pesada enquanto isso for executado e o banco de dados nunca ficará bloqueado por muito tempo. A desvantagem é que isso levará mais tempo do que algumas das outras abordagens.

    • 2

  3. Antes de cavar mais, você considerou a replicação lógica? É muito legal no Postgres. Pode ou não ser a combinação certa para suas necessidades, mas vale a pena tê-la na mistura à medida que você considera as opções. Como exemplo, você pode ter a tabela de arquivamento em uma segunda instância do Postgres e usar a replicação lógica para replicar todas as operações INSERT e UPDATE, mas sem exclusões. Não são necessários gatilhos. (Se você deseja uma solução baseada em gatilho, existem soluções abrangentes disponíveis.) Se você precisar pesquisar o arquivo morto, ele está em outra tabela em outro servidor, mas você pode usar um Foreign Data Wrapper para torná-lo visível em seu servidor primário .

    Algumas perguntas:

    • Que tipo de requisitos de consulta você tem em seus dados arquivados?

    • Como você excluirá as linhas na tabela mestra? Pode ser melhor excluir em lotes em um loop?

    • Que tipo de números você está falando sobre excluir de uma só vez? AUTOVACUUM não é algo que você necessariamente antecipará vindo do SQL Server.

    • 0

relate perguntas