{"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: Sektsioonimise evolutsioon Postgres 9.6-lt Postgres 11-le","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Head nagu p\u00e4ev! Aeg kursuse k\u00e4ivitamiseks on j\u00e4rjest v\u00e4hem. Relationaalsed andmebaasid<\/a><\/noindex>, seega jagame t\u00e4na t\u00f5lget veel \u00fchest kasulikust materjalist selle teema kohta.<\/p>\n

Arendusprotsessis PostgreSQL 11<\/a><\/noindex> on tehtud muljetavaldavat t\u00f6\u00f6d tabelite sektsioneerimise parandamiseks. Tabelite sektsioneerimine<\/b> on funktsioon, mis on PostgreSQL-is eksisteerinud juba pikka aega, kuid mille sisu ei olnud kriitiliselt funktsionaalne enne versiooni 10, kus see t\u00f5epoolest kasulikuks osutus. Oleme varasemalt v\u00e4itnud, et tabelite p\u00e4randamine on meie teostus sektsioneerimist, ja see on t\u00f5si. Ainult see meetod n\u00f5udis, et suur osa t\u00f6\u00f6st tehti k\u00e4sitsi. N\u00e4iteks, kui soovisite, et kandeid sisestatakse sektsioonidesse INSERT-ide ajal, pidite seadistama triggereid, et seda teie eest teha. P\u00e4randamisega sektsioneerimine oli v\u00e4ga aeglane ja keeruline, et arendada lisafunktsioone selle peale.<\/a><\/noindex><\/p>\n

PostgreSQL 10 t\u00f5i endaga kaasa \u201edeklareeriva sektsioneerimise\u201c, mis on funktsioon, mis on loodud lahendama paljusid probleeme, mis varem olid vanade p\u00e4randimeetodite kasutamisel lahendamatud. See viis palju v\u00f5imsama t\u00f6\u00f6riista v\u00e4ljat\u00f6\u00f6tamiseni, mis v\u00f5imaldab meil andmeid horisontaalselt jagada!<\/p>\n

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

PostgreSQL 11 t\u00f5i kaasa muljetavaldava hulga uusi funktsioone, mis aitavad suurendada j\u00f5udlust 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 tabeli jaoks, mis viitab mitte-sektsioneeritud tabelile
\n4. Indeksid peavad sisaldama k\u00f5iki sektsiooni v\u00f5ti-sambaid
\n5. Piirangud sektsiooni m\u00f5lemal k\u00fcljel peavad olema samad<\/i><\/p>\n

Tootlikkus<\/b><\/p>\n

Siin on meil ka h\u00e4id uudiseid! Uus meetod sektsioonide eemaldamiseks<\/a><\/noindex>. See uus algoritm suudab tuvastada sobivad sektsioonid, vaadates p\u00e4ringu tingimust WHERE<\/code>. Eelmine algoritm kontrollis aga igat sektsiooni, et m\u00e4\u00e4rata, kas see vastab tingimusele. WHERE<\/code>. See t\u00f5i kaasa t\u00e4iendava planeerimise aja suurenemise sektsioonide arvu suurenedes.<\/p>\n

Versioonis 9.6, kasutades p\u00e4rimisega sektsioonimist, toimus ridade marsruutimine sektsioonides tavaliselt triggereerimisfunktsiooni kirjutamise kaudu, mis sisaldas IF-lauseid ridade sisestamiseks \u00f5igetesse sektsioonidesse. Need funktsioonid v\u00f5isid olla t\u00f6\u00f6tluses v\u00e4ga aeglased. Deklaratiivse sektsioonimise lisamisega versioonis 10 hakkas see t\u00f6\u00f6tama palju kiiremini.<\/p>\n

Kasutades sektsioonitud tabelit, kus on 100 sektsiooni, saame hinnata 10 miljoni rea laadimise tulemuslikkust tabelisse, kus on 1 BIGINT veerg ja 5 INT veergu.<\/p>\n

\"PostgreSQL
\n
\nSelle tabeli p\u00e4ringu tulemuslikkus \u00fche indekseeritud kirje otsimisel ja DML-i t\u00e4itmisel \u00fche kirje manipuleerimiseks (kasutades ainult 1 protsessorit):<\/p>\n

\"PostgreSQL
\n
\nSiin n\u00e4eme, et iga operatsiooni tulemuslikkus on p\u00e4rast PG 9.6 oluliselt suurenenud. P\u00e4ringud SELECT<\/code> n\u00e4evad palju paremad v\u00e4lja, eriti need, mis suudavad plaanimise k\u00e4igus mitmeid sektsioone v\u00e4lja j\u00e4tta. See t\u00e4hendab, et planeerija saab suure osa t\u00f6\u00f6st vahele j\u00e4tta, mida ta varem tegema pidi. N\u00e4iteks ei ehitata enam teid tarbetutele 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 kuvada ja neid offline'iks t\u00f5lkida, oodates, et aeglased massilised DML-operatsioonid l\u00f5petatakse.<\/b>See t\u00e4hendab ka, et seotud andmed v\u00f5ivad olla koos, mis t\u00e4hendab, et vajalikule infole on oluliselt t\u00f5husam juurdep\u00e4\u00e4s. Selle versiooni parandused ei oleks olnud v\u00f5imalikud ilma arendajate, \u00fclevaatajate ja kommitteriteta, kes on nende k\u00f5igi funktsioonide nimel v\u00e4simatult t\u00f6\u00f6tanud.
\nT\u00e4name neid k\u00f5iki! PostgreSQL 11 n\u00e4eb lihtsalt fantastiline v\u00e4lja!<\/b><\/p>\n

Siin on l\u00fchike, kuid \u00fcsna huvitav artikkel. Jagage oma kommentaare ja \u00e4rge unustage registreeruda avatud uste p\u00e4ev<\/a><\/noindex>, kus kursuse programmi \u00fcksikasjad esitletakse.
\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: Postgres 9.6-st Postgres 11-ni partitsioneerimise evolutsioon | ProHoster","description":"Headset k\u00f5igile! Aeg kursuse \"Relaatsioonilised andmebaasis\u00fcsteemid\" k\u00e4ivitamiseks hakkab l\u00f5ppema, seega t\u00e4na jagame veel \u00fchte kasulikku materjali teema kohta. PostgreSQL 11 arendamise k\u00e4igus on saavutatud muljetavaldavaid edusamme tabelite partitsioneerimise parandamisel. Tabelite partitsioneerimine on funktsioon, mis on PostgreSQL-is olemas olnud juba pikka aega, kuid mis on, kui nii v\u00f5ib \u00f6elda, p\u00f5him\u00f5tteliselt","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! \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","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"},"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}]}}