{"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\/bs\/blog\/administrirovanie\/postgresql-11-evolyutsiya-sektsionirovaniya-ot-postgres-9-6-do-postgres-11","title":{"rendered":"PostgreSQL 11: Evolucija sekcionisanja od Postgres 9.6 do Postgres 11","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"<p>Sjajan petak svima! Sve manje vremena ostaje do pokretanja kursa <noindex><a rel=\"nofollow\" href=\"https:\/\/otus.pw\/CIBR\/\">\u201eRelacione DBMS\u201c<\/a><\/noindex>, zato danas delimo prevod jo\u0161 jednog korisnog materijala na ovu temu.<\/p>\n<p>Tokom razvoja <noindex><a rel=\"nofollow\" href=\"https:\/\/www.2ndquadrant.com\/postgresql\/2ndquadrants-passion-postgresql\/contributions-postgresql-11\/\">PostgreSQL 11<\/a><\/noindex> ura\u0111ena je impresivna koli\u010dina posla na pobolj\u0161anju particionisanja tabela. <b>Particionisanje tabela<\/b> je funkcija koja postoji u PostgreSQL ve\u0107 dugo vremena, ali zapravo nije bila prisutna do verzije 10, u kojoj je postala vrlo korisna funkcija. Ranije smo tvrdili da je nasle\u0111ivanje tabela na\u0161a implementacija particionisanja, i to je ta\u010dno. Samo, ovaj na\u010din je zahtevao da ve\u0107inu posla obavljate ru\u010dno. Na primer, ako ste \u017eeleli da tuplirane vrednosti budu umetnute u sekcije tokom INSERT-ova, morali ste da postavite okida\u010de da to urade umesto vas. Particionisanje putem nasle\u0111ivanja bilo je vrlo sporo i te\u0161ko za razvoj dodatnih funkcija iznad njega.<noindex><a rel=\"nofollow\" name=\"habracut\"><\/a><\/noindex><\/p>\n<p>U PostgreSQL 10 smo videli ro\u0111enje \u201edeklarativnog particionisanja\u201c \u2014 funkcije dizajnirane da re\u0161i mnoge probleme koji su bili nerazre\u0161ivi kori\u0161\u0107enjem starog metoda nasle\u0111ivanja. To je dovelo do pojave mnogo mo\u0107nijeg alata koji nam omogu\u0107ava horizontalno deljenje podataka!<\/p>\n<p><b>Upore\u0111ivanje funkcija<\/b><\/p>\n<p>U PostgreSQL 11 se pojavio impresivan skup novih funkcija koje poma\u017eu u pobolj\u0161anju performansi i \u010dine particionisane tabele transparentnijim za aplikacije.<\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL 11: Evolucija sekcionisanja od Postgres 9.6 do Postgres 11\" src=\"\/wp-content\/uploads\/2019\/05\/13ef675b91bb0701719c816882bd3e8b.png\" style=\"display:block;margin: 0 auto;\" \/><br \/>\n<img decoding=\"async\" alt=\"PostgreSQL 11: Evolucija sekcionisanja od Postgres 9.6 do Postgres 11\" src=\"\/wp-content\/uploads\/2019\/05\/d87349281f0dc0820a064d81f80407d1.png\" style=\"display:block;margin: 0 auto;\" \/><br \/>\n<img decoding=\"async\" alt=\"PostgreSQL 11: Evolucija sekcionisanja od Postgres 9.6 do Postgres 11\" src=\"\/wp-content\/uploads\/2019\/05\/3bb3beeedf803f1fdbe58a858076478c.png\" style=\"display:block;margin: 0 auto;\" \/><br \/>\n<i>1. Koriste\u0107i ograni\u010davaju\u0107e izuzetke<br \/>\n2. Dodaje samo \u010dvorove<br \/>\n3. Samo za particionisanu tabelu koja se odnosi na nepodijeljenu<br \/>\n4. Indeksi moraju sadr\u017eati sve klju\u010dne kolone sekcije<br \/>\n5. Ograni\u010denja na sekciji s obe strane moraju se poklapati<\/i><\/p>\n<p><b>Performanse<\/b><\/p>\n<p>Imamo dobre vesti! Dodata je nova metoda <noindex><a rel=\"nofollow\" href=\"https:\/\/www.2ndquadrant.com\/blog\/http2ndquadrant-com\/partition-elimination-postgresql-11\/%22\">uklanjanja sekcija<\/a><\/noindex>. Ovaj novi algoritam mo\u017ee da utvrdi odgovaraju\u0107e sekcije pregledavaju\u0107i uslov upita <code>GDJE<\/code>. Prethodni algoritam, s druge strane, je proveravao svaku sekciju da utvrdi mo\u017ee li odgovarati uslovu <code>GDJE<\/code>. To je dovelo do dodatnog pove\u0107anja vremena planiranja kako je broj sekcija rasla.<\/p>\n<p>U verziji 9.6, sa segmentacijom putem naslje\u0111ivanja, usmjeravanje tuplova u sekciji obi\u010dno se vr\u0161ilo pisanjem okida\u010dke funkcije koja je sadr\u017eavala niz IF naredbi za umetanje tuplova u odgovaraju\u0107u sekciju. Ove funkcije mogle su biti vrlo spore prilikom izvo\u0111enja. Sa deklarativnom segmentacijom dodanom u verziji 10, to je po\u010delo raditi mnogo br\u017ee.<\/p>\n<p>Koriste\u0107i segmentiranu tabelu sa 100 sekcija, mo\u017eemo procijeniti performanse u\u010ditavanja 10 miliona redova u tabelu s 1 BIGINT kolonom i 5 INT kolona.<\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL 11: Evolucija sekcionisanja od Postgres 9.6 do Postgres 11\" src=\"\/wp-content\/uploads\/2019\/05\/02c33ba997f187890046215997a689fc.png\" style=\"display:block;margin: 0 auto;\" \/><br \/>\n<br \/>\nPerformanse upita na ovoj tabeli za pretra\u017eivanje jednog indeksiranog zapisa i izvr\u0161avanje DML za manipulaciju jednim zapisom (koriste\u0107i samo 1 procesor):<\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL 11: Evolucija sekcionisanja od Postgres 9.6 do Postgres 11\" src=\"\/wp-content\/uploads\/2019\/05\/8e69519dd97c6f2e60112908418e5c97.png\" style=\"display:block;margin: 0 auto;\" \/><br \/>\n<br \/>\nOvde vidimo da su performanse svake operacije zna\u010dajno porasle nakon PG 9.6. Upiti <code>SELECT<\/code> izgledaju mnogo bolje, posebno oni koji su u mogu\u0107nosti da isklju\u010de mnoge sekcije tokom planiranja upita. To zna\u010di da planirator mo\u017ee presko\u010diti veliki deo posla koji je ranije morao da obavi. Na primjer, vi\u0161e se ne grade putanje za nepotrebne sekcije.<\/p>\n<p><b>Zaklju\u010dak<\/b><\/p>\n<p>Segmentacija tabela po\u010dinje postajati vrlo mo\u0107na funkcija u PostgreSQL. <b>Ona omogu\u0107ava brzo izdavanje podataka u online re\u017eimu i njihovo prebacivanje u offline, ne \u010dekaju\u0107i zavr\u0161etak sporih masovnih DML operacija.<\/b>To tako\u0111e zna\u010di da se povezani podaci mogu \u010duvati zajedno, odnosno da se potrebnim podacima mo\u017ee pristupiti mnogo efikasnije. Pobolj\u0161anja napravljena u ovoj verziji ne bi bila mogu\u0107a bez programera, recenzenata i komitara koji su neumorno radili na svim ovim funkcijama.<br \/>\nHvala svima njima! <b>PostgreSQL 11 izgleda jednostavno fantasti\u010dno!<\/b><\/p>\n<p>Evo jedan kratak, ali prili\u010dno zanimljiv \u010dlanak. Podijelite komentare, a tako\u0111e ne zaboravite da se prijavite na <noindex><a rel=\"nofollow\" href=\"https:\/\/otus.pw\/37jj\/\">dan otvorenih vrata<\/a><\/noindex>, u okviru kojeg \u0107e biti detaljno izlo\u017eena programska struktura kursa.<br \/>\n<br \/>Izvor: <a content=\"nofollow\" rel=\"nofollow\" href=\"https:\/\/habr.com\/ru\/company\/otus\/blog\/452280\/\">habr.com<\/a><\/p>","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"excerpt":{"rendered":"<p>\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 [&hellip;]<\/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<!-- All in One SEO 5.0.5.1 - aioseo.com -->\n\t<meta name=\"description\" content=\"\u041e\u0442\u043b\u0438\u0447\u043d\u043e\u0439 \u0432\u0441\u0435\u043c \u043f\u044f\u0442\u043d\u0438\u0446\u044b!\" \/>\n\t<meta name=\"robots\" content=\"max-image-preview:large\" \/>\n\t<meta name=\"author\" content=\"Yuri Gagarin\"\/>\n\t<link rel=\"canonical\" href=\"https:\/\/prohoster.info\/bs\/blog\/administrirovanie\/postgresql-11-evolyutsiya-sektsionirovaniya-ot-postgres-9-6-do-postgres-11\" \/>\n\t\t<meta name=\"generator\" content=\"All in One SEO (AIOSEO) 5.0.5.1\" \/>\n\t\t<meta property=\"og:locale\" content=\"bs_BA\" \/>\n\t\t<meta property=\"og:site_name\" content=\"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\" \/>\n\t\t<meta property=\"og:type\" content=\"article\" \/>\n\t\t<meta property=\"og:title\" content=\"\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\" \/>\n\t\t<meta property=\"og:description\" content=\"\u041e\u0442\u043b\u0438\u0447\u043d\u043e\u0439 \u0432\u0441\u0435\u043c \u043f\u044f\u0442\u043d\u0438\u0446\u044b!\" \/>\n\t\t<meta property=\"og:url\" content=\"https:\/\/prohoster.info\/bs\/blog\/administrirovanie\/postgresql-11-evolyutsiya-sektsionirovaniya-ot-postgres-9-6-do-postgres-11\" \/>\n\t\t<meta property=\"og:image\" content=\"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg\" \/>\n\t\t<meta property=\"og:image:secure_url\" content=\"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg\" \/>\n\t\t<meta property=\"og:image:width\" content=\"350\" \/>\n\t\t<meta property=\"og:image:height\" content=\"350\" \/>\n\t\t<meta property=\"article:published_time\" content=\"2019-10-31T18:55:09+00:00\" \/>\n\t\t<meta property=\"article:modified_time\" content=\"2019-10-31T18:55:09+00:00\" \/>\n\t\t<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/prohoster\" \/>\n\t\t<meta property=\"article:author\" content=\"https:\/\/www.facebook.com\/prohoster\" \/>\n\t\t<!-- All in One SEO -->\n\n","aioseo_head_json":{"title":"\ud83e\udd47PostgreSQL 11: Evolucija segmentacije od Postgres 9.6 do Postgres 11 | ProHoster","description":"Svim sretnim petkom!","canonical_url":"https:\/\/prohoster.info\/bs\/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":"bs_BA","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\/bs\/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\/bs\/wp-json\/wp\/v2\/posts\/33872","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/bs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/bs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/bs\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/bs\/wp-json\/wp\/v2\/comments?post=33872"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/bs\/wp-json\/wp\/v2\/posts\/33872\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/bs\/wp-json\/wp\/v2\/media\/25538"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/bs\/wp-json\/wp\/v2\/media?parent=33872"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/bs\/wp-json\/wp\/v2\/categories?post=33872"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/bs\/wp-json\/wp\/v2\/tags?post=33872"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}