Pulogalamu ya ProHoster > Blog > nkhani zapaintaneti > PostgreSQL 14 DBMS kutulutsidwa

PostgreSQL 14 DBMS kutulutsidwa

Pambuyo pa chaka cha chitukuko, nthambi yatsopano yokhazikika ya PostgreSQL 14 DBMS yasindikizidwa. Zosintha za nthambi yatsopanoyi zidzatulutsidwa kwa zaka zisanu mpaka November 2026.

Zatsopano zazikulu:

  • Thandizo lowonjezera lofikira ku data ya JSON pogwiritsa ntchito mawu otikumbutsa kugwira ntchito ndi magulu: SINKHANI ('{"postgres": {"kutulutsa": 14}}'::jsonb)['postgres']['release']; SANKHANI * KUCHOKERA ku mayeso PALI zambiri['makhalidwe']['size'] = '"zapakatikati"';

    Kalankhulidwe kofananako kamagwiritsidwa ntchito pa kiyi / mtengo wa data woperekedwa ndi mtundu wa hstore. Kalankhulidwe kameneka kanagwiritsidwa ntchito poyambira ponseponse, zomwe m'tsogolomu zitha kugwiritsidwa ntchito pamitundu ina. Chitsanzo cha mtundu wa hstore: LOWANI M'MFUNDO za mytable ('a=>b, c=>d'); SANKHANI h[‘a’] KWA mytable; UPDATE mytable SET h['c'] = 'zatsopano';

  • Banja la mitundu yofotokozera milingo yawonjezedwa ndi mitundu yatsopano ya "multirange", yomwe imakulolani kuti mutchule mndandanda wazinthu zomwe sizinadutse. Kuphatikiza pa mtundu uliwonse womwe ulipo, mtundu wake wamitundu yosiyanasiyana umaperekedwa, mwachitsanzo, mtundu wa "int4range" umafanana ndi "int4multirange", ndipo mtundu wa "daterange" umafanana ndi "datemultirange". Kugwiritsiridwa ntchito kwa mitundu yatsopano kumathandizira mapangidwe a mafunso omwe amatha kutsatizana movutikira. SANKHANI '{[3,7), [8,9)}'::int4multirange; SANKHANI nummultirange(numrange(1.0, 14.0), numrange(20.0, 25.0));
  • Kukhathamiritsa kwapangidwa kuti apititse patsogolo ntchito zamakina olemetsa kwambiri pokonza zolumikizira zambiri. M'mayesero ena, kuwonjezeka kawiri pakuchita kumawonedwa.
  • Kuchita bwino kwa ma index a mitengo ya B kwasinthidwa ndipo vuto la kukula kwa index pomwe matebulo amasinthidwa pafupipafupi lathetsedwa.
  • Thandizo lowonjezera la kasitomala-mbali (lomwe limakhazikitsidwa pamlingo wa libpq) njira yotumizira mapaipi, yomwe imakupatsani mwayi wofulumizitsa zochitika za database zomwe zimagwirizanitsidwa ndikuchita ntchito zambiri zolembera (INSERT / UPDATE / DELETE) potumiza pempho lotsatira popanda kuyembekezera zotsatira za m'mbuyomu. Njirayi imathandizanso kufulumizitsa ntchito pamalumikizidwe ndi kuchedwa kwapaketi yayitali.
  • Kuthekera kowonjezereka kwamasinthidwe omwe amagawidwa ophatikiza ma seva angapo a PostgreSQL. Pokhazikitsa kubwereza zomveka, tsopano zimakhala zotheka kutumiza mumayendedwe akukhamukira omwe ali m'kati mwa kuphedwa, zomwe zitha kusintha kwambiri magwiridwe antchito azinthu zazikulu. Kuphatikiza apo, kumasulira momveka bwino kwa data yomwe idalandiridwa panthawi yobwereza momveka bwino kwakonzedwanso.
  • Njira yolumikizira matebulo akunja a Foreign Data Wrapper (postgres_fdw) yawonjezera kuthandizira pakukonzanso kwamafunso, komwe kumangogwiritsidwa ntchito polumikizana ndi ma seva ena a PostgreSQL. postgres_fdw imawonjezeranso chithandizo chowonjezera deta kumatebulo akunja mumachitidwe a batch komanso kuthekera kolowetsa matebulo ogawidwa pofotokoza za "IMPORT FOREIGN SCHEMA".
  • Kukhathamiritsa kwapangidwa pakukhazikitsa ntchito ya VACUUM (kusonkhanitsa zinyalala ndi kulongedza kwa disk yosungirako). Njira yoyeretsera mwadzidzidzi yawonjezedwa yomwe imadumpha ntchito zosafunikira zoyeretsa ngati ma ID achinsinsi apangidwa. Kuchepetsedwa pamwamba pokonza ma index mu mtundu wa B-Tree. Kuchita kwa ntchito ya "ANALYZE", yomwe imasonkhanitsa ziwerengero zokhudzana ndi ntchito ya database, yakhala ikufulumira kwambiri.
  • Anawonjezera luso lokonzekera njira yopondereza yomwe imagwiritsidwa ntchito mu TOAST system, yomwe ili ndi udindo wosunga deta yaikulu, monga midadada ya malemba kapena chidziwitso cha geometric. Kuphatikiza pa njira yopondereza ya pglz, TOAST tsopano ikhoza kugwiritsa ntchito algorithm ya LZ4.
  • Zida zowunikira momwe DBMS ikugwirira ntchito zakulitsidwa. Mawonedwe owonjezera kuti awone momwe malamulo a COPY akuyendera (pg_stat_progress_copy), ziwerengero za malo obwerezabwereza (pg_stat_replication_slots) ndi zochitika zokhudzana ndi chipika cha WAL (pg_stat_wal). Ntchito ya compute_query_id yawonjezedwa, yomwe imathandizira ma subsystem osiyanasiyana, monga pg_stat_activity ndi EXPLAIN VERBOSE, kuti azitsata zopempha popereka chozindikiritsa chapadera pachofunsa chilichonse.
  • Kukhathamiritsa kwawonjezedwa kwa okonza mafunso kuti apititse patsogolo kukonzanso kwamafunso ndikuwongolera magwiridwe antchito munthawi imodzi yojambulira zolemba zotsatizana, kutsata mafunso mu PL/pgSQL pogwiritsa ntchito lamulo la "RETURN QUERY", ndi kuyankha kofanana kwa mafunso mu " WONSEZANI MAONERO OTHANDIZA”. Kupititsa patsogolo magwiridwe antchito a ma cyclic nested merges (kujowina), kuthandizira pakusungirako kowonjezera kwakhazikitsidwa.
  • Ziwerengero zapamwamba tsopano zitha kugwiritsidwa ntchito kukhathamiritsa mawu, ndipo kusanja mowonjezera tsopano kungagwiritsidwe ntchito kukhathamiritsa magwiridwe antchito awindo.
  • Njira zosungidwa zomwe zimakulolani kuti muzitha kuyang'anira zochitika mu midadada yama code tsopano zimathandizira kutanthauzira deta yobwerera pogwiritsa ntchito magawo a "OUT".
  • Adawonjezera ntchito ya date_bin pamitengo yozungulira yanthawi yake malinga ndi nthawi yodziwika. SANKHANI deti_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-01-01'); 2020-02-11 15:30:00
  • Mawu owonjezera a SEARCH ndi CYCLE ofotokozedwa mu SQL mulingo kuti zikhale zosavuta kuyitanitsa ndikuzindikira mizungulira muzobwereza za Common Table Expressions (CTE). NDI RECURSIVE search_tree(id, ulalo, data) AS (SANKHANI t.id, t.link, t.data KUCHOKERA ku mtengo t UNION ONSE SANKHANI t.id, t.link, t.data KUCHOKERA ku mtengo t, search_tree st KODI t. id = st.link ) PANGANI KUYA KOYAMBA NDI id SET ordercol SINKHANI * KUCHOKERA search_tree KUKONZERA BY ordercol;
  • Muzogwiritsira ntchito psql, kutsirizitsa kwa malamulo ndi ma tabo kwakonzedwa bwino, kuthekera kowonetsera zotsutsana za ntchito kwawonjezeredwa ku lamulo la "\ df", ndipo ziwerengero zowonetsedwa zawonjezeredwa ku lamulo la "\ dX".
  • Ndizotheka kupatsa ogwiritsa ntchito mwayi wowerengera okha kapena kulemba okha. Mwayi ukhoza kukhazikitsidwa pamatebulo, mawonedwe, ndi schemas pogwiritsa ntchito pg_read_all_data ndi pg_write_all_data predefined roles. GRANT pg_read_all_data KWA wosuta1;
  • Kuyika kwatsopano kosasinthika kutsimikizira mawu achinsinsi pogwiritsa ntchito SCRAM-SHA-256 m'malo mwa md5 (gawo la "password_encryption" popanga postgresql.conf tsopano lakhazikitsidwa ku 'scram-sha-256').

Source: opennet.ru

Kuwonjezera ndemanga