, inguqulo yakamuva "yesizindalwazi esingcono kakhulu somthombo ovulekile sobudlelwano emhlabeni," izophuma emavikini ambalwa (uma konke kuhamba ngokohlelo). Lokhu kulandela ishejuli evamile yokukhipha inguqulo entsha enenani lezici ezintsha kanye ngonyaka, futhi ngokungananazi, lokho kuyamangaza. Yingakho ngibe yilungu elisebenzayo lomphakathi we-PostgreSQL.
Ngokubona kwami, ngokungafani nokukhishwa kwangaphambilini, i-PostgreSQL 12 ayinayo isici esisodwa noma ezimbili eziguquguqukayo (njengokwahlukanisa noma ukufana kombuzo). Ngake ngantela ukuthi isici esiyinhloko se-PostgreSQL 12 ukuzinza okukhulu. Ingabe akukhona lokho okudingayo uma uphatha idatha ebalulekile yebhizinisi lakho?
Kodwa i-PostgreSQL 12 ayigcini lapho: ngezici ezintsha nentuthuko, izinhlelo zokusebenza zizokwenza kangcono, futhi konke okudingeka ukwenze ukuthuthukisa!
(Hhayi-ke, mhlawumbe yakha kabusha izinkomba, kepha kulokhu kukhululwa akwesabisi njengoba besijwayele.)
Kuzoba kuhle ukuthuthukisa i-PostgreSQL futhi ujabulele ngokushesha ukuthuthuka okubalulekile ngaphandle kokuxabana okungadingekile. Eminyakeni embalwa edlule, ngabuyekeza ukuthuthukiswa kusuka ku-PostgreSQL 9.4 kuya ku-PostgreSQL 10 futhi ngabona ukuthi uhlelo lokusebenza lusheshisa kanjani ngenxa yokufana kombuzo okuthuthukisiwe ku-PostgreSQL 10. Futhi, okubaluleke kakhulu, cishe akukho lutho olwaludingeka kimi (vele usethe ipharamitha yokumisa. max_parallel_workers).
Vumelana, kulula uma izinhlelo zokusebenza zisebenza kangcono ngokushesha ngemva kokuthuthukiswa. Futhi sizama kanzima ukujabulisa abasebenzisi, ngoba i-PostgreSQL inokuningi kwayo.
Ngakho-ke ukuthuthukela okulula ku-PostgreSQL 12 kungakwenza kanjani ujabule? Ngizokutshela manje.
Ukuthuthukiswa okukhulu kwenkomba
Ngaphandle kokukhomba, isizindalwazi ngeke siye kude. Ungaluthola kanjani olunye ulwazi ngokushesha? Uhlelo lokukhomba oluyisisekelo lwe-PostgreSQL lubizwa . Lolu hlobo lwenkomba lulungiselelwe izinhlelo zokugcina.
Simane sisebenzisa opharetha CREATE INDEX ON some_table (some_column), futhi i-PostgreSQL yenza umsebenzi omningi wokugcina inkomba isesikhathini ngenkathi sifaka njalo, sibuyekeza, futhi sisusa amanani. Yonke into isebenza ngokwayo, njengokungathi ngomlingo.
Kepha izinkomba ze-PostgreSQL zinenkinga eyodwa - zona futhi uthathe isikhala sediski esengeziwe futhi unciphise ukusebenza kokubuyiswa kwedatha nokubuyekeza. Ngokuthi "bloat" ngiqonde ukulondoloza ukwakheka kwenkomba ngendlela engafanele. Lokhu kungase - noma kungahlobene - nama-tuples kadoti ewasusayo (sibonga uPeter Gaghan ngolwazi))). I-Index bloat ibonakala ikakhulukazi emisebenzini lapho inkomba ishintsha khona.
I-PostgreSQL 12 ithuthukisa kakhulu ukusebenza kwezinkomba ze-B-tree, futhi ukuhlola okunezilinganiso ezifana ne-TPC-C kubonise ukuthi ngokwesilinganiso isikhala esincane esingu-40% manje siyasetshenziswa. Manje asichithi isikhathi esincane hhayi kuphela ekugcineni izinkomba ze-B-tree (okungukuthi, emisebenzini yokubhala), kodwa futhi ekubuyiseni idatha, ngoba izinkomba zincane kakhulu.
Izinhlelo zokusebenza ezibuyekeza amathebula azo ngokusebenzayo - ngokuvamile izinhlelo zokusebenza ze-OLTP () - izosebenzisa idiski futhi icubungule izicelo ngempumelelo kakhulu. Uma kunediski eningi, kulapho isizindalwazi kufanele sikhule khona ngaphandle kokuthuthukisa ingqalasizinda.
Amanye amasu okuthuthukisa adinga ukwakhiwa kabusha kwezinkomba ze-B-tree ukuze kuzuze lezi zinzuzo (isb. ngeke yakhe kabusha izinkomba ngokuzenzakalelayo). Ezinguqulweni zangaphambilini ze-PostgreSQL, ukwakha kabusha izinkomba ezinkulu ematafuleni kubangele ukwehla okukhulu ngoba izinguquko azikwazanga ukwenziwa ngaleso sikhathi. Kepha i-PostgreSQL 12 inesinye isici esihle: manje usungakwazi ukwakha kabusha izinkomba ngokuhambisana nomyalo. ukugwema ngokuphelele isikhathi sokuphumula.
Kukhona okunye ukuthuthukiswa kwengqalasizinda yenkomba ku-PostgreSQL 12. Enye into lapho kwakukhona umlingo - , i-WAL (ilogi yokubhala phambili). Ilogi yokubhala phambili irekhoda konke okwenziwayo ku-PostgreSQL uma kwenzeka yehluleka nokuphindaphinda. Izicelo ziyisebenzisela ukufaka kungobo yomlando kanye . Yiqiniso, ilogi yokubhala phambili ibhalwe kudiski, okungaba nomthelela ekusebenzeni.
I-PostgreSQL 12 yehlise i-overhead yamarekhodi e-WAL adalwe yizinkomba ze-GiST, GIN, ne-SP-GiST phakathi nokwakhiwa kwenkomba. Lokhu kunikeza izinzuzo ezimbalwa ezibambekayo: Amarekhodi e-WAL athatha isikhala sediski esincane, futhi idatha idlalwa kabusha ngokushesha, njengalapho kutholwa inhlekelele noma ukutholwa kwephoyinti ngesikhathi. Uma usebenzisa izinkomba ezinjalo ezinhlelweni zakho (isibonelo, izinhlelo zokusebenza ze-geospatial ezisekelwe ku-PostGIS zisebenzisa inkomba ye-GiST kakhulu), lesi esinye isici esizothuthukisa kakhulu ukuzizwisa ngaphandle komzamo ohlangothini lwakho.
Ukuhlukaniswa - okukhulu, okungcono, ngokushesha
I-PostgreSQL 10 yethulwe . Ku-PostgreSQL 11 sekulula kakhulu ukuyisebenzisa. Ku-PostgreSQL 12 ungashintsha isikali sezigaba.
Ku-PostgreSQL 12, ukusebenza kwesistimu yokuhlukanisa kube ngcono kakhulu, ikakhulukazi uma kunezinkulungwane zezingxenye etafuleni. Isibonelo, uma umbuzo uthinta izingxenye ezimbalwa zetafula ezinezinkulungwane zazo, uzosebenza ngokushesha okukhulu. Ukusebenza akuthuthukisiwe nje kulezi zinhlobo zemibuzo. Uzoqaphela futhi ukuthi imisebenzi ye-FAKA ishesha kangakanani kumathebula anezihlukanisi eziningi.
Ukurekhoda idatha usebenzisa - Ngendlela, lena yindlela enhle futhi nasi isibonelo - amatafula ahlukanisiwe ku-PostgreSQL 12 nawo asebenza kahle kakhulu. Nge-COPY yonke into yayivele ishesha, kepha ku-PostgreSQL 12 indiza ngokuphelele.
Ngenxa yalezi zinzuzo, i-PostgreSQL ikuvumela ukuthi ugcine amasethi wedatha amakhudlwana futhi uwenze kube lula ukuwathola. Futhi akukho mzamo ohlangothini lwakho. Uma uhlelo lokusebenza lunama-partitions amaningi, njengedatha yochungechunge lwesikhathi sokurekhoda, ukuthuthukiswa okulula kuzothuthukisa kakhulu ukusebenza kwalo.
Yize lokhu kungekona ukuthuthuka "kokuthuthukisa futhi ujabulele", i-PostgreSQL 12 ikuvumela ukuthi udale okhiye bakwamanye amazwe ababhekisela kumatafula ahlukanisiwe, okwenza ukwahlukanisa kube yintokozo ukusebenza nabo.
NGEMIBUZO sekungcono kakhulu
Nini (aka CTE, aka NGEMIBUZO), angikwazanga ukulinda ukubhala isihloko mayelana . Lesi ngesinye salezo zici ezizosheshisa uhlelo lokusebenza. Ngaphandle uma kunjalo, usebenzisa i-CTE.
Ngivame ukuthola ukuthi abasanda kuhlanganyela ku-SQL bathanda ukusebenzisa ama-CTE; uma uwabhala ngendlela ethile, kuzwakala sengathi ubhala uhlelo olubalulekile. Ngokwami, ngithande ukubhala kabusha le mibuzo ukuze ngihambe ngaphandle CTE futhi andise umkhiqizo. Manje konke kuhlukile.
I-PostgreSQL 12 ikuvumela ukuthi uxhume uhlobo oluthile lwe-CTE ngaphandle kwemiphumela emibi (SELECT), esetshenziswa kanye kuphela ngasekupheleni kwesicelo. Uma ngilandelela imibuzo ye-CTE engiyibhale kabusha, iningi layo lingawela kulesi sigaba. Lokhu kusiza onjiniyela babhale ikhodi ecacile manje esebenza ngokushesha.
Ngaphezu kwalokho, i-PostgreSQL 12 ithuthukisa ukusebenza kwe-SQL ngokwayo, ngaphandle kokuthi wenze noma yini. Futhi noma mhlawumbe ngeke ngidinge ukukhulisa imibuzo enjalo manje, kuhle ukuthi i-PostgreSQL iqhubeke nokusebenza ekwenzeni imibuzo.
I-Just-in-Time (JIT) - manje izenzakalelayo
Kuzinhlelo ze-PostgreSQL 12 ezinokwesekwa Ukuhlanganiswa kwe-JIT kunikwe amandla ngokuzenzakalela. Okokuqala, uthola ukwesekwa kweminye imisebenzi yangaphakathi, futhi okwesibili, imibuzo enezisho (isibonelo esilula kakhulu ngu-x + y) ezinhlwini ezikhethiwe (onazo ngemva kokuthi KHETHA), ukuhlanganiswa, izinkulumo ezinezigatshana zokuthi LAPHO kanye nezinye zingasebenzisa i-JIT ukuthuthukisa ukusebenza.
Njengoba i-JIT inikwe amandla ngokuzenzakalelayo ku-PostgreSQL 12, ukusebenza kuzothuthuka ngokwakho, kodwa ngincoma ukuhlola uhlelo lokusebenza ku-PostgreSQL 11, eyethula i-JIT, ukukala ukusebenza kombuzo futhi ubone ukuthi udinga ukushuna noma yini.
Kuthiwani ngazo zonke ezinye izici ezintsha ku-PostgreSQL 12?
I-PostgreSQL 12 inezici ezintsha ezipholile, kusukela ekhonweni lokuhlola idatha ye-JSON kusetshenziswa izinkulumo ezijwayelekile zomzila we-SQL/JSON ukuya ekuqinisekiseni izinto eziningi ngepharamitha. clientcert=verify-full, amakholomu adalwe nokunye okuningi. Kwanele okuthunyelwe okuhlukile.
Njenge-PostgreSQL 10, i-PostgreSQL 12 izothuthukisa ukusebenza okuphelele ngokushesha ngemva kokuthuthukiswa. Wena, vele, ungaba nendlela yakho - hlola uhlelo lokusebenza ngaphansi kwezimo ezifanayo ohlelweni lokukhiqiza ngaphambi kokuvumela ukuthuthukiswa, njengoba ngenza nge-PostgreSQL 10. Ngisho noma i-PostgreSQL 12 isivele izinzile kunalokho engangikulindele, ungavilaphi ekuhloleni. izicelo ngokucophelela, ngaphambi kokuzikhulula ekukhiqizeni.
Source: www.habr.com