Скачать или смотреть How to Parallelize Expensive Operations on Small Tables in Postgres 13.1

Discover effective methods to enhance the performance of `CPU-intensive functions` in PostgreSQL by parallelizing operations on small tables.
---
This video is based on the question https://stackoverflow.com/q/65043736/ asked by the user 'THX1138' ( https://stackoverflow.com/u/878272/ ) and on the answer https://stackoverflow.com/a/65044001/ 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: Parallelize very expensive operation on a table that has a small number of rows in Postgres 13.1

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.
---
How to Parallelize Expensive Operations on Small Tables in Postgres 13.1

In the world of databases, optimizing performance is a constant challenge, especially when dealing with expensive operations. If you're using PostgreSQL 13.1, you might be wondering how to parallelize a very CPU-intensive function that needs to run on each row of a small table. This guide will help you explore the solution to this problem in a step-by-step manner.

Understanding the Problem

Imagine you have a table with a small number of rows, but you need to perform a very expensive operation on each row. In a typical scenario, when using parallel processing, you expect that multiple workers will execute these operations concurrently. However, you find that despite launching several worker processes, the function runs sequentially, leading to longer execution times than anticipated.

A Case Study

For instance, you set the parallel_workers parameter to the maximum number of worker processes and implement a function to simulate an expensive operation. The goal is to distribute the workload across available CPU cores effectively. But you notice that the EXPLAIN ANALYZE output reveals all the processing is occurring sequentially — one worker processes all the rows from the single block of data.

Key Insight

The crux of the problem is that all the rows reside in a single block of the table. The parallel sequential scan strategy assigns blocks to workers, meaning if all rows are in one block, only one worker scans and processes them.

Solution Overview

To rectify this and effectively utilize multiple workers (with each occupying a separate core), we can consider several strategies:

1. Adjust the Table's Fill Factor

One practical solution is to artificially bloat the table. This involves adjusting the fillfactor setting, which allows you to control the amount of space used by table pages (blocks). By setting a lower fill factor, you ensure that more blocks exist with fewer rows. This can enhance distribution among workers.

Steps to Adjust Fill Factor:

Set the fill factor: ALTER TABLE expensive_rows SET (fillfactor = 10);

Execute a full vacuum: VACUUM (FULL) expensive_rows;

2. Partitioning the Table

Another option is to partition your table. This method splits your data into smaller, manageable pieces, allowing PostgreSQL to distribute rows across different partitions. Each partition can then be scanned in parallel by separate workers.

Steps for Partitioning:

Create partitions of your table based on certain criteria, such as ranges or lists, that suits your data structure.

Direct your queries to each partition to ensure they’re processed concurrently.

3. Use Parallel Safe Functions

Ensure the functions you're executing are parallel safe. PostgreSQL has specific guidelines to regulate what can run in a parallel context:

Functions marked with PARALLEL SAFE can be executed in parallel mode.

If your function isn’t parallel safe, it will fall back to a sequential execution plan.

Conclusion

Parallelizing operations in PostgreSQL on small tables may seem challenging, but with the right adjustments and techniques, it's entirely achievable. By manipulating the fillfactor, considering partitioning strategies, and ensuring functions are marked as parallel safe, you can significantly enhance performance and cut down on execution times.

Implement these solutions in your PostgreSQL setup and watch your CPU-intensive operations run more efficiently. Happy querying!