{"id":37975,"date":"2019-10-31T22:20:56","date_gmt":"2019-10-31T19:20:56","guid":{"rendered":"https:\/\/prohoster.info\/blog\/apgrejd-dlya-lenivyh-kak-postgresql-12-povyshaet-proizvoditelnost\/"},"modified":"2019-10-31T22:20:56","modified_gmt":"2019-10-31T19:20:56","slug":"apgrejd-dlya-lenivyh-kak-postgresql-12-povyshaet-proizvoditelnost","status":"publish","type":"post","link":"https:\/\/prohoster.info\/en\/blog\/administrirovanie\/apgrejd-dlya-lenivyh-kak-postgresql-12-povyshaet-proizvoditelnost","title":{"rendered":"Upgrade for the lazy: how PostgreSQL 12 boosts performance","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

\"Upgrade<\/p>\n

PostgreSQL 12<\/a><\/noindex>, the latest version of the \"best open-source relational database in the world\" is set to release in a couple of weeks (if all goes according to plan). This follows the usual schedule \u2014 a new version with a wealth of new features comes out once a year, and, frankly, it\u2019s impressive. That\u2019s why I became an active member of the PostgreSQL community.<\/p>\n

<\/p>\n

In my opinion, unlike previous releases, PostgreSQL 12 doesn't include one or two revolutionary features (like partitioning or query parallelism). I jokingly mentioned that the main highlight of PostgreSQL 12 is its greater stability. And isn\u2019t that what you need when managing your business\u2019s critical data?<\/p>\n

<\/p>\n

But PostgreSQL 12 doesn\u2019t stop there: with new features and enhancements, applications will perform better, and all you need to do is upgrade!<\/em><\/p>\n

<\/p>\n

(Well, maybe also rebuild the indexes, but this release makes it less daunting than we\u2019re used to.)<\/p>\n

<\/a><\/noindex><\/p>\n

It will be great to upgrade PostgreSQL and immediately enjoy significant improvements without extra hassle. A few years ago, I analyzed the update from PostgreSQL 9.4 to PostgreSQL 10 and saw how much faster the application became thanks to improved query parallelism in PostgreSQL 10. And, most importantly, it required almost nothing from me (just setting a configuration parameter. max_parallel_workers<\/code>).<\/p>\n

<\/p>\n

Agree that it\u2019s convenient when applications work better right after an upgrade. And we really strive to please our users, as the number of PostgreSQL users continues to grow.<\/p>\n

<\/p>\n

And how will a simple upgrade to PostgreSQL 12 make you happy? Let me tell you.<\/p>\n

<\/p>\n

Significant improvements in indexing<\/h3>\n

<\/p>\n

Without indexing, a database won't get far. How else can you quickly find information? The foundational indexing system of PostgreSQL is called B-tree<\/a><\/noindex>. This type of index is optimized for storage systems.<\/p>\n

<\/p>\n

We simply use the statement CREATE INDEX ON some_table (some_column)<\/code>, and PostgreSQL does the heavy lifting to keep the index up to date while we continuously insert, update, and delete values. Everything works seamlessly, as if by magic.<\/p>\n

<\/p>\n

But PostgreSQL indexes have one issue \u2014 they bloat.<\/a><\/noindex> and take up unnecessary disk space, while the performance of data extraction and updates decreases. By 'bloat' I mean ineffective maintenance of the index structure. This may or may not be related to dead tuples removed VACUUM<\/a><\/noindex> (thanks for the information to Peter Geoghegan (Peter Geoghegan<\/a><\/noindex>)). Index bloat is especially noticeable in workloads where the index is actively modified.<\/p>\n

<\/p>\n

PostgreSQL 12 significantly improves the performance of B-tree indexes, and experiments with TPC-C type tests have shown that space is now used, on average, 40% less. We now spend less time not only maintaining B-tree indexes (i.e., on write operations) but also on data extraction, as the indexes have become much smaller.<\/p>\n

<\/p>\n

Applications that actively update their tables \u2014 typically OLTP applications (online transaction processing<\/a><\/noindex>) \u2014 will utilize disk more efficiently and handle requests better. The more space available on the disk, the more room the database has for growth without upgrading the infrastructure.<\/p>\n

<\/p>\n

Some upgrade strategies require rebuilding B-tree indexes to take advantage of these benefits (for example, pg_upgrade<\/a><\/noindex> does not automatically rebuild indexes). In previous versions of PostgreSQL, rebuilding large indexes in tables resulted in significant downtime, as changes could not be made during this period. However, PostgreSQL 12 has another great feature: you can now rebuild indexes in parallel with the command REINDEX CONCURRENTLY<\/a><\/noindex>, so as to completely avoid downtime.<\/p>\n

<\/p>\n

PostgreSQL 12 also includes other improvements in index infrastructure. Another area where magic was involved is write-ahead logging<\/a><\/noindex>, also known as WAL (write-ahead log). The write-ahead log records every transaction in PostgreSQL in case of failure and replication. Applications use it for archiving and point-in-time recovery<\/a><\/noindex>. Of course, the write-ahead log is written to disk, which can affect performance.<\/p>\n

<\/p>\n

In PostgreSQL 12, the costs of WAL records created by GiST, GIN, and SP-GiST indexes during index building have been reduced. This offers several tangible benefits: WAL records take up less disk space, and data is restored faster, for example, during recovery from a failure or point-in-time recovery. If you are using such indexes in your applications (for instance, geospatial applications based on PostGIS heavily utilize the GiST index), this is another feature that significantly enhances performance with no effort on your part.<\/p>\n

<\/p>\n

Partitioning - more, better, faster<\/h3>\n

<\/p>\n

PostgreSQL 10 introduced declarative partitioning<\/a><\/noindex>. In PostgreSQL 11, it became much easier to use. In PostgreSQL 12, you can change the scale of partitions.<\/p>\n

<\/p>\n

In PostgreSQL 12, the performance of the partitioning system has significantly improved, especially when there are thousands of partitions in a table. For example, if a query only accesses a few partitions in a table with thousands, it will execute much faster. Performance has improved not just for those types of queries. You will also notice how INSERT operations have sped up in tables with many partitions.<\/p>\n

<\/p>\n

Data entry via COPY<\/a><\/noindex> \u2014 by the way, this is a great way for bulk data loading<\/a><\/noindex> and here is an example of the JSON method<\/a><\/noindex> \u2014 has also become more efficient for partitioned tables in PostgreSQL 12. The COPY command was already fast, but in PostgreSQL 12, it\u2019s even faster.<\/p>\n

<\/p>\n

Thanks to these advantages, PostgreSQL can store even larger datasets, and retrieving them has become easier. And it requires no effort on your part. If your application has many partitions, for example, if it writes time series data, a simple upgrade will greatly improve its performance.<\/p>\n

<\/p>\n

And although this improvement doesn't quite fall into the category of 'upgraded and rejoice', in PostgreSQL 12 you can create foreign keys that reference partitioned tables, making partitioning work a delight.<\/p>\n

<\/p>\n

WITH queries have improved significantly<\/h3>\n

<\/p>\n

Once a patch has been applied for common table expressions<\/a><\/noindex> (CTEs, also known as WITH queries), I couldn\u2019t wait to write an article about how ecstatic application developers using PostgreSQL were. This is one of those features that will speed up applications. If, of course, you are using CTEs.<\/a><\/noindex>.<\/p>\n

<\/p>\n

I often notice that newcomers to SQL love to use CTEs: if you write them in a certain way, you can really feel like you're writing an imperative program. Personally, I enjoyed rewriting these queries to make do with without<\/em> CTEs and improve performance. Now, everything is different.<\/p>\n

<\/p>\n

PostgreSQL 12 allows embedding a certain type of CTE without side effects (SELECT<\/code>), which is used only once closer to the end of the query. If I kept statistics on the CTE queries I rewrote, most of them would fall into this category. This helps developers write clear code that now also runs quickly.<\/p>\n

<\/p>\n

Moreover, PostgreSQL 12 optimizes SQL execution on its own, you won't have to do anything. And while it\u2019s probably not necessary for me to optimize such queries now, it\u2019s great that PostgreSQL continues to work on query optimization.<\/p>\n

<\/p>\n

Just-in-Time (JIT) is now enabled by default.<\/h3>\n

<\/p>\n

In PostgreSQL 12 systems with support for LLVM<\/a><\/noindex> JIT compilation is enabled by default. First, you get support for JIT<\/a><\/noindex> for certain internal operations, and secondly, queries with expressions (the simplest example is x + y) in the select lists (which follow your SELECT), aggregates, expressions with WHERE clauses, and others can utilize JIT to boost performance.<\/p>\n

<\/p>\n

Since JIT is enabled by default in PostgreSQL 12, performance will improve on its own, but I recommend testing the application in PostgreSQL 11, where JIT first appeared, to measure query performance and see if any adjustments are needed.<\/p>\n

<\/p>\n

But what about the other new features of PostgreSQL 12?<\/h3>\n

<\/p>\n

PostgreSQL 12 has plenty of new cool features\u2014from the ability to explore JSON data using standard SQL\/JSON path expressions to multi-factor authentication with the parameter clientcert=verify-full<\/code>, generated columns, and much more. Enough for a separate post.<\/p>\n

<\/p>\n

Like PostgreSQL 10, PostgreSQL 12 will enhance overall performance right after the upgrade. Of course, you may have your own path\u2014test the application under similar conditions in a production system before enabling the improvements, as I did with PostgreSQL 10. Even if PostgreSQL 12 is already more stable than I anticipated, don't neglect quality testing of applications before releasing them into production.<\/p>\n

Source: habr.com<\/a><\/p>","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"excerpt":{"rendered":"

PostgreSQL 12, \u043f\u043e\u0441\u043b\u0435\u0434\u043d\u044f\u044f \u0432\u0435\u0440\u0441\u0438\u044f \u00ab\u043b\u0443\u0447\u0448\u0435\u0439 \u0432 \u043c\u0438\u0440\u0435 \u0440\u0435\u043b\u044f\u0446\u0438\u043e\u043d\u043d\u043e\u0439 \u0431\u0430\u0437\u044b \u0434\u0430\u043d\u043d\u044b\u0445 \u0441 \u043e\u0442\u043a\u0440\u044b\u0442\u044b\u043c \u0438\u0441\u0445\u043e\u0434\u043d\u044b\u043c \u043a\u043e\u0434\u043e\u043c\u00bb, \u0432\u044b\u0445\u043e\u0434\u0438\u0442 \u0447\u0435\u0440\u0435\u0437 \u043f\u0430\u0440\u0443-\u0442\u0440\u043e\u0439\u043a\u0443 \u043d\u0435\u0434\u0435\u043b\u044c (\u0435\u0441\u043b\u0438 \u0432\u0441\u0435 \u043f\u043e\u0439\u0434\u0435\u0442 \u043f\u043e \u043f\u043b\u0430\u043d\u0443). \u042d\u0442\u043e \u0441\u043e\u043e\u0442\u0432\u0435\u0442\u0441\u0442\u0432\u0443\u0435\u0442 \u043e\u0431\u044b\u0447\u043d\u043e\u043c\u0443 \u0440\u0430\u0441\u043f\u0438\u0441\u0430\u043d\u0438\u044e \u2014 \u043d\u043e\u0432\u0430\u044f \u0432\u0435\u0440\u0441\u0438\u044f \u0441 \u0443\u0439\u043c\u043e\u0439 \u043d\u043e\u0432\u044b\u0445 \u0432\u043e\u0437\u043c\u043e\u0436\u043d\u043e\u0441\u0442\u0435\u0439 \u0432\u044b\u0445\u043e\u0434\u0438\u0442 \u0440\u0430\u0437 \u0432 \u0433\u043e\u0434, \u0438, \u0447\u0435\u0441\u0442\u043d\u043e \u0433\u043e\u0432\u043e\u0440\u044f, \u044d\u0442\u043e \u0432\u043f\u0435\u0447\u0430\u0442\u043b\u044f\u0435\u0442. \u041f\u043e\u044d\u0442\u043e\u043c\u0443 \u044f \u0438 \u0441\u0442\u0430\u043b \u0430\u043a\u0442\u0438\u0432\u043d\u044b\u043c \u0447\u043b\u0435\u043d\u043e\u043c \u0441\u043e\u043e\u0431\u0449\u0435\u0441\u0442\u0432\u0430 PostgreSQL. \u041f\u043e-\u043c\u043e\u0435\u043c\u0443, \u0432 \u043e\u0442\u043b\u0438\u0447\u0438\u0435 \u043e\u0442 […]<\/p>\n","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"author":1,"featured_media":28500,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[688],"tags":[],"class_list":["post-37975","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\n","aioseo_head_json":{"title":"\ud83e\udd47An Upgrade for the Lazy: How PostgreSQL 12 Increases Performance | ProHoster","description":"","canonical_url":"https:\/\/prohoster.info\/en\/blog\/administrirovanie\/apgrejd-dlya-lenivyh-kak-postgresql-12-povyshaet-proizvoditelnost","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"en_US","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\udd47\u0410\u043f\u0433\u0440\u0435\u0439\u0434 \u0434\u043b\u044f \u043b\u0435\u043d\u0438\u0432\u044b\u0445: \u043a\u0430\u043a PostgreSQL 12 \u043f\u043e\u0432\u044b\u0448\u0430\u0435\u0442 \u043f\u0440\u043e\u0438\u0437\u0432\u043e\u0434\u0438\u0442\u0435\u043b\u044c\u043d\u043e\u0441\u0442\u044c | ProHoster","og:url":"https:\/\/prohoster.info\/en\/blog\/administrirovanie\/apgrejd-dlya-lenivyh-kak-postgresql-12-povyshaet-proizvoditelnost","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-31T19:20:56+00:00","article:modified_time":"2019-10-31T19:20:56+00:00","article:publisher":"https:\/\/www.facebook.com\/prohoster","article:author":"https:\/\/www.facebook.com\/prohoster"},"aioseo_meta_data":{"post_id":"37975","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-23 19:59:19","breadcrumb_settings":null,"limit_modified_date":false,"reviewed_by":null,"ai":null,"created":"2021-03-01 01:17:22","updated":"2026-01-23 19:59:19","focus_keyword":null,"additional_keywords":null,"truseo_locale":null},"gt_translate_keys":[{"key":"link","format":"url"}],"_links":{"self":[{"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/posts\/37975","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/comments?post=37975"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/posts\/37975\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/media\/28500"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/media?parent=37975"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/categories?post=37975"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/tags?post=37975"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}