ProHoster > Blog > internet nhau > PostgreSQL 14 DBMS kuburitswa

PostgreSQL 14 DBMS kuburitswa

Mushure megore rekuvandudzwa, bazi idzva rakatsiga rePostgreSQL 14 DBMS raburitswa. Zvigadziriso zvebazi idzva zvichaburitswa kwemakore mashanu kusvika Mbudzi 2026.

Zvitsva zvikuru:

  • Yakawedzerwa tsigiro yekuwana data yeJSON uchishandisa array-like mataurirwo: SARUDZA ('{"postgres": { "release": 14 }}'::jsonb)['postgres']['release']; SARUDZA * KUBVA bvunzo PANE mashoko['maitiro']['saizi'] = '"pakati"';

    Syntax yakafanana inoshandiswa kune kiyi / kukosha data inopihwa neiyo hstore mhando. Iyi syntax yakatanga kushandiswa pachishandiswa chimiro chepasi rose, icho mune ramangwana chinogona kushandiswa kune mamwe marudzi. Muenzaniso wemhando yehstore: PINDA MUZVINOKOSHA zvinogoneka ('a=>b, c=>d'); SARUDZA h['a'] KUBVA mutafura yangu; UPDATE mytable SET h['c'] = 'itsva';

  • Mhuri yemhando yekutsanangura mitsara yakawedzerwa nemhando nyowani dze "multirange", izvo zvinokutendera kuti utaure akaodha rondedzero yezvisingapindirane nhanho dzezvikoshi. Pamusoro pemhando yega yega iripo, mhando yayo yemhando dzakasiyana-siyana inokurudzirwa, semuenzaniso, mhando "int4range" inoenderana ne "int4multirange", uye mhando "daterange" inoenderana ne "datemultirange". Kushandiswa kwemhando itsva kunorerutsa dhizaini yemibvunzo inoshandura kutevedzana kwakaoma kwemisara. SARUDZA '{[3,7), [8,9)}'::int4multirange; SARUDZA nhamba yakawanda(nhamba(1.0, 14.0), nhamba(20.0, 25.0));
  • Optimizations dzakaitwa kuvandudza mashandiro emhando yepamusoro-soro masisitimu anogadzirisa nhamba huru yekubatanidza. Mune mamwe maedzo, kuwedzera kaviri kwekuita kunoonekwa.
  • Kubudirira kweB-muti indexes kwakagadziridzwa uye dambudziko nekukura kwe index kana matafura achiwanzo gadziridzwa akagadziriswa.
  • Yakawedzerwa rutsigiro rwemutengi-parutivi (inoitwa padanho re libpq) nzira yekufambiswa kwepombi yezvikumbiro, iyo inokutendera iwe kuti ikurumidze zvakanyanya dhatabhesi zviitiko zvine chekuita nekuita nhamba huru yezvidiki zvinyorwa (INSERT / UPDATE / DELETE) nekutumira iyo chikumbiro chinotevera pasina kumirira mhedzisiro yeyapfuura. Iyo modhi inobatsirawo kukurumidzira basa pakubatanidza nekunonoka kwekutakura kwepaketi.
  • Kuvandudzwa kwehunyanzvi hwezvirongwa zvakagoverwa zvinosanganisira akawanda PostgreSQL maseva. Mukuita kwekudzokorodza zvine musoro, zvino zvinogoneka kutumira mukutenderera modhi kutengeserana kuri mukuita kwekuita, izvo zvinogona kuvandudza zvakanyanya mashandiro ekudzokorora kwekutengeserana kukuru. Pamusoro pezvo, iyo inonzwisisika decoding yedata yakagamuchirwa panguva yekudzokorora zvine musoro yakagadziridzwa.
  • Iyo nzira yekubatanidza matafura ekunze Kunze Data Wrapper (postgres_fdw) yakawedzera tsigiro yekuenderana nemubvunzo kugadzirisa, iyo parizvino inoshanda chete kana ichibatanidza kune mamwe maseva ePostgreSQL. postgres_fdw inowedzerawo rutsigiro rwekuwedzera data kumatafura ekunze mubatch modhi uye kugona kupinza matafura akapatsanurwa nekutsanangura iyo "IMPORT FOREIGN SCHEMA" kuraira.
  • Kugadziridzwa kwakaitwa pakuitwa kweVACUUM kushanda (kuunganidza marara uye kurongedza kwedhisiki yekuchengetedza). Iyo yekukurumidzira yekuchenesa modhi yakawedzerwa iyo inosvetuka-asina kukosha ekuchenesa mashandiro kana transaction ID wraparound mamiriro akagadzirwa. Yakaderedzwa pamusoro paunenge uchigadzira indexes muB-Tree fomati. Kuitwa kwe "ANALYZE" kushanda, iyo inounganidza nhamba pamusoro pekushanda kwe database, yakakurumidza kukurumidza.
  • Yakawedzera kugona kugadzirisa nzira yekumanikidza inoshandiswa muTOAST system, iyo ine basa rekuchengetedza data hombe, senge mabhuroko ezvinyorwa kana ruzivo rwejometri. Pamusoro peiyo pglz yekumanikidza nzira, TOAST ikozvino inogona kushandisa iyo LZ4 algorithm.
  • Zvishandiso zvekutarisa kushanda kweDBMS zvakawedzerwa. Maonero akawedzerwa ekutevera kufambira mberi kwemirairo yeCOPY (pg_stat_progress_copy), nhamba nezve replication slots (pg_stat_replication_slots) uye chiitiko chine chekuita neWAL transaction log (pg_stat_wal). Iyo compute_query_id basa rawedzerwa, iro rinogonesa masisitimu akasiyana siyana, senge pg_stat_activity uye TSANANGURA VERBOSE, kuronda zvikumbiro nekupa chiziviso chakasiyana pachikumbiro chega chega.
  • Optimizations yakawedzerwa kune query planner kuti ivandudze parallel processing yemibvunzo uye kuvandudza mashandiro ekuita panguva imwe chete yekuteedzana rekodhi scan mashandiro, kuenzanirana kwemibvunzo muPL/pgSQL uchishandisa iyo "RETURN QUERY" murairo, uye kuenderana kwekuita kwemibvunzo mu " REFRESSHA MATERIALIZED VIEW”. Kuvandudza mashandiro e cyclic nested merges (join), rutsigiro rwekuwedzera caching rwakaitwa.
  • Nhamba dzepamberi dzinogona kushandiswa kukwenenzvera matauriro, uye kuwedzera kurongedza kwave kugona kushandiswa kukwenenzvera mabasa emahwindo.
  • Matanho akachengetwa anokutendera kuti utore kutengeserana mumabhuraki ekodhi ikozvino inotsigira kutsanangura yekudzoka data uchishandisa "OUT" paramita.
  • Yakawedzera date_bin basa kune yakatenderedza timestamp kukosha zvinoenderana nenguva yakatarwa. SARUDZA date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-01-01'); 2020-02-11 15:30:00
  • Yakawedzerwa SEARCH uye CYCLE mataurirwo anotsanangurwa muSQL chiyero kuita kuti zvive nyore kuodha uye kuona matenderedzwa mune inodzokororwa Common Table Expressions (CTE). NERECURSIVE search_tree(id, link, data) AS (SARUDZA t.id, t.link, t.data KUBVA mumuti t UNION ZVOSE SARUDZA t.id, t.link, t.data KUBVA mumuti t, tsvaga_muti st PAPI t. id = st.link ) TSVAGA ZVAKAdzama PAKUTANGA NE id SET ordercol SARUDZA * KUBVA search_tree ORDER BY ordercol;
  • Muchishandiso che psql, kugadziriswa kwemirairo nematabo kwakagadziridzwa, kukwanisa kuratidza nharo dzekushanda kwakawedzerwa kune "\ df" murairo, uye nhamba dzakaratidzwa dzakawedzerwa kune "\ dX" murairo.
  • Zvinogoneka kupa ropafadzo dzekuverenga chete kana kunyora-chete kune vashandisi. Ropafadzo dzinogona kuiswa pamatafura ega ega, maonero, uye schemas uchishandisa iyo pg_read_all_data uye pg_write_all_data predefined mabasa. GRANT pg_read_all_data KUTI mushandisi1;
  • Kumisikidzwa kutsva kunoenderana nekusimbisa password uchishandisa SCRAM-SHA-256 pachinzvimbo chemd5 (iyo "password_encryption" parameter paunenge uchigadzira postgresql.conf ikozvino yakaiswa ku'scram-sha-256').

Source: opennet.ru

Voeg