ProHoster > Блог > ozi ịntanetị > Mwepụta PostgreSQL 14 DBMS

Mwepụta PostgreSQL 14 DBMS

Ka otu afọ nke mmepe gachara, e bipụtala alaka ọhụrụ kwụsiri ike nke PostgreSQL 14 DBMS. A ga-ewepụta mmelite maka alaka ọhụrụ ahụ n'ime afọ ise ruo Nọvemba 2026.

Isi ihe ọhụrụ:

  • Nkwado agbakwunyere maka ịnweta data JSON site na iji okwu n'usoro: Họrọ ('{"postgres": {"mhapụ": 14}}'::jsonb)['postgres']['nhapụ']; Họrọ * SITE n'ule ebe nkọwa['àgwà']['size'] = "ọkara"';

    A na-emejuputa syntax yiri nke ahụ maka data igodo/uru nke ụdị hstore nyere. Emebere syntax a na mbido site na iji usoro zuru ụwa ọnụ, nke enwere ike iji n'ọdịniihu maka ụdị ndị ọzọ. Ọmụmaatụ maka ụdị hstore: Tinye INTO mytable VALUES ('a=>b, c=>d'); Họrọ h['a'] SITE mytable; Melite mytable SET h['c'] = 'ọhụrụ';

  • Ezinụlọ nke ụdị maka ịkọwapụta ọnụọgụ ejirila ụdị “multirange” ọhụrụ gbasaa, nke na-enye gị ohere ịkọwapụta ndepụta ahaziri ahazi nke ụkpụrụ ụkpụrụ na-abụghị nke na-agafe agafe. Na mgbakwunye na ụdị nso nke ọ bụla dị, a na-atụ aro ụdị multirange nke ya, dịka ọmụmaatụ, ụdị "int4range" dabara na "int4multirange", na ụdị "daterange" kwekọrọ na "datemultirange". Ojiji nke ụdị ọhụrụ na-eme ka nhazi nke ajụjụ dị mfe nke na-emegharị usoro mgbagwoju anya nke oke. Họrọ '{[3,7), [8,9)}'::int4multirange; Họrọ ọnụọgụgụ (nọmba (1.0, 14.0), ọnụọgụ (20.0, 25.0));
  • Emeela ihe kachasị mma iji melite arụmọrụ nke sistemu ibu dị elu na-edozi ọnụ ọgụgụ dị ukwuu nke njikọ. N'ime ule ụfọdụ, a na-ahụ mmụba okpukpu abụọ na arụmọrụ.
  • A na-emeziwanye arụmọrụ nke ndepụta B-osisi yana nsogbu na-eto eto mgbe a na-emelite tebụl ugboro ugboro ka edoziwo ya.
  • Nkwado agbakwunyere maka akụkụ ndị ahịa (nke etinyere na ọkwa libpq) nke nnyefe pipeline nke arịrịọ, nke na-enye gị ohere ime ka ọnọdụ nchekwa data dị ngwa ngwa nke jikọtara ọnụ na ịrụ nnukwu ọrụ ide ihe (INSERT/UPDATE/Hichapụ) site na izipu arịrịọ ọzọ na-echeghị nsonaazụ nke gara aga. Ụdị ahụ na-enyekwa aka mee ka ọrụ dị ngwa na njikọ nwere ogologo oge nnyefe nke ngwugwu.
  • Ikike emelitere maka nhazi ekesa nke gụnyere ọtụtụ sava PostgreSQL. Na mmejuputa atumatu ezi uche, ọ na-aghọ ugbu a na-eziga na gụgharia mode azụmahịa nke na-na usoro nke igbu, nke nwere ike budata melite arụmọrụ nke replication nke nnukwu azụmahịa. Na mgbakwunye, emeziwanye ngbanwe ezi uche dị na ya nke data enwetara n'oge mmegharị ezi uche.
  • Usoro maka ijikọ tebụl mpụga mpụga Data Wrapper (postgres_fdw) agbakwunyela nkwado maka nhazi ajụjụ ọnụ, nke dị ugbu a naanị mgbe ejikọtara na sava PostgreSQL ndị ọzọ. postgres_fdw na-agbakwụnyekwa nkwado maka ịgbakwunye data na tebụl dị n'èzí na ọnọdụ batch yana ike ibubata tebụl ndị kewara ekewa site na ịkọwa ntụziaka "IMPORT FOREIGN SCHEMA".
  • Emebela nkwalite maka mmejuputa ọrụ VACUUM (nchịkọta mkpofu na nkwakọba nchekwa diski). Agbakwunyela ụdị nchacha ihe mberede nke na-awụpụ ọrụ nhicha na-adịghị mkpa ma ọ bụrụ na emepụtara ọnọdụ njigide ID azụmahịa. Ebelata n'elu mgbe ị na-ahazi ndeksi n'ụdị B-Osisi. Ogbugbu nke ọrụ "ANALYZE", nke na-anakọta ọnụ ọgụgụ banyere ọrụ nke nchekwa data, agbagoro ngwa ngwa.
  • Agbakwunyere ikike ịhazi usoro mkpakọ ejiri na sistemụ TOAST, nke na-ahụ maka ịchekwa nnukwu data, dị ka ngọngọ nke ederede ma ọ bụ ozi geometric. Na mgbakwunye na usoro mkpakọ pglz, TOAST nwere ike iji LZ4 algọridim ugbu a.
  • A gbasaala ngwa ọrụ maka nlekota oru nke DBMS. Echiche agbakwunyere iji soro ọganihu nke iwu COPY (pg_stat_progress_copy), ọnụ ọgụgụ gbasara oghere mmeghari (pg_stat_replication_slots) yana ọrụ metụtara ndekọ azụmahịa WAL (pg_stat_wal). Agbakwunyela ọrụ compute_query_id, nke na-enyere sistemu dị iche iche aka, dị ka pg_stat_activity na EXPLAIN VERBOSE, iji soro arịrịọ site n'inye ihe nchọpụta pụrụ iche maka arịrịọ ọ bụla.
  • agbakwunyere nkwalite na onye na-eme atụmatụ ajụjụ iji melite nhazi nke ajụjụ na imeziwanye arụmọrụ nke mmezu nke ọrụ nyocha usoro ihe omume, mmezu nke ajụjụ na PL/pgSQL site na iji iwu "Return QUERY", yana mmezu nke ajụjụ na " IHE NDỊ KWESỊRỊ IHE”. Iji kwalite arụmọrụ nke njikọta ọnụ cyclic (njikọ), emejuputala nkwado maka ndochi ọzọ.
  • Enwere ike iji ọnụ ọgụgụ dị elu ugbu a iji kwalite okwu, yana enwere ike iji nhazi agbakwunyere ugbu a iji kwalite ọrụ windo.
  • Usoro echekwara nke na-enye gị ohere ijikwa azụmahịa na ngọngọ koodu ugbu a na-akwado ịkọwa data nloghachi site na iji paramita "OUT".
  • Agbakwunyere ọrụ date_bin na akara timestamp gburugburu dịka oge a kapịrị ọnụ. Họrọ date_bin('nkeji 15', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP'2001-01-01'); 2020-02-11 15:30:00
  • agbakwunyere SEARCH na okwu CYCLE akọwapụtara na ọkọlọtọ SQL iji mee ka ọ dị mfe ịtụ na ịchọpụta okirikiri na nkwuwa okwu tebụl nkịtị (CTE). NA RECURSIVE search_tree(id, njikọ, data) AS (Họrọ t.id, t.link, t.data SITE n'osisi t UNION ALL Họrọ t.id, t.link, t.data SITE n'osisi t, search_tree st WHERE t. id = st.link ) Chọọ Omimi nke Mbụ BY id SET ordercol Họrọ * SITE search_osisi OKWU SITE n'usoro;
  • N'ime ngwa psql, emeziwanye iwu nke iwu na taabụ, ike igosipụta arụmụka ọrụ agbakwunyere na iwu "\ df", na ọnụ ọgụgụ egosiri agbasawanye na iwu "\ dX".
  • Ọ ga-ekwe omume ịnye ndị ọrụ ohere naanị ịgụ ma ọ bụ ide naanị. Enwere ike ịtọ ihe ùgwù na tebụl, nlele, na schemas site na iji pg_read_all_data na pg_write_all_data akọwapụtara. KWESỊRỊ pg_read_all_data TO onye ọrụ1;
  • Nrụnye ọhụrụ na-adabere na njirimara paswọọdụ site na iji SCRAM-SHA-256 kama md5 (nkeji "password_encryption" mgbe ị na-emepụta postgresql.conf ka edobere 'scram-sha-256').

isi: opennet.ru

Tinye a comment