Kamora selemo sa nts'etsopele, ho hatisitsoe lekala le lecha le tsitsitseng la PostgreSQL 14 DBMS. Lintlafatso tsa lekala le lecha li tla lokolloa nakong ea lilemo tse hlano ho fihlela ka Pulungoana 2026.
Litlhahiso tse ka sehloohong:
- Ts'ehetso e ekelitsoeng bakeng sa ho fihlella lintlha tsa JSON ho sebelisoa mantsoe a tšoanang le a mofuta: KHETHA ('{"postgres": { "release": 14 }}'::jsonb)['postgres']['release']; KHETHA * HO TSOA tekong MOO lintlha['litšobotsi']['size'] = '"bohareng"';
Syntax e ts'oanang e kengoa ts'ebetsong bakeng sa lintlha tsa bohlokoa / tsa boleng tse fanoeng ke mofuta oa hstore. Syntax ena qalong e ile ea kenngoa ts'ebetsong ho sebelisoa moralo oa bokahohle, oo nakong e tlang o ka sebelisoang bakeng sa mefuta e meng. Mohlala bakeng sa mofuta oa hstore: KENYA HO mytable VALUES ('a=>b, c=>d'); KHETHA h['a'] HO TSOA ho mytable; Ntlafatsa mytable SET h['c'] = 'ncha';
- Lelapa la mefuta bakeng sa ho hlalosa mabala le atolositsoe ka mefuta e mecha ea "multirange", e leng se u lumellang ho hlakisa manane a laetsoeng a mefuta e sa feteng ea boleng. Ntle le mofuta o mong le o mong o teng oa mefuta e mengata, ho hlahisoa mofuta oa eona oa mefuta e mengata, mohlala, mofuta oa "int4range" o tsamaellana le "int4multirange", 'me mofuta oa "daterange" o tsamaellana le "datemultirange". Tšebeliso ea mefuta e mecha e nolofatsa moralo oa lipotso tse laolang tatellano e rarahaneng ea mefuta. KHETHA '{[3,7), [8,9)}'::int4multirange; KHETHA nummultirange(palo(1.0, 14.0), palo(20.0, 25.0));
- Lintlafatso li entsoe ho ntlafatsa ts'ebetso ea litsamaiso tse boima haholo tse sebetsanang le likhokahano tse ngata. Litekong tse ling, keketseho ea ts'ebetso e habeli e bonoa.
- Ho sebetsa hantle ha li-index tsa B-tree ho ntlafalitsoe mme bothata ba kholo ea index ha litafole li nchafatsoa khafetsa bo rarollotsoe.
- Ts'ehetso e ekelitsoeng bakeng sa lehlakore la bareki (e kentsoeng boemong ba libpq) ea phetisetso ea lipeipi tsa likopo, e u lumellang ho potlakisa haholo maemo a database a amanang le ho etsa palo e kholo ea ts'ebetso e nyane ea ho ngola (INSERT / UPDATE / DELETE) ka ho romella kopo e latelang ntle le ho emela sephetho sa e fetileng. Mokhoa o boetse o thusa ho potlakisa mosebetsi mabapi le likhokahano tse nang le tieho e telele ea ho fana ka lipakete.
- Matla a ntlafetseng bakeng sa litlhophiso tse ajoang tse kenyelletsang li-server tse ngata tsa PostgreSQL. Ts'ebetsong ea ho pheta-pheta ka mokhoa o utloahalang hona joale ho na le bokhoni ba ho romela litšebelisano tse ntseng li tsoela pele ka mokhoa oa ho phallela, o ka ntlafatsang haholo ts'ebetso ea ho pheta-pheta ha litšebelisano tse kholo. Ho feta moo, ho lokisoa ka mokhoa o utloahalang oa data e amohetsoeng nakong ea ho pheta-pheta ka mokhoa o utloahalang e ntlafalitsoe.
- Mochine oa ho hokahanya litafole tsa kantle ho naha ea kantle ho naha Wrapper (postgres_fdw) e ekelitse ts'ehetso bakeng sa ts'ebetso ea lipotso tse ts'oanang, e sebetsang feela ha o hokela ho li-server tse ling tsa PostgreSQL. postgres_fdw e boetse e eketsa ts'ehetso ea ho kenyelletsa data ho litafole tsa kantle ka mokhoa oa batch le bokhoni ba ho kenya litafole tse arohaneng ka ho hlakisa taelo ea "IMPORT FOREIGN SCHEMA".
- Ntlafatso e entsoe mabapi le ts'ebetsong ea ts'ebetso ea VACUUM (ho bokella litšila le ho paka polokelo ea disk). Ho ekelitsoe mokhoa oa ho hloekisa maemo a tšohanyetso o tlolang ts'ebetso eo e seng ea bohlokoa ea ho hloekisa haeba ho entsoe maemo a koahelang ID ea transaction. E fokotsehile holimo ha ho sebetsoa li-index ka sebopeho sa B-Tree. Ts'ebetso ea ts'ebetso ea "ANALYZE", e bokellang lipalo-palo mabapi le ts'ebetso ea database, e potlakisitsoe haholo.
- E ekelitse bokhoni ba ho lokisa mokhoa oa ho hatella o sebelisoang tsamaisong ea TOAST, e ikarabellang bakeng sa ho boloka lintlha tse kholo, tse kang li-blocks tsa mongolo kapa tlhahisoleseding ea geometri. Ntle le mokhoa oa compression oa pglz, TOAST e se e ka sebelisa algorithm ea LZ4.
- Lisebelisoa tsa ho beha leihlo tšebetso ea DBMS li ekelitsoe. Maikutlo a ekelitsoeng ho latela tsoelopele ea litaelo tsa COPY (pg_stat_progress_copy), lipalo-palo mabapi le lipehelo tsa ho pheta-pheta (pg_stat_replication_slots) le mesebetsi e amanang le tlaleho ea WAL ea transaction (pg_stat_wal). Mosebetsi oa compute_query_id o kentsoe, o nolofalletsang litsamaiso tse fapaneng tse fapaneng, joalo ka pg_stat_activity le EXPLAIN VERBOSE, ho sala morao likopo ka ho fana ka sekhetho se ikhethileng bakeng sa kopo ka 'ngoe.
- Lintlafatso li kenyellelitsoe ho moralo oa lipotso ho ntlafatsa ts'ebetso e ts'oanang ea lipotso le ho ntlafatsa ts'ebetso ea ts'ebetso e ts'oanang ea tlhahlobo ea rekoto e latellanang, ho etsa lipotso ka ho ts'oana ho PL/pgSQL ho sebelisa taelo ea "RETURN QUERY", le ho etsa lipotso tse tšoanang ho " TSOSOA PONO EA LITŠOANTŠISO”. Ho ntlafatsa ts'ebetso ea li-cyclic nested merges (ho kopanya), tšehetso ea caching e eketsehileng e kentsoe tšebetsong.
- Hona joale lipalo-palo tse tsoetseng pele li ka sebelisoa ho ntlafatsa lipolelo, 'me ho hlopha ka ho eketsa hona joale ho ka sebelisoa ho ntlafatsa mesebetsi ea lifensetere.
- Mekhoa e bolokiloeng e u lumellang hore u tsamaise litšebelisano ka har'a li-block tsa khoutu joale li tšehetsa ho hlalosa lintlha tsa ho khutlisa u sebelisa "OUT" parameters.
- E kentse ts'ebetso ea date_bin ho boleng ba setempe sa linako ho latela nako e boletsoeng. KHETHA letsatsi_bin('metsotso e 15', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-01-01'); 2020-02-11 15:30:00
- E kentse SEARCH le CYCLE lipolelo tse hlalositsoeng ho SQL standard ho etsa hore ho be bonolo ho odara le ho khetholla lipotoloho ka mokhoa o pheta-phetoang oa Common Table Expressions (CTE). LE RECURSIVE search_tree(id, link, data) AS ( KHETHA t.id, t.link, t.data HO TSOA sefateng t UNION TSOHLE KHETHA t.id, t.link, t.data HO TSOA sefateng t, search_tree st KAE t. id = st.link ) BATLISA BOTEBO PELE KA id SET ordercol KHETHA * HO TSOA ho search_tree ORDER BY ordercol;
- Ts'ebetsong ea psql, ho phetheloa ha litaelo ka li-tab ho ntlafalitsoe, bokhoni ba ho bonts'a likhang tsa ts'ebetso bo kentsoe ho taelo ea "\df", 'me lipalo-palo tse bonts'itsoeng li atolositsoe ho taelo ea "\dX".
- Hoa khoneha ho abela basebelisi litokelo tsa ho bala feela kapa ho ngola feela. Litokelo li ka beoa litafoleng ka bomong, lipono, le meralo ho sebelisoa pg_read_all_data le pg_write_all_data likarolo tse hlalositsoeng esale pele. GRANT pg_bala_all_data HO mosebedisi1;
- Lits'ebetso tse ncha tsa kamehla ho netefatso ea password ka ho sebelisa SCRAM-SHA-256 sebakeng sa md5 (parameter ea "password_encryption" ha e hlahisa postgresql.conf joale e se e behiloe ho 'scram-sha-256').
Source: opennet.ru