{"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\/da\/blog\/administrirovanie\/postgresql-11-evolyutsiya-sektsionirovaniya-ot-postgres-9-6-do-postgres-11","title":{"rendered":"PostgreSQL 11: Udviklingen af partitionering fra Postgres 9.6 til Postgres 11","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

God fredag til alle! Der er mindre tid tilbage til lanceringen af kurset \u00bbRelationelle DBMS'er\u00ab<\/a><\/noindex>, derfor deler vi i dag overs\u00e6ttelsen af endnu et nyttigt materiale om emnet.<\/p>\n

I udviklingsprocessen PostgreSQL 11<\/a><\/noindex> blev der udf\u00f8rt et imponerende stykke arbejde med at forbedre partitioneringen af tabeller. Partitionering af tabeller<\/b> \u2014 det er en funktion, der har eksisteret i PostgreSQL i lang tid, men som, hvis man kan sige det s\u00e5dan, reelt ikke eksisterede f\u00f8r version 10, hvor den blev en temmelig nyttig funktion. Tidligere har vi p\u00e5st\u00e5et, at tabelarv er vores implementering af partitionering, og det er sandt. Men denne metode tvang dig til at udf\u00f8re det meste af arbejdet manuelt. For eksempel, hvis du \u00f8nskede, at r\u00e6kker skulle inds\u00e6ttes i sektioner under INSERTs, skulle du konfigurere triggere til at g\u00f8re det for dig. Partitionering ved hj\u00e6lp af arv var meget langsom og besv\u00e6rlig at udvikle ekstra funktioner ovenp\u00e5.<\/a><\/noindex><\/p>\n

I PostgreSQL 10 s\u00e5 vi f\u00f8dslen af \"deklaraiv partitionering\" \u2014 en funktion designet til at l\u00f8se mange af de problemer, der var ul\u00f8selige ved brug af den gamle metode med arv. Dette f\u00f8rte til en langt mere kraftfuld v\u00e6rkt\u00f8j, der g\u00f8r det muligt for os at opdele data horisontalt!<\/p>\n

Sammenligning af funktioner<\/b><\/p>\n

I PostgreSQL 11 kom der et imponerende s\u00e6t nye funktioner, der hj\u00e6lper med at \u00f8ge ydeevnen og g\u00f8re partitionerede tabeller mere gennemsigtige for applikationer.<\/p>\n

\"PostgreSQL
\n\"PostgreSQL
\n\"PostgreSQL
\n1. Brug af begr\u00e6nsende undtagelser
\n2. Tilf\u00f8jer kun noder
\n3. Kun for partitionerede tabeller, der henviser til ikke-partitionerede
\n4. Indekser skal indeholde alle n\u00f8glekolonner i sektionen
\n5. Begrundelse for sektionen p\u00e5 begge sider skal v\u00e6re ens<\/i><\/p>\n

Ydeevne<\/b><\/p>\n

Her har vi ogs\u00e5 gode nyheder! En ny metode til at fjerne sektioner<\/a><\/noindex>. Denne nye algoritme kan bestemme passende sektioner ved at gennemg\u00e5 foresp\u00f8rgselsbetingelsen HVOR<\/code>. Den tidligere algoritme kontrollerede derimod hver sektion for at afg\u00f8re, om den kunne overholde betingelsen HVOR<\/code>. Det f\u00f8rte til en yderligere stigning i planl\u00e6gningstiden, efterh\u00e5nden som antallet af sektioner voksede.<\/p>\n

I version 9.6, med sektionering ved hj\u00e6lp af arv, blev ruting af tuples i sektionen normalt udf\u00f8rt ved at skrive en triggerfunktion, der indeholdt en r\u00e6kke IF-operatorer til at inds\u00e6tte tuple i den rigtige sektion. Disse funktioner kunne v\u00e6re meget langsomme at udf\u00f8re. Med deklarativ sektionering, der blev tilf\u00f8jet i version 10, fungerede det meget hurtigere.<\/p>\n

Ved at bruge en sektioneret tabel med 100 sektioner kan vi vurdere ydeevnen ved at indl\u00e6se 10 millioner r\u00e6kker i en tabel fra 1 BIGINT-kolonne og 5 INT-kolonner.<\/p>\n

\"PostgreSQL
\n
\nYdeevnen af foresp\u00f8rgslen til denne tabel for at finde en indekseret post og udf\u00f8re DML for at manipulere en post (ved kun at bruge 1 processor):<\/p>\n

\"PostgreSQL
\n
\nHer ser vi, at ydeevnen af hver operation er steget betydeligt efter PG 9.6. Foresp\u00f8rgsler SELECT<\/code> ser meget bedre ud, is\u00e6r dem, der kan ekskludere mange sektioner under foresp\u00f8rgselsplanl\u00e6gning. Det betyder, at planl\u00e6ggeren kan springe det meste af arbejdet over, som det skulle have gjort tidligere. For eksempel bliver der ikke l\u00e6ngere bygget stier til un\u00f8dvendige sektioner.<\/p>\n

Konklusion<\/b><\/p>\n

Sektionering af tabeller begynder at blive en meget kraftfuld funktion i PostgreSQL. Det g\u00f8r det muligt at hurtigt levere data online og overf\u00f8re dem offline uden at vente p\u00e5 afslutningen af langsomme massive DML-operationer.<\/b>Det betyder ogs\u00e5, at relaterede data kan opbevares sammen, hvilket g\u00f8r det muligt at f\u00e5 adgang til de n\u00f8dvendige data langt mere effektivt. Forbedringerne, der blev lavet i denne version, ville ikke have v\u00e6ret mulige uden udviklerne, anmelderne og committerne, der utr\u00e6tteligt arbejdede p\u00e5 alle disse funktioner.
\nTak til dem alle! PostgreSQL 11 ser simpelthen fantastisk ud!<\/b><\/p>\n

Her er en kort, men temmelig interessant artikel. Del gerne kommentarer, og glem ikke at tilmelde dig \u00e5bent hus<\/a><\/noindex>, hvor programmet for kurset vil blive pr\u00e6senteret i detaljer.
\n
Kilde: 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: Evolution of Partitioning from Postgres 9.6 to Postgres 11 | ProHoster","description":"God fredag til alle!","canonical_url":"https:\/\/prohoster.info\/da\/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":"da_DK","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\/da\/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\/da\/wp-json\/wp\/v2\/posts\/33872","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/da\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/da\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/da\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/da\/wp-json\/wp\/v2\/comments?post=33872"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/da\/wp-json\/wp\/v2\/posts\/33872\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/da\/wp-json\/wp\/v2\/media\/25538"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/da\/wp-json\/wp\/v2\/media?parent=33872"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/da\/wp-json\/wp\/v2\/categories?post=33872"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/da\/wp-json\/wp\/v2\/tags?post=33872"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}