{"id":303307,"date":"2020-05-10T03:02:38","date_gmt":"2020-05-10T03:02:38","guid":{"rendered":"http:\/\/savepearlharbor.com\/?p=303307"},"modified":"-0001-11-30T00:00:00","modified_gmt":"-0001-11-29T21:00:00","slug":"","status":"publish","type":"post","link":"https:\/\/savepearlharbor.com\/?p=303307","title":{"rendered":"Parallelism in PostgreSQL: treatment of trees and conscience"},"content":{"rendered":"\n
<\/p>\n

Database scaling is a continually coming future. DBMS get improved and better scaled on hardware platforms, while the hardware platforms themselves increase the performance, number of cores, and memory \u2014 Achilles is trying to catch up with the turtle, but has not caught up yet. The database scaling challenge manifests itself in all its magnitude.<\/p>\n

Postgres Professional had to face the scaling problem not only theoretically, but also in practice: through their customers. Even more than once. It’s one of these real-life cases that this article
will discuss. <\/p>\n

Many thanks to Elena Indrupskaya for the translation. <\/p><\/blockquote>\n

<\/a>
PostgreSQL scales well on NUMA systems in the case of a single motherboard with multiple processors and multiple data buses. You can read about some optimizations here<\/a> and here<\/a>.<\/p>\n

However, there is another group of systems: they have several motherboards that exchange data using the interconnect and run one instance of the OS; for the user this design looks like a single machine. And although formally such systems can also be referred to NUMA, in essence, they are closer to supercomputers because access to the local memory of a node drastically differs from access to the memory of a neighboring node.<\/p>\n

The PostgreSQL community believes that the cause of the issues is the only instance of Postgres running on such architectures, and there is no systematic approach to resolving them yet. Designers of shared-memory oriented software initially presumed that access times to local and remote memory would be more or less comparable. When we work with many nodes, it doesn\u2019t make sense to rely on shared memory as on a fast communication channel: because of latency, it is much \u00abcheaper\u00bb to send a request to perform a certain action to the node where the data of interest is located than to send the data through the bus. So, cluster solutions are relevant for supercomputers and for multi-node systems in general.<\/p>\n

This does not mean that the combination of multi-node systems and the shared-memory architecture, which is typical for Postgres, should be trashed. After all, if Postgres processes spend most of their time performing complex computations locally, this architecture will be even more efficient. In our situation, the customer had already purchased a powerful multi-node server, and we had to resolve PostgreSQL issues on it.<\/p>\n

And the issues were serious: execution of simplest write queries (to change several field values in one row) took from a few minutes to an hour. These issues came out full force exactly due to a large number of cores and accordingly, to massive parallelism in query execution with a relatively slow exchange between nodes.<\/p>\n

Therefore, the article will be somewhat dual-purpose:<\/p>\n