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

V\u0161em p\u0159eji skv\u011bl\u00fd p\u00e1tek! \u010c\u00edm d\u00e1l m\u00e9n\u011b \u010dasu zb\u00fdv\u00e1 do spu\u0161t\u011bn\u00ed kurzu \u201eRela\u010dn\u00ed DBMS\u201c<\/a><\/noindex>, a proto dnes sd\u00edl\u00edme p\u0159eklad dal\u0161\u00edho u\u017eite\u010dn\u00e9ho materi\u00e1lu na toto t\u00e9ma.<\/p>\n

B\u011bhem v\u00fdvoje PostgreSQL 11<\/a><\/noindex> byla odvedena ohromn\u00e1 pr\u00e1ce na zlep\u0161en\u00ed sekcionov\u00e1n\u00ed tabulek. Sekcionov\u00e1n\u00ed tabulek<\/b> \u2014 je to funkce, kter\u00e1 v PostgreSQL existovala dost dlouho, ale v podstat\u011b neexistovala a\u017e do verze 10, ve kter\u00e9 se stala velmi u\u017eite\u010dnou funkc\u00ed. D\u0159\u00edve jsme tvrdili, \u017ee d\u011bdi\u010dnost tabulek je na\u0161e implementace sekcionov\u00e1n\u00ed, a to je pravda. Jen\u017ee tento zp\u016fsob v\u00e1s nutil prov\u00e1d\u011bt v\u011bt\u0161inu pr\u00e1ce ru\u010dn\u011b. Nap\u0159\u00edklad, pokud jste cht\u011bli, aby se tuplety vkl\u00e1daly do sekc\u00ed b\u011bhem INSERT\u016f, museli jste nastavit spou\u0161t\u011b\u010de, aby to ud\u011blaly za v\u00e1s. Sekcionov\u00e1n\u00ed pomoc\u00ed d\u011bdi\u010dnosti bylo velmi pomal\u00e9 a slo\u017eit\u00e9 pro vyv\u00edjen\u00ed dal\u0161\u00edch funkc\u00ed nad n\u00edm.<\/a><\/noindex><\/p>\n

V PostgreSQL 10 jsme vid\u011bli zrozen\u00ed \u201edeklarativn\u00edho sekcionov\u00e1n\u00ed\u201c \u2014 funkce ur\u010den\u00e9 k \u0159e\u0161en\u00ed mnoha probl\u00e9m\u016f, kter\u00e9 nebyly vy\u0159e\u0161iteln\u00e9 p\u0159i pou\u017e\u00edv\u00e1n\u00ed star\u00e9ho metody s d\u011bdi\u010dnost\u00ed. To vedlo k vytvo\u0159en\u00ed mnohem mocn\u011bj\u0161\u00edho n\u00e1stroje, kter\u00fd n\u00e1m umo\u017e\u0148uje rozd\u011blovat data horizont\u00e1ln\u011b!<\/p>\n

Srovn\u00e1n\u00ed funkc\u00ed<\/b><\/p>\n

Ve verzi PostgreSQL 11 se objevil ohromuj\u00edc\u00ed soubor nov\u00fdch funkc\u00ed, kter\u00e9 pom\u00e1haj\u00ed zvy\u0161ovat v\u00fdkon a zp\u0159ehled\u0148uj\u00ed sekcionovan\u00e9 tabulky pro aplikace.<\/p>\n

\"PostgreSQL
\n\"PostgreSQL
\n\"PostgreSQL
\n1. Pou\u017eit\u00ed restriktivn\u00edch v\u00fdjimek
\n2. P\u0159id\u00e1v\u00e1 pouze uzly
\n3. Pouze pro sekcionovanou tabulku, kter\u00e1 se odvol\u00e1v\u00e1 na nesectionovanou
\n4. Indexy mus\u00ed obsahovat v\u0161echny kl\u00ed\u010dov\u00e9 sloupce sekce
\n5. Omezen\u00ed na sekci z obou stran mus\u00ed odpov\u00eddat<\/i><\/p>\n

V\u00fdkon<\/b><\/p>\n

M\u00e1me zde tak\u00e9 dobrou zpr\u00e1vu! Byla p\u0159id\u00e1na nov\u00e1 metoda odstra\u0148ov\u00e1n\u00ed sekc\u00ed<\/a><\/noindex>. Tento nov\u00fd algoritmus m\u016f\u017ee ur\u010dovat vhodn\u00e9 sekce proch\u00e1zen\u00edm podm\u00ednky dotazu nen\u00ed spolehliv\u00fd:<\/code>. P\u0159edchoz\u00ed algoritmus mezit\u00edm kontroloval ka\u017edou sekci, aby ur\u010dil, zda m\u016f\u017ee vyhov\u011bt podm\u00ednce nen\u00ed spolehliv\u00fd:<\/code>. To vedlo k dal\u0161\u00edmu zvy\u0161ov\u00e1n\u00ed \u010dasu pl\u00e1nov\u00e1n\u00ed s rostouc\u00edm po\u010dtem sekc\u00ed.<\/p>\n

V 9.6, s odd\u011blov\u00e1n\u00edm pomoc\u00ed d\u011bdi\u010dnosti, se sm\u011brov\u00e1n\u00ed tuple do sekce obvykle prov\u00e1d\u011blo psan\u00edm triggerov\u00e9 funkce, kter\u00e1 obsahovala s\u00e9rii p\u0159\u00edkaz\u016f IF pro vlo\u017een\u00ed tuple do spr\u00e1vn\u00e9 sekce. Tyto funkce mohly b\u00fdt p\u0159i prov\u00e1d\u011bn\u00ed velmi pomal\u00e9. S deklarativn\u00edm odd\u011blov\u00e1n\u00edm p\u0159idan\u00fdm ve verzi 10 to nyn\u00ed funguje mnohem rychleji.<\/p>\n

Pou\u017eit\u00edm odd\u011blen\u00e9 tabulky se 100 sekcemi m\u016f\u017eeme vyhodnotit v\u00fdkon na\u010d\u00edt\u00e1n\u00ed 10 milion\u016f \u0159\u00e1dk\u016f do tabulky z 1 sloupce BIGINT a 5 sloupc\u016f INT.<\/p>\n

\"PostgreSQL
\n
\nV\u00fdkon dotazu na tuto tabulku pro vyhled\u00e1n\u00ed jednoho indexovan\u00e9ho z\u00e1znamu a vykon\u00e1n\u00ed DML pro manipulaci s jedn\u00edm z\u00e1znamem (pouze s 1 procesorem):<\/p>\n

\"PostgreSQL
\n
\nZde vid\u00edme, \u017ee v\u00fdkon ka\u017ed\u00e9 operace v\u00fdrazn\u011b vzrostl po PG 9.6. Dotazy nebude prov\u00e1d\u011bn p\u0159i nepravdivosti prvn\u00ed podm\u00ednky. Opravy pomoc\u00ed<\/code> vypadaj\u00ed mnohem l\u00e9pe, zejm\u00e9na ty, kter\u00e9 mohou b\u011bhem pl\u00e1nov\u00e1n\u00ed dotaz\u016f vylou\u010dit v\u00edce sekc\u00ed. To znamen\u00e1, \u017ee pl\u00e1nova\u010d m\u016f\u017ee p\u0159esko\u010dit velkou \u010d\u00e1st pr\u00e1ce, kterou musel d\u0159\u00edve vykon\u00e1vat. Nap\u0159\u00edklad ji\u017e se nestav\u00ed cesty pro nepot\u0159ebn\u00e9 sekce.<\/p>\n

Z\u00e1v\u011br<\/b><\/p>\n

Odd\u011blov\u00e1n\u00ed tabulek se za\u010d\u00edn\u00e1 st\u00e1vat velmi silnou funkc\u00ed v PostgreSQL. Umo\u017e\u0148uje rychle vyv\u00e9st data online a p\u0159ev\u00e9st je do offline, ani\u017e by \u010dekala na dokon\u010den\u00ed pomal\u00fdch masivn\u00edch operac\u00ed DML.<\/b>To tak\u00e9 znamen\u00e1, \u017ee souvisej\u00edc\u00ed data mohou b\u00fdt ulo\u017eena spole\u010dn\u011b, co\u017e znamen\u00e1, \u017ee k po\u017eadovan\u00fdm dat\u016fm lze z\u00edskat p\u0159\u00edstup mnohem efektivn\u011bji. Vylep\u0161en\u00ed proveden\u00e1 v t\u00e9to verzi by nebyla mo\u017en\u00e1 bez v\u00fdvoj\u00e1\u0159\u016f, recenzent\u016f a komit\u00e9r\u016f, kte\u0159\u00ed ne\u00fanavn\u011b pracovali na v\u0161ech t\u011bchto funkc\u00edch.
\nD\u011bkujeme v\u0161em! PostgreSQL 11 vypad\u00e1 prost\u011b fantasticky!<\/b><\/p>\n

Toto je takov\u00fd kr\u00e1tk\u00fd, ale docela zaj\u00edmav\u00fd \u010dl\u00e1nek. Sd\u00edlejte koment\u00e1\u0159e a nezapome\u0148te se zaregistrovat na den otev\u0159en\u00fdch dve\u0159\u00ed<\/a><\/noindex>, v r\u00e1mci kter\u00e9ho bude podrobn\u011b prezentov\u00e1na programov\u00e1 struktura kurzu.
\n
Zdroj: 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: Evoluce odd\u011blov\u00e1n\u00ed od Postgres 9.6 po Postgres 11 | ProHoster","description":"P\u0159eji v\u0161em skv\u011bl\u00fd p\u00e1tek!","canonical_url":"https:\/\/prohoster.info\/cs\/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":"cs_CZ","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\/cs\/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\/cs\/wp-json\/wp\/v2\/posts\/33872","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/comments?post=33872"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/posts\/33872\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/media\/25538"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/media?parent=33872"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/categories?post=33872"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/tags?post=33872"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}