Sinthani kwa aulesi: momwe PostgreSQL 12 imasinthira magwiridwe antchito

PostgreSQL 12, mtundu waposachedwa wa "nkhokwe yapadziko lonse lapansi yotseguka yolumikizirana," ikutuluka m'masabata angapo (ngati zonse zikuyenda molingana ndi dongosolo). Izi zikutsatira ndondomeko yanthawi zonse yotulutsa mtundu watsopano wokhala ndi matani azinthu zatsopano kamodzi pachaka, ndipo kunena zoona, ndizosangalatsa. Ichi ndichifukwa chake ndidakhala membala wagulu la PostgreSQL.

M'malingaliro anga, mosiyana ndi zomwe zidatulutsidwa m'mbuyomu, PostgreSQL 12 ilibe chinthu chimodzi kapena ziwiri zosinthira (monga kugawa kapena kufunsa kufanana). Nthawi ina ndidachita nthabwala kuti gawo lalikulu la PostgreSQL 12 ndilokhazikika. Kodi sizomwe mumafunikira mukamayang'anira zofunikira zabizinesi yanu?

Koma PostgreSQL 12 siima pamenepo: ndi zatsopano ndi zosintha, mapulogalamu adzachita bwino, ndipo zomwe muyenera kuchita ndikukweza!

(Chabwino, mwina panganinso ma index, koma pakumasulidwa uku sizowopsa monga momwe timachitira.)

Zidzakhala zabwino kukweza PostgreSQL ndikusangalala nthawi yomweyo ndikusintha kwakukulu popanda kukangana kosafunikira. Zaka zingapo zapitazo, ndinayang'ananso kukweza kuchokera ku PostgreSQL 9.4 kupita ku PostgreSQL 10 ndikuwona momwe ntchitoyo inapitirizira chifukwa cha kufanana kwa mafunso mu PostgreSQL 10. max_parallel_workers).

Gwirizanani, ndizosavuta ngati mapulogalamu agwira ntchito bwino mukangokweza. Ndipo timayesetsa kusangalatsa ogwiritsa ntchito, chifukwa PostgreSQL ili ndi zambiri.

Ndiye kukweza kosavuta ku PostgreSQL 12 kungakupangitseni kukhala osangalala? Ine ndikuwuzani inu tsopano.

Kusintha kwakukulu kwa indexing

Popanda indexing, nkhokwe sangapite patali. Kodi mungapeze bwanji zambiri mwachangu? Dongosolo lofunikira la PostgreSQL limatchedwa B-mtengo. Mtundu uwu wa index umakometsedwa pamakina osungira.

Timangogwiritsa ntchito woyendetsa CREATE INDEX ON some_table (some_column), ndipo PostgreSQL imagwira ntchito zambiri kuti zisungidwe zidziwitso zatsopano pomwe timayika, kusintha, ndi kufufuta zomwe timafunikira nthawi zonse. Chilichonse chimagwira ntchito pachokha, ngati ndi matsenga.

Koma ma index a PostgreSQL ali ndi vuto limodzi - iwo akhutitsidwa ndikutenga malo owonjezera a disk ndikuchepetsa magwiridwe antchito a kubweza ndi kukonzanso. Ndi "bloat" ndikutanthauza kusasunga bwino dongosolo la index. Izi zikhoza - kapena ayi - zokhudzana ndi zinyalala zomwe zimachotsa VACUUM (zikomo kwa Peter Gaghan chifukwa cha chidziwitso)Peter Geoghegan)). Index bloat imawonekera makamaka pazantchito zomwe index ikusintha mwachangu.

PostgreSQL 12 imathandizira kwambiri magwiridwe antchito a B-tree indexes, ndipo kuyesa ndi ma benchmark ngati TPC-C awonetsa kuti pafupifupi 40% malo ochepa tsopano akugwiritsidwa ntchito. Tsopano timathera nthawi yocheperako osati pa kusunga ma index a B-tree (ndiko kuti, pa ntchito zolembera), komanso pochotsa deta, chifukwa ma index ndi ochepa kwambiri.

Mapulogalamu omwe amasinthitsa matebulo awo mwachangu - nthawi zambiri mapulogalamu a OLTP (real-time transaction processing) - idzagwiritsa ntchito disk ndikukonza zopempha bwino kwambiri. Malo ochulukirapo a disk, malo ochulukirapo a database ayenera kukula popanda kukonzanso zomangamanga.

Njira zina zokwezera zimafunika kukonzanso ma index a B-tree kuti apindule ndi izi (mwachitsanzo. pg_upgrade sichingapangenso ma index okha). M'matembenuzidwe am'mbuyomu a PostgreSQL, kumanganso ma index akulu pamatebulo kunapangitsa kuti pakhale nthawi yopumira chifukwa zosintha sizingapangidwe pakadali pano. Koma PostgreSQL 12 ili ndi chinthu china chabwino: tsopano mutha kumanganso ma index ofanana ndi lamulo. REINDEX PAMODZI PAMODZIkupeweratu nthawi yopuma.

Palinso zosintha zina pazida zolozera mu PostgreSQL 12. Chinanso pomwe panali matsenga - kulemba-patsogolo chipika, aka WAL (lolemba-tsogola chipika). chipika cholembera kutsogolo chimalemba zochitika zonse mu PostgreSQL ngati zitalephera komanso kubwereza. Mapulogalamuwa amagwiritsa ntchito posungira komanso kuchira kwanthawi yayitali. Zoonadi, chipika cholembera kutsogolo chimalembedwa ku disk, zomwe zingakhudze ntchito.

PostgreSQL 12 yachepetsa kuchuluka kwa zolemba za WAL zomwe zimapangidwa ndi ma index a GiST, GIN, ndi SP-GiST pakumanga index. Izi zimapereka mapindu angapo owoneka: Zolemba za WAL zimatenga malo ochepa a disk, ndipo deta imabwerezedwanso mofulumira, monga panthawi yobwezeretsa masoka kapena kubwezeretsanso nthawi. Ngati mugwiritsa ntchito ma index oterowo pamapulogalamu anu (mwachitsanzo, ma postGIS-based geospatial applications amagwiritsa ntchito index ya GiST kwambiri), ichi ndi chinthu china chomwe chingasinthire bwino zomwe mwakumana nazo popanda kuyesetsa kwanu.

Kugawa - zazikulu, bwino, mofulumira

PostgreSQL 10 idayambitsidwa declarative partitioning. Mu PostgreSQL 11 zakhala zosavuta kugwiritsa ntchito. Mu PostgreSQL 12 mutha kusintha kukula kwa magawo.

Mu PostgreSQL 12, magwiridwe antchito amagawo ayamba bwino kwambiri, makamaka ngati pali magawo masauzande patebulo. Mwachitsanzo, ngati funso likukhudza magawo ochepa patebulo ndi masauzande aiwo, lizichita mwachangu kwambiri. Kachitidwe sikungowongoleredwa pamafunso awa. Mudzaonanso kuchuluka kwa ntchito za INSERT pamatebulo okhala ndi magawo angapo.

Kujambula deta pogwiritsa ntchito COPY - Mwa njira, iyi ndi njira yabwino kutsitsa kochuluka kwa data ndipo nachi chitsanzo kulandira JSON - matebulo ogawidwa mu PostgreSQL 12 nawonso achita bwino. Ndi COPY zonse zinali zachangu kale, koma mu PostgreSQL 12 zimawuluka.

Chifukwa cha maubwino awa, PostgreSQL imakupatsani mwayi wosunga ma data okulirapo ndikupangitsa kuti ikhale yosavuta kupeza. Ndipo palibe khama kumbali yanu. Ngati pulogalamuyo ili ndi magawo ambiri, monga kujambula zotsatizana za nthawi, kukweza kosavuta kumathandizira kwambiri magwiridwe ake.

Ngakhale uku sikuli ndendende "kusintha ndi kusangalala", PostgreSQL 12 imakupatsani mwayi wopanga makiyi akunja omwe amatchula matebulo ogawa, ndikupangitsa kugawa kukhala kosangalatsa kugwira nawo ntchito.

NDI mafunso zakhala bwino kwambiri

pamene chigamba chinagwiritsidwa ntchito paziganizo zomwe zimapangidwira patebulo (aka CTE, aka NDI mafunso), sindingathe kudikira kuti ndilembe nkhani Momwe opanga mapulogalamu omwe ali ndi PostgreSQL anali okondwa. Ichi ndi chimodzi mwa zinthu zomwe zidzafulumizitse ntchito. Pokhapokha, mukamagwiritsa ntchito CTE.

Nthawi zambiri ndimapeza kuti ongoyamba kumene ku SQL amakonda kugwiritsa ntchito ma CTE; ngati muwalemba mwanjira inayake, zimamveka ngati mukulemba pulogalamu yofunikira. Inemwini, ndimakonda kulembanso mafunso awa kuti ndizungulira popanda CTE ndikuwonjezera zokolola. Tsopano zonse ndi zosiyana.

PostgreSQL 12 imakulolani kuti mulowetse mtundu wina wa CTE popanda zotsatirapo (SELECT), yomwe imagwiritsidwa ntchito kamodzi kokha kumapeto kwa pempho. Ndikadasunga mafunso a CTE omwe ndidalembanso, ambiri atha kugwera mgululi. Izi zimathandiza opanga kulemba code yomveka bwino yomwe tsopano imayendanso mwachangu.

Komanso, PostgreSQL 12 imapangitsa kuti SQL iwonongeke yokha, popanda kuchita chilichonse. Ndipo ngakhale sindidzafunikira kukhathamiritsa mafunso otere tsopano, ndizabwino kuti PostgreSQL ikupitilizabe kukhathamiritsa mafunso.

Just-in-Time (JIT) - tsopano kusakhazikika

Pa machitidwe a PostgreSQL 12 ndi chithandizo Zithunzi za LLVM Kupanga kwa JIT kumathandizidwa ndi kusakhazikika. Choyamba, mumapeza chithandizo JIT pazantchito zina zamkati, ndipo kachiwiri, mafunso okhala ndi mawu (chitsanzo chosavuta ndi x + y) pamndandanda wosankhidwa (omwe muli nawo mutatha KUSANKHA), zophatikiza, mawu okhala ndi KUTI ziganizo ndi ena angagwiritse ntchito JIT kukonza magwiridwe antchito.

Popeza JIT imayatsidwa mwachisawawa mu PostgreSQL 12, magwiridwe antchito aziyenda okha, koma ndikupangira kuyesa pulogalamuyo mu PostgreSQL 11, yomwe idayambitsa JIT, kuyeza magwiridwe antchito ndikuwona ngati mukufuna kuyimba chilichonse.

Nanga bwanji zina zatsopano mu PostgreSQL 12?

PostgreSQL 12 ili ndi matani azinthu zatsopano zabwino, kuyambira pakutha kuyesa deta ya JSON pogwiritsa ntchito njira zofananira za SQL/JSON mpaka kutsimikizika kwazinthu zambiri ndi parameter. clientcert=verify-full, adapanga mizati ndi zina zambiri. Zokwanira pa malo osiyana.

Monga PostgreSQL 10, PostgreSQL 12 isintha magwiridwe antchito atangomaliza kukweza. Inu, ndithudi, mutha kukhala ndi njira yanu - yesani kugwiritsa ntchito pansi pamikhalidwe yofananira pamapangidwe opangira musanayambe kusintha, monga momwe ndinachitira ndi PostgreSQL 10. Ngakhale PostgreSQL 12 ili kale yokhazikika kuposa momwe ndimayembekezera, musakhale aulesi poyesa. ntchito bwino, asanawatulutse kuti apange.

Source: www.habr.com