{"id":119954,"date":"2024-09-26T21:43:01","date_gmt":"2024-09-26T19:43:01","guid":{"rendered":"https:\/\/prohoster.info\/blog\/novosti-interneta\/reliz-subd-postgresql-17"},"modified":"2024-09-26T21:43:01","modified_gmt":"2024-09-26T19:43:01","slug":"reliz-subd-postgresql-17","status":"publish","type":"post","link":"https:\/\/prohoster.info\/sq\/blog\/novosti-interneta\/reliz-subd-postgresql-17","title":{"rendered":"L\u00ebshimi i DBMS PostgreSQL 17","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"
Pas nj\u00eb vit zhvillimi, \u00ebsht\u00eb publikuar dega e re e q\u00ebndrueshme e DBMS PostgreSQL 17. P\u00ebrdit\u00ebsimet p\u00ebr deg\u00ebn e re do t\u00eb dalin p\u00ebr pes\u00eb vjet deri n\u00eb n\u00ebntor 2029. Mb\u00ebshtetje p\u00ebr PostgreSQL 12.x, dega m\u00eb e vjet\u00ebr e mb\u00ebshtetur, do t\u00eb p\u00ebrfundoj\u00eb m\u00eb 14 n\u00ebntor. <\/p>\n
T\u00eb rejat kryesore: <\/p>\n
\n
Gjat\u00eb ekzekutimit t\u00eb operacionit VACUUM (mbledhja e mbeturinave, pastrimi dhe paketimi i magazin\u00ebs s\u00eb diskut) \u00ebsht\u00eb angazhuar nj\u00eb struktur\u00eb e re t\u00eb dh\u00ebnash, e cila konsumon deri n\u00eb 20 her\u00eb m\u00eb pak memorie. Kalimi n\u00eb struktur\u00ebn e re ka lejuar p\u00ebrshpejtimin e ekzekutimit t\u00eb operacioneve VACUUM dhe ka reduktuar konsumimin e burimeve t\u00eb p\u00ebrbashk\u00ebta.\n
\u00cbsht\u00eb vazhduar optimizimi i performanc\u00ebs s\u00eb hyrjes\/daljes. Fal\u00eb p\u00ebrmir\u00ebsimit t\u00eb kodit p\u00ebr trajtimin e logut t\u00eb regjistrimit t\u00eb vonuar WAL, \u00ebsht\u00eb arritur t\u00eb dyfishohet kapaciteti i operacioneve t\u00eb regjistrimit n\u00eb sisteme me nj\u00eb num\u00ebr t\u00eb madh t\u00eb k\u00ebrkesave q\u00eb ekzekutohen nj\u00ebkoh\u00ebsisht. \u00cbsht\u00eb propozuar nj\u00eb nd\u00ebrfaqe e re e hyrjes\/daljes n\u00eb rrjedh\u00eb, e cila ka lejuar rritjen e shpejt\u00ebsis\u00eb s\u00eb kalimit t\u00eb t\u00eb dh\u00ebnave n\u00eb tabela dhe ka p\u00ebrshpejtuar azhurnimin e statistikave p\u00ebr planifikuesin e k\u00ebrkesave pas ekzekutimit t\u00eb operacionit ANALYZE.\n
Ka k\u00ebrcyer performanca e k\u00ebrkesave me shprehjen «IN», n\u00ebse ato p\u00ebrdorin indekse t\u00eb bazuara n\u00eb struktur\u00ebn B-tree. \u00cbsht\u00eb realizuar mund\u00ebsia e nd\u00ebrtimit paralel t\u00eb indekseve BRIN (Block Range Index). N\u00eb planifikuesin e k\u00ebrkesave jan\u00eb optimizuar kontrollat «NOT NULL» dhe \u00ebsht\u00eb p\u00ebrshpejtuar p\u00ebrpunimi i shprehjeve t\u00eb p\u00ebrgjithshme t\u00eb tabelave (Common Table Expression, CTE) t\u00eb p\u00ebrcaktuara me komand\u00ebn SQL «WITH». Sigurohet p\u00ebrdorimi i instrukcioneve t\u00eb procesor\u00ebve vektor\u00eb (SIMD) p\u00ebr t\u00eb p\u00ebrshpejtuar disa llogaritje; p\u00ebr shembull, p\u00ebrshpejtimi i funksionit bit_count p\u00ebrdor instrukcione AVX-512.\n
\u00cbsht\u00eb realizuar mb\u00ebshtetje p\u00ebr mund\u00ebsi t\u00eb reja p\u00ebr pun\u00ebn me formatin JSON, t\u00eb p\u00ebrcaktuara n\u00eb standardin SQL\/JSON. \u00cbsht\u00eb shtuar funksioni JSON-TABLE p\u00ebr t\u00eb transformuar t\u00eb dh\u00ebnat nga formati JSON n\u00eb tabela t\u00eb zakonshme PostgreSQL, si dhe operator\u00ebt e rinj jsonpath p\u00ebr t\u00eb transformuar t\u00eb dh\u00ebnat JSON n\u00eb llojet e t\u00eb dh\u00ebnave native p\u00ebr PostgreSQL. Jan\u00eb propozuar funksione t\u00eb reja p\u00ebr krijimin (JSON, JSON_SCALAR dhe JSON_SERIALIZE) dhe k\u00ebrkes\u00ebn (JSON_EXISTS, JSON_QUERY dhe JSON_VALUE) e t\u00eb dh\u00ebnave n\u00eb formatin JSON.\n
Jan\u00eb zgjeruar mund\u00ebsit\u00eb e komand\u00ebs SQL «MERGE», e cila lejon krijimin e shprehjeve SQL kushtore q\u00eb i bashkojn\u00eb operacionet INSERT, UPDATE dhe DELETE n\u00eb nj\u00eb shprehje. \u00cbsht\u00eb shtuar mb\u00ebshtetje p\u00ebr shprehjen RETURNING, e cila lejon llogaritjen dhe kthimin e vlerave n\u00eb baz\u00eb t\u00eb rreshtave t\u00eb shtuar, t\u00eb azhurnuar ose t\u00eb fshir\u00eb. \u00cbsht\u00eb realizuar mb\u00ebshtetje p\u00ebr azhurnimin e pamjeve.\n
\u00cbsht\u00eb zgjeruar kapaciteti p\u00ebr ngarkimin dhe eksportimin e t\u00eb dh\u00ebnave n\u00eb modin e paket\u00ebs. N\u00eb komand\u00ebn COPY, shpejt\u00ebsia e eksportimit t\u00eb rreshtave t\u00eb m\u00ebdhenj \u00ebsht\u00eb dyfishuar, \u00ebsht\u00eb optimizuar kopjimi kur p\u00ebrputhen kodet burimore dhe t\u00eb destinuara, \u00ebsht\u00eb shtuar opsioni ON_ERROR, i cili lejon vazhdimin e importit pas gabimeve gjat\u00eb futjes s\u00eb t\u00eb dh\u00ebnave.\n
P\u00ebr tabelat e ndara \u00ebsht\u00eb shtuar mund\u00ebsia e p\u00ebrdorimit t\u00eb kolonave me identifikues unik\u00eb dhe mb\u00ebshtetje p\u00ebr teknik\u00ebn e optimizimit t\u00eb k\u00ebrkesave «Constraint exclusion».\n
N\u00eb mekanizmin e lidhjes s\u00eb tabelave t\u00eb jashtme Foreign Data Wrapper (postgres_fdw) \u00ebsht\u00eb realizuar mund\u00ebsia e p\u00ebrcjelljes n\u00eb t\u00eb jashtme server<\/a> n\u00ebnk\u00ebrkesat «EXISTS» dhe «IN.\n