Скачать или смотреть Understanding max_parallel_workers vs. max_parallel_workers_per_gather in PostgreSQL

Explore the differences between `max_parallel_workers` and `max_parallel_workers_per_gather` settings in PostgreSQL to optimize parallel query performance effectively.
---
This video is based on the question https://stackoverflow.com/q/63498603/ asked by the user 'Boom' ( https://stackoverflow.com/u/8523137/ ) and on the answer https://stackoverflow.com/a/63498839/ provided by the user 'Laurenz Albe' ( https://stackoverflow.com/u/6464308/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: max_parallel_workers vs. max_parallel_workers_per_gather

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Understanding max_parallel_workers vs. max_parallel_workers_per_gather in PostgreSQL

In the world of databases, controlling how tasks are managed while running complex queries can significantly impact performance. With PostgreSQL, two important settings come into play when discussing parallel processing: max_parallel_workers and max_parallel_workers_per_gather. If you've recently dived into optimizing query performance and stumbled upon these terms, you may find yourself asking – what do they mean and how do they function?

The Problem: Understanding Parallel Query Execution

When executing queries, especially on large datasets (like the example of a table containing around 16 million rows), you expect to make use of parallel processing capabilities effectively. However, you may find results not aligning with expectations, especially when the number of parallel workers used doesn't represent the total capacity.

Example Query:

You may run a query like this:

[[See Video to Reveal this Text or Code Snippet]]

Upon reviewing the execution plan output, you may see:

Workers planned: 2

Workers launched: 2

The questions arise:

Why was only 2 workers used, and not the maximum of 8?

What’s the distinction between max_parallel_workers and max_parallel_workers_per_gather?

The Solution: Clarifying the Parameters

1. max_parallel_workers

This parameter sets the upper limit on the number of parallel worker processes that can be active across the entire PostgreSQL database cluster at any one time. Here are some key points about this setting:

It must not exceed the value of max_worker_processes.

It's essentially a global control, limiting how many processes can be running in parallel for the entire system.

2. max_parallel_workers_per_gather

In contrast to max_parallel_workers, this parameter is much more focused:

It restricts the number of worker processes that can be utilized for a single query operation (in this case, each execution of your SQL command).

This setting helps manage resources efficiently by ensuring that no single query monopolizes all available parallel processing capabilities.

Key Differences

Scope: max_parallel_workers is a cluster-wide limit, while max_parallel_workers_per_gather is specific to individual queries.

Purpose: The aim of max_parallel_workers is to manage the overall load on the database, while max_parallel_workers_per_gather ensures fair distribution of resources for queries.

Example In Action

Given your query execution, the output indicates that it adhered to the max_parallel_workers_per_gather limit of 2, meaning:

Even though the cluster allows for 8 workers, only 2 were needed/allocated for the query execution at that moment, as governed by the aforementioned parameter.

This balance is crucial, especially during peak load times or when several simultaneous queries are being processed.

Conclusion

Understanding the nuances of max_parallel_workers and max_parallel_workers_per_gather can help optimize your PostgreSQL performance. By managing these settings, you can ensure efficient resource utilization, avoid bottlenecks in query processing, and maintain a responsive system while dealing with large datasets.

Next time you analyze your query performance, remember how these parameters control your database's parallel worker processes effectively!