Início / dba / Perguntas / 287316
Accepted
cis
Asked: 2021-03-20 01:18:32 +0800 CST

Execute DELETE em cada INSERT para impor o limite de linhas

  • 772

Usando o PostgreSQL 11. Considere uma tabela como

CREATE TABLE "logs" 
    (
      "id" INTEGER NOT NULL,
      "userId" INTEGER NOT NULL, 
      "timestamp" TIMESTAMP NOT NULL,
      CONSTRAINT "PK_8d33b9f1a33b412e4865d1e5465" PRIMARY KEY ("id")
     )

Agora, o requisito é que apenas 100 linhas sejam armazenadas por userId. Se mais dados entrarem, os logs mais antigos devem ser excluídos. Se, por um curto período de tempo, 101 linhas forem armazenadas, não será o fim do mundo. Tudo bem se a linha supérflua for excluída com alguns segundos de atraso.

Não consigo criar um banco de dados TRIGGER. Então, eu preciso escrever uma consulta que é acionada em um evento de criação de log na camada do aplicativo.

O SQL puro é preferível ao plpgsql.

Esta é a solução que encontrei:

WITH "userLogs" AS (SELECT id, timestamp FROM "logs"
                    WHERE "userId" = $1
                ),
"countLogs" AS (SELECT count(id) FROM "userLogs")
        
DELETE FROM "logs" WHERE id = ANY
                (
                    SELECT id FROM "userLogs" 
                    ORDER BY "timestamp" ASC 
                    LIMIT GREATEST( (SELECT count FROM "countLogs") - 100, 0)
                );

A ideia é: Sempre execute a DELETEe baseie a decisão se realmente algo tiver que ser excluído em LIMITuma subconsulta. Se houver mais de 100 logs, a subconsulta retornará os ids dos mais antigos a serem descartados. Caso contrário, LIMITserá 0, a subconsulta não retornará nada e nada será excluído.

Minhas perguntas agora são:

  1. É sensível executar uma DELETEconsulta em cada um INSERT- mesmo que não exclua nada?
  2. Existem implicações de desempenho aqui? (Ou outras armadilhas que talvez eu não conheça?)
  3. Não tenho certeza se preciso de um LOCK. Em meus testes, não consegui produzir nenhum comportamento inesperado ao executar INSERTs em paralelo, no entanto, pode haver casos de borda em que eu precisaria de um LOCK?

Edit : é difícil prever quantas vezes um INSERTserá executado nessa tabela. Se tudo correr bem (em termos de negócios), pode ser alguns milhares de vezes por dia em soma - e algumas dezenas de vezes por usuário a cada dia.

Edit 2 : timestampos valores não são necessariamente únicos por usuário: pode haver várias entradas de log com o mesmo timestampe o mesmo userId. Espera-se que a tabela obtenha mais colunas contendo o que realmente aconteceu.

postgresql delete

1 respostas

  1. Best Answer

    Se você tiver um índice em user_id, poderá eliminá-lo e substituí-lo por um índice em (user_id, timestamp). Isso também salvará uma classificação ao exibir as entradas de log mais recentes (WHERE user_id=... ORDER BY timestamp DESC LIMIT n).

    Então:

    SELECT timestamp FROM logs WHERE userid=1 ORDER BY timestamp DESC LIMIT 1 OFFSET 100

    Se houver mais de 100 linhas, isso retornará o carimbo de data/hora da 100ª linha. Caso contrário, não retornará nada. Para excluir os logs antigos de um usuário:

    DELETE FROM logs WHERE userid=1 AND timestamp <=
(SELECT timestamp FROM logs WHERE userid=1 ORDER BY timestamp DESC LIMIT 1 OFFSET 100);

    Esta é uma consulta muito rápida. Se o select não encontrar nenhuma linha para excluir, será bem abaixo de 1ms.

    Para excluir todos os logs antigos:

    DELETE FROM logs
USING (SELECT userid, f.timestamp FROM users CROSS JOIN LATERAL (SELECT timestamp FROM logs WHERE logs.userid=users.userid ORDER BY timestamp DESC LIMIT 1 OFFSET 100) f) oldlogs
WHERE logs.userid=oldlogs.userid AND logs.timestamp<=oldlogs.timestamp;

    Isso provavelmente fará uma varredura seq nos logs, então pode ser lento. Aqui está um melhor que explorará o índice em (userid, timestamp) e será rápido se não houver nada a fazer:

    DELETE FROM logs USING
( SELECT userid,timestamp FROM users 
  CROSS JOIN LATERAL (SELECT timestamp FROM logs WHERE logs.userid=users.userid ORDER BY timestamp DESC OFFSET 100) oldlogs ) o
WHERE logs.userid=o.userid AND logs.timestamp=o.timestamp;

    Para responder ao seu comentário "e se muitos logs tiverem o mesmo timestamp?"... Bem, isso nunca deve acontecer, pois se você deseja que seus logs sejam úteis, eles devem ser ordenados por algo único, caso contrário você não sabe em que ordem em que foram registrados. Mas... você pode simplesmente usar a chave primária:

    -- one user
DELETE FROM logs USING
( SELECT id FROM logs WHERE logs.userid=123 ORDER BY timestamp DESC, id DESC OFFSET 100 ) o
WHERE logs.id=o.id;

-- all users
DELETE FROM logs USING
( SELECT oldlogs.id FROM users 
  CROSS JOIN LATERAL (SELECT id FROM logs WHERE logs.userid=users.userid ORDER BY timestamp DESC, id DESC OFFSET 100) oldlogs ) o
WHERE logs.id=o.id;

    Portanto, se eles tiverem o mesmo timestamp, o ORDER BY manterá os ids mais altos que deveriam ter sido inseridos por último.

    • 1

relate perguntas