DB del messaggero (parte 2): sezioniamo "in diretta"

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

• Parte 1: progettiamo la struttura del database
• Parte 2: sectioniamo 'in tempo reale'

Il fatto è che con l'aumento del volume della tabella cresce anche la 'profondità' degli indici - anche se in modo logaritmico. Ma nel tempo questo costringe il server a gestire per svolgere le stesse operazioni di lettura/scrittura un numero di pagine di dati di gran lunga maggiore, rispetto all'inizio.

E qui interviene la suddivisione.

Voglio sottolineare che non parliamo di sharding, ovvero della distribuzione dei dati tra diversi database o server. Perché, anche se distribuisci i dati su alcune server, non ti libererai del problema dell' 'espansione' degli indici nel tempo. È chiaro che se puoi permetterti di mettere in funzione un nuovo server ogni giorno, i tuoi problemi si sposteranno su una dimensione diversa rispetto a quella del database specifico.

Discuteremo non di script concreti per implementare la suddivisione 'in hardware', ma del concetto stesso: cosa e come dovremmo 'tagliare a fette' e a cosa porta questo desiderio.

Ribadiamo il nostro obiettivo: vogliamo fare in modo che oggi, domani e tra un anno la quantità di dati PostgreSQL leggibili durante qualsiasi operazione di lettura/scrittura rimanga più o meno la stessa.

Definiamo di nuovo il nostro obiettivo: vogliamo fare in modo che oggi, domani e tra un anno il numero di dati PostgreSQL leggibili durante qualsiasi operazione di lettura/scrittura rimanga all'incirca lo stesso.

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

Notiamo che gli utenti praticamente sempre lavorano solo con i 'ultimi' di questi dati — leggono gli ultimi messaggi, analizzano gli ultimi log,... No, certo, possono scorrere anche indietro nel tempo, ma lo fanno molto raramente.

Da queste limitazioni diventa ovvio che la soluzione ottimale per i messaggi sarà suddivisioni 'giornaliere' — poiché quasi sempre il nostro utente leggerà ciò che ha ricevuto 'oggi' o 'ieri'.

Se durante il giorno scriviamo e leggiamo praticamente solo in una suddivisione, questo ci consente anche un uso più efficiente della memoria e del disco. — poiché tutti gli indici della sezione si adattano facilmente in memoria, a differenza di quelli "grandi e grassi" su tutta la tabella.

passo dopo passo

In generale, tutto ciò che è stato detto sopra suona come un unico enorme profitto. E può essere realizzato, ma per questo dobbiamo fare un buon lavoro — perché la decisione di sezionare una delle entità comporta la necessità di "scomporre" anche quelle collegate.

Il messaggio, le sue proprietà e le proiezioni

Dato che abbiamo deciso di suddividere i messaggi in base alle date, ha senso anche dividere le entità-proprietà a essi dipendenti (allegati, elenco dei destinatari) e anche in base alla data del messaggio.

Poiché uno dei nostri compiti tipici è proprio la visualizzazione dei registri dei messaggi (non letti, in arrivo, tutti), è logico "includerli" nella sezionatura per data dei messaggi.

Aggiungiamo la chiave di sezionamento (data del messaggio) in tutte le tabelle: destinatari, file, registri. Non è necessario aggiungerla nel messaggio, ma utilizzare la data e ora esistenti.

Temi

Poiché il tema è uno per più messaggi, non si può semplicemente "sezionare" in quel modello, bisogna basarsi su qualcos'altro. Nel nostro caso, si adatta perfettamente la data del primo messaggio nella corrispondenza — cioè il momento della creazione effettiva del tema.

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

Ma ora abbiamo subito due problemi:

• in quale sezione cercare messaggi per tema?
• in quale sezione cercare il tema da un messaggio?

Si può, ovviamente, continuare a cercare in tutte le sezioni, ma sarebbe molto triste e annullerebbe tutti i nostri guadagni. Pertanto, per sapere dove cercare esattamente, faremo riferimenti logici/puntatori alle sezioni:

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

Poiché le modifiche all'elenco delle date dei messaggi per ogni singola corrispondenza saranno poche (dopotutto, quasi tutti i messaggi rientrano in 1-2 giorni consecutivi), mi fermerò proprio su questa opzione.

In sintesi, la struttura del nostro database ha assunto il seguente aspetto tenendo conto della sezionatura:

Tabelle: RU, se si ha avversione per il cirillico nei nomi delle tabelle/campi è meglio non guardare

-- sezioni per data del messaggio
CREATE TABLE "Messaggio_YYYYMMDD"(
  "Messaggio"
    uuid
      PRIMARY KEY
, "Oggetto"
    uuid
, "DataOggetto"
    date
, "Autore"
    uuid
, "DataOra" -- usiamo 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 per 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
);

Risparmiamo qualche centesimo

E se non usiamo la variante classica di partizionamento basata sulla distribuzione dei valori del campo (tramite trigger e ereditarietà o PARTITION BY), ma 'manualmente' a livello di applicazione, si può notare che il valore della chiave di partizionamento è già conservato nel nome stesso della tabella.

Quindi, se ti preoccupi così tanto del volume dei dati memorizzati, puoi eliminare questi 'superflui' campi e indirizzarti direttamente a tabelle specifiche. Tuttavia, in questo caso tutte le selezioni da più sezioni dovranno essere gestite a livello di applicazione.

Fonte: habr.com