ProHoster > Π‘Π»ΠΎΠ³ > wararka internetka > PostgreSQL 14 DBMS sii deynta

PostgreSQL 14 DBMS sii deynta

Sannad ka dib horumarinta, laan cusub oo xasilloon oo PostgreSQL 14 DBMS ah ayaa la daabacay. Cusbooneysiinta laanta cusub ayaa la sii deyn doonaa shan sano ilaa Noofambar 2026.

Hal-abuurka ugu muhiimsan:

  • Taageero lagu daray gelitaanka xogta JSON iyada oo la adeegsanayo tibaaxo u eg: SELECT ('{"postgres": {"siideyn": 14}}'::jsonb)['postgres']['sii dayn']; KA DOORAN * Imtixaanka HALKEE tafaasiisha['size'] ['size'] = "dhexdhexaad"';

    Sintax la mid ah ayaa loo hirgeliyay xogta muhiimka ah/qiimaha ee uu bixiyo nooca hstore. Ereygan ayaa markii hore la hirgeliyey iyadoo la adeegsanayo qaab-dhismeedka caalamiga ah, kaas oo mustaqbalka loo isticmaali karo noocyo kale. Tusaalaha nooca hstore: Geli qiyamka la taaban karo ('a=>b, c=>d'); KA XUL H['a'] Miiska-khadka; UPDATE mytable SET h['c'] = 'cusub';

  • Qoyska noocyada kala duwan ee qeexida kala duwan ayaa lagu kordhiyey noocyo cusub oo "multirange", taas oo kuu ogolaanaysa inaad qeexdo liisaska la dalbaday ee kala duwanaanshaha qiimaha aan is-dul-saarnayn. Marka lagu daro nooc kasta oo kala duwan oo jira, waxaa la soo jeedinayaa nooc ka mid ah noocyada kala duwan ee u gaar ah, tusaale ahaan, nooca "int4range" u dhigma "int4multirange", iyo nooca "daterange" u dhigma "datemultirange". Isticmaalka noocyada cusub waxay fududaynaysaa naqshadaynta su'aalaha kuwaas oo wax ka beddelaya taxanaha adag ee kala duwan. Xulo '{[3,7), [8,9)}':: int4multirange; Xulo nambarada (1.0, 14.0), nambarka (20.0, 25.0));
  • Hagaajinta ayaa la sameeyay si loo hagaajiyo waxqabadka nidaamyada culeyska sare leh ee ka shaqeynaya isku xirka tiro badan. Imtixaanada qaar, laba laab kor u kaca waxqabadka ayaa lagu arkay.
  • Waxtarka tusmooyinka B-geedka waa la hagaajiyay iyo dhibaatada kobaca tusmada marka miisaska si joogta ah loo cusbooneysiiyo ayaa la xalliyay.
  • Taageerada lagu daray ee dhinaca macmiilka (oo lagu hirgeliyay heerka libpq) habka gudbinta dhuumaha codsiyada, taas oo kuu ogolaanaysa inaad si weyn u dedejiso xaaladaha xogta ee la xidhiidha fulinta tiro badan oo hawlgalo qoraal ah codsiga xiga adiga oo aan sugin natiijada kii hore. Habkani waxa kale oo uu ka caawiyaa in uu dedejiyo shaqada isku xidhka dib u dhigista baakooyinka dheer.
  • Awoodaha la wanaajiyey ee isku xidhka la qaybiyey ee ku lug leh adeegayaal badan oo PostgreSQL ah. Hirgelinta dib-u-celinta macquulka ah, hadda waxay suurtogal u tahay in la soo diro hab-socodka dhaqdhaqaaqa dhaqdhaqaaqa kaas oo ku jira habka fulinta, taas oo si weyn u wanaajin karta waxqabadka ganacsiyada waaweyn. Intaa waxaa dheer, dejinta macquulka ah ee xogta la helay inta lagu jiro ku celcelinta macquulka ah ayaa la hagaajiyay.
  • Habka isku xirka miisaska dibadda Xogta Shisheeye (postgres_fdw) waxay ku dartay taageerada habaynta weydiinta isbarbar-dhigga, taas oo hadda kaliya lagu dabaqi karo marka lagu xirayo adeegayaasha kale ee PostgreSQL. postgres_fdw waxa kale oo ay ku darsataa taageerada ku darida xogta miisaska dibadda ee qaabka dufcada iyo awooda lagu soo dejiyo miisaska qaybsan iyada oo la qeexayo dardaaranka "SCHEMA SHISHEEYE".
  • Hagaajinta ayaa lagu sameeyay hirgelinta hawlgalka VACUUM (qashin ururinta iyo baakadaha kaydinta saxanka). Habka nadiifinta degdega ah ayaa lagu daray in uu ka boodo hawlaha nadiifinta ee aan daruuriga ahayn haddii shuruudaha duubista aqoonsiga macaamil ganacsi la abuuro. Hoos-u-dhac ku yimaadda marka la farsameynayo tusmooyinka qaabka B-Tree. Fulinta hawlgalka "ANALYZE", kaas oo ururinaya tirakoobyada ku saabsan hawlgalka xogta, ayaa si weyn loo dardargeliyay.
  • Waxaa lagu daray awoodda lagu habeeyo habka isku-buufinta ee loo isticmaalo nidaamka TOAST, kaas oo mas'uul ka ah kaydinta xogta waaweyn, sida blocks of qoraalka ama macluumaadka joomatari. Marka lagu daro habka isku-buufinta pglz, TOAST waxay hadda isticmaali kartaa algorithm-ka LZ4.
  • Qalabka lagula socdo hawlgalka DBMS waa la balaariyay. Aragtiyo lagu daray si ay ula socdaan horumarka amarada COPY (pg_stat_progress_copy), tirakoobka ku saabsan boosaska ku celcelinta (pg_stat_replication_slots) iyo hawsha la xidhiidha diiwaanka wax kala iibsiga WAL (pg_stat_wal). Shaqada compute_query_id ayaa lagu daray, kaasoo awood u siinaya nidaamyo hoosaadyo kala duwan, sida pg_stat_activity iyo EXPLAIN VERBOSE, si ay ula socdaan codsiyada iyadoo loo xilsaarayo aqoonsi u gaar ah codsi kasta.
  • Hagaajinta ayaa lagu daray qorsheeeyaha weydiinta si loo wanaajiyo isbarbardhigga su'aalaha oo loo wanaajiyo waxqabadka isku mar ah ee hawlgallada iskaanka diiwaanka ee isdaba jooga ah, fulinta isbarbardhigga su'aalaha PL/pgSQL iyadoo la adeegsanayo amarka "RETURN QUERY", iyo fulinta isbarbar-dhigga su'aalaha ee " Dib u cusboonaysii ARAGTI LAGU SAMEEYEY”. Si kor loogu qaado waxqabadka isku-dhafka buul-buulka ah (ku biir), taageerada kaydinta dheeraadka ah ayaa la hirgeliyay.
  • Tirokoob sare ayaa hadda loo isticmaali karaa si loo wanaajiyo tibaaxaha, kala-soocidda korodhka ayaa hadda loo isticmaali karaa si kor loogu qaado shaqooyinka daaqada.
  • Nidaamyada kaydsan ee kuu oggolaanaya inaad maamusho wax kala iibsiga ee blocks of code hadda waxay taageerayaan qeexida xogta soo celinta iyadoo la adeegsanayo xuduudaha "OUT".
  • Date_bin oo lagu daray qiyamka shaambada wakhtiga iyadoo loo eegayo muddo cayiman. Xulo date_bin ('15 daqiiqo', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP'2001-01-01'); 2020-02-11 15:30:00
  • Lagu daray SEARCH iyo tibaaxaha wareegtada ee lagu qeexay heerka SQL si ay ugu fududaato dalbashada iyo aqoonsiga wareegyada soo noqnoqda ee Tibaaxaha Miiska Caadiga ah (CTE). LEH RECURSIVE search_tree(id, link, data) AS ( SELECT t.id, t.link, t.data from geed t UNION ALL SELECT t.id, t.link, t.data from geed t, search_tree st WHERE t. id = st.link
  • Utility psql, dhamaystirka amarrada leh tabs waa la hagaajiyay, awoodda lagu soo bandhigo doodaha shaqada ayaa lagu daray amarka "\ df", iyo tirakoobyada la soo bandhigay ayaa lagu ballaariyay amarka "\ dX".
  • Waa suurtagal in isticmaalayaasha loo qoondeeyo mudnaanta akhri-kaliya ama qorista-kaliya. Mudnaanta waxaa lagu dejin karaa miisaska gaarka ah, aragtida, iyo schemas iyadoo la adeegsanayo pg_read_all_data iyo pg_write_all_data hore loo qeexay. SII Pg_read_all_data TO user1;
  • Rakibado cusub oo u dhigma aqoonsiga erayga sirta ah ee isticmaalaya SCRAM-SHA-256 halkii ay ka ahaan lahaayeen md5 ("password_encryption" parameter marka la soo saarayo postgresql.conf ayaa hadda loo dejiyay 'scram-sha-256').

Source: opennet.ru

Add a comment