como girar com segurança uma tabela postgresql?

Question

Eu tenho uma tabela pgsql que cresce bastante. Eu gostaria de rodá-lo usando um cron job - os dados só são usados para gerar relatórios e depois de uma semana ou duas, não é necessário.

    

Answer 1

A maneira correta de fazer isso é configurar o particionamento de tabelas.

link

Você estabelece uma restrição de verificação em uma coluna e, em seguida, usa uma regra orientada por essa verificação para direcionar as linhas inseridas na tabela pai para uma das várias tabelas filho. No seu caso, eu sugeriria o particionamento semanal. Se você quiser permitir que os usuários consultem uma única partição, basta nomear as partições logicamente - por exemplo, 2009_week_32 ou 2009_august_week_1 e fazer com que elas questionem as consultas na tabela correta.

Se você quiser que eles consultem várias tabelas juntos, você configura uma exibição que seleciona um união em várias tabelas e os usuários consultam a exibição.

Quando você deseja remover dados que não são mais necessários, basta alterar a tabela para alterar a regra e soltar a tabela que contém os dados antigos.

Isso é um pouco mais de trabalho do que a resposta do disabledleopard, mas a técnica é bastante comum em outros mecanismos de banco de dados. A desvantagem de usar apenas um "delete where" em uma coluna de registro de data e hora é que ela pode levar muito tempo em tabelas grandes e que os dados são gravados no diário. O Truncate foi projetado para contornar o problema de redação do diário, mas você não pode especificar uma cláusula where. Deixar cair uma tabela é muito rápido, pois você não está lidando com linhas individuais.

Você precisará de algum tipo de manutenção para configurar regras para cobrir datas futuras. Algumas pessoas fazem o roteiro mensalmente (basta checar 5 dias antes do final do mês se a partição do próximo mês existe, e se não criá-la e executar a mágica da alteração de tabela para alterar as regras) enquanto outras configuram vários meses ou anos de regras com antecedência e realizar o trabalho de manutenção manualmente a cada trimestre / ano, conforme o caso.

    

Answer 2

meu pensamento seria alterar o esquema dessa tabela para registrar o timestamp em que os dados foram inseridos.

ALTER TABLE blah ADD COLUMN entry_time timestamp DEFAULT current_timestamp;

Dessa forma, o que está criando atualmente a tabela não precisa ser alterado.

Então você pode então ser apenas o comando cron (deve funcionar com o postgres 8.x mas não tenho um servidor em execução para confirmar desculpas)

psql -c "DELETE FROM blah WHERE entry_time < (current_timestamp - interval '14 days') RETURNING *;" > deletelog.$( date "+%Y%m%d").log

Edit: Esqueci de mencionar que o bit "RETURNING *" faz com que o psql retorne todos os detalhes de todas as linhas deletadas, assim como as "N rows removed" usuais para que este log possa ser usado com um pouco de awk magic dados, se removidos prematuramente. Além disso, os registros de auditoria para exclusões automáticas são sempre uma boa ideia.