Neeg xa xov liaison database (part 2): partitioning "rau profit"

Peb tau ua tiav cov qauv ntawm peb PostgreSQL database rau khaws cov ntawv xov xwm, ib xyoos dhau los, cov neeg siv tau nquag sau nws, thiab tam sim no nws muaj. tsheej lab ntawm cov ntaub ntawv, thiab ... ib yam dab tsi pib qeeb.

• Part 1: tsim lub hauv paus ncej
• Part 2: sectioning “rau profit”

Point yog tias Raws li lub rooj loj hlob, yog li "qhov tob" ntawm qhov ntsuas. - Txawm tias logarithmically. Tab sis dhau sij hawm qhov no yuam cov neeg rau zaub mov ua tib yam nyeem / sau cov haujlwm txheej txheem ntau zaus ntau nplooj ntawv ntawm cov ntaub ntawvtshaj thaum pib.

Nov yog qhov uas nws tuaj cawm kev faib tawm.

Cia kuv nco ntsoov tias peb tsis tau tham txog kev sib faib, uas yog, faib cov ntaub ntawv ntawm cov databases lossis servers sib txawv. Vim tias txawm faib cov ntaub ntawv rau hauv ntau servers, koj yuav tsis tau tshem ntawm qhov teeb meem ntawm indexes "o" dhau sij hawm. Nws yog qhov tseeb tias yog tias koj tuaj yeem them taus muab lub server tshiab rau hauv kev ua haujlwm txhua hnub, tom qab ntawd koj cov teeb meem yuav tsis dag txhua yam hauv lub dav hlau ntawm cov ntaub ntawv tshwj xeeb.

Peb yuav txiav txim siab tsis yog cov ntawv tshwj xeeb rau kev siv partitioning "hauv hardware", tab sis txoj hauv kev nws tus kheej - dab tsi thiab yuav ua li cas yuav tsum tau "txiav rau hauv cov hlais", thiab qhov kev ntshaw ua rau.

Tswv yim

Cia peb txhais peb lub hom phiaj ib zaug ntxiv: peb xav kom paub tseeb tias hnub no, tag kis, thiab hauv ib xyoos, cov ntaub ntawv nyeem los ntawm PostgreSQL thaum lub sijhawm nyeem / sau ua haujlwm tseem nyob li qub.

Rau ib qho twg chronologically sau cov ntaub ntawv (cov lus, ntaub ntawv, cav, archives, ...) lub ntuj xaiv raws li ib tug partitioning yuam sij yog hnub tim / sijhawm. Nyob rau hauv peb cov ntaub ntawv, xws li ib tug tshwm sim lub sijhawm xa xov.

Nco ntsoov tias cov neeg siv yuav luag ib txwm ua haujlwm nrog cov "kawg" nkaus xwb xws li cov ntaub ntawv - lawv nyeem cov lus tseeb, txheeb xyuas cov cav tshiab, ... Tsis yog, lawv tuaj yeem scroll ntxiv rov qab rau lub sijhawm, tab sis lawv ua qhov no tsis tshua muaj.

Los ntawm cov kev txwv no nws yog qhov tseeb tias cov lus pom zoo yuav yog "txhua hnub" ntu - Tom qab tag nrho, peb cov neeg siv yuav luag ib txwm nyeem dab tsi tuaj rau nws "hnub no" lossis " nag hmo".

Yog tias peb sau thiab nyeem yuav luag hauv ib ntu thaum nruab hnub, ces qhov no kuj muab rau peb siv tau zoo dua ntawm kev nco thiab disk - txij li tag nrho cov seem indexes yooj yim haum rau hauv RAM, sib piv rau cov "loj thiab rog" thoob plaws lub rooj.

ib qib zuj zus

Feem ntau, txhua yam hais saum toj no zoo li ib qho txiaj ntsig txuas ntxiv. Thiab nws yog qhov ua tau, tab sis rau qhov no peb yuav tau sim ua kom nyuaj - vim qhov kev txiav txim siab los faib ib qho ntawm cov koom haum ua rau qhov xav tau "pom" qhov cuam tshuam.

Cov lus, nws cov khoom thiab qhov projections

Txij li thaum peb txiav txim siab txiav cov lus los ntawm cov hnub, nws ua rau kev txiav txim siab los faib cov chaw-cov khoom uas nyob ntawm lawv (cov ntaub ntawv txuas, cov npe ntawm cov neeg txais), thiab kuj los ntawm hnub ntawm cov lus.

Txij li thaum ib qho ntawm peb cov haujlwm tseem ceeb yog saib cov ntawv sau npe (tsis tau nyeem, tuaj, tag nrho), nws kuj yog qhov tsim nyog rau "kos rau hauv" rau hauv kev faib tawm los ntawm hnub xov.

Peb ntxiv qhov tseem ceeb muab faib (hnub xov xwm) rau txhua lub rooj: cov neeg txais, cov ntaub ntawv, npe. Koj tsis tas yuav ntxiv nws rau cov lus nws tus kheej, tab sis siv DateTime uas twb muaj lawm.

threads

Txij li thaum tsuas muaj ib lub ntsiab lus rau ntau cov lus, tsis muaj txoj hauv kev los "txiav" nws hauv tib tus qauv; koj yuav tsum vam khom lwm yam. Hauv peb rooj plaub nws yog qhov zoo tagnrho hnub ntawm thawj cov lus nyob rau hauv correspondence - uas yog, lub sij hawm ntawm creation, qhov tseeb, ntawm lub ncauj lus.

Ntxiv tus yuam sij muab faib (hnub hnub tim) rau txhua lub rooj: lub ntsiab lus, tus neeg koom.

Tab sis tam sim no peb muaj ob qhov teeb meem ib zaug:

• Hauv seem twg kuv yuav tsum nrhiav cov lus ntawm lub ncauj lus?
• Hauv seem twg kuv yuav tsum nrhiav lub ntsiab lus los ntawm cov lus?

Peb tuaj yeem, tau kawg, txuas ntxiv mus tshawb hauv txhua ntu, tab sis qhov no yuav tu siab heev thiab yuav tsis lees paub tag nrho peb qhov kev yeej. Yog li ntawd, txhawm rau paub qhov tseeb saib, peb yuav ua kom muaj kev sib txuas / taw qhia rau ntu:

• peb yuav ntxiv rau hauv cov lus topic hnub tim teb
• cia peb ntxiv rau lub ntsiab lus xov hnub teem daim ntawv no (yuav ua tau ib lub rooj sib cais, los yog ib tug array ntawm hnub)

Txij li thaum yuav muaj qee qhov kev hloov kho rau cov npe ntawm cov lus hnub rau txhua tus neeg sau ntawv (tom qab tag nrho, yuav luag tag nrho cov lus poob rau 1-2 hnub nyob ib sab), kuv yuav tsom mus rau qhov kev xaiv no.

Nyob rau hauv tag nrho, tus qauv ntawm peb cov ntaub ntawv khaws tseg tau ua daim ntawv hauv qab no, coj mus rau hauv tus account partitioning:

Cov lus: RU, yog tias koj muaj kev ntxub ntxaug rau cov tsiaj ntawv Cyrillic hauv cov npe ntawm cov ntxhuav / teb, nws zoo dua tsis txhob saib

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

Txuag tau ib lub penny zoo nkauj

Zoo, yog tias peb tsis siv classic sectioning kev xaiv raws li kev faib tawm ntawm qhov muaj nuj nqis (los ntawm kev ua thiab cov qub txeeg qub teg los yog PARTITION BY), thiab "manually" ntawm cov ntawv thov, koj yuav pom tias tus nqi ntawm qhov tseem ceeb ntawm qhov muab faib yog twb khaws cia rau hauv lub npe ntawm lub rooj nws tus kheej.

Yog li koj hais mas Koj puas txhawj xeeb txog tus nqi ntawm cov ntaub ntawv khaws cia?, tom qab ntawd koj tuaj yeem tshem tawm cov "ntxiv" teb thiab chaw nyob cov rooj tshwj xeeb. Muaj tseeb tiag, txhua qhov kev xaiv los ntawm ntau ntu hauv qhov no yuav tsum tau hloov mus rau sab ntawv thov.

Tau qhov twg los: www.hab.com