Messenger database (part 2): live partitioneren

We successfully designed the structure of our PostgreSQL database for storing correspondence. A year has passed, and users are actively filling it up; it already contains millions of records, and… everything has begun to lag a bit.

• Deel 1: we ontwerpen de basisstructuur van de database
• Deel 2: we secties ‘live’

Het punt is dat With the increase in the volume of the table, the 'depth' of the indexes also increases — albeit logarithmically. But over time, this forces the server to handle significantly more pages of data for the same read/write tasks, than at the beginning.

This is where partitioning.

comes into play. I note that we are not talking about sharding, or distributing data across different databases or servers. Because even if you divide the data among different een aantal servers, you will not eliminate the problem of index 'bloating' over time. It is clear that if you can afford to bring a new server online every day, your problems will be located in a completely different plane than that of a specific database.

We will examine not specific scripts for implementing partitioning 'in hardware', but rather the approach — what and how should be 'cut into pieces', and what this desire leads to.

Concept

Once again, let us define our goal: we want to ensure that the amount of readable PostgreSQL data during any read/write operation remains approximately the same today, tomorrow, and a year from now.

For any chronologically accumulating data (messages, documents, logs, archives, …), the natural choice for a partitioning key is the date/time of the event. In our case, that event is the moment of sending the message.

Note that users almost always work only with the 'latest' such data — they read the most recent messages, analyze the latest logs,… Of course, they may scroll further back in time, but they do this very rarely.

From these limitations, it becomes obvious that the optimal solution for messages would be 'daily' sections — since almost always our user will read what arrived 'today' or 'yesterday'.

If we write and read practically only within one section throughout the day, this also gives us more efficient use of memory and disk — because all the indexes of the section easily fit into RAM, unlike 'large and bulky' ones across the entire table.

step-by-step

In het algemeen klinkt alles wat hierboven is gezegd als één grote winst. En die is haalbaar, maar daarvoor moeten we wel ons best doen — want de beslissing om een van de entiteiten te splitsen leidt tot de noodzaak om ook gerelateerde.

Berichten, hun eigenschappen en projecties

Aangezien we besloten hebben om berichten op datum te splitsen, is het logisch om ook de aan hen gerelateerde entiteiten-eigenschappen (bijgevoegde bestanden, lijst van ontvangers) te splitsen, en ook op datum van het bericht.

Omdat een van onze typische taken het bekijken van berichtenregisters (ongelezen, inkomend, alles) is, is het ook logisch om deze in de datumsegmentatie van berichten op te nemen.

We voegen de splitsingssleutel (datum van het bericht) toe aan alle tabellen: ontvangers, bestand, registers. In het bericht zelf kan dit weggelaten worden, en gebruiken we de bestaande DatumTijd.

Thema's

Aangezien het onderwerp over meerdere berichten gaat, is het niet mogelijk om dit in hetzelfde model te splitsen; we moeten op iets anders steunen. In ons geval is perfect geschiktheid de datum van het eerste bericht in de correspondentie — dat is het moment van creatie, namelijk, het onderwerp.

We voegen de splitsingssleutel (datum van het onderwerp) toe aan alle tabellen: onderwerp, deelnemer.

Maar nu hebben we direct twee problemen:

• in welke sectie moeten we berichten op onderwerp zoeken?
• in welke sectie moeten we het onderwerp van het bericht zoeken?

We kunnen natuurlijk blijven zoeken in alle secties, maar dat zou erg teleurstellend zijn, en al onze winsten tenietdoen. Daarom, om te weten waar precies te zoeken, maken we logische links/verwijzingen naar secties:

• in het bericht voegen we toe een veld met de datum van het onderwerp
• aan het onderwerp voegen we toe een set datums van berichten van deze correspondentie (dit kan een aparte tabel zijn, of een array van datums)

Aangezien er voor elke afzonderlijke correspondentie slechts enkele aanpassingen van de lijst van berichtdatums zullen zijn (bijna alle berichten vallen binnen 1-2 aangrenzende dagen), houd ik me aan deze optie.

Al met al heeft onze database de volgende structuur aangenomen met inachtneming van de segmentatie:

Tabellen: RU, als je een afkeer hebt van het cyrillische schrift in de namen van tabellen/velden, kun je het beter niet bekijken

-- secties op datum van bericht
CREATE TABLE "Bericht_YYYYMMDD"(
  "Bericht"
    uuid
      PRIMARY KEY
, "Onderwerp"
    uuid
, "DatumOnderwerp"
    date
, "Auteur"
    uuid
, "DatumTijd" -- gebruiken als datum
    timestamp
, "Tekst"
    text
);

CREATE TABLE "Ontvanger_YYYYMMDD"(
  "DatumBericht"
    date
, "Bericht"
    uuid
, "Persoon"
    uuid
, PRIMARY KEY("Bericht", "Persoon")
);

CREATE TABLE "Bestand_YYYYMMDD"(
  "DatumBericht"
    date
, "Bestand"
    uuid
      PRIMARY KEY
, "Bericht"
    uuid
, "BLOB"
    uuid
, "Naam"
    text
);

CREATE TABLE "RegisterBerichten_YYYYMMDD"(
  "DatumBericht"
    date
, "Eigenaar"
    uuid
, "TypeRegister"
    smallint
, "DatumTijd"
    timestamp
, "Bericht"
    uuid
, PRIMARY KEY("Eigenaar", "TypeRegister", "Bericht")
);
CREATE INDEX ON "RegisterBerichten_YYYYMMDD"("Eigenaar", "TypeRegister", "DatumTijd" DESC);

-- secties op datum van onderwerp
CREATE TABLE "Onderwerp_YYYYMMDD"(
  "DatumOnderwerp"
    date
, "Onderwerp"
    uuid
      PRIMARY KEY
, "Document"
    uuid
, "Titel"
    text
);

CREATE TABLE "DeelnemerOnderwerp_YYYYMMDD"(
  "DatumOnderwerp"
    date
, "Onderwerp"
    uuid
, "Persoon"
    uuid
, PRIMARY KEY("Onderwerp", "Persoon")
);

CREATE TABLE "DatumsBerichtenOnderwerp_YYYYMMDD"(
  "DatumOnderwerp"
    date
, "Onderwerp"
    uuid
      PRIMARY KEY
, "Datum"
    date
);

We besparen een cent.

Maar als we niet de klassieke variant van partitionering gebaseerd op de verdeling van de waarde van het veld (via triggers en erfelijkheid of PARTITION BY), maar 'handmatig' op applicatieniveau, dan merken we dat de waarde van de partitioneringssleutel al in de naam van de tabel is opgeslagen.

Daarom, als je zo heel erg bezorgd bent over de hoeveelheid opgeslagen gegevens, dan kunnen we van deze 'overtollige' velden afkomen en direct naar specifieke tabellen verwijzen. Maar dan moeten alle selecties uit meerdere secties al aan de applicatiezijde worden uitgevoerd.

Bron: habr.com