{"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\/hr\/blog\/administrirovanie\/postgresql-11-evolyutsiya-sektsionirovaniya-ot-postgres-9-6-do-postgres-11","title":{"rendered":"PostgreSQL 11: Evolucija sekcioniranja od Postgresa 9.6 do Postgresa 11","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"<p>Svi\u0111a vam se petak! Sve je manje vremena do po\u010detka te\u010daja <noindex><a rel=\"nofollow\" href=\"https:\/\/otus.pw\/CIBR\/\">\u201eRelacijske DBMS\u201d<\/a><\/noindex>, pa danas dijelimo prijevod jo\u0161 jednog korisnog materijala na ovu temu.<\/p>\n<p>U procesu razvoja <noindex><a rel=\"nofollow\" href=\"https:\/\/www.2ndquadrant.com\/postgresql\/2ndquadrants-passion-postgresql\/contributions-postgresql-11\/\">PostgreSQL 11<\/a><\/noindex> obavljeno je impresivno pobolj\u0161anje sekcioniranja tablica. <b>Sekcioniranje tablica<\/b> je funkcija koja postoji u PostgreSQL dovoljno dugo, ali, ako to mo\u017eemo re\u0107i, prakti\u010dki nije postojala do verzije 10, u kojoj je postala vrlo korisna funkcija. Ranije smo tvrdili da su naslije\u0111ene tablice na\u0161a implementacija sekcioniranja, i to je istina. Samo je ovaj na\u010din tjerao raditi ve\u0107i dio posla ru\u010dno. Na primjer, ako ste \u017eeljeli da se tupke umetnu u sekcije tijekom INSERT-a, morali ste postaviti okida\u010de da to u\u010dine umjesto vas. Sekcioniranje putem naslje\u0111ivanja bilo je vrlo sporo i slo\u017eeno za razvoj dodatnih funkcija preko njega.<noindex><a rel=\"nofollow\" name=\"habracut\"><\/a><\/noindex><\/p>\n<p>U PostgreSQL 10 svjedo\u010dili smo ro\u0111enju \u201edeklarativnog sekcioniranja\u201d \u2014 funkcije koja je namijenjena rje\u0161avanju mnogih problema koji su bili nerazrije\u0161eni kori\u0161tenjem starog naslje\u0111ivanja. To je dovelo do pojave mnogo mo\u0107nijeg alata koji nam omogu\u0107ava horizontalnu podjelu podataka!<\/p>\n<p><b>Usporedba zna\u010dajki<\/b><\/p>\n<p>U PostgreSQL 11 pojavio se impresivan skup novih zna\u010dajki koje poma\u017eu pove\u0107ati u\u010dinkovitost i u\u010diniti sekcionirane tablice transparentnijima za aplikacije.<\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL 11: Evolucija sekcioniranja od Postgresa 9.6 do Postgresa 11\" src=\"\/wp-content\/uploads\/2019\/05\/13ef675b91bb0701719c816882bd3e8b.png\" style=\"display:block;margin: 0 auto;\" \/><br \/>\n<img decoding=\"async\" alt=\"PostgreSQL 11: Evolucija sekcioniranja od Postgresa 9.6 do Postgresa 11\" src=\"\/wp-content\/uploads\/2019\/05\/d87349281f0dc0820a064d81f80407d1.png\" style=\"display:block;margin: 0 auto;\" \/><br \/>\n<img decoding=\"async\" alt=\"PostgreSQL 11: Evolucija sekcioniranja od Postgresa 9.6 do Postgresa 11\" src=\"\/wp-content\/uploads\/2019\/05\/3bb3beeedf803f1fdbe58a858076478c.png\" style=\"display:block;margin: 0 auto;\" \/><br \/>\n<i>1. Kori\u0161tenje ograni\u010davaju\u0107ih iznimaka<br \/>\n2. Dodaje samo \u010dvorove<br \/>\n3. Samo za sekcioniranu tablicu koja se referira na nesectioniranu<br \/>\n4. Indeksi moraju sadr\u017eavati sve klju\u010dne stupce sekcije<br \/>\n5. Ograni\u010denja na sekciji s obje strane moraju se podudarati<\/i><\/p>\n<p><b>Performanse<\/b><\/p>\n<p>Ovdje tako\u0111er imamo dobre vijesti! Dodana 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 odrediti odgovaraju\u0107e sekcije pregledavaju\u0107i uvjet upita <code>GDJE<\/code>. Prethodni algoritam, s druge strane, provjeravao je svaku sekciju kako bi utvrdio mo\u017ee li ispuniti uvjet <code>GDJE<\/code>. To je dovelo do dodatnog pove\u0107anja vremena planiranja kako se broj sekcija pove\u0107ava.<\/p>\n<p>U verziji 9.6, s particioniranjem uz naslje\u0111ivanje, usmjeravanje tuplica unutar particije obi\u010dno se provodilo pisanjem triggerske funkcije koja je sadr\u017eavala niz IF izraza za umetanje tuplice u ispravnu particiju. Ove su funkcije mogle biti vrlo spore tijekom izvo\u0111enja. S deklarativnim particioniranjem, dodanim u verziji 10, to je postalo znatno br\u017ee.<\/p>\n<p>Kori\u0161tenjem particionirane tablice sa 100 particija, mo\u017eemo procijeniti performanse u\u010ditavanja 10 milijuna redaka u tablicu s 1 BIGINT stupcem i 5 INT stupaca.<\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL 11: Evolucija sekcioniranja od Postgresa 9.6 do Postgresa 11\" src=\"\/wp-content\/uploads\/2019\/05\/02c33ba997f187890046215997a689fc.png\" style=\"display:block;margin: 0 auto;\" \/><br \/>\n<br \/>\nPerformanse upita na ovoj tablici za pretra\u017eivanje jedne indeksirane stavke i izvr\u0161enje DML za manipulaciju jednom stavkom (koriste\u0107i samo 1 procesor):<\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL 11: Evolucija sekcioniranja od Postgresa 9.6 do Postgresa 11\" src=\"\/wp-content\/uploads\/2019\/05\/8e69519dd97c6f2e60112908418e5c97.png\" style=\"display:block;margin: 0 auto;\" \/><br \/>\n<br \/>\nOvdje vidimo da su performanse svake operacije zna\u010dajno porasle nakon PG 9.6. Upiti <code>SELECT<\/code> izgledaju mnogo bolje, posebno oni koji su sposobni isklju\u010diti mnoge particije tijekom planiranja upita. To zna\u010di da planirnik mo\u017ee presko\u010diti ve\u0107i dio posla koji je ranije trebao obaviti. Na primjer, vi\u0161e se ne grade putanje za nepotrebne particije.<\/p>\n<p><b>Zaklju\u010dak<\/b><\/p>\n<p>Particioniranje tablica po\u010dinje postajati vrlo sna\u017ena funkcija u PostgreSQL-u. <b>Omogu\u0107uje brzo izno\u0161enje podataka online i njihovo prebacivanje u offline, ne \u010dekaju\u0107i zavr\u0161etak sporih masovnih DML operacija.<\/b>To tako\u0111er zna\u010di da se povezani podaci mogu \u010duvati zajedno, \u0161to omogu\u0107uje pristup potrebnim podacima mnogo u\u010dinkovitije. Pobolj\u0161anja u\u010dinjena u ovoj verziji bila bi nemogu\u0107a bez programera, recenzenta i komitera koji su neumorno radili na svim ovim zna\u010dajkama.<br \/>\nHvala svima njima! <b>PostgreSQL 11 izgleda jednostavno fantasti\u010dno!<\/b><\/p>\n<p>Evo jednog kratkog, ali zanimljivog \u010dlanka. Podijelite komentare, kao i ne zaboravite se prijaviti na <noindex><a rel=\"nofollow\" href=\"https:\/\/otus.pw\/37jj\/\">dan otvorenih vrata<\/a><\/noindex>, u okviru kojeg \u0107e biti detaljno izlo\u017eena programa te\u010daja.<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\/hr\/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=\"hr_HR\" \/>\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\/hr\/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 particioniranja od Postgresa 9.6 do Postgresa 11 | ProHoster","description":"Svima \u017eelim odli\u010dan petak!","canonical_url":"https:\/\/prohoster.info\/hr\/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":"hr_HR","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\/hr\/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\/hr\/wp-json\/wp\/v2\/posts\/33872","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/hr\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/hr\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/hr\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/hr\/wp-json\/wp\/v2\/comments?post=33872"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/hr\/wp-json\/wp\/v2\/posts\/33872\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/hr\/wp-json\/wp\/v2\/media\/25538"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/hr\/wp-json\/wp\/v2\/media?parent=33872"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/hr\/wp-json\/wp\/v2\/categories?post=33872"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/hr\/wp-json\/wp\/v2\/tags?post=33872"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}