How to Ensure a Query Runs in Parallel in PostgreSQL?
1
vote
3
answers
1209
views
I have a PostgreSQL 16 database setup with the following configuration:
-- DB Version: 16
-- OS Type: linux
-- DB Type: oltp
-- Total Memory (RAM): 64 GB
-- CPUs num: 32
-- Connections num: 200
-- Data Storage: ssd
show effective_cache_size; --48 GB
show effective_io_concurrency; --200
show huge_pages; --try
show maintenance_work_mem; --4GB
show max_connections; --200
show max_parallel_maintenance_workers; --4
show max_parallel_workers; --32
show max_parallel_workers_per_gather; --4
show max_wal_size; --8GB
show max_worker_processes; --32
show min_wal_size; --2GB
show parallel_setup_cost; --0.1
show random_page_cost; --1.1
show shared_buffers; --16GB
show wal_buffers; --16MB
show work_mem; --256MB
I am running a CREATE TABLE
query with multiple joins. Sometimes the query runs in parallel, but other times it does not. I want to ensure that the query runs in parallel or at least increase the chances of it running in parallel for that specific transaction session. It is also fine if parallel workers assigned are fewer than usual because without parallel workers the query is very slow.
The configuration below worked best for me for some time, but it caused an error similar to this [issue](https://stackoverflow.com/questions/64094389/postgresql-11-5-could-not-resize-shared-memory-segment-postgresql-xxx-to-yy) .
ALTER SYSTEM SET maintenance_work_mem = '8GB';
ALTER SYSTEM SET work_mem = '2GB';
ALTER SYSTEM SET effective_io_concurrency = '400';
ALTER SYSTEM SET max_parallel_workers_per_gather = '16';
ALTER SYSTEM SET wal_buffers = '1GB';
I am attaching a select query plan for reference: [query plan](https://pastebin.com/pE9k1Sfc) .
I want to maximize CPU utilization (currently less than 10%) and RAM utilization (currently less than 30%).
I am aware that force_parallel_mode
has been deprecated. How can I achieve consistent parallel execution for my queries in PostgreSQL?
Asked by Purushottam Nawale
(161 rep)
Jan 17, 2025, 09:04 PM
Last activity: Jan 21, 2025, 06:07 PM
Last activity: Jan 21, 2025, 06:07 PM