, inguqulelo yamva nje "yeyona ndawo ivulekileyo yobudlelwane bedatha yehlabathi," iphuma kwiiveki ezimbalwa (ukuba konke kuhamba ngokwesicwangciso). Oku kulandela ishedyuli eqhelekileyo yokukhupha inguqulelo entsha enetoni yeempawu ezintsha kanye ngonyaka, kwaye ngokuphandle, oko kuyancomeka. Yiyo loo nto ndiye ndaba lilungu elisebenzayo loluntu lwePostgreSQL.
Ngokombono wam, ngokungafaniyo nokukhutshwa kwangaphambili, i-PostgreSQL 12 ayinayo into enye okanye ezimbini eziguqukayo (njengokwahlula okanye ukubuza imibuzo). Ndakhe ndaqhula ukuba eyona nto iphambili yePostgreSQL 12 luzinzo olukhulu. Ngaba ayisiyiyo le nto uyifunayo xa ulawula idatha ebalulekileyo yeshishini lakho?
Kodwa i-PostgreSQL 12 ayipheli apho: ngeempawu ezintsha kunye nophuculo, izicelo ziya kwenza ngcono, kwaye konke okufuneka ukwenze kukuphucula!
(Ewe, mhlawumbi wakha kwakhona izalathisi, kodwa kolu kukhululwa ayisoyiki njengoko besiqhele ukwenza.)
Kuya kuba kuhle ukuphucula i-PostgreSQL kwaye kwangoko ukonwabele uphuculo olubalulekileyo ngaphandle kwengxabano engeyomfuneko. Kwiminyaka embalwa edlulileyo, ndaphonononga uphuculo olusuka kwi-PostgreSQL 9.4 ukuya kwi-PostgreSQL 10 kwaye ndabona indlela isicelo esonyuswe ngayo sibulela kwi-parallelism ephuculweyo yombuzo kwi-PostgreSQL 10. Kwaye, okona kubaluleke kakhulu, phantse akukho nto ifunekayo kum (seta nje iparameter yoqwalaselo. max_parallel_workers).
Vuma, ilungile xa usetyenziso lusebenza ngcono kwangoko emva kophuculo. Kwaye sizama ngamandla ukukholisa abasebenzisi, kuba iPostgreSQL inobuninzi babo.
Ke uphuculo olulula kwiPostgreSQL 12 lungakwenza njani wonwabe? Ndiza kukuxelela ngoku.
Uphuculo olukhulu lwesalathisi
Ngaphandle kwesalathisi, isiseko sedatha asiyi kuya kude. Yiyiphi enye indlela onokulufumana ngayo ulwazi ngokukhawuleza? Inkqubo yesalathiso esisiseko yePostgreSQL ibizwa ngokuba . Olu hlobo lwesalathiso lulungiselelwe iinkqubo zokugcina.
Sisebenzisa ngokulula umsebenzisi CREATE INDEX ON some_table (some_column), kunye nePostgreSQL yenza umsebenzi omninzi ukugcina isalathiso sisexesheni ngelixa sihlala sifaka, sihlaziya, kwaye sicima amaxabiso. Yonke into isebenza ngokwayo, ngokungathi ngomlingo.
Kodwa izalathisi zePostgreSQL zinengxaki enye - zona kwaye uthathe indawo yedisk eyongezelelweyo kwaye unciphise ukusebenza kokufunyanwa kwedatha kunye nokuhlaziya. Ngokuthi "bloat" ndithetha ukungasebenzi kakuhle kwesakhiwo sesalathisi. Oku kunokuthi-okanye kungabi-kunxulunyaniswa nee-tuples zenkunkuma ezisusayo (enkosi kuPeter Gaghan ngolwazi))). I-Index bloat ibonakala ngokukodwa kwimithwalo yomsebenzi apho isalathisi sitshintsha ngokusebenzayo.
I-PostgreSQL 12 iphucula kakhulu ukusebenza kwezalathisi ze-B-tree, kwaye iimvavanyo ezineempawu ezifana ne-TPC-C zibonise ukuba kumyinge we-40% indawo encinci ngoku isetyenziswa. Ngoku sichitha ixesha elincinci kungekhona kuphela ekugcineni i-B-tree indexes (oko kukuthi, kwimisebenzi yokubhala), kodwa kwakhona ekufumaneni idatha, kuba izalathisi zincinci kakhulu.
Izicelo ezihlaziya iitafile zazo ngenkuthalo- ngokwesiqhelo OLTP izicelo () - izakusebenzisa idisk kwaye iqhubeke nezicelo ngokufanelekileyo ngakumbi. Indawo eninzi yedisk, indawo eninzi i-database kufuneka ikhule ngaphandle kokuphucula isiseko.
Ezinye izicwangciso zokuphucula zifuna ukuphinda kwakhiwe izalathisi zemithi ye-B ukuze zithathe inzuzo yezi nzuzo (umz. ayizukwakha kwakhona izalathisi ngokuzenzekelayo). Kwiinguqulelo zangaphambili ze-PostgreSQL, ukwakha kwakhona izalathisi ezinkulu kwiitafile kubangele ukwehla okukhulu ngenxa yokuba utshintsho alukwazanga ukwenziwa okwangoku. Kodwa i-PostgreSQL 12 inenye into epholileyo: ngoku unokwakha kwakhona izalathisi ngokuhambelana nomyalelo. ukuphepha ngokupheleleyo ixesha lokuphumla.
Kukho olunye uphuculo kwisiseko sesalathisi kwiPostgreSQL 12. Enye into apho kwakukho umlingo- , aka WAL (bhala-phambili log). I-log yokubhala phambili irekhoda yonke intengiselwano kwi-PostgreSQL kwimeko yokungaphumeleli kunye nokuphindaphinda. Izicelo ziyisebenzisela ukugcina kunye . Ngokuqinisekileyo, i-log-ahead log ibhalwe kwidisk, enokuchaphazela ukusebenza.
I-PostgreSQL 12 iye yanciphisa i-overhead yeerekhodi ze-WAL ezidalwe yi-GiST, GIN, kunye ne-SP-GiST indexes ngexesha lokwakhiwa kwesalathisi. Oku kubonelela ngeenzuzo ezininzi ezibonakalayo: Iirekhodi ze-WAL zithatha indawo encinci yediski, kwaye idatha idlalwa ngokukhawuleza, njengokuba ngexesha lokubuyisela intlekele okanye ukubuyiswa kwexesha. Ukuba usebenzisa izalathisi ezinjalo kwizicelo zakho (umzekelo, iPostGIS-based geospatial applications isebenzisa isalathiso seGiST kakhulu), eli lolunye uphawu oluya kuphucula kakhulu amava ngaphandle komzamo kwicala lakho.
Ukwahlula - inkulu, ingcono, ngokukhawuleza
I-PostgreSQL 10 yaziswa . Kwi-PostgreSQL 11 sele kulula kakhulu ukuyisebenzisa. Kwi-PostgreSQL 12 ungatshintsha isikali samacandelo.
Kwi-PostgreSQL 12, ukusebenza kwenkqubo yokwahlula kube ngcono kakhulu, ngakumbi ukuba kukho amawaka ezahlulo kwitafile. Umzekelo, ukuba umbuzo uchaphazela kuphela izahlulo ezimbalwa kwitafile enamawakawaka, iya kuphunyezwa ngokukhawuleza okukhulu. Ukusebenza akuphuculwanga kwezi ntlobo zemibuzo. Uyakuqaphela kwakhona ukuba i-INSERT ikhawuleza kangakanani imisebenzi kwiitafile ezinezahlulo ezininzi.
Ukurekhoda idatha usebenzisa - ngendlela, le yindlela enkulu kwaye nanku umzekelo - iitheyibhile ezahluliweyo kwi-PostgreSQL 12 nazo ziye zasebenza ngakumbi. Nge-COPY yonke into yayisele ikhawuleza, kodwa kwi-PostgreSQL 12 ibhabha ngokupheleleyo.
Enkosi kwezi zibonelelo, iPostgreSQL ikuvumela ukuba ugcine iiseti zedatha ezinkulu kwaye wenze kube lula ukuzifumana. Kwaye akukho mzamo kwicala lakho. Ukuba isicelo sinezahlulo ezininzi, ezifana nokurekhoda idatha yochungechunge lwexesha, ukuphuculwa okulula kuya kuphucula kakhulu ukusebenza kwayo.
Ngelixa oku ayisiyiyo ncam "yokuphucula kwaye uyonwabele" uphuculo, iPostgreSQL 12 ikuvumela ukuba wenze izitshixo zangaphandle ezibhekisa kwiitafile ezahlulahlulweyo, ukwenza ukwahlula kube luyolo ukusebenza kunye.
NGEmibuzo ibengcono kakhulu
Xa (aka CTE, aka NGEmibuzo), andinakulinda ukubhala inqaku malunga . Le yenye yezo mpawu ziya kukhawulezisa isicelo. Ngaphandle kokuba, kunjalo, usebenzisa i-CTE.
Ndihlala ndifumanisa ukuba abaqalayo ukuya kwi-SQL bathanda ukusebenzisa ii-CTEs ukuba uzibhala ngendlela ethile, kuvakala ngathi ubhala inkqubo enyanzelekileyo. Ngokwam, ndithande ukuphinda ndibhale le mibuzo ukuze ndijikeleze ngaphandle CTE kunye nokwandisa imveliso. Ngoku yonke into yahlukile.
I-PostgreSQL 12 ikuvumela ukuba ungenise uhlobo oluthile lwe-CTE ngaphandle kweziphumo ebezingalindelekanga (SELECT), esetyenziswa kube kanye kuphela ekupheleni kwesicelo. Ukuba ndigcine umkhondo wemibuzo ye-CTE endiyibhale ngokutsha, uninzi lwayo luya kuwela kolu didi. Oku kunceda abaphuhlisi babhale ikhowudi ecacileyo ngoku nayo isebenza ngokukhawuleza.
Ngapha koko, i-PostgreSQL 12 yenza i-SQL isebenze ngokwayo, ngaphandle kokuba wenze nantoni na. Kwaye nangona ndingasayi kufuna ukwandisa imibuzo enje ngoku, kulungile ukuba iPostgreSQL iqhubeke nokusebenza ekwenziweni kwemibuzo.
Kanye-ngexesha (JIT) - ngoku isisiseko
Kwiinkqubo ze-PostgreSQL ezili-12 ezinenkxaso Ukuhlanganiswa kweJIT kwenziwe ngokungagqibekanga. Okokuqala, ufumana inkxaso kweminye imisebenzi yangaphakathi, kwaye okwesibini, imibuzo enentetho (owona mzekelo ulula ngu x + y) kwizintlu ezikhethiweyo (onazo emva KHETHA), aggregates, amabinzana nge APHO amagatya kunye nabanye banokusebenzisa iJIT ukuphucula ukusebenza.
Ekubeni i-JIT inikwe amandla ngokungagqibekanga kwi-PostgreSQL 12, ukusebenza kuya kuphucula ngokwayo, kodwa ndincoma ukuvavanya isicelo kwi-PostgreSQL 11, eyazisa i-JIT, ukulinganisa ukusebenza kombuzo kwaye ubone ukuba ufuna ukuthunga nantoni na.
Kuthekani ngezinye izinto ezintsha kwiPostgreSQL 12?
I-PostgreSQL 12 inetoni yeempawu ezintsha ezipholileyo, ukusuka ekukwazini ukuhlola idatha ye-JSON usebenzisa indlela ye-SQL/JSON yokubonisa indlela yokuqinisekisa izinto ezininzi ngeparameter. clientcert=verify-full, iikholamu ezenziweyo kunye nokunye okuninzi. Kwanele kwisithuba esahlukileyo.
Njenge-PostgreSQL 10, i-PostgreSQL 12 iya kuphucula ukusebenza ngokubanzi ngokukhawuleza emva kokuphucula. Wena, ngokuqinisekileyo, unokuba nomendo wakho - ukuvavanya isicelo phantsi kweemeko ezifanayo kwinkqubo yokuvelisa ngaphambi kokwenza uphuculo, njengoko ndenzayo nge-PostgreSQL 10. Nokuba i-PostgreSQL 12 sele izinzile ngakumbi kunokuba bendilindele, musa ukuvila ekuvavanyeni. izicelo ngokucokisekileyo, phambi kokuba zikhuphele kwimveliso.
umthombo: www.habr.com