Soluções de arquivo de banco de dados

Minha sugestão sobre arquivamento:

1. Criar archive_tablespace(se você quiser, pode separar o hardware no arquivo)

2. Crie tabelas. Por exemplo, queremos arquivar postagens de tabela.

   create table  posts_all ( LIKE public.posts)  ;
   create table  posts_archive () inherits  ( public.posts_all)  ;
   alter table  public.posts  inherits ( public.posts_all ) ;
   

   Depois disso teremos 2 novas tabelas: public.posts_all (com as mesmas colunas dos posts) para consultar todos os posts (arquivo e produção) e public.posts_archive para consultar todos os posts do arquivo. Public.posts herdará de posts_all.
   As inserções devem ser feitas da maneira antiga (para a tabela public.posts), a menos que você escreva gatilhos em posts_all para redirecionar as inserções para a tabela de postagens. Se você tiver particionamento, será mais complicado. Com o aplicativo funcionando e antes da migração de dados antigos, você não precisa alterar nada no código do aplicativo para trabalhar com essa abordagem.

3. Crie um arquivo de esquema para separação lógica. Minha sugestão será separar os dados do arquivo por algum período de tempo (ano ou mês), se possível (archive_2005).

4. Crie tabelas de arquivo no esquema archive_year

   create table archive_2005.posts (
     check(record_date >= '2005-01-01 00:00:00'::timestamp 
       and record_date <  '2006-01-01 00:00:00'::timestamp)
   ) inherits (posts_archive) tablespace archive_tablesapce;
   

   Depois disso, você terá novas postagens de tabela no esquema archive_2005 e o planejador postgresql saberá que os dados existem apenas no período de tempo projetado. Se você consultar por outro período de tempo, o postgresql não pesquisará nesta tabela.

5. Crie funções/procedimentos/gatilhos para mover dados para arquivar tabelas.

6. Arquive uma vez por um período de tempo (ano aqui) e aspire a tabela antiga ou faça-o automaticamente por gatilhos (mais pesado no autovacuum). Existem muitas vantagens e desvantagens em ambas as técnicas.

Se implementado:

1. Pode consultar dados de arquivo (selecione * de posts_archive), todos (selecione * de posts_all) e de produção (selecione * de public.posts) separadamente
2. Pode descarregar esquemas de arquivo separadamente e descarregá-los em cascata de maneira fácil. pg_dump -s archive_2005 datase_name drop schema archive_2005 cascata; --tenha cuidado porque remove todas as tabelas relacionadas
3. Dados antigos separados fisicamente por tablespace e logicamente por schema.
4. Estrutura bastante complicada para gerenciar o processo de arquivamento
5. Pode criar diferentes índices em tabelas de produção e arquivamento para otimizar consultas para ambos (índices menores e especializados = consultas mais rápidas e menos espaço necessário)
6. Se você tiver tabelas particionadas (por ano ou mês), o processo de arquivamento será apenas para mover a tabela inteira archive_tablespaceou apenas alterá-la para herdar de posts_archive (não testei isso)
7. Se você não deseja acessar dados antigos (arquivados), não precisa alterar nada no aplicativo.

Esta é uma técnica geral e você deve adaptá-la às suas necessidades. Alguma sugestão para melhorar isso?

Leitura adicional: Herança do PostgreSQL , particionamento