Projetamos com sucesso a estrutura de nosso banco de dados PostgreSQL para armazenar correspondência, um ano se passou, os usuários estão preenchendo-o ativamente e agora ele contém milhões de registros, e... algo começou a desacelerar.
- Parte 2: seção “com fins lucrativos”
Fato é que À medida que o tamanho da tabela aumenta, também aumenta a “profundidade” dos índices. - embora logaritmicamente. Mas com o tempo isso força o servidor a executar as mesmas tarefas de leitura/gravação processar muitas vezes mais páginas de dadosdo que no início.
É aqui que vem ao resgate seccionando.
Deixe-me observar que não estamos falando de sharding, ou seja, distribuição de dados entre diferentes bancos de dados ou servidores. Porque mesmo dividindo os dados em alguns servidores, você não se livrará do problema de “inchaço” dos índices com o tempo. É claro que se você puder colocar um novo servidor em operação todos os dias, seus problemas não estarão mais no plano de um banco de dados específico.
Consideraremos não scripts específicos para implementar o particionamento “em hardware”, mas a abordagem em si - o que e como deve ser “cortado em fatias” e a que leva tal desejo.
Conceito
Vamos definir nosso objetivo mais uma vez: queremos ter certeza de que hoje, amanhã e daqui a um ano, a quantidade de dados lidos pelo PostgreSQL durante qualquer operação de leitura/gravação permaneça aproximadamente a mesma.
Para qualquer dados acumulados cronologicamente (mensagens, documentos, logs, arquivos, ...) a escolha natural como chave de particionamento é data/hora do evento. No nosso caso, tal evento é momento de enviar a mensagem.
Observe que os usuários quase sempre trabalhe apenas com os “mais recentes” esses dados - eles leem as mensagens mais recentes, analisam os registros mais recentes,... Não, é claro, eles podem voltar no tempo, mas fazem isso muito raramente.
A partir destas restrições fica claro que a solução de mensagem ideal seria seções "diárias" - afinal, nosso usuário quase sempre lerá o que lhe ocorreu “hoje” ou “ontem”.
Se escrevermos e lermos quase apenas uma seção durante o dia, isso também nos dará uso mais eficiente de memória e disco - já que todos os índices de seção cabem facilmente na RAM, ao contrário dos “grandes e gordos” em toda a tabela.
passo a passo
Em geral, tudo o que foi dito acima soa como um lucro contínuo. E é possível, mas para isso teremos que nos esforçar muito - porque a decisão de particionar uma das entidades leva à necessidade de “ver” o associado.
Mensagem, suas propriedades e projeções
Como decidimos cortar as mensagens por datas, faz sentido dividir também as entidades-propriedades que delas dependem (arquivos anexados, lista de destinatários), e também por data da mensagem.
Como uma de nossas tarefas típicas é visualizar precisamente os registros de mensagens (não lidas, recebidas, todas), também é lógico “atraí-los” para o particionamento por datas de mensagens.
Adicionamos a chave de particionamento (data da mensagem) a todas as tabelas: destinatários, arquivo, registros. Você não precisa adicioná-lo à mensagem em si, mas use o DateTime existente.
Tópicos
Como existe apenas um tópico para várias mensagens, não há como “cortá-lo” no mesmo modelo, é preciso contar com outra coisa. No nosso caso é ideal data da primeira mensagem na correspondência — isto é, o momento da criação, de fato, do tema.
Adicione a chave de particionamento (data do tópico) a todas as tabelas: tópico, participante.
Mas agora temos dois problemas ao mesmo tempo:
- Em qual seção devo procurar mensagens sobre o tema?
- Em qual seção devo procurar o tópico da mensagem?
Podemos, claro, continuar a pesquisar em todas as secções, mas isso será muito triste e anulará todos os nossos ganhos. Portanto, para saber exatamente onde procurar, faremos links/ponteiros lógicos para as seções:
- vamos adicionar na mensagem campo de data do tópico
- vamos adicionar ao tópico data da mensagem definida esta correspondência (pode ser uma tabela separada ou uma matriz de datas)
Como haverá poucas modificações na lista de datas de mensagens para cada correspondência individual (afinal, quase todas as mensagens caem em 1-2 dias adjacentes), vou me concentrar nesta opção.
No total, a estrutura da nossa base de dados assumiu a seguinte forma, tendo em conta o particionamento:
Tabelas: RU, se você tem aversão ao alfabeto cirílico nos nomes das tabelas/campos, é melhor não procurar
-- секции по дате сообщения
CREATE TABLE "Сообщение_YYYYMMDD"(
"Сообщение"
uuid
PRIMARY KEY
, "Тема"
uuid
, "ДатаТемы"
date
, "Автор"
uuid
, "ДатаВремя" -- используем как дату
timestamp
, "Текст"
text
);
CREATE TABLE "Адресат_YYYYMMDD"(
"ДатаСообщения"
date
, "Сообщение"
uuid
, "Персона"
uuid
, PRIMARY KEY("Сообщение", "Персона")
);
CREATE TABLE "Файл_YYYYMMDD"(
"ДатаСообщения"
date
, "Файл"
uuid
PRIMARY KEY
, "Сообщение"
uuid
, "BLOB"
uuid
, "Имя"
text
);
CREATE TABLE "РеестрСообщений_YYYYMMDD"(
"ДатаСообщения"
date
, "Владелец"
uuid
, "ТипРеестра"
smallint
, "ДатаВремя"
timestamp
, "Сообщение"
uuid
, PRIMARY KEY("Владелец", "ТипРеестра", "Сообщение")
);
CREATE INDEX ON "РеестрСообщений_YYYYMMDD"("Владелец", "ТипРеестра", "ДатаВремя" DESC);
-- секции по дате темы
CREATE TABLE "Тема_YYYYMMDD"(
"ДатаТемы"
date
, "Тема"
uuid
PRIMARY KEY
, "Документ"
uuid
, "Название"
text
);
CREATE TABLE "УчастникТемы_YYYYMMDD"(
"ДатаТемы"
date
, "Тема"
uuid
, "Персона"
uuid
, PRIMARY KEY("Тема", "Персона")
);
CREATE TABLE "ДатыСообщенийТемы_YYYYMMDD"(
"ДатаТемы"
date
, "Тема"
uuid
PRIMARY KEY
, "Дата"
date
);
Economize um lindo centavo
Bem, e se não usarmos com base na distribuição dos valores dos campos (através de triggers e herança ou PARTITION BY), e “manualmente” no nível da aplicação, você notará que o valor da chave de particionamento já está armazenado no nome da própria tabela.
Então, se você é tão Você está muito preocupado com a quantidade de dados armazenados?, então você pode se livrar desses campos “extras” e endereçar tabelas específicas. É verdade que todas as seleções de várias seções, neste caso, deverão ser transferidas para o lado do aplicativo.
Fonte: habr.com