ProHoster > Blog > talafou initaneti > PostgreSQL 14 faʻamalolo

PostgreSQL 14 faʻamalolo

I le mae'a ai o se tausaga o atina'e, ua fa'asalalauina se lala fou o le PostgreSQL 14 DBMS. O fa'afouga mo le lala fou o le a fa'asalalauina i le lima tausaga se'ia o'o ia Novema 2026.

Autu fa'afouga:

  • Fa'aopoopoina le lagolago mo le mauaina o fa'amaumauga a le JSON e fa'aaoga ai fa'amatalaga e pei o le array: SELECT ('{ "postgres": { "release": 14 }}'::jsonb)['postgres']['release']; FILIFILI * MAI su'ega WHERE fa'amatalaga['uiga']['size'] = '"medium"';

    O se fa'atusa fa'atusa e fa'atinoina mo fa'amaumauga autu/taua na tu'uina mai e le ituaiga hstore. O lenei syntax na muamua faʻatinoina e faʻaaoga ai se faʻavae lautele, lea e mafai ona faʻaaogaina i le lumanaʻi mo isi ituaiga. Fa'ata'ita'iga mo le ituaiga faleteuoloa: INSERT INTO mytable VALUES ('a => b, c => d'); FILIFILI h['a'] MAI mytable; FA'AVAE mytable SET h['c'] = 'fou';

  • O le aiga o ituaiga mo le faʻamalamalamaina o laina ua faʻalauteleina i ituaiga fou "televaega", lea e mafai ai ona e faʻamaonia lisi faʻatonuina o laina e le o faʻapipiʻiina o tau. I le faaopoopo atu i ituaiga taʻitasi o loʻo i ai nei, o lona lava ituaiga multirange ua faʻatulagaina, mo se faʻataʻitaʻiga, o le ituaiga "int4range" e fetaui ma le "int4multirange", ma le ituaiga "daterange" e fetaui ma le "datemultirange". O le fa'aogaina o ituaiga fou e fa'afaigofie ai le mamanu o fesili e fa'aogaina ai fa'asologa lavelave o laina. FILIFILI '{[3,7), [8,9)}'::int4multirange; FILIFILI nummultirange(numrange(1.0, 14.0), numrange(20.0, 25.0));
  • O fa'atonuga ua faia e fa'aleleia atili ai le fa'atinoga o faiga fa'apipi'i maualuga o lo'o fa'agaoioia le tele o feso'ota'iga. I nisi o suʻega, e faaluaina le faʻatupulaia o le faʻatinoga o loʻo matauina.
  • Ua fa'aleleia atili le lelei o fa'asinomaga B-laau ma ua fo'ia le fa'afitauli i le fa'atupulaia o fa'asinomaga pe a fa'afou so'o laulau.
  • Faʻaopoopoina le lagolago mo le itu o tagata faʻatau (faʻatinoina i le libpq level) auala o le tuʻuina atu o talosaga, lea e mafai ai ona e faʻavavevaveina faʻamatalaga faʻamaumauga e fesoʻotaʻi ma le faia o se numera tele o galuega tusitusi laiti (INSERT/UPDATE/DELETE) e ala i le lafoina o le le isi talosaga e aunoa ma le faatali i le taunuuga o le muamua. E fesoasoani fo'i le faiga fa'avavevave galuega i feso'ota'iga fa'atasi ma fa'atuai umi o le tu'uina atu o pepa.
  • Faʻaleleia le gafatia mo faʻasalalauga tufatufaina e aofia ai le tele o sapalai PostgreSQL. I le faʻatinoga o faʻataʻitaʻiga talafeagai, ua mafai nei ona tuʻuina atu i le faʻafefeina o faiga fefaʻatauaʻiga o loʻo i ai i le faagasologa o le faʻataunuʻuina, lea e mafai ona faʻaleleia atili ai le faʻatinoina o le toe faia o fefaʻatauaiga tetele. E le gata i lea, o le fa'avasegaina talafeagai o fa'amaumauga na maua i le taimi o le toe faia fa'atatau ua fa'asilisiliina.
  • O le masini mo le faʻafesoʻotaʻi o laulau i fafo Foreign Data Wrapper (postgres_fdw) ua faʻaopoopoina le lagolago mo le faʻaogaina o fesili tutusa, lea e naʻo le faʻaaogaina pe a fesoʻotaʻi i isi PostgreSQL servers. postgres_fdw fa'aopoopoina fo'i le lagolago mo le fa'aopoopoina o fa'amatalaga i laulau i fafo i le faiga fa'aputuga ma le mafai ona fa'aulufale mai laulau vaelua e ala i le fa'ama'otiina o le fa'atonuga o le "FA'AMATALAGA MAI FAIFO".
  • Ua faia ni fa'alelei i le fa'atinoga o le VACUUM fa'agaioiga (aoina o lapisi ma le afifiina o le teuina o tisiki). Ua fa'aopoopoina se faiga fa'amama fa'alavelave fa'afuase'i e fa'ase'e ai galuega fa'amama e le'o mana'omia pe a fa'atupu tulaga fa'apipi'i ID fa'atau. Fa'aiti'itia luga a'o fa'agasolo fa'asino igoa ile fa'asologa o B-Tree. O le faʻatinoina o le faʻatinoga o le "ANALYZE", lea e aoina ai faʻamaumauga e uiga i le faʻaogaina o faʻamaumauga, ua matua faʻavavevaveina.
  • Faʻaopoopo le gafatia e faʻapipiʻi le auala faʻapipiʻi faʻaaogaina i le TOAST system, lea e nafa ma le teuina o faʻamatalaga tetele, e pei o poloka o tusitusiga poʻo faʻamatalaga geometric. I le faaopoopo atu i le pglz compression method, TOAST e mafai nei ona faʻaogaina le LZ4 algorithm.
  • O meafaigaluega mo le mataʻituina o le faʻaogaina o le DBMS ua faʻalauteleina. Fa'aopoopo manatu e siaki ai le aga'i i luma o fa'atonuga COPY (pg_stat_progress_copy), fa'amaumauga e uiga i avanoa toe faia (pg_stat_replication_slots) ma gaioiga e feso'ota'i ma le WAL transaction log (pg_stat_wal). Ua fa'aopoopo le galuega compute_query_id, lea e mafai ai e subsystems eseese, e pei o le pg_stat_activity ma FA'AMATALAGA VERBOSE, e siaki ai talosaga e ala i le tu'uina atu o se fa'amatalaga tulaga ese mo talosaga ta'itasi.
  • Ua fa'aopoopoina fa'atonuga ile fuafuaga ole fesili e fa'aleleia ai le fa'agasologa tutusa o fesili ma fa'aleleia atili le fa'atinoga o le fa'atinoina o fa'asologa fa'asologa o fa'amaumauga fa'asologa o fa'amaumauga, fa'atinoina tutusa o fesili ile PL/pgSQL e fa'aaoga ai le "RETURN QUERY" poloaiga, ma le fa'atinoina tutusa o fesili ile " TOE FA'AVAE VA'A'IGA MATERIALIZED". Ina ia fa'aleleia atili le fa'atinoga o fa'aputuga fa'ata'amilomilo (so'o'i), ua fa'atinoina le lagolago mo fa'aopoopo fa'apipi'i.
  • Ua mafai nei ona fa'aoga fa'amaumauga maualuluga e fa'asili ai fa'amatalaga, ma ua mafai nei ona fa'aoga fa'aopoopo fa'aopoopo e fa'asili ai galuega fa'amalama.
  • O faiga fa'apolopolo e fa'ataga ai oe e pulea fefa'ataua'iga i poloka o fa'ailoga ua lagolagoina nei le fa'amalamalamaina o fa'amaumauga o fa'amatalaga e fa'aaoga ai "OUT" parakalafa.
  • Fa'aopoopo le date_bin galuega e fa'ata'amilo fa'ailoga taimi e tusa ai ma se va'aiga fa'apitoa. SELECT date_bin('15 minute', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-01-01'); 2020-02-11 15:30:00
  • Fa'aopoopo le SEARCH ma le CYCLE fa'amatalaga fa'amatalaina i le SQL standard e fa'afaigofie ai ona fa'atonu ma fa'ailoa ta'amilosaga i Fa'amatalaga Fa'amatalaga masani masani (CTE). IA RECURSIVE search_tree(id, link, data) AS ( SELECT t.id, t.link, t.data FROM tree t UNION ALL SELECT t.id, t.link, t.data FROM tree t, search_tree st WHERE t. id = st.link ) SEARCH DEPTH MUAMUA BY id SET ordercol FILIFILI * MAI search_tree ORDER BY ordercol;
  • I le aoga psql, o le autocompletion of commands with tabs ua faʻaleleia, o le mafai ona faʻaalia finauga o galuega ua faʻaopoopoina i le "\df" poloaiga, ma o fuainumera faʻaalia ua faʻalauteleina i le "\dX" poloaiga.
  • E mafai ona tu'uina atu na'o le faitau pe na'o le tusitusi i tagata fa'aoga. E mafai ona fa'atulaga avanoa i luga o laulau ta'itasi, va'aiga, ma fa'ata'ita'iga e fa'aaoga ai le pg_read_all_data ma pg_write_all_data matafaioi ua uma ona fa'avasegaina. FAATUATUA pg_read_all_data TO user1;
  • O mea fou fa'apipi'i e le mafai ona fa'amaonia upu fa'amaonia e fa'aaoga ai le SCRAM-SHA-256 nai lo le md5 (o le "password_encryption" parakalafa pe a fatuina postgresql.conf ua seti nei i le 'scram-sha-256').

puna: opennet.ru

Faaopoopo i ai se faamatalaga