Devo usar essa estratégia de particionamento/indexação para uma tabela que cresce cerca de um bilhão de linhas por ano?

Sou um engenheiro de software com cerca de 10 anos de experiência na construção de aplicações web Laravel para pequenas empresas. Estou bastante confortável com o design de banco de dados, mas esse não é meu maior ponto forte.

Um dos aplicativos que desenvolvi é uma ferramenta de terceiros para monitorar aberturas e cliques em boletins informativos por e-mail. Este aplicativo armazena eventos de abertura e clique, e os usuários da ferramenta consultam esses eventos para ver o desempenho de seus boletins informativos por e-mail. A contagem atual de linhas da tabela de eventos analíticos é de cerca de 1,5 bilhão, o que representa cerca de um ano e meio de dados.

Pretendo migrar para um novo design de banco de dados e gostaria de ver se meu entendimento do design de banco de dados está no caminho certo.

Estas são as tabelas do novo banco de dados:

Table contacts {
  id integer
  email_address string
  created_at datetime
  updated_at datetime
  indexes {
    id [pk]
    email_address
    created_at
  }
}

Table contact_opens {
  id unsignedBigInteger
  contact_id integer
  sent_at datetime
  opened_at datetime
  indexes {
    id [pk]
    (contact_id, sent_at)
    sent_at
    opened_at
  }
}

Table contact_clicks {
  id unsignedBigInteger
  contact_id integer
  sent_at datetime
  clicked_at datetime
  indexes {
    id [pk]
    (contact_id, sent_at)
    sent_at
    clicked_at
  }
}

Pretendo usar o MariaDB em um servidor linode dedicado com 64 GB de RAM, particionar o banco de dados por mês e ajustar o InnoDB da seguinte maneira:

innodb_buffer_pool_size = 48GB
InnoDB File-Per-Table = ON
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = 0_DIRECT
innodb_log_file_size = 8GB
table_open_cache = 20

As consultas neste banco de dados segmentarão os Contatos com base em Aberturas e Cliques. Por exemplo:

• Contatos com 5 ou mais aberturas nos últimos 30 dias
• Contatos com pelo menos 1 clique nos últimos 5 dias
• Contatos com 1 clique em cada um dos últimos 30 dias
• etc., consultas padrão de segmentação de boletins informativos por e-mail (como visto no Mailchimp)

As consultas retornarão de 10 mil a 300 mil contatos por vez.

Fiz muitas pesquisas e parece que indexação, particionamento e/ou fragmentação parecem ser a melhor opção para ajudar a acelerar as consultas. Parece que uma combinação de indexação e particionamento seria mais adequada para este caso de uso específico.

Esse uso parece um design de banco de dados sólido, onde a tabela de aberturas cresce cerca de um bilhão de linhas por ano e a tabela de cliques cresce cerca de 300 milhões de linhas por ano?

Atualização 1: o esquema não será alterado. Ele está mudando um pouco em relação à forma como foi projetado anos atrás, mas apenas para acomodar as alterações descritas nesta questão para tornar as consultas mais rápidas.

Atualização 2: As 300 mil consultas de contato são para correspondências em massa (boletins informativos por e-mail). Atualizados os tipos de dados nas tabelas MySQL acima.