ProHoster > Π‘Π»ΠΎΠ³ > xov xwm hauv internet > PostgreSQL 14 DBMS tso tawm

PostgreSQL 14 DBMS tso tawm

Tom qab ib xyoos ntawm kev txhim kho, ib ceg ruaj khov tshiab ntawm PostgreSQL 14 DBMS tau tshaj tawm. Kev hloov tshiab rau ceg tshiab yuav raug tso tawm ntau tshaj tsib xyoos txog rau lub Kaum Ib Hlis 2026.

Main innovations:

  • Ntxiv kev txhawb nqa rau kev nkag mus rau JSON cov ntaub ntawv siv array-zoo li kab lus: SELECT ('{ "postgres": { "tso": 14 }}'::jsonb)['postgres']['release']; SELECT * LOS NTAWM qhov xeem qhov twg cov ntsiab lus['tus cwj pwm']['size'] = '" nruab nrab"';

    Ib qho syntax zoo sib xws yog siv rau cov ntaub ntawv tseem ceeb / tus nqi muab los ntawm hom hstore. Cov syntax no tau pib siv los siv lub hauv paus ntsiab lus, uas yav tom ntej yuav raug siv rau lwm hom. Piv txwv rau hom hstore: INSERT INTO mytable VALUES ('a=>b, c=>d'); SELECT h['a'] Los ntawm mytable; UPDATE mytable SET h['c'] = 'new';

  • Tsev neeg ntawm hom kev txhais cov kab lus tau nthuav dav nrog cov "ntau yam" tshiab, uas tso cai rau koj los qhia cov npe ntawm cov tsis sib tshooj ntawm qhov muaj nuj nqis. Ntxiv rau txhua hom ntau yam uas twb muaj lawm, nws tus kheej ntau hom tau npaj tseg, piv txwv li, hom "int4range" sib raug rau "int4multirange", thiab hom "daterange" sib raug rau "datemultirange". Kev siv hom tshiab ua kom yooj yim rau kev tsim cov lus nug uas tswj cov txheej txheem nyuaj ntawm ntau yam. SELECT '{[3,7), [8,9)}'::int4multirange; SELECT nummultirange(numrange(1.0, 14.0), numrange(20.0, 25.0));
  • Optimizations tau tsim los txhim kho kev ua tau zoo ntawm cov tshuab ua haujlwm siab ua haujlwm ntau ntawm kev sib txuas. Hauv qee qhov kev xeem, kev ua haujlwm tau nce ob npaug.
  • Kev ua tau zoo ntawm B-ntoo indexes tau raug txhim kho thiab qhov teeb meem nrog kev nce qib thaum cov ntxhuav tau hloov kho tshiab tau raug daws.
  • Ntxiv kev txhawb nqa rau cov neeg siv khoom-sab (ua tiav ntawm qib libpq) hom kev xa cov raj xa dej ntawm kev thov, uas tso cai rau koj kom ceev cov ntaub ntawv xwm txheej cuam tshuam nrog kev ua haujlwm ntau ntawm cov ntawv sau me me (INSERT / HLOOV / DELETE) los ntawm kev xa cov kev thov tom ntej yam tsis tau tos qhov tshwm sim ntawm qhov dhau los. Hom kuj tseem pab ua kom nrawm ua haujlwm ntawm kev sib txuas nrog cov pob ntawv xa mus ntev.
  • Txhim kho lub peev xwm rau kev faib cov teeb tsa uas cuam tshuam nrog ntau lub PostgreSQL servers. Nyob rau hauv qhov kev siv ntawm cov logical replication, nws tam sim no ua tau rau xa nyob rau hauv streaming hom kev lag luam uas nyob rau hauv tus txheej txheem ntawm kev ua, uas yuav ua tau zoo txhim kho cov kev ua tau zoo ntawm replication ntawm loj muas. Tsis tas li ntawd, lub logical decoding ntawm cov ntaub ntawv tau txais thaum lub sij hawm logical replication tau optimized.
  • Cov txheej txheem rau kev sib txuas cov rooj sab nraud Txawv Teb Chaws Cov Ntaub Ntawv Wrapper (postgres_fdw) tau ntxiv kev txhawb nqa rau kev sib txuas lus nug, uas tam sim no tsuas yog siv tau thaum txuas rau lwm lub PostgreSQL servers. postgres_fdw kuj tseem ntxiv kev txhawb nqa ntxiv rau kev ntxiv cov ntaub ntawv rau cov rooj sab nraud hauv batch hom thiab lub peev xwm los import cov lus sib faib los ntawm kev qhia txog "IMPORT FOREIGN SCHEMA" cov lus qhia.
  • Kev ua kom zoo tshaj plaws tau ua rau kev ua haujlwm ntawm VACUUM (kev sau cov khib nyiab thiab ntim cov disk cia). Ib hom kev ntxuav thaum muaj xwm txheej ceev tau ntxiv uas hla kev ua haujlwm tsis tseem ceeb yog tias kev hloov pauv ID wraparound cov xwm txheej raug tsim. Txo cov nyiaj siv ua haujlwm thaum ua cov indexes hauv B-Tree hom. Kev ua tiav ntawm "ANALYZE" kev ua haujlwm, uas sau cov txheeb cais txog kev ua haujlwm ntawm cov ntaub ntawv, tau nrawm nrawm heev.
  • Ntxiv lub peev xwm los teeb tsa txoj kev compression siv hauv TOAST system, uas yog lub luag haujlwm rau khaws cov ntaub ntawv loj, xws li cov ntawv thaiv cov ntawv lossis cov ntaub ntawv geometric. Ntxiv rau pglz compression txoj kev, TOAST tam sim no tuaj yeem siv LZ4 algorithm.
  • Cov cuab yeej saib xyuas kev ua haujlwm ntawm DBMS tau nthuav dav. Ntxiv cov kev xav los taug qab qhov kev nce qib ntawm COPY cov lus txib (pg_stat_progress_copy), kev txheeb cais txog qhov sib txuas (pg_stat_replication_slots) thiab kev ua ub no cuam tshuam nrog WAL kev sib pauv log (pg_stat_wal). Lub compute_query_id muaj nuj nqi tau ntxiv, uas ua rau muaj ntau yam subsystems, xws li pg_stat_activity thiab EXPLAIN VERBOSE, txhawm rau taug qab kev thov los ntawm kev muab tus cim tshwj xeeb rau txhua qhov kev thov.
  • Optimizations tau ntxiv rau cov lus nug npaj los txhim kho kev sib txuas ua ke ntawm cov lus nug thiab txhim kho kev ua tau zoo ntawm kev ua tiav ib txhij ntawm kev ua tiav cov ntaub ntawv tshawb xyuas, sib npaug ntawm cov lus nug hauv PL / pgSQL siv "RETURN QUERY" hais kom ua, thiab ua tib zoo ua raws cov lus nug hauv " REFRESH MATERIALIZED VIEW ". Txhawm rau txhim kho kev ua haujlwm ntawm cyclic nested merges (sib koom), kev txhawb nqa ntxiv caching tau ua tiav.
  • Cov kev txheeb cais qib siab tam sim no tuaj yeem siv los ua kom zoo dua cov kab lus, thiab kev sib ntxiv ntxiv tam sim no tuaj yeem siv los ua kom zoo dua qhov rais ua haujlwm.
  • Cov txheej txheem khaws cia uas tso cai rau koj los tswj kev lag luam hauv cov blocks ntawm cov cai tam sim no txhawb kev txhais cov ntaub ntawv xa rov qab siv "OUT" tsis.
  • Ntxiv date_bin muaj nuj nqi rau round timestamp qhov tseem ceeb raws li lub sijhawm teev tseg. SELECT date_bin('15 feeb', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-01-01'); 2020-02-11 15:30:00 TSΔ°
  • Ntxiv SEARCH thiab CYCLE cov kab lus uas tau teev tseg hauv SQL tus qauv kom nws yooj yim rau kev txiav txim thiab txheeb xyuas cov voj voog hauv recursive Common Table Expressions (CTE). NROG RECURSIVE search_tree(id, link, data) AS ( SELECT t.id, t.link, t.data NTAWM tsob ntoo t UNION TAG NRHO SELECT t.id, t.link, t.data NTAWM tsob ntoo t, search_tree st WHERE t. id = st.link ) SEARCH DEPTH UA NTEJ LOS NTAWM id SET ordercol SELECT * NTAWM search_tree ORDER BY ordercol;
  • Hauv psql cov nqi hluav taws xob, kev ua tiav ntawm cov lus txib nrog cov tabs tau txhim kho, muaj peev xwm los tso saib cov lus sib cav tau ntxiv rau "\ df" hais kom ua, thiab cov ntawv txheeb cais tau nthuav dav rau "\ dX" hais kom ua.
  • Nws muaj peev xwm muab cov cai nyeem nkaus xwb lossis sau nkaus xwb rau cov neeg siv. Cov cai muaj peev xwm raug teeb tsa rau ntawm tus kheej cov lus, saib, thiab schemas siv pg_read_all_data thiab pg_write_all_data lub luag haujlwm ua ntej. GRANT pg_read_all_data TO user1;
  • Kev teeb tsa tshiab ua ntej rau kev lees paub tus password siv SCRAM-SHA-256 es tsis txhob md5 (tus "password_encryption" parameter thaum tsim postgresql.conf tam sim no tau teeb tsa rau 'scram-sha-256').

Tau qhov twg los: opennet.ru

Ntxiv ib saib