{"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\/sv\/blog\/administrirovanie\/postgresql-11-evolyutsiya-sektsionirovaniya-ot-postgres-9-6-do-postgres-11","title":{"rendered":"PostgreSQL 11: Evolutionen av partitionering fr\u00e5n Postgres 9.6 till Postgres 11","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

\u00d6nskar alla en fin fredag! Det \u00e4r mindre och mindre tid kvar till lanseringen av kursen Relational DBMS<\/a><\/noindex>, d\u00e4rf\u00f6r delar vi idag med oss av en \u00f6vers\u00e4ttning av ytterligare ett anv\u00e4ndbart material i \u00e4mnet.<\/p>\n

Under utvecklingen PostgreSQL 11<\/a><\/noindex> gjordes imponerande arbeten f\u00f6r att f\u00f6rb\u00e4ttra sektioneringen av tabeller. Sektionering av tabeller<\/b> \u2014 det h\u00e4r \u00e4r en funktion som funnits i PostgreSQL under l\u00e5ng tid, men om man ska uttrycka det s\u00e5, existerade den i princip inte f\u00f6rr\u00e4n version 10, d\u00e4r den blev en mycket anv\u00e4ndbar funktion. Tidigare h\u00e4vdade vi att arv av tabeller \u00e4r v\u00e5r implementering av sektionering, och det st\u00e4mmer. Men den h\u00e4r metoden tvingade er att g\u00f6ra det mesta av arbetet manuellt. Till exempel, om ni ville att tupler skulle s\u00e4ttas in i sektioner under INSERT, beh\u00f6vde ni konfigurera triggers f\u00f6r att g\u00f6ra det f\u00f6r er. Sektionering med hj\u00e4lp av arv var mycket l\u00e5ngsam och sv\u00e5r att utveckla extra funktioner ovanp\u00e5.<\/a><\/noindex><\/p>\n

I PostgreSQL 10 s\u00e5g vi f\u00f6delsen av den \"deklarativa sektioneringen\" \u2014 en funktion avsedd att l\u00f6sa m\u00e5nga problem som var ol\u00f6sliga med den gamla metoden med arv. Detta ledde till uppkomsten av ett mycket kraftfullare verktyg som till\u00e5ter oss att dela upp data horisontellt!<\/p>\n

J\u00e4mf\u00f6relse av funktioner<\/b><\/p>\n

I PostgreSQL 11 introducerades en imponerande upps\u00e4ttning nya funktioner som hj\u00e4lper till att \u00f6ka prestandan och g\u00f6ra sektionerade tabeller mer transparenta f\u00f6r applikationer.<\/p>\n

\"PostgreSQL
\n\"PostgreSQL
\n\"PostgreSQL
\n1. Anv\u00e4nder begr\u00e4nsande undantag
\n2. L\u00e4gger till endast noder
\n3. Endast f\u00f6r sektionerad tabell, som h\u00e4nvisar till en icke-sektionerad
\n4. Index m\u00e5ste inneh\u00e5lla alla nyckelskolumner i sektionen
\n5. Begr\u00e4nsningar p\u00e5 sektionen p\u00e5 b\u00e5da sidor m\u00e5ste \u00f6verensst\u00e4mma<\/i><\/p>\n

Performance<\/b><\/p>\n

H\u00e4r har vi ocks\u00e5 goda nyheter! En ny metod f\u00f6r borttagning av sektioner<\/a><\/noindex>. Denna nya algoritm kan identifiera l\u00e4mpliga sektioner genom att granska fr\u00e5gans villkor D\u00c4R<\/code>. Den tidigare algoritmen, \u00e5 sin sida, kontrollerade varje sektion f\u00f6r att avg\u00f6ra om den kunde uppfylla villkoret D\u00c4R<\/code>. Detta ledde till ytterligare \u00f6kning av planeringstiden n\u00e4r antalet sektioner v\u00e4xte.<\/p>\n

I version 9.6, n\u00e4r sektionering med hj\u00e4lp av arv anv\u00e4ndes, genomf\u00f6rdes ruttening av tuples i sektionen vanligtvis genom att skriva en triggerfunktion som inneh\u00f6ll en serie IF-satser f\u00f6r att infoga tuple i r\u00e4tt sektion. Dessa funktioner kunde vara mycket l\u00e5ngsamma vid k\u00f6rning. Med deklarativ sektionering som lades till i version 10, b\u00f6rjade detta fungera mycket snabbare.<\/p>\n

Genom att anv\u00e4nda en sektionerad tabell med 100 sektioner kan vi uppskatta prestandan vid inladdning av 10 miljoner rader i tabellen fr\u00e5n 1 kolumn BIGINT och 5 kolumner INT.<\/p>\n

\"PostgreSQL
\n
\nPrestandan f\u00f6r att fr\u00e5ga denna tabell f\u00f6r att hitta en indexerad post och utf\u00f6ra DML f\u00f6r att manipulera en post (anv\u00e4nda endast 1 processor):<\/p>\n

\"PostgreSQL
\n
\nH\u00e4r ser vi att prestandan f\u00f6r varje operation har \u00f6kat avsev\u00e4rt efter PG 9.6. F\u00f6rfr\u00e5gningarna V\u00c4LJ<\/code> ser mycket b\u00e4ttre ut, s\u00e4rskilt de som kan utesluta flera sektioner under planeringen av f\u00f6rfr\u00e5gningar. Detta inneb\u00e4r att planl\u00e4ggaren kan hoppa \u00f6ver en stor del av arbetet som den tidigare var tvungen att g\u00f6ra. Till exempel byggs det inte l\u00e4ngre v\u00e4gar f\u00f6r on\u00f6diga sektioner.<\/p>\n

Slutsats<\/b><\/p>\n

Sektionering av tabeller b\u00f6rjar bli en mycket kraftfull funktion i PostgreSQL. Det g\u00f6r det m\u00f6jligt att snabbt exportera data online och konvertera dem till offline, utan att v\u00e4nta p\u00e5 att l\u00e5ngsamma massoperationer DML ska slutf\u00f6ras.<\/b>. Detta inneb\u00e4r ocks\u00e5 att relaterade data kan lagras tillsammans, vilket g\u00f6r att n\u00f6dv\u00e4ndiga data kan n\u00e5s mycket effektivare. F\u00f6rb\u00e4ttringarna som gjorts i denna version skulle inte ha varit m\u00f6jliga utan utvecklarna, granskare och bidragsgivare som of\u00f6rtr\u00f6ttligt arbetade med alla dessa funktioner.
\nTack till dem alla! PostgreSQL 11 ser helt fantastiskt ut!<\/b><\/p>\n

S\u00e5 h\u00e4r \u00e4r en kort, men ganska intressant artikel. Dela g\u00e4rna kommentarer och gl\u00f6m inte att registrera dig f\u00f6r \u00f6ppen hus dag<\/a><\/noindex>, d\u00e4r programmet f\u00f6r kursen kommer att redovisas i detalj.
\n
K\u00e4lla: 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: Evolutionen av sektionering fr\u00e5n Postgres 9.6 till Postgres 11 | ProHoster","description":"Ha en fantastisk fredag alla!","canonical_url":"https:\/\/prohoster.info\/sv\/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":"sv_SE","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\/sv\/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\/sv\/wp-json\/wp\/v2\/posts\/33872","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/sv\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/sv\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/sv\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/sv\/wp-json\/wp\/v2\/comments?post=33872"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/sv\/wp-json\/wp\/v2\/posts\/33872\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/sv\/wp-json\/wp\/v2\/media\/25538"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/sv\/wp-json\/wp\/v2\/media?parent=33872"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/sv\/wp-json\/wp\/v2\/categories?post=33872"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/sv\/wp-json\/wp\/v2\/tags?post=33872"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}