Laurenz Albe: PostgreSQL parallel query problems in JDBC and DBeaver

© Laurenz Albe 2024 I recently investigated a surprising behavior of the DBeaver database client in connection with PostgreSQL parallel query, which I’d like to share with you. This might be interesting for everybody who accesses PostgreSQL using the JDBC driver. Some basics about PostgreSQL parallel query Parallel query concepts Parallel query was introduced in PostgreSQL 9.6 and has been improved in later versions. It breaks with the “traditional” PostgreSQL architecture of using a single backend process per database connection to process SQL statements. If the optimizer thinks that parallel processing would reduce the execution time, it will plan additional parallel worker processes. These processes are created by the query executor and live only for the duration of a single SQL statement. The parallel worker processes calculate intermediate results, which eventually are collected at the original backend process. This collection happens in the “Gather” node of the execution plan: PostgreSQL executes all steps below the Gather node in parallel, while everything above Gather is single-threaded. Parallel query limits While parallel query can speed up query execution, it incurs some overhead: starting parallel worker processes is an expensive operation exchanging data between worker processes and the backend requires inter-process communication via dynamic shared memory segments (DSM) The PostgreSQL optimizer takes this overhead into account by planning parallel query only expensive statements that process large tables or indexes. There are some additional limits to prevent parallel query from consuming too many resources: max_parallel_workers limits the number of parallel workers for the entire database cluster at any given time max_parallel_workers_per_gather limits the number of parallel workers that a single statement can use If the pool defined by max_parallel_workers is exhausted, the query executor cannot start all the parallel processe[...]