{"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\/sl\/blog\/administrirovanie\/postgresql-11-evolyutsiya-sektsionirovaniya-ot-postgres-9-6-do-postgres-11","title":{"rendered":"PostgreSQL 11: Evolucija particioniranja od Postgres 9.6 do Postgres 11","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

\u017delim vam lep petak! Sve manje vremena ostaje do pokretanja kursa \u201eRelacijske DBMS\u201c<\/a><\/noindex>, zato danas delimo prevod jo\u0161 jednog korisnog materijala na temu.<\/p>\n

Tokom razvoja PostgreSQL 11<\/a><\/noindex> obavljena je impresivna koli\u010dina posla na pobolj\u0161anju sekcioniranja tabela. Sekcioniranje tabela<\/b> je funkcija koja postoji u PostgreSQL ve\u0107 dugo vremena, ali, ako se mo\u017ee tako re\u0107i, su\u0161tinski je nije bilo do verzije 10, kada je postala veoma korisna funkcija. Ranije smo tvrdili da je nasle\u0111ivanje tabela na\u0161a implementacija sekcioniranja, i to je ta\u010dno. Samo, ovaj na\u010din vas je prisilio da mnogo posla uradite ru\u010dno. Na primer, ako ste \u017eeleli da se tuple-ovi umetnu u sekcije tokom INSERT-ova, morali ste da postavite okida\u010de da to urade umesto vas. Sekcioniranje pomo\u0107u nasle\u0111ivanja bilo je vrlo sporo i komplikovano za razvoj dodatnih funkcija iznad njega.<\/a><\/noindex><\/p>\n

U PostgreSQL 10 videli smo ro\u0111enje \"deklarativnog sekcioniranja\" \u2014 funkcionalnosti dizajnirane da re\u0161i mnoge probleme koji su bili nerazre\u0161eni kori\u0161\u0107enjem starog metoda s nasle\u0111ivanjem. To je dovelo do stvaranja mnogo mo\u0107nijeg alata koji nam omogu\u0107ava horizontalno deljenje podataka!<\/p>\n

Upore\u0111ivanje funkcionalnosti<\/b><\/p>\n

U PostgreSQL 11 pojavio se impresivan skup novih funkcija koje poma\u017eu u pobolj\u0161anju performansi i \u010dine sekcionirane tabele transparentnijim za aplikacije.<\/p>\n

\"PostgreSQL
\n\"PostgreSQL
\n\"PostgreSQL
\n1. Kori\u0161\u0107enje ograni\u010davaju\u0107ih izuzetaka
\n2. Dodavanje samo \u010dvorova
\n3. Samo za sekcioniranu tabelu koja se poziva na nesekcioniranu
\n4. Indeksi moraju sadr\u017eati sve klju\u010dne kolone sekcije
\n5. Ograni\u010denje na sekciju sa obe strane mora se poklapati<\/i><\/p>\n

Performansa<\/b><\/p>\n

Ovde imamo jo\u0161 dobre vesti! Dodata je nova metoda za uklanjanje sekcija<\/a><\/noindex>. Ovaj novi algoritam mo\u017ee da utvrdi odgovaraju\u0107e sekcije pregledaju\u0107i uslove upita WHERE<\/code>. Prethodni algoritam, s druge strane, proveravao je svaku sekciju da bi utvrdio da li mo\u017ee da ispunjava uslov WHERE<\/code>. Ovo je dovelo do dodatnog pove\u0107anja vremena planiranja kako je broj sekcija rastao.<\/p>\n

V razli\u010dici 9.6 je bila usmeritev tuple v sekciji obi\u010dajno izvedena z napisom spro\u017eilne funkcije, ki je vsebovala vrsto IF operatorjev za vstavljanje tuple v pravilno sekcijo. Te funkcije so lahko bile zelo po\u010dasne pri izvajanju. S deklarativnim sekcioniranjem, dodanim v razli\u010dici 10, je to za\u010delo delovati bistveno hitreje.<\/p>\n

Z uporabo sekcionirane tabele s 100 sekcijami lahko ocenimo uspe\u0161nost nalaganja 10 milijonov vrstic v tabelo, ki ima 1 stolpec BIGINT in 5 stolpcev INT.<\/p>\n

\"PostgreSQL
\n
\nUspe\u0161nost poizvedbe v to tabelo za iskanje ene indeksirane \u0161tevilke in izvajanje DML za manipulacijo enega vnosa (z uporabo samo 1 procesorja):<\/p>\n

\"PostgreSQL
\n
\nTukaj vidimo, da se je uspe\u0161nost vsake operacije po PG 9.6 ob\u010dutno pove\u010dala. Poizvedbe SELECT<\/code> so videti precej bolje, zlasti tiste, ki lahko izklju\u010dijo ve\u010d sekcij med na\u010drtovanjem poizvedb. To pomeni, da lahko na\u010drtovalec presko\u010di velik del dela, ki ga je moral opraviti prej. Na primer, poti za nepotrebne sekcije se ve\u010d ne gradijo.<\/p>\n

Zaklju\u010dek<\/b><\/p>\n

Sekcioniranje tabel postaja zelo zmogljiva funkcija v PostgreSQL. Omogo\u010da hitro prikazovanje podatkov v online na\u010dinu in njihovo preklapljanje v offline, ne da bi bilo potrebno \u010dakati na kon\u010danje po\u010dasnih masovnih DML operacij.<\/b>To tudi pomeni, da se lahko povezani podatki shranijo skupaj, kar omogo\u010da dostop do zahtevanih podatkov veliko u\u010dinkoviteje. Izbolj\u0161ave, dose\u017eene v tej razli\u010dici, ne bi bile mogo\u010de brez razvijalcev, recenzentov in komitente, ki so neomajno delali na vseh teh funkcijah.
\nHvala vsem njim! PostgreSQL 11 je videti preprosto fantasti\u010dno!<\/b><\/p>\n

Tukaj je kratek, a zelo zanimiv \u010dlanek. Delite svoje komentarje in ne pozabite se prijaviti na dan odprtih vrat<\/a><\/noindex>, kjer bo podrobno predstavljena programa te\u010daja.
\n
Izvor: 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\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: Evolucija sekcioniranja od Postgres 9.6 do Postgres 11 | ProHoster","description":"Vsem \u017eelim odli\u010den petek!","canonical_url":"https:\/\/prohoster.info\/sl\/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":"sl_SI","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\/sl\/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\/sl\/wp-json\/wp\/v2\/posts\/33872","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/sl\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/sl\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/sl\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/sl\/wp-json\/wp\/v2\/comments?post=33872"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/sl\/wp-json\/wp\/v2\/posts\/33872\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/sl\/wp-json\/wp\/v2\/media\/25538"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/sl\/wp-json\/wp\/v2\/media?parent=33872"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/sl\/wp-json\/wp\/v2\/categories?post=33872"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/sl\/wp-json\/wp\/v2\/tags?post=33872"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}