Meus 2 cents extendidos,
Minha situacao foi um projeto de IoT onde dispositivos mandavam telemetria e a pesquisa poderia acontecer de diversas formas.
Como resolvi:
- Separar tabela de persistencia de tabela de consulta
A de persistencia nao tinha indices, exceto pela PK (indices sao um problema serio em tabelas de alto volume)
Eu tinha uma tabela simples separada que mantinha a data e a PK (em alguns casos - sem PK so a Heap), assim se precisasse por alguma razao consultar os registros inseridos no dia X, consultava a tabela simples, pela qual sabia o range de ID que tinha de usar para a primera tabela.
Cheguei a usar um ID como BigInt e numeracao que comecava em ANOMESDIASEQ, p.ex., 260328000000001 e uma cron que resetava isso para o dia seguinte (260329000000000) mas no final das contas comecei a usar a tabela com ID normal e a outra so indicando o ID inicial e a data dele.
- Quando chegava um registro de telemetria, ele ia para 2 filas separadas
Fila 1: insert na tabela de persistencia
Fila 2: insert na tabela de consulta, totalmente assincrona
- Na tabela de consulta, joguei sujo como podia
3.1. As vezes nao era apenas uma tabela de consulta, as vezes tinha mais de uma com campos e normalizacao diferentes para diferentes tipos de consulta
3.2. Mandei a 3FN as favas - se um JOIN me complicava a vida (latencia), era eliminado com o campo equivalente sendo de-normalizado direto na tabela de consulta. Eh de matar DBA de desgosto e comia espaco em disco, mas agilidade contava mais
3.3. Em alguns BD se voce criar um indice que seja igual a tabela, ele ignora a tabela e usa o indice direto para manter os dados. Tem custo de insercao, mas depois a consulta e recuperacao sao absurdamente altos. Por isso filas separadas - a fila de consulta era assincrona e poderia demorar mais para inserir os dados, mas isso nao era um problema.
3.4. Logs de SQL eram registrados e olhavamos o que o pessoal consultava mais - criamos indices e otimizacoes especificas para aquilo que era mais usado.
3.5. Nao dava da escalar verticalmente (muito caro e nao era tao eficiente), enntao era tudo na horizontal: criei instancias separadas para consultas especificas.
3.6. Tabela de consulta nao tinha "redo logs" e tudo era cache
3.7. Lotes: a fila de consulta fazia buferizacao antes de inserir, de forma a mandar lotes de registros, tanto para diminuir overhead de rede quando processamento nos BDs de consulta (e tambem nao ficar com tanto sync wait).
3.8. Tabelas de consulta realizam sync para disco "se e quando" desse - o prioritario era manter em memoria o maximo possivel.
Tudo isso agilizava, mas o trade-off era risco de ter de reprocessar a fila de consulta caso uma instancia travasse (por isso tinha a tabela dos ID e data da tabela de persistencia) - mas na pratica do dia-a-dia isso se mostrou um medo que raramente ocorria.
Saude e Sucesso !