PostgreSQL Parallel Query: Configuration, JIT, and Tuning for Analytics

PostgreSQL can execute queries using multiple CPU cores in parallel. For analytical queries over large tables, parallel query can reduce execution time by 4-8x. Here is how to enable and tune it.

Parallel Query Configuration

# postgresql.conf
max_parallel_workers_per_gather = 4   # workers per query node
max_parallel_workers = 8              # total parallel workers system-wide
max_worker_processes = 16             # total background workers
parallel_tuple_cost = 0.1
parallel_setup_cost = 1000
min_parallel_table_scan_size = 8MB
min_parallel_index_scan_size = 512kB

Verify Parallel Execution

EXPLAIN (ANALYZE, BUFFERS)
SELECT region, sum(amount)
FROM orders
GROUP BY region;

-- Look for:
-- Gather (cost=... rows=... width=...)
--   Workers Planned: 4
--   Workers Launched: 4
--   -> Partial HashAggregate
--        -> Parallel Seq Scan on orders

Force Parallel Query (Testing)

SET max_parallel_workers_per_gather = 4;
SET parallel_tuple_cost = 0;
SET parallel_setup_cost = 0;
SET min_parallel_table_scan_size = 0;

Disable Parallel for Specific Queries

-- Parallel query is not always faster (small tables, OLTP)
SET max_parallel_workers_per_gather = 0;

-- Or per table
ALTER TABLE small_lookup_table SET (parallel_workers = 0);

Parallel-Unsafe Functions

Custom functions must be marked PARALLEL SAFE to be used in parallel queries:

CREATE OR REPLACE FUNCTION calculate_discount(price NUMERIC)
RETURNS NUMERIC
LANGUAGE sql
IMMUTABLE PARALLEL SAFE
AS $$
  SELECT price * 0.9;
$$;

JIT Compilation

-- Enable JIT for long-running analytical queries
SET jit = on;
SET jit_above_cost = 100000;     -- only for expensive queries
SET jit_inline_above_cost = 500000;
SET jit_optimize_above_cost = 500000;

Measure Parallelism as a Shared Resource

A planned worker count is a request, not a reservation. A Gather or Gather Merge node can launch fewer workers than planned, or none, when the cluster has exhausted max_parallel_workers or max_worker_processes. The leader also participates by default, but when workers emit many tuples the leader can spend most of its time reading and processing worker output. A parallel plan that is faster in an isolated session can therefore increase tail latency under concurrency. Tune against a workload-level CPU and worker budget, not a fixed formula based only on core count.

Read the Plan in Context

Capture EXPLAIN (ANALYZE, BUFFERS, SETTINGS) for representative parameter values and data volumes. Compare Workers Planned with Workers Launched, per-worker row counts, leader work above Gather, buffer reads, temporary I/O, planning time, and total execution time. Gather Merge preserves sorted order and adds merge work; plain Gather does not. Repeat tests with warm and cold-cache conditions where practical, then run at production-like concurrency. One fast execution does not justify a cluster-wide cost change.

BEGIN;
SET LOCAL max_parallel_workers_per_gather = 2;
EXPLAIN (ANALYZE, BUFFERS, SETTINGS) SELECT ...;
ROLLBACK;

Check Eligibility Before Lowering Costs

PostgreSQL will not produce or execute parallel plans in several situations, including when a query contains parallel-unsafe operations or when no background worker is available. User-defined functions are parallel unsafe by default. Label a function PARALLEL SAFE only after reviewing everything it calls: functions that write, alter transaction state, access sequences, or make persistent setting changes are unsafe, while access to temporary tables, cursors, client state, and similar backend-local state is restricted. Incorrectly labeling a function safe can produce errors or wrong answers. Fix safety and cardinality issues before driving parallel_setup_cost or scan thresholds downward.

Tune JIT Separately

Parallel query and JIT solve different problems. JIT can help a long-running, CPU-bound expression workload, but compilation overhead often makes short queries slower. PostgreSQL compares the plan's estimated cost with jit_above_cost, jit_inline_above_cost, and jit_optimize_above_cost; these are cost units, not milliseconds. The decision is made at plan time. For a prepared statement using a generic plan, the settings in effect when that plan was prepared govern the decision. Inspect the JIT timing section in EXPLAIN ANALYZE and test SET LOCAL jit = off against the same query before changing thresholds.

Capacity, Monitoring, and Rollback

Track CPU saturation, runnable processes, parallel worker availability, query concurrency, temporary bytes, and latency percentiles by query identifier. If workers are frequently planned but not launched, either reduce per-query demand, reserve analytical work for a separate pool, or increase cluster limits only after checking memory and background-worker consumers. Treat changes to max_worker_processes and related restart-required settings as capacity changes with a restart plan. Roll out one role or database at a time with ALTER ROLE ... SET or session settings where suitable. Keep the previous values recorded; rollback is restoring them and invalidating or reconnecting sessions whose prepared plans retain earlier planning decisions.

Validation Checklist

Validate result equivalence, not only duration. Test zero-row and skewed partitions, functions newly labeled for parallel use, cancellation, replica execution if applicable, and concurrency at the expected worker ceiling. Confirm that OLTP latency does not regress while analytics improve. The target is predictable total workload throughput, not the largest possible number beside Workers Launched.

Official PostgreSQL References

• How parallel query works
• When parallel query can be used
• Parallel safety rules
• PostgreSQL JIT cost decisions