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

Ilusat reedet k\u00f5igile! Aeg kursuse k\u00e4ivitamiseks l\u00e4heneb kiiresti. \u201eSuhtelised andmebaasid\u201c<\/a><\/noindex>, seet\u00f5ttu jagame t\u00e4na veel \u00fche kasuliku materjali t\u00f5lget teema kohta.<\/p>\n

Arendamise protsessis PostgreSQL 11<\/a><\/noindex> tehti muljetavaldav t\u00f6\u00f6 tabelite sektsioneerimise parandamiseks. Tabelite sektsioneerimine<\/b> on funktsioon, mis on PostgreSQLis eksisteerinud piisavalt kaua, kuid kui nii v\u00f5ib \u00f6elda, polnud see sisuliselt olemas kuni 10. versioonini, kus see muutus t\u00f5eliselt kasulikuks funktsiooniks. Me oleme varem \u00f6elnud, et tabelite p\u00e4rimine on meie sektsioneerimise rakendus, ja see on t\u00f5si. Ainult see meetod sundis teid tegema suurema osa t\u00f6\u00f6st k\u00e4sitsi. N\u00e4iteks kui soovisite, et ridade sisestamine toimuks sektsioonides INSERT-idel, pidite seadistama k\u00e4ivitajad, et see teie eest teha. P\u00e4rimisega sektsioneerimine oli v\u00e4ga aeglane ja keeruline, et arendada selle \u00fcle t\u00e4iendavaid funktsioone.<\/a><\/noindex><\/p>\n

PostgreSQL 10-s n\u00e4gime \u201edeklaratiivse sektsioneerimise\u201c tekkimist \u2013 funktsiooni, mis pidi lahendama paljusid probleeme, mis olid vanema p\u00e4rimismeetodi kasutamise korral lahendamatu. See t\u00f5i endaga kaasa palju v\u00f5imsama t\u00f6\u00f6riista, mis v\u00f5imaldab meil andmeid horisontaalselt jagada!<\/p>\n

Funktsioonide v\u00f5rdlemine<\/b><\/p>\n

PostgreSQL 11-s ilmus muljetavaldav hulk uusi funktsioone, mis aitavad suurendada sooritusv\u00f5imet ja muuta sektsioneeritud tabelid rakendustele l\u00e4bipaistvamaks.<\/p>\n

\"PostgreSQL
\n\"PostgreSQL
\n\"PostgreSQL
\n1. Kasutades piiravaid erandeid
\n2. Lisab ainult s\u00f5lmed
\n3. Ainult sektsioneeritud tabel, mis viitab sektsioneerimata tabelile
\n4. Indeksid peavad sisaldama k\u00f5iki sektsiooni v\u00f5tmeveeru
\n5. Sektsiooni piirangud m\u00f5lemal pool peavad kokku langema<\/i><\/p>\n

T\u00f5husus<\/b><\/p>\n

Siin on meil ka h\u00e4id uudiseid! Lisatud on uus meetod sektsioonide eemaldamiseks<\/a><\/noindex>. See uus algoritm suudab m\u00e4\u00e4rata sobivad sektsioonid, vaadates l\u00e4bi p\u00e4ringu tingimuse KUS<\/code>. Eelmise algoritmi puhul kontrolliti seevastu iga sektsiooni, et m\u00e4\u00e4rata, kas see vastab tingimusele KUS<\/code>. See t\u00f5i kaasa planeerimise aja lisanduse, kui sektsioonide arv kasvas.<\/p>\n

Versioonis 9.6, p\u00e4randitega sektsioneerimise korral, toimus ridade marsruutimine sektsioonis tavaliselt triggervaba funktsiooni kirjutamise teel, mis sisaldas IF-lauselise seeria, et sisestada rida \u00f5iges sektsioonis. Need funktsioonid v\u00f5isid t\u00e4itmisel olla v\u00e4ga aeglased. Deklaratiivse sektsioneerimise lisandumisega versioonis 10 t\u00f6\u00f6tas see palju kiiremini.<\/p>\n

Kasutades sektsioneeritud tabelit, kus on 100 sektsiooni, saame hinnata 10 miljoni rea laadimise sooritust \u00fchest BIGINT veerust ja 5 INT veerust.<\/p>\n

\"PostgreSQL
\n
\nSelle tabeli p\u00e4ringu soorituse kohta, et leida \u00fcks indekseeritud kirje ja teha DML, et manipuleerida \u00fche kirjega (kasutades ainult 1 protsessorit):<\/p>\n

\"PostgreSQL
\n
\nSiin n\u00e4eme, et iga operatsiooni sooritus on p\u00e4rast PG 9.6 oluliselt paranenud. P\u00e4ringud SELECT<\/code> n\u00e4evad v\u00e4lja palju paremad, eriti need, mis suudavad p\u00e4ringu planeerimise ajal v\u00e4listada mitmeid sektsioone. See t\u00e4hendab, et planeerija v\u00f5ib j\u00e4tta suure osa t\u00f6\u00f6st vahele, mida ta varem tegema pidi. N\u00e4iteks ei ehitata enam teid mittevajalikele sektsioonidele.<\/p>\n

Kokkuv\u00f5te<\/b><\/p>\n

Tabelite sektsioneerimine hakkab PostgreSQL-is muutuma v\u00e4ga v\u00f5imsaks funktsiooniks. See v\u00f5imaldab kiiresti andmeid online'ist v\u00e4lja tuua ja viia need offline'i, ootamata aeglaste massiliste DML operatsioonide l\u00f5puleviimist.<\/b>. See t\u00e4hendab ka, et seotud andmeid saab hoida koos, st vajalikule andmele p\u00e4\u00e4seb oluliselt efektiivsemalt ligi. Selle versiooni tegemised oleksid olnud v\u00f5imatud arendajate, \u00fclevaatajate ja komponendite\u00e4nukate, kes pidevalt t\u00f6\u00f6tasid k\u00f5igi nende funktsioonide kallal.
\nAit\u00e4h neile k\u00f5igile! PostgreSQL 11 paistab t\u00f5eliselt fantastiline!<\/b><\/p>\n

Siin on l\u00fchike, kuid \u00fcsna huvitav artikkel. Jagage oma kommentaare ja \u00e4rge unustage registreeruda avatud uste p\u00e4ev<\/a><\/noindex>, mille raames tutvustatakse kursuse programmi \u00fcksikasjalikult.
\n
Allikas: 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\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: Sektsioneerimise areng Postgres 9.6-st Postgres 11-ni | ProHoster","description":"Head reedet k\u00f5igile!","canonical_url":"https:\/\/prohoster.info\/et\/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":"et_EE","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\/et\/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\/et\/wp-json\/wp\/v2\/posts\/33872","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/comments?post=33872"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/posts\/33872\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/media\/25538"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/media?parent=33872"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/categories?post=33872"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/tags?post=33872"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}