PostgreSQL creates a server process for each client connection. This means that only one CPU core will be used to perform all the work. Of course, when multiple connections are active, the resources of the server machine will be used intensively. However, in the data warehouse solutions, the number of concurrent sessions is usually not very big. They tend to perform big complex queries. It makes sense to utilize multiple CPU cores to process the queries of a single client connection.
PostgreSQL supports a feature called parallel query that makes it possible to use multiple CPUs for one query. Certain operations like table scans, joins, or aggregation can be executed in several processes concurrently. The administrator can configure the number of workers that the PostgreSQL server will create for parallel query execution. When the query optimizer can detect a benefit from parallel execution, it will request some workers, and if they are available the query (or a part of it...
