Database del messaggero (Parte 2): sezioniamo «live»

Abbiamo progettato con successo la struttura del nostro database PostgreSQL per memorizzare le conversazioni; è passato un anno, gli utenti lo stanno riempiendo attivamente e ora contiene già milioni di record, e... qualcosa ha cominciato a rallentare.

• Parte 1: progettazione della struttura del database
• Parte 2: sezioniamo 'in tempo reale'

Il fatto è che Con l'aumento della dimensione della tabella cresce anche la "profondità" degli indici — anche se in modo logaritmico. Ma col tempo questo costringe il server a elaborare un numero significativamente maggiore di pagine di dati , rispetto all'inizio., rispetto all'inizio.

Ecco che entra in gioco la partizionamento.

Vorrei sottolineare che non stiamo parlando di sharding, ovvero della distribuzione dei dati su database o server diversi. Infatti, anche suddividendo i dati tra alcuni server, non ci libereremo del problema dell'espansione degli indici nel tempo. È chiaro che se puoi permetterti di introdurre un nuovo server ogni giorno, i tuoi problemi non saranno più legati a un singolo database.

Noi analizzeremo non script specifici per implementare il partizionamento "in hardware", ma il concetto stesso — cosa e come dovrebbe essere "tagliato a fette", e quale impatto può avere questo approccio.

Concetto

Definiamo nuovamente il nostro obiettivo: vogliamo assicurarci che oggi, domani e tra un anno la quantità di dati PostgreSQL leggibili durante qualsiasi operazione di lettura/scrittura rimanga approssimativamente la stessa.

Per qualsiasi dati accumulati nel tempo (messaggi, documenti, log, archivi, …) la scelta naturale come chiave di partizionamento è la data/ora dell'evento. Nel nostro caso, tale evento è il momento di invio del messaggio.

Notiamo che gli utenti di solito lavorano solo con i "più recenti" di questi dati — leggono gli ultimi messaggi, analizzano gli ultimi log,... No, certo, possono scorrere ulteriormente nel passato, ma lo fanno molto raramente.

Dai questi vincoli, diventa evidente che la soluzione ottimale per i messaggi sarà "sezioni giornaliere" — poiché quasi sempre il nostro utente andrà a leggere ciò che è arrivato "oggi" o "ieri".

Se durante il giorno scriviamo e leggiamo praticamente solo in una sezione, questo ci offre anche un utilizzo più efficiente della memoria e del disco — poiché tutti gli indici della sezione possono facilmente stare in memoria, a differenza dei "grandi e pesanti" dell'intera tabella.

step-by-step

In generale, quanto sopra suona come un grande vantaggio. È raggiungibile, ma per ottenerlo dobbiamo esforzarci bene — perché decidere di partizionare una delle entità comporta anche la necessità di "tagliare" le relative.

Messaggio, le sue proprietà e proiezioni

Dal momento che abbiamo deciso di segmentare i messaggi per data, ha senso dividere anche le entità-proprietà a essi collegate (file allegati, elenco dei destinatari), e anche per data del messaggio.

Poiché uno dei nostri compiti tipici è proprio quello di visualizzare i registri dei messaggi (non letti, in arrivo, tutti), è logico coinvolgerli nel partizionamento per data dei messaggi.

Aggiungiamo la chiave di partizionamento (data del messaggio) in tutte le tabelle: destinatari, file, registri. Nella stessa messaggistica non è necessario aggiungerla, ma utilizzare il già esistente DataOra.

Temi

Poiché il tema è comune a più messaggi, non è possibile "tagliarlo" nello stesso modello, bisogna basarsi su qualcos'altro. Nel nostro caso, si adatta perfettamente la data del primo messaggio nella conversazione — cioè il momento della creazione, di per sé, del tema.

Aggiungiamo la chiave di partizionamento (data del tema) in tutte le tabelle: tema, partecipante.

Ma ora ci sono subito due problemi:

• in quale sezione cercare messaggi per tema?
• in quale sezione cercare il tema del messaggio?

Possiamo, certo, continuare a cercare in tutte le sezioni, ma questo sarebbe molto triste, e annullerebbe tutti i nostri guadagni. Pertanto, per sapere dove cercare specificamente, creeremo link/logiche puntatori sulle sezioni:

• nel messaggio aggiungeremo un campo con la data del tema
• al tema aggiungeremo un insieme di date dei messaggi di questa conversazione (può anche essere una tabella separata, o un array di date)

Poiché le modifiche dell'elenco delle date dei messaggi per ciascuna conversazione saranno poche (poiché quasi tutti i messaggi appartengono a 1-2 giorni associati), mi fermerò proprio su questa opzione.

In totale, la struttura del nostro database ha preso la seguente forma tenendo conto del partizionamento:

Tabelle: RU, se c'è avversione alle lettere cirilliche nei nomi di tabelle/campi meglio non guardare

-- sezioni in base alla data del messaggio
CREATE TABLE "Messaggio_YYYYMMDD"(
  "Messaggio"
    uuid
      PRIMARY KEY
, "Oggetto"
    uuid
, "DataOggetto"
    date
, "Autore"
    uuid
, "DataOra" -- usato come data
    timestamp
, "Testo"
    text
);

CREATE TABLE "Destinatario_YYYYMMDD"(
  "DataMessaggio"
    date
, "Messaggio"
    uuid
, "Persona"
    uuid
, PRIMARY KEY("Messaggio", "Persona")
);

CREATE TABLE "File_YYYYMMDD"(
  "DataMessaggio"
    date
, "File"
    uuid
      PRIMARY KEY
, "Messaggio"
    uuid
, "BLOB"
    uuid
, "Nome"
    text
);

CREATE TABLE "RegistroMessaggi_YYYYMMDD"(
  "DataMessaggio"
    date
, "Proprietario"
    uuid
, "TipoRegistro"
    smallint
, "DataOra"
    timestamp
, "Messaggio"
    uuid
, PRIMARY KEY("Proprietario", "TipoRegistro", "Messaggio")
);
CREATE INDEX ON "RegistroMessaggi_YYYYMMDD"("Proprietario", "TipoRegistro", "DataOra" DESC);

-- sezioni in base alla data dell'oggetto
CREATE TABLE "Oggetto_YYYYMMDD"(
  "DataOggetto"
    date
, "Oggetto"
    uuid
      PRIMARY KEY
, "Documento"
    uuid
, "Titolo"
    text
);

CREATE TABLE "PartecipanteOggetto_YYYYMMDD"(
  "DataOggetto"
    date
, "Oggetto"
    uuid
, "Persona"
    uuid
, PRIMARY KEY("Oggetto", "Persona")
);

CREATE TABLE "DateMessaggiOggetto_YYYYMMDD"(
  "DataOggetto"
    date
, "Oggetto"
    uuid
      PRIMARY KEY
, "Data"
    date);

Facciamo economia

Ma se non utilizziamo il metodo classico di partizionamento basato sulla distribuzione dei valori del campo (tramite trigger e ereditarietà oppure PARTITION BY), ma ‘manualmente’ a livello di applicazione, si può notare che il valore della chiave di partizionamento è già conservato nel nome della tabella stessa.

Quindi, se sei così preoccupato per il volume dei dati conservati, si possono eliminare questi ‘campi superflui’ e accedere direttamente a tabelle specifiche. Tuttavia, tutte le selezioni da più sezioni dovranno già essere gestite a livello di applicazione.

Fonte: habr.com