Removendo registros do banco de dados periodicamente que ultrapassam a linha do tempo

Recentemente, configurei uma solução de arquivamento semelhante. Os números e o destino são um pouco diferentes (este último movendo dados antigos para uma partição de arquivo), mas a ideia é a mesma.

Primeiro, você precisará de um índice no carimbo de data/hora, para localizar rapidamente as linhas a serem excluídas. Para evitar travamentos excessivos e consequente bloqueio de outros processos, isso é feito preferencialmente com a CONCURRENTLYopção.

Em segundo lugar, você deve manter sua tabela organizada limpando-a regularmente - se você tiver uma versão relativamente recente do PostgreSQL, há chances de que o banco de dados cuide disso com autovacuum.

A DELETEdeclaração real será bastante simples. No meu caso, utilizo uma função para isso, para que possamos manter a lógica do banco de dados sob controle de versão. Isso pode parecer

CREATE OR REPLACE FUNCTION delete_function()
RETURNS void
LANGUAGE sql
AS $body$
DELETE FROM big_table
WHERE report_timestamp < now() - interval '6 months';
$body$;

Você precisará de um script (digamos, um script bash), que simplesmente chama a instrução, como

#!/bin/bash

psql {connection parameters} -c 'SELECT delete_function()'

A única coisa que resta é configurar uma tarefa cron que chama esse script nos intervalos desejados.

Ainda há uma coisa a considerar, ou seja, que a primeira exclusão seria enorme. Eu evitaria isso excluindo a grande quantidade de dados antigos em blocos de tamanho adequado, novamente para evitar o bloqueio de outros processos que desejam acessar a tabela. O tamanho desses pedaços pode ser muito diferente com base em suas circunstâncias. Observe que este é mais ou menos um processo manual, a menos que você já tenha um 'chunker' em uso (como nós).