Messenger database (vaega 1): mamanuina le faavae faavae

E fa'afefea ona e fa'aliliuina mana'oga fa'apisinisi i fa'amaumauga fa'amaumauga fa'apitoa e fa'aaoga ai le fa'ata'ita'iga o le fa'ata'ita'iina o se fa'amaumauga avefe'au mai le sasa.

• Vaega 1: fuafuaina o le fa'avaa faavae
• Vaega 2: vaega "mo tupe mama"

O lo tatou faavae o le a le tele ma tufatufa, pei o VKontakte poʻo Badoo, ae "ina ia saʻo", ae sa lelei - galue, vave ma fetaui ile tasi server PostgreSQL - ina ia mafai ona e faʻapipiʻiina se isi faʻataʻitaʻiga o le tautua i se mea i le itu, mo se faʻataʻitaʻiga.

O le mea lea, matou te le pa'i atu i mataupu o le sharding, replication ma geo-distributed systems, ae o le a taulaʻi atu i fofo matagaluega i totonu o le database.

Laasaga 1: O nisi pisinisi faʻapitoa

O le a matou le mamanuina a matou feʻau faʻapitoa, ae o le a tuʻufaʻatasia i totonu o le siosiomaga feso'ota'iga lautele fa'apisinisi. O lona uiga, o tatou tagata e le "na o le fetuutuunai," ae fesootai le tasi i le isi i le tulaga o le foia o nisi o faafitauli tau pisinisi.

Ae o a galuega a se pisinisi?.. Sei o tatou tilotilo i le faataitaiga a Vasily, le ulu o le matagaluega o atinae.

• "Nikolai, mo lenei galuega tatou te manaʻomia se patch i le asō!"
  O lona uiga e mafai ona faia fetusiaiga i tulaga o nisi pepa.
• "Kolya, e te alu i Dota i lenei afiafi?"
  O lona uiga, e o'o lava i se paga e tasi e mafai ona feso'ota'i i le taimi e tasi i mataupu eseese.
• “Petero, Nikolay, vaai i totonu o le pepa faaopoopo mo le lisi o tau mo le server fou.”
  O lea, e tasi le savali e mafai ona maua le tele o tagata e mauaina. I lenei tulaga, e mafai ona i ai i le savali Fa'apipi'i faila.
• “Semyon, vaai foi.”
  Ma e tatau ona i ai se avanoa e ulufale ai i fetusiaiga o iai valaaulia se tagata fou.

Sei o tatou mafaufau i lenei lisi o manaoga "manino" mo le taimi nei.

A aunoa ma le malamalama i le faʻaaogaina faʻapitoa o le faʻafitauli ma tapulaʻa na tuʻuina atu i ai, mamanu lelei fa'amaumauga fa'amaumauga e fo'ia e toetoe lava a le mafai.

Laasaga 2: La'ititi Logic Circuit

E oʻo mai i le taimi nei, o mea uma e tutusa lelei lava ma fesoʻotaʻiga imeli - a traditional business tool. Ioe, "algorithmically" tele faʻafitauli faʻapisinisi e tutusa le tasi ma le isi, o le mea lea o meafaigaluega mo le foia o le a tutusa lelei.

Se'i o tatou fa'aleleia le fa'ata'ita'iga ua uma ona maua o so'otaga fa'alapotopotoga. Ina ia faʻafaigofie ona malamalama i la matou faʻataʻitaʻiga, o le a matou faʻaogaina le faʻaaliga sili ona faʻaalia ER fa'ata'ita'iga e aunoa ma le lavelave o faʻamatalaga UML poʻo IDEF:

I la matou faʻataʻitaʻiga, o le tagata, pepa ma le binary "tino" o le faila o ni faʻalapotopotoga "fafo" o loʻo tutoʻatasi e aunoa ma la matou tautua. O le mea lea, o le a faigofie ona tatou vaʻaia i latou i le lumanaʻi o nisi o fesoʻotaʻiga "i se mea" e le UUID.

Tusi ata i le faigofie e mafai - o le tele o tagata e te fa'aali atu ia i latou e le o ni tagata popoto i le faitau UML/IDEF. Ae ia mautinoa e tusi ata.

Laasaga 3: Tusia le fausaga o le laulau

E uiga ile laulau ma igoa ole fanuaO igoa "Rusia" o fanua ma laulau e mafai ona faʻavasega ese, ae o se mea o le tofo. Ona o le iinei i Tensor e leai ni tagata atinaʻe mai fafo, ma PostgreSQL faʻatagaina matou e tuʻuina atu igoa e oʻo lava i tusitusiga, pe a latou fa'amauina i upusii, ona matou manaʻo lea e taʻu manino mea ma manino ina ia leai ni eseesega.
Talu ai e toʻatele tagata e tusi mai ni feʻau iā i tatou i le taimi e tasi, e ono faia foʻi faapea e nisi o i latou tuimotu, o le filifiliga sili ona faigofie fa'aoga UUID e fai ma fa'amatalaga e le gata mo faʻalapotopotoga i fafo, ae faʻapea foʻi mo mea uma i totonu o la matou auaunaga. E le gata i lea, e mafai ona fa'atupuina e o'o lava i le itu a le tagata o tausia - o le a fesoasoani lea ia i matou e lagolago le lafoina o fe'au pe a le avanoa le fa'amaumauga mo sina taimi, ma e matua maualalo lava le fa'alavelave.

O le faʻatulagaina o laulau faʻataʻitaʻi i totonu o la matou faʻamaumauga o le a pei o lenei:
Laupapa : RU

CREATE TABLE "Тема"(
  "Тема"
    uuid
      PRIMARY KEY
, "Документ"
    uuid
, "Название"
    text
);

CREATE TABLE "Сообщение"(
  "Сообщение"
    uuid
      PRIMARY KEY
, "Тема"
    uuid
, "Автор"
    uuid
, "ДатаВремя"
    timestamp
, "Текст"
    text
);

CREATE TABLE "Адресат"(
  "Сообщение"
    uuid
, "Персона"
    uuid
, PRIMARY KEY("Сообщение", "Персона")
);

CREATE TABLE "Файл"(
  "Файл"
    uuid
      PRIMARY KEY
, "Сообщение"
    uuid
, "BLOB"
    uuid
, "Имя"
    text
);

Laupapa: EN

CREATE TABLE theme(
  theme
    uuid
      PRIMARY KEY
, document
    uuid
, title
    text
);

CREATE TABLE message(
  message
    uuid
      PRIMARY KEY
, theme
    uuid
, author
    uuid
, dt
    timestamp
, body
    text
);

CREATE TABLE message_addressee(
  message
    uuid
, person
    uuid
, PRIMARY KEY(message, person)
);

CREATE TABLE message_file(
  file
    uuid
      PRIMARY KEY
, message
    uuid
, content
    uuid
, filename
    text
);

O le mea sili ona faigofie pe a faʻamatalaina se faatulagaga o le amata "faʻamavae" le kalafi fesoʻotaʻiga mai laulau e le o fa'asino latou lava i se tasi.

Laasaga 4: Su'e mana'oga e le o manino

O le mea lena, ua matou mamanuina se faʻamaumauga e mafai ai ona e tusitusi atoatoa ma i se isi itu faitau.

Tatou tuu i tatou lava i tulaga o le tagata e faaaogaina la tatou auaunaga - o le a le mea tatou te mananao e fai i ai?

• Feau mulimuli
  lea fa'avasega fa'asologa se resitala o "a'u" fe'au e fa'atatau i ta'iala eseese. O le mea o aʻu o se tasi o tagata e mauaina, o aʻu o le tusitala, lea na latou tusi mai ai ia te aʻu ae ou te leʻi tali atu, lea latou te leʻi tali mai ai ia te aʻu, ...
• Tagata auai o le fetusiaiga
  O ai o lo'o auai i lenei talatalanoaga umi, umi?

O la matou fausaga e mafai ai ona matou foia uma nei faafitauli "i le lautele," ae le vave. O le fa'afitauli o le fa'avasegaina i totonu o le galuega muamua le mafai ona fai index, talafeagai mo tagata taʻitoʻatasi o loʻo auai (ma e tatau ona e aveese uma faʻamaumauga), ma ia foia le mea lona lua e te manaʻomia aveese uma fe'au i lenei autu.

O galuega e le'i fa'amoemoeina e fa'aoga e mafai ona fa'amalosi kolosi i le gaosiga.

Laasaga 5: Smart Denormalization

O a matou faʻafitauli uma e lua o le a foia e ni laulau faʻaopoopo matou te faia fa'alua vaega o fa'amaumauga, e mana'omia e fai i luga o latou fa'ailoga talafeagai mo a tatou galuega.


Laupapa : RU

CREATE TABLE "РеестрСообщений"(
  "Владелец"
    uuid
, "ТипРеестра"
    smallint
, "ДатаВремя"
    timestamp
, "Сообщение"
    uuid
, PRIMARY KEY("Владелец", "ТипРеестра", "Сообщение")
);
CREATE INDEX ON "РеестрСообщений"("Владелец", "ТипРеестра", "ДатаВремя" DESC);

CREATE TABLE "УчастникТемы"(
  "Тема"
    uuid
, "Персона"
    uuid
, PRIMARY KEY("Тема", "Персона")
);

Laupapa: EN

CREATE TABLE message_registry(
  owner
    uuid
, registry
    smallint
, dt
    timestamp
, message
    uuid
, PRIMARY KEY(owner, registry, message)
);
CREATE INDEX ON message_registry(owner, registry, dt DESC);

CREATE TABLE theme_participant(
  theme
    uuid
, person
    uuid
, PRIMARY KEY(theme, person)
);

O iinei ua matou faʻaogaina ai auala masani e lua na faʻaaogaina pe a fatuina laulau fesoasoani:

• Fa'ateleina fa'amaumauga
  I le fa'aaogaina o le fa'amaumauga muamua o le fe'au, matou te faia ai ni fa'amaumauga tulitatao i ituaiga resitara 'ese'ese mo tagata 'ese'ese - e le gata mo lē na auina atu ma mo lē na te mauaina. Ae o tusi resitala taʻitasi ua paʻu nei i luga o le faasino igoa - pe a uma, i se tulaga masani, matou te fia vaʻai naʻo le itulau muamua.
• Faamaumauga tulaga ese
  Soo se taimi lava e te lafoina ai se feʻau i totonu o se autu patino, e lava e siaki pe o iai sea faʻamatalaga. Afai e leai, fa'aopoopo i la tatou “lomifefiloi”.

I le isi vaega o le mataupu o le a tatou talanoaina fa'atinoga o vaeluaga i totonu o le fausaga o la tatou database.

puna: www.habr.com