{"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\/de\/blog\/administrirovanie\/postgresql-11-evolyutsiya-sektsionirovaniya-ot-postgres-9-6-do-postgres-11","title":{"rendered":"PostgreSQL 11: Die Evolution der Partitionierung von Postgres 9.6 bis Postgres 11","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Einen hervorragenden Freitag allerseits! Es bleibt immer weniger Zeit bis zum Start des Kurses \u201eRelationale DBMS\u201c<\/a><\/noindex>, deshalb teilen wir heute eine weitere n\u00fctzliche \u00dcbersetzung zu diesem Thema.<\/p>\n

Im Entwicklungsprozess PostgreSQL 11<\/a><\/noindex> wurde beeindruckende Arbeit zur Verbesserung der Tabellensektionierung geleistet. Partitionierung von Tabellen<\/b> \u2014 das ist eine Funktion, die in PostgreSQL schon lange existiert, aber, wenn man so will, bis zur Version 10 nicht wirklich nutzbar war, in der sie zu einer sehr n\u00fctzlichen Funktion wurde. Fr\u00fcher haben wir gesagt, dass die Tabellenvererbung unsere Implementierung der Sektionierung ist, und das stimmt. Allerdings mussten Sie dabei den gr\u00f6\u00dften Teil der Arbeit manuell erledigen. Wenn Sie beispielsweise wollten, dass Tuples w\u00e4hrend des INSERTs in Sektionen eingef\u00fcgt werden, mussten Sie Trigger einrichten, um dies f\u00fcr Sie zu erledigen. Die Sektionierung durch Vererbung war sehr langsam und schwierig, um zus\u00e4tzliche Funktionen dar\u00fcber hinaus zu entwickeln.<\/a><\/noindex><\/p>\n

In PostgreSQL 10 sahen wir die Geburt der \"deklarativen Sektionierung\" \u2014 eines Features, das viele Probleme l\u00f6st, die mit der alten Methode der Vererbung nicht gel\u00f6st werden konnten. Dies f\u00fchrte zu einem viel leistungsf\u00e4higeren Werkzeug, mit dem wir Daten horizontal strukturieren k\u00f6nnen!<\/p>\n

Vergleich der Features<\/b><\/p>\n

In PostgreSQL 11 gab es eine beeindruckende Reihe neuer Features, die helfen, die Leistung zu steigern und die sektionierten Tabellen f\u00fcr Anwendungen transparenter zu gestalten.<\/p>\n

\"PostgreSQL
\n\"PostgreSQL
\n\"PostgreSQL
\n1. Verwendung von beschr\u00e4nkenden Ausnahmen
\n2. F\u00fcgt nur Knoten hinzu
\n3. Nur f\u00fcr sektionierte Tabellen, die auf nicht sektionierte verweisen
\n4. Indizes m\u00fcssen alle Schl\u00fcssels\u00e4ulen der Sektion enthalten
\n5. Einschr\u00e4nkungen auf der Sektion auf beiden Seiten m\u00fcssen \u00fcbereinstimmen<\/i><\/p>\n

Leistung<\/b><\/p>\n

Hier haben wir auch gute Nachrichten! Eine neue Methode wurde hinzugef\u00fcgt zum Entfernen von Sektionen<\/a><\/noindex>. Dieser neue Algorithmus kann geeignete Sektionen bestimmen, indem er die Bedingung der Anfrage \u00fcberpr\u00fcft WHERE<\/code>. Der vorherige Algorithmus wiederum pr\u00fcfte jede Sektion, um festzustellen, ob sie der Bedingung entsprechen k\u00f6nnte WHERE<\/code>. Dies f\u00fchrte zu einer zus\u00e4tzlichen Erh\u00f6hung der Planungszeit mit wachsender Anzahl an Sektionen.<\/p>\n

In 9.6, with partitioning using inheritance, routing tuples in a partition was typically done by writing a trigger function that contained a series of IF statements to insert the tuple into the correct partition. These functions could be very slow in execution. With declarative partitioning added in version 10, this started to work much faster.<\/p>\n

Using a partitioned table with 100 partitions, we can estimate the performance of loading 10 million rows into the table from 1 BIGINT column and 5 INT columns.<\/p>\n

\"PostgreSQL
\n
\nThe performance of a query to this table for searching one indexed record and executing DML to manipulate one record (using only 1 processor):<\/p>\n

\"PostgreSQL
\n
\nHere we see that the performance of each operation has significantly increased since PG 9.6. Queries SELECT<\/code> look much better, especially those capable of excluding multiple partitions during query planning. This means that the planner can skip most of the work it had to do earlier. For example, paths for unnecessary partitions are no longer built.<\/p>\n

Fazit<\/b><\/p>\n

Table partitioning is becoming a very powerful feature in PostgreSQL. It allows for quick output of data online and their transition to offline without waiting for the completion of slow massive DML operations.<\/b>This also means that related data can be stored together, making access to the required data much more efficient. The improvements made in this version would not have been possible without the developers, reviewers, and committers who tirelessly worked on all these features.
\nThanks to them all! PostgreSQL 11 looks simply fantastic!<\/b><\/p>\n

Here's a short but quite interesting article. Share your comments, and don't forget to sign up for Tag der offenen T\u00fcr<\/a><\/noindex>, where the course program will be detailed.
\n
Quelle: 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: The Evolution of Partitioning from Postgres 9.6 to Postgres 11 | ProHoster","description":"Have a great Friday everyone!","canonical_url":"https:\/\/prohoster.info\/de\/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":"de_DE","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\/de\/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\/de\/wp-json\/wp\/v2\/posts\/33872","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/de\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/de\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/de\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/de\/wp-json\/wp\/v2\/comments?post=33872"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/de\/wp-json\/wp\/v2\/posts\/33872\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/de\/wp-json\/wp\/v2\/media\/25538"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/de\/wp-json\/wp\/v2\/media?parent=33872"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/de\/wp-json\/wp\/v2\/categories?post=33872"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/de\/wp-json\/wp\/v2\/tags?post=33872"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}