Esse foi um post muito bacana sobre otimização. Mas sinceramente, foi uma gambiarra sem igual.
O próprio PostgreSQL possui um sistema de particionamento nativo. Bastava criar uma tabela particionada por data e o banco lidaria em restringir consultas automaticamente para sua partição correta.
Além disso daria para usar índices parciais, ou índices BRIN que teria um efeito semelhante ao que você alcançou.
De qualquer forma, essa foi uma ideia bastante criativa para solucionar seu problema
Opa.
Obrigado pela resposta.
Eu fiz testes com o sistema de particionamento do Postgres mas não obtive resultados satisfatórios.
O problema era que em uma parte era preciso fazer um count nos registros pra fins de paginação e em nenhum dos meus testes eu consegui resultados aceitáveis com o postgres.
Entendo. No caso, eu evitaria usar o "Count" diretamente e, ao invés disso, criaria uma tabela separada para gerenciar a contagem de registros de forma incremental, o que tornaria a operação de paginação muito mais eficiente.
Na realidade eu partiria de algumas premissas de manutenção do banco de dados como:
Índices por data (366 por ano) com Time-To-Live (TTL) de 1 ano:
Sub tabela de contagem para cada índice, para facilitar a paginação sem usar o caro COUNT:
Assim, sempre que for necessário realizar paginação, a contagem já estará disponível de forma simples e rápida, com um SELECT.
Mas, obviamente, isso é especulação minha com base nas informações fornecidas. Foi mais um exercício lógico sobre como eu estruturaria a arquitetura no PostgreSQL, do que uma solução definitiva para a sua situação.
Estava pensando exatamente nisso, tenho um cenário parecido, porém eu tenho 20MM de registros ao dia que tenho que manter durante 5 anos, sem particionamento eu teria me arrebentado.