{"id":33872,"date":"2019-10-31T21:55:09","date_gmt":"2019-10-31T18:55:09","guid":{"rendered":"https:\/\/prohoster.info\/blog\/postgresql-11-evolyutsiya-sektsionirovaniya-ot-postgres-9-6-do-postgres-11\/"},"modified":"2019-10-31T21:55:09","modified_gmt":"2019-10-31T18:55:09","slug":"postgresql-11-evolyutsiya-sektsionirovaniya-ot-postgres-9-6-do-postgres-11","status":"publish","type":"post","link":"https:\/\/prohoster.info\/ro\/blog\/administrirovanie\/postgresql-11-evolyutsiya-sektsionirovaniya-ot-postgres-9-6-do-postgres-11","title":{"rendered":"PostgreSQL 11: Evolu\u021bia partaj\u0103rii de la Postgres 9.6 la Postgres 11","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

O zi minunat\u0103 de vineri pentru to\u021bi! R\u0103m\u00e2ne din ce \u00een ce mai pu\u021bin timp p\u00e2n\u0103 la lansarea cursului \u00abBaze de date rela\u021bionale\u00bb<\/a><\/noindex>, a\u0219adar ast\u0103zi \u00eemp\u0103rt\u0103\u0219im traducerea unei alte resurse utile pe tema aceasta.<\/p>\n

\u00cen procesul de dezvoltare PostgreSQL 11<\/a><\/noindex> s-a realizat o munc\u0103 impresionant\u0103 pentru a \u00eembun\u0103t\u0103\u021bi sec\u021bionarea tabelelor. Sec\u021bionarea tabelelor<\/b> \u2014 este o func\u021bie care exist\u0103 \u00een PostgreSQL de mult timp, dar, dac\u0103 putem spune a\u0219a, nu a fost practic utilizabil\u0103 p\u00e2n\u0103 la versiunea 10, \u00een care a devenit o func\u021bie foarte util\u0103. Anterior, afirmasem c\u0103 mo\u0219tenirea tabelelor este implementarea noastr\u0103 a sec\u021bion\u0103rii, \u0219i acest lucru este adev\u0103rat. Doar c\u0103 acest mod te obliga s\u0103 realizezi cea mai mare parte a muncii manual. De exemplu, dac\u0103 doreai ca tuplurile s\u0103 fie inserate \u00een sec\u021biuni \u00een timpul INSERT-urilor, trebuia s\u0103 configurezi trigger-e s\u0103 o fac\u0103 pentru tine. Sec\u021bionarea prin mo\u0219tenire era foarte lent\u0103 \u0219i complicat\u0103 pentru a dezvolta func\u021bii suplimentare deasupra ei.<\/a><\/noindex><\/p>\n

\u00cen PostgreSQL 10 am v\u0103zut na\u0219terea \u201esec\u021bion\u0103rii declarative\u201d \u2014 o func\u021bie destinat\u0103 s\u0103 rezolve multe probleme care nu erau solu\u021bionabile prin utilizarea vechiului metod\u0103 cu mo\u0219tenire. Acest lucru a condus la apari\u021bia unui instrument mult mai puternic, care ne permite s\u0103 \u00eemp\u0103r\u021bim datele pe orizontal\u0103!<\/p>\n

Compararea func\u021biilor<\/b><\/p>\n

\u00cen PostgreSQL 11 a ap\u0103rut un set impresionant de func\u021bii noi, care ajut\u0103 la cre\u0219terea performan\u021bei \u0219i la transformarea tabelelor sec\u021bionate \u00eentr-o form\u0103 mai transparent\u0103 pentru aplica\u021bii.<\/p>\n

\"PostgreSQL
\n\"PostgreSQL
\n\"PostgreSQL
\n1. Folosind excep\u021bii restric\u021bionante
\n2. Adaug\u0103 doar noduri
\n3. Doar pentru tabela sec\u021bionat\u0103, care se refer\u0103 la o nesec\u021bionat\u0103
\n4. Indexurile trebuie s\u0103 con\u021bin\u0103 toate coloanele cheie ale sec\u021biunii
\n5. Restric\u021biile pe sec\u021biune din ambele p\u0103r\u021bi trebuie s\u0103 coincid\u0103<\/i><\/p>\n

Performan\u021b\u0103<\/b><\/p>\n

Aici avem \u0219i ve\u0219ti bune! A fost ad\u0103ugat o nou\u0103 metod\u0103 de \u0219tergere a sec\u021biunilor<\/a><\/noindex>. Acest nou algoritm poate determina sec\u021biunile potrivite, analiz\u00e2nd condi\u021bia interog\u0103rii WHERE<\/code>. Algoritmul anterior, pe de alt\u0103 parte, verifica fiecare sec\u021biune pentru a determina dac\u0103 poate \u00eendeplini condi\u021bia WHERE<\/code>. Acest lucru ducea la o cre\u0219tere suplimentar\u0103 a timpului de planificare pe m\u0103sur\u0103 ce num\u0103rul sec\u021biunilor cre\u0219tea.<\/p>\n

\u00cen versiunea 9.6, cu sec\u021bionarea prin mo\u0219tenire, rutarea tuplilor \u00een sec\u021biune se realiza de obicei prin scrierea unei func\u021bii trigger care con\u021binea o serie de operatori IF pentru a insera tupla \u00een sec\u021biunea corect\u0103. Aceste func\u021bii puteau fi foarte lente \u00een execu\u021bie. Odat\u0103 cu sec\u021bionarea declara\u021biilor, ad\u0103ugat\u0103 \u00een versiunea 10, acest lucru a \u00eenceput s\u0103 func\u021bioneze mult mai repede.<\/p>\n

Folosind o tabel\u0103 sec\u021bionat\u0103 cu 100 de sec\u021biuni, putem evalua performan\u021ba \u00eenc\u0103rc\u0103rii a 10 milioane de r\u00e2nduri \u00een tabel din 1 coloan\u0103 BIGINT \u0219i 5 coloane INT.<\/p>\n

\"PostgreSQL
\n
\nPerforman\u021ba interog\u0103rii acestei tabele pentru a c\u0103uta o \u00eenregistrare indexat\u0103 \u0219i a executa DML pentru a manipula o singur\u0103 \u00eenregistrare (folosind doar 1 procesor):<\/p>\n

\"PostgreSQL
\n
\nAici vedem c\u0103 performan\u021ba fiec\u0103rei opera\u021bii a crescut semnificativ dup\u0103 PG 9.6. Interog\u0103rile SELECT<\/code> arat\u0103 mult mai bine, \u00een special cele care pot exclude multe sec\u021biuni \u00een timpul planific\u0103rii interog\u0103rilor. Aceasta \u00eenseamn\u0103 c\u0103 planificatorul poate s\u0103ri peste o mare parte din munca pe care trebuia s\u0103 o fac\u0103 anterior. De exemplu, nu mai sunt construite c\u0103i pentru sec\u021biuni inutile.<\/p>\n

Concluzie<\/b><\/p>\n

Sec\u021bionarea tabelelor \u00eencepe s\u0103 devin\u0103 o func\u021bie foarte puternic\u0103 \u00een PostgreSQL. Aceasta permite extragerea rapid\u0103 a datelor \u00een online \u0219i transferul lor \u00een offline, f\u0103r\u0103 a a\u0219tepta finalizarea unor opera\u021biuni DML masive lente.<\/b>Aceasta \u00eenseamn\u0103, de asemenea, c\u0103 datele asociate pot fi stocate \u00eempreun\u0103, ceea ce face accesul la datele necesare mult mai eficient. \u00cembun\u0103t\u0103\u021birile aduse \u00een aceast\u0103 versiune nu ar fi fost posibile f\u0103r\u0103 dezvoltatorii, recenzorii \u0219i commit-erii care au muncit neobosit la toate aceste func\u021bii.
\nLe mul\u021bumim tuturor! PostgreSQL 11 arat\u0103 pur \u0219i simplu fantastic!<\/b><\/p>\n

Iat\u0103 un articol scurt, dar destul de interesant. \u00cemp\u0103rt\u0103\u0219i\u021bi comentarii \u0219i nu uita\u021bi s\u0103 v\u0103 \u00eenscrie\u021bi la ziua por\u021bilor deschise<\/a><\/noindex>, \u00een cadrul c\u0103ruia va fi prezentat \u00een detaliu programul cursului.
\n
Sursa: habr.com<\/a><\/p>","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"excerpt":{"rendered":"

\u041e\u0442\u043b\u0438\u0447\u043d\u043e\u0439 \u0432\u0441\u0435\u043c \u043f\u044f\u0442\u043d\u0438\u0446\u044b! \u0412\u0441\u0435 \u043c\u0435\u043d\u044c\u0448\u0435 \u0432\u0440\u0435\u043c\u0435\u043d\u0438 \u043e\u0441\u0442\u0430\u0435\u0442\u0441\u044f \u0434\u043e \u0437\u0430\u043f\u0443\u0441\u043a\u0430 \u043a\u0443\u0440\u0441\u0430 \u00ab\u0420\u0435\u043b\u044f\u0446\u0438\u043e\u043d\u043d\u044b\u0435 \u0421\u0423\u0411\u0414\u00bb, \u043f\u043e\u044d\u0442\u043e\u043c\u0443 \u0441\u0435\u0433\u043e\u0434\u043d\u044f \u0434\u0435\u043b\u0438\u043c\u0441\u044f \u043f\u0435\u0440\u0435\u0432\u043e\u0434\u043e\u043c \u0435\u0449\u0435 \u043e\u0434\u043d\u043e\u0433\u043e \u043f\u043e\u043b\u0435\u0437\u043d\u043e\u0433\u043e \u043c\u0430\u0442\u0435\u0440\u0438\u0430\u043b\u0430 \u043f\u043e \u0442\u0435\u043c\u0435. \u0412 \u043f\u0440\u043e\u0446\u0435\u0441\u0441\u0435 \u0440\u0430\u0437\u0440\u0430\u0431\u043e\u0442\u043a\u0438 PostgreSQL 11 \u0431\u044b\u043b\u0430 \u043f\u0440\u043e\u0434\u0435\u043b\u0430\u043d\u0430 \u0432\u043f\u0435\u0447\u0430\u0442\u043b\u044f\u044e\u0449\u0430\u044f \u0440\u0430\u0431\u043e\u0442\u0430 \u043f\u043e \u0443\u043b\u0443\u0447\u0448\u0435\u043d\u0438\u044e \u0441\u0435\u043a\u0446\u0438\u043e\u043d\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u044f \u0442\u0430\u0431\u043b\u0438\u0446. \u0421\u0435\u043a\u0446\u0438\u043e\u043d\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u0435 \u0442\u0430\u0431\u043b\u0438\u0446 \u2014 \u044d\u0442\u043e \u0444\u0443\u043d\u043a\u0446\u0438\u044f, \u043a\u043e\u0442\u043e\u0440\u0430\u044f \u0441\u0443\u0449\u0435\u0441\u0442\u0432\u043e\u0432\u0430\u043b\u0430 \u0432 PostgreSQL \u0434\u043e\u0441\u0442\u0430\u0442\u043e\u0447\u043d\u043e \u0434\u043e\u043b\u0433\u043e\u0435 \u0432\u0440\u0435\u043c\u044f, \u043d\u043e \u0435\u0435, \u0435\u0441\u043b\u0438 \u043c\u043e\u0436\u043d\u043e \u0442\u0430\u043a \u0432\u044b\u0440\u0430\u0437\u0438\u0442\u044c\u0441\u044f, \u043f\u043e \u0441\u0443\u0442\u0438 […]<\/p>\n","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"author":1,"featured_media":25538,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[688],"tags":[],"class_list":["post-33872","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-administrirovanie"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\n","aioseo_head_json":{"title":"\ud83e\udd47PostgreSQL 11: Evolu\u021bia sec\u021bion\u0103rii de la Postgres 9.6 la Postgres 11 | ProHoster","description":"O zi minunat\u0103 de vineri tuturor!","canonical_url":"https:\/\/prohoster.info\/ro\/blog\/administrirovanie\/postgresql-11-evolyutsiya-sektsionirovaniya-ot-postgres-9-6-do-postgres-11","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"ro_RO","og:site_name":"ProHoster | \u041a\u0443\u043f\u0438\u0442\u044c \u043d\u0430\u0434\u0435\u0436\u043d\u044b\u0439 \u0445\u043e\u0441\u0442\u0438\u043d\u0433 \u0434\u043b\u044f \u0441\u0430\u0439\u0442\u043e\u0432 \u0441 \u0437\u0430\u0449\u0438\u0442\u043e\u0439 \u043e\u0442 DDoS, VPS VDS \u0441\u0435\u0440\u0432\u0435\u0440\u044b","og:type":"article","og:title":"\ud83e\udd47PostgreSQL 11: \u042d\u0432\u043e\u043b\u044e\u0446\u0438\u044f \u0441\u0435\u043a\u0446\u0438\u043e\u043d\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u044f \u043e\u0442 Postgres 9.6 \u0434\u043e Postgres 11 | ProHoster","og:description":"\u041e\u0442\u043b\u0438\u0447\u043d\u043e\u0439 \u0432\u0441\u0435\u043c \u043f\u044f\u0442\u043d\u0438\u0446\u044b!","og:url":"https:\/\/prohoster.info\/ro\/blog\/administrirovanie\/postgresql-11-evolyutsiya-sektsionirovaniya-ot-postgres-9-6-do-postgres-11","og:image":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:secure_url":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:width":350,"og:image:height":350,"article:published_time":"2019-10-31T18:55:09+00:00","article:modified_time":"2019-10-31T18:55:09+00:00","article:publisher":"https:\/\/www.facebook.com\/prohoster","article:author":"https:\/\/www.facebook.com\/prohoster"},"aioseo_meta_data":{"post_id":"33872","title":null,"description":null,"keywords":null,"keyphrases":null,"primary_term":null,"canonical_url":null,"og_title":null,"og_description":null,"og_object_type":"default","og_image_type":"default","og_image_url":null,"og_image_width":null,"og_image_height":null,"og_image_custom_url":null,"og_image_custom_fields":null,"og_video":null,"og_custom_url":null,"og_article_section":null,"og_article_tags":null,"twitter_use_og":false,"twitter_card":"default","twitter_image_type":"default","twitter_image_url":null,"twitter_image_custom_url":null,"twitter_image_custom_fields":null,"twitter_title":null,"twitter_description":null,"schema":{"blockGraphs":[],"customGraphs":[],"default":{"data":{"Article":[],"Course":[],"Dataset":[],"FAQPage":[],"Movie":[],"Person":[],"Product":[],"ProductReview":[],"Car":[],"Recipe":[],"Service":[],"SoftwareApplication":[],"WebPage":[]},"graphName":"","isEnabled":true},"graphs":[]},"schema_type":null,"schema_type_options":null,"pillar_content":false,"robots_default":true,"robots_noindex":false,"robots_noarchive":false,"robots_nosnippet":false,"robots_nofollow":false,"robots_noimageindex":false,"robots_noodp":false,"robots_notranslate":false,"robots_max_snippet":null,"robots_max_videopreview":null,"robots_max_imagepreview":"large","priority":null,"frequency":null,"local_seo":null,"seo_analyzer_scan_date":"2026-01-21 17:02:19","breadcrumb_settings":null,"limit_modified_date":false,"reviewed_by":null,"ai":null,"created":"2021-03-01 02:32:26","updated":"2026-01-21 17:02:19","focus_keyword":null,"additional_keywords":null,"truseo_locale":null},"gt_translate_keys":[{"key":"link","format":"url"}],"_links":{"self":[{"href":"https:\/\/prohoster.info\/ro\/wp-json\/wp\/v2\/posts\/33872","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/ro\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/ro\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/ro\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/ro\/wp-json\/wp\/v2\/comments?post=33872"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/ro\/wp-json\/wp\/v2\/posts\/33872\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/ro\/wp-json\/wp\/v2\/media\/25538"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/ro\/wp-json\/wp\/v2\/media?parent=33872"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/ro\/wp-json\/wp\/v2\/categories?post=33872"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/ro\/wp-json\/wp\/v2\/tags?post=33872"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}