Simudzira kune vane usimbe: kuti PostgreSQL 12 inovandudza sei kuita

PostgreSQL 12, iyo yazvino vhezheni ye "yepasirese yakavhurika sosi relational dhatabhesi," iri kubuda mumavhiki mashoma (kana zvese zvichienda sekuronga). Izvi zvinotevera hurongwa hwenguva dzose hwekuburitsa vhezheni itsva ine toni yezvinhu zvitsva kamwe pagore, uye kutaura chokwadi, zvinokatyamadza. Ndosaka ndakava nhengo inoshanda yePostgreSQL nharaunda.

Sekuona kwangu, kusiyana nekuburitswa kwakamboitwa, PostgreSQL 12 haina chinhu chimwe chete kana maviri ekuchinja (sekuparadzanisa kana kubvunza kufanana). Ndakamboseka kuti chinhu chikuru chePostgreSQL 12 ndechekugadzikana kukuru. Handizvo here zvaunoda kana iwe uchitonga bhizinesi rako rakakosha data?

Asi PostgreSQL 12 haigumire ipapo: nezvitsva nekuvandudzwa, maapplication achaita zvirinani, uye zvese zvaunoda kuti uite kusimudzira!

(Zvakanaka, pamwe kuvakazve ma indexes, asi mukuburitswa uku hakusi kutyisa sezvatakajaira.)

Zvichave zvakanaka kukwidziridza PostgreSQL uye nekukasira kunakidzwa nekuvandudzwa kwakakosha pasina kupokana kusingakoshi. Makore mashoma apfuura, ndakaongorora kukwidziridzwa kubva kuPostgreSQL 9.4 kuenda kuPostgreSQL 10 uye ndakaona kuti application yakakwirisa sei nekuda kweiyo yakagadziridzwa yemubvunzo parallelism muPostgreSQL 10. Uye, zvinonyanya kukosha, hapana chaidiwa kubva kwandiri (ingoisa gadziriso parameter. max_parallel_workers).

Bvumiranai, zviri nyore kana maapplication achishanda zvirinani mushure mekusimudzira. Uye isu tinoedza zvakanyanya kufadza vashandisi, nekuti PostgreSQL ine yakawanda uye yakawanda yavo.

Saka kukwidziridzwa kuri nyore kuPostgreSQL 12 kungaite sei kuti ufare? Ndichakuudza ikozvino.

Kuvandudzwa kukuru kwe indexing

Pasina indexing, database haiende kure. Iwe unogona sei zvakare kuwana ruzivo nekukurumidza? PostgreSQL's yakakosha indexing system inonzi B-muti. Iyi mhando ye index yakagadziridzwa kuitira kuchengetedza masisitimu.

Isu tinongoshandisa opareta CREATE INDEX ON some_table (some_column), uye PostgreSQL inoita basa rakawanda kuchengetedza index kusvika parizvino isu tichigara tichiisa, kugadzirisa, uye kudzima kukosha. Zvinhu zvese zvinoshanda zvega, sekunge nemashiripiti.

Asi PostgreSQL indexes vane dambudziko rimwe - ivo vari kufuridzirwa uye tora imwe dhisiki nzvimbo uye kuderedza mashandiro ekutora data nekuvandudza. Ne "bloat" ndinoreva kusashanda kuchengetedza chimiro che index. Izvi zvinogona - kana kusave - zvine chekuita nemarara tuples ayo anobvisa VACUUM (ndatenda kuna Peter Gaghan neruzivo)Peter Geoghegan)). Index bloat inonyanya kuoneka mukuremerwa kwebasa uko iyo index iri kushingaira kuchinja.

PostgreSQL 12 inonatsiridza zvakanyanya mashandiro eB-muti indexes, uye kuyedza nemabhenji seTPC-C kwakaratidza kuti paavhareji 40% nzvimbo shoma yave kushandiswa. Iye zvino tinopedza nguva shoma kwete chete pakuchengetedza B-tree indexes (kureva, pamabasa ekunyora), asiwo pakutora data, nokuti zvinyorwa zviduku zvikuru.

Zvikumbiro zvinovandudza matafura avo - kazhinji OLTP application (real-time transaction processing) - ichashandisa dhisiki uye kugadzirisa zvikumbiro zvakanyanya. Iyo yakawanda dhisiki nzvimbo, iyo yakawanda nzvimbo iyo dhatabhesi inofanirwa kukura pasina kusimudzira zvivakwa.

Dzimwe nzira dzekusimudzira dzinoda kuvaka patsva B-miti indexes kutora mukana weizvi mabhenefiti (e.g. pg_upgrade haizovakazve indexes otomatiki). Mushanduro dzakapfuura dzePostgreSQL, kuvakazve maindex makuru pamatafura kwakakonzera kuderera kukuru nekuti shanduko haina kukwanisa kuitwa panguva iyi. Asi PostgreSQL 12 ine chimwe chinhu chinotonhorera: ikozvino iwe unogona kuvakazve indexes mukuenderana nemurairo. REINDEX CONCURRENTLYkudzivirira zvachose downtime.

Pane kumwe kuvandudzwa kweiyo indexing infrastructure muPostgreSQL 12. Chimwe chinhu pakanga paine mamwe mashiripiti - kunyora-mberi log, aka WAL (nyora-mberi danda). Rogi yekunyora-mberi inorekodha kutengeserana kwese muPostgreSQL kana ikatadza uye kudzokorora. Zvishandiso zvinoshandisa iyo kuchengetedza uye point-in-time recovery. Ehe, iyo yekunyora-yepamberi log inonyorerwa dhisiki, iyo inogona kukanganisa kushanda.

PostgreSQL 12 yakadzikisa pamusoro peWAL zvinyorwa zvinogadzirwa neGiST, GIN, uye SP-GiST indexes panguva yekuvaka index. Izvi zvinopa mabhenefiti akati wandei: WAL marekodhi anotora shoma dhisiki nzvimbo, uye data inodzokororwa nekukurumidza, senge panguva yekudzoreredza njodzi kana point-in-time kupora. Kana iwe ukashandisa ma indexes akadaro mumashandisirwo ako (semuenzaniso, PostGIS-based geospatial application inoshandisa iyo GiST index zvakanyanya), ichi ndicho chimwe chimiro chinozovandudza zvakanyanya chiitiko pasina chero kuedza kwauri.

Partitioning - yakakura, nani, nekukurumidza

PostgreSQL 10 yakaunzwa declarative partitioning. MuPostgreSQL 11 zvave nyore kushandisa. MuPostgreSQL 12 unogona kushandura chiyero chezvikamu.

MuPostgreSQL 12, kuita kweiyo partitioning system kwave nani zvakanyanya, kunyanya kana paine zviuru zvezvikamu mutafura. Semuyenzaniso, kana mubvunzo uchingobata zvikamu zvishoma mutafura ine zviuru zvazvo, zvinozoita nekukurumidza. Kuita hakusi kungovandudzwa kwemhando idzi dzemibvunzo. Iwe unozoona zvakare kuti nekukurumidza INSERT mashandiro ari pamatafura ane mapartitions akawanda.

Kurekodha data uchishandisa KOPA - nenzira, iyi inzira yakanaka dhawunirodha data yakawanda uye heino muenzaniso kugamuchira JSON - akapatsanurwa matafura muPostgreSQL 12 avewo akanyanya kushanda. NeCOPY zvese zvaive zvatove nekukurumidza, asi muPostgreSQL 12 inobhururuka zvachose.

Nekuda kweaya mabhenefiti, PostgreSQL inokutendera iwe kuti uchengetedze akakura maseti edata uye kuita kuti zvive nyore kutora. Uye hapana kuedza kwauri. Kana iyo application iine akawanda partitions, senge yekurekodha nguva yakateedzana data, kusimudzira kuri nyore kunovandudza zvakanyanya kuita kwayo.

Kunyange iyi isiri chaiyo "kusimudzira uye kunakidzwa" kuvandudzwa, PostgreSQL 12 inokutendera iwe kuti ugadzire makiyi ekunze anonongedza matafura akapatsanurwa, zvichiita kuti kugovera kuve kunakidzwa kushanda nawo.

NEmibvunzo yatowedzera kuita nani

When chigamba chakaiswa kune akavakirwa-mukati metafura mataurirwo (aka CTE, aka NEmibvunzo), handina kukwanisa kumirira kunyora chinyorwa nezve vakafara sei vagadziri vekushandisa nePostgreSQL vaive. Ichi ndicho chimwe cheaya maficha anomhanyisa application. Kunze kwekunge, hongu, iwe unoshandisa CTE.

Ini ndinowanzoona kuti vatsva kuSQL vanoda kushandisa CTEs; kana iwe ukavanyora neimwe nzira, zvinonzwa sekunge uri kunyora chirongwa chakakosha. Ini pachangu, ndakafarira kunyorazve iyi mibvunzo kuti ndifambe kunze CTE uye kuwedzera kubereka. Iye zvino zvinhu zvose zvasiyana.

PostgreSQL 12 inokubvumira kuti uise mukati meimwe mhando yeCTE isina mhedzisiro (SELECT), iyo inoshandiswa kamwe chete pedyo nekupera kwekukumbira. Dai ndakachengeta mibvunzo yeCTE yandakanyora zvakare, mazhinji acho aizowira muchikamu ichi. Izvi zvinobatsira vanogadzira kunyora kodhi yakajeka iyo ikozvino inomhanya nekukurumidza.

Uyezve, PostgreSQL 12 inogonesa SQL kuurayiwa kwayo, pasina iwe chekuita chero chinhu. Uye kunyangwe ini pamwe ndisingazoda kukwidziridza mibvunzo yakadai izvozvi, zvakanaka kuti PostgreSQL irambe ichishanda pakugadzirisa mibvunzo.

Just-in-Time (JIT) - ikozvino default

PaPostgreSQL 12 masisitimu ane rutsigiro LLVM JIT kuunganidzwa kunogoneswa nekusarudzika. Chokutanga pane zvose, unowana rubatsiro HIT kune mamwe mashandisirwo emukati, uye chechipiri, mibvunzo ine mataurirwo (muenzaniso wakapfava ndewe x + y) mune zvakasarudzwa zvinyorwa (zvauinazvo mushure meKUSARUDZA), akaunganidza, mataurirwo ane HERE zvikamu uye zvimwe zvinogona kushandisa JIT kuvandudza mashandiro.

Sezvo JIT ichigoneswa nekusarudzika muPostgreSQL 12, mashandiro achavandudza ari ega, asi ini ndinokurudzira kuyedza application muPostgreSQL 11, iyo yakaunza JIT, kuyera kuita kwemubvunzo uye kuona kana iwe uchida kurongedza chero chinhu.

Zvakadini nezvimwe zvese zvitsva muPostgreSQL 12?

PostgreSQL 12 ine toni yezvinhu zvitsva zvinotonhorera, kubva pakukwanisa kuongorora data yeJSON uchishandisa yakajairwa SQL/JSON nzira mataurirwo kune akawanda-factor authentication ine parameter. clientcert=verify-full, akagadzira makoramu uye zvimwe zvakawanda. Zvakakwana kune imwe nzvimbo yakasiyana.

Kufanana nePostgreSQL 10, PostgreSQL 12 ichavandudza kushanda kwese pakarepo mushure mekusimudzira. Iwe, hongu, unogona kuva neyako nzira - edza chikumbiro pasi pemamiriro akafanana pane yekugadzira system usati wagonesa kuvandudzwa, sezvandakaita nePostgreSQL 10. Kunyange kana PostgreSQL 12 yatove yakagadzikana kupfuura zvandaitarisira, usaite nungo mukuyedza. zvikumbiro zvakakwana, usati wazvisunungura mukugadzirwa.

Source: www.habr.com