Base de données Messenger (partie 2) : partitionner « à but lucratif »

Nous avons conçu avec succès la structure de notre base de données PostgreSQL pour stocker la correspondance, un an s'est écoulé, les utilisateurs la remplissent activement et maintenant elle contient des millions de disques, et... quelque chose a commencé à ralentir.

• Partie 1 : conception du châssis de base
• Partie 2 : sectionnement « à but lucratif »

Le fait est que À mesure que la taille de la table augmente, la « profondeur » des index augmente également. - quoique logarithmiquement. Mais au fil du temps, cela oblige le serveur à effectuer les mêmes tâches de lecture/écriture. traiter beaucoup plus de pages de donnéesqu'au début.

C'est ici qu'il vient à la rescousse sectionnement.

Permettez-moi de noter que nous ne parlons pas de sharding, c'est-à-dire de distribution de données entre différentes bases de données ou serveurs. Parce que même en divisant les données en certains serveurs, vous ne vous débarrasserez pas du problème du « gonflement » des index au fil du temps. Il est clair que si vous pouvez vous permettre de mettre en service un nouveau serveur chaque jour, alors vos problèmes ne résideront plus du tout dans le plan d'une base de données spécifique.

Nous ne considérerons pas des scripts spécifiques pour implémenter le partitionnement « dans le matériel », mais l'approche elle-même - quoi et comment doit être « coupé en tranches », et à quoi conduit un tel désir.

Concept

Définissons à nouveau notre objectif : nous voulons nous assurer qu'aujourd'hui, demain et dans un an, la quantité de données lues par PostgreSQL lors de toute opération de lecture/écriture reste à peu près la même.

Pour toute données accumulées chronologiquement (messages, documents, journaux, archives, ...) le choix naturel comme clé de partitionnement est date/heure de l'événement. Dans notre cas, un tel événement est moment de l'envoi du message.

Notez que les utilisateurs presque toujours travailler uniquement avec les "derniers" ces données - ils lisent les derniers messages, analysent les derniers journaux,... Non, bien sûr, ils peuvent remonter plus loin dans le temps, mais ils le font très rarement.

A partir de ces contraintes, il est clair que la solution optimale pour le message serait rubriques "quotidiennes" - après tout, notre utilisateur lira presque toujours ce qui lui est arrivé « aujourd'hui » ou « hier ».

Si nous écrivons et lisons presque seulement dans une seule section pendant la journée, cela nous donne également utilisation plus efficace de la mémoire et du disque - puisque tous les index de section s'intègrent facilement dans la RAM, contrairement aux « gros et gros » tout au long du tableau.

pas à pas

En général, tout ce qui précède ressemble à un profit continu. Et c'est réalisable, mais pour cela, nous devrons faire de gros efforts - car la décision de partitionner l’une des entités conduit à la nécessité de « voir » les entités associées.

Message, ses propriétés et projections

Puisque nous avons décidé de découper les messages par dates, il est logique de diviser également les entités-propriétés qui en dépendent (fichiers joints, liste des destinataires), et également par date du message.

Étant donné que l'une de nos tâches typiques consiste à visualiser précisément les registres de messages (non lus, entrants, tous), il est également logique de les « intégrer » dans le partitionnement par date de message.

Nous ajoutons la clé de partitionnement (date du message) à toutes les tables : destinataires, fichier, registres. Vous n'êtes pas obligé de l'ajouter au message lui-même, mais utilisez le DateTime existant.

fils

Puisqu’il n’y a qu’un seul sujet pour plusieurs messages, il n’y a aucun moyen de le « découper » dans le même modèle, il faut s’appuyer sur autre chose. Dans notre cas c'est l'idéal date du premier message en correspondance — c'est-à-dire le moment de la création, en fait, du sujet.

Ajoutez la clé de partitionnement (date du sujet) à toutes les tables : sujet, participant.

Mais maintenant, nous sommes confrontés à deux problèmes à la fois :

• Dans quelle section dois-je rechercher des messages sur le sujet ?
• Dans quelle section dois-je rechercher le sujet du message ?

Nous pouvons bien sûr continuer à chercher dans toutes les sections, mais cela sera très triste et annulera tous nos gains. Par conséquent, afin de savoir exactement où chercher, nous ferons des liens/pointeurs logiques vers des sections :

• nous ajouterons le message champ de date du sujet
• ajoutons au sujet date du message définie cette correspondance (peut être un tableau séparé ou un tableau de dates)

Puisqu'il y aura peu de modifications dans la liste des dates de message pour chaque correspondance individuelle (après tout, presque tous les messages tombent 1 à 2 jours adjacents), je me concentrerai sur cette option.

Au total, la structure de notre base de données a pris la forme suivante, en tenant compte du partitionnement :

Tableaux : RU, si vous avez une aversion pour l'alphabet cyrillique dans les noms de tables/champs, mieux vaut ne pas chercher

-- секции по дате сообщения
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
);

Économisez un joli centime

Eh bien, et si nous n'utilisons pas option de coupe classique en fonction de la distribution des valeurs des champs (via des déclencheurs et l'héritage ou PARTITION BY), et « manuellement » au niveau de l'application, vous remarquerez que la valeur de la clé de partitionnement est déjà stockée dans le nom de la table elle-même.

Alors si tu l'es Êtes-vous très inquiet de la quantité de données stockées ?, vous pouvez alors vous débarrasser de ces champs « supplémentaires » et adresser des tables spécifiques. Certes, dans ce cas, toutes les sélections de plusieurs sections devront être transférées du côté de l'application.

Source: habr.com